Async await sqlite in javascript

Question

I'm looking at this tutorial, which has a library called aa-sqlite in order to replace Promises() syntax with async-await.

I'm not seeing aa-sqlite on npm. Is there another, updated syntax for async await sqlite?

Here is what I'm trying with the standard sqlite library:

const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database("tmp.db")

async function myfunc(db) {
  let sql = "SELECT id id FROM TABLE LIMIT 2"
  let res1 = await db.run(sql)
  console.log(res1)
  for (row of res1) {
    console.log(row);   
  }

But this yields

TypeError: res1 is not iterable

I am not expecting res1 to be an object, but instead an iterator of results. How can I async/await the results of a db.run query in ES7/ES8?

Answer 1

I sort of tried sqlite npm package, which implements async/await over splite3, but it is not that easy to use.

A simple way is to create a little module and promessify the main sqlite3 functions.

Here is my simple module I created for a Discord chatbot database:

const sqlite3 = require('sqlite3');
const util    = require('util');

let db = new sqlite3.Database('./db/chatbot.sqlite3', sqlite3.OPEN_READWRITE, (err) => {
    if (err) {
        console.error(err.message);
    }
    console.log('Connected to the chatbot database.');
});

db.run = util.promisify(db.run);
db.get = util.promisify(db.get);
db.all = util.promisify(db.all);

// empty all data from db
db.clean_db = async function() {
  await db.run("delete from users");
  await db.run("delete from members");
  await db.run("delete from guilds");
  db.run("vacuum");
}

// any kind of other function ...

// and then export your module

module.exports = db;

How to use - now you can use the module like this in your code:

const db = require('./db');

// get one user
const myUser = await db.get("select * from users where id = ?", [id]);
if (! myUser)
    return console.log("User with id", id, "not found");

// get all users
const users = await db.all("select * from users");
users.map((user) => { console.log(user.id, "/", user.name); });

// ... etc ...

Answer 2

For me the simplest solution would be to encapsulate the operation in a Promise like so:

const res = await new Promise((resolve, reject) => {
    db.each('SELECT id FROM table', [], (err, row) => {
        if (err)
            reject(err)

        resolve(row)
    })
})

console.log(res)

With this you'll have the row result in res outside the callback and synchronously.