SQLite на Node.js с async/await

Interface for synchronizing each SQLite function and using them with await.

Requires SQLite 3 module for Node and Node.js 8.0 that supports async/await.

SQLite is more often used as a data storage tool for local and mobile applications, as well as an asynchronous style for reading and writing to the database, and callbacks are not the best solution for accessing data in different parts of the program .

To get more natural access to the data in the procedural program, I wrote an interface that converts callbacks into promised ones so that each function can be used with the await word reserved.

This is not an alternative to asnichon mode, but rather an add-on: you can use asynchronous and synchronous functions together .

Module aa-sql lite

The interface in SQLite is a module called aa-sqlLite, it can be placed in the node_modules section of the application. Here is the source code:

const sqlite3 = require('sqlite3').verbose()
var db

exports.db = db

exports.open=function(path) {
    return new Promise(function(resolve) {
    this.db = new sqlite3.Database(path, 
        function(err) {
            if(err) reject("Open error: "+ err.message)
            else    resolve(path + " opened")
        }
    )   
    })
}

// any query: insert/delete/update
exports.run=function(query) {
    return new Promise(function(resolve, reject) {
        this.db.run(query, 
            function(err)  {
                if(err) reject(err.message)
                else    resolve(true)
        })
    })    
}

// first row read
exports.get=function(query, params) {
    return new Promise(function(resolve, reject) {
        this.db.get(query, params, function(err, row)  {
            if(err) reject("Read error: " + err.message)
            else {
                resolve(row)
            }
        })
    }) 
}

// set of rows read
exports.all=function(query, params) {
    return new Promise(function(resolve, reject) {
        if(params == undefined) params=[]

        this.db.all(query, params, function(err, rows)  {
            if(err) reject("Read error: " + err.message)
            else {
                resolve(rows)
            }
        })
    }) 
}

// each row returned one by one 
exports.each=function(query, params, action) {
    return new Promise(function(resolve, reject) {
        var db = this.db
        db.serialize(function() {
            db.each(query, params, function(err, row)  {
                if(err) reject("Read error: " + err.message)
                else {
                    if(row) {
                        action(row)
                    }    
                }
            })
            db.get("", function(err, row)  {
                resolve(true)
            })            
        })
    }) 
}

exports.close=function() {
    return new Promise(function(resolve, reject) {
        this.db.close()
        resolve(true)
    }) 
}

The get method returns a string from the base, and all returns an array of strings .

In the case of each, this is more complicated than SQLite calling the callback function for each row that meets the query condition. The solution is to use Database.serialize to process them one by one, and then call the final empty get method to solve the promised.

Display

This demonstration shows how to use each aa-sql function. In the first part, open the database, add a table and fill it with a couple of rows. Then the database is closed, it is opened again to execute several synchronous requests.

const fs = require("fs")
const sqlite = require("aa-sqlite")

async function mainApp() {
    
    console.log(await sqlite.open('./users.db'))
    
    // Adds a table
    
    var r = await sqlite.run('CREATE TABLE users(ID integer NOT NULL PRIMARY KEY, name text, city text)')
    if(r) console.log("Table created")

    // Fills the table
    
    let users = {
        "Naomi": "chicago",
        "Julia": "Frisco",
        "Amy": "New York",
        "Scarlett": "Austin",
        "Amy": "Seattle"
    }
    
    var id = 1 
    for(var x in users) {
        var entry = `'${id}','${x}','${users[x]}'`
        var sql = "INSERT INTO users(ID, name, city) VALUES (" + entry + ")"
        r = await sqlite.run(sql)
        if(r) console.log("Inserted.")
        id++        
    }

    // Starting a new cycle to access the data

    await sqlite.close();
    await sqlite.open('./users.db') 

    console.log("Select one user:")
    
    var sql = "SELECT ID, name, city FROM users WHERE name='Naomi'"
    r = await sqlite.get(sql)
    console.log("Read:", r.ID, r.name, r.city)
    
    console.log("Get all users:")
    
    sql = "SELECT * FROM users"
    r = await sqlite.all(sql, [])
    r.forEach(function(row) {
        console.log("Read:", row.ID, row.name, row.city)    
    })
    
    console.log("Get some users:")
    
    sql = "SELECT * FROM users WHERE name=?"
    r = await sqlite.all(sql, ['Amy'])
    r.forEach(function(row) {
        console.log("Read:", row.ID, row.name, row.city)    
    })

    console.log("One by one:")
    
    sql = "SELECT * FROM users"
    r = await sqlite.each(sql, [], function(row) {
        console.log("Read:", row.ID, row.name, row.city)    
    })

    if(r) console.log("Done.")

    sqlite.close();
}

try {
    fs.unlinkSync("./users.db")
}
catch(e) {
}

mainApp()

Because the all method returns an array, forEach is used to process the contents of each string.

You can specifically check in the case of the each method that each returned string is processed before the program shows "Done." This would not be the case with the original asynchronous methods.

You can download an archive containing the aa-sql litet module and demo.

You still need to install SQLite 3 before running the demo.