SQLite on Node.js with async/await

Interface to turn each SQLite function synchronous and use them with await.

Requires the SQLite for Node module and Node.js 8.0 with support to async/await.

SQLite is most commonly used as a storage unit for local or mobile apps, so the asynchrous style to write and read in the database is useless, and callbacks are not the best solution when one need to access the data from various parts of a program.

To access the data more naturally in a procedural program, I have writen an interface which convert callback to promises, so we can use each function with the await keyword.

The is not an alternative to the asynchronous functions, it is a complement, you can use the original functions and the synchronous function all together.

The aa-sqlite module

The interface to SQLite is a module named aa-sqlite, you have to store it in the node_modules section of your application. Here is the full 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 function return a row, while the all function returns an array of rows.

In the case of each, it is more complicated, because SQLite call a function for each row which matches the condition of the query. The solution used is to use Database.serialize to process them one after the other, and then call an ultimate dummy get method to resolve the promise.

Demonstration

This demo shows an example of each function of aa-sqlite. In the first part, we open a database, add a table and fill this table with some rows. Then the database is closed, we open it again and do some synchronous queries.

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()

Since the all method returns an array of row, we use forEach to process the content of each row.

You can verify especially in the case of the each method, that each row returned is processed before the program display "Done". That would not have been the case with the original asynchronous methods.

You may download an archive with the module and the demo.

You have to install SQLite 3 to run the demo.