How to use async/await in sqlite3 db.get and db.all?

Question

Here is my server mcve:

const express = require("express");
const app = express();
const fs = require("fs");

const dbFile = "./sqlite.db";
const exists = fs.existsSync(dbFile);
const sqlite3 = require("sqlite3").verbose();
const db = new sqlite3.Database(dbFile);

app.get("/", async (req, resp) => {
  await db.run(`INSERT INTO Times VALUES (${ Date.now() })`);
  let rows = await db.all("SELECT time FROM Times");
  console.log(rows); // The line where I console log rows
  resp.send(rows);
});

app.listen(process.env.PORT || 8080);
process.on("uncaughtException", console.log);

The above server is logging a database object like so,

Database {}

in console every time I refresh the site, but I expect it to log the row which I've inserted in the database.

What I'm doing wrong here?

Answer 1

There are a couple of SQLite packages on NPM.

sqlite3

This is the package you're using. It's callback based and should be used like so:

db.all("SELECT time FROM Times", function(err, rows) { });

Note: The .all() function returns the database instance, not the results, so that you could do this: db.all(query1, (err, rows) => {}).all(query2, (err, rows) => {});. Query2 would not wait for query1 to finish.

sqlite

This is a wrapper around the sqlite3 package and, in fact, needs it to be installed in order to function. It's promise based:

const rows = await db.all("SELECT time FROM Times");

better-sqlite

This is a different package altogether. Rather than run queries asynchronously like the two examples above, it runs every query in the main thread. Its author is of the opinion that this is better (for typical SQLite workloads).

const rows = db.prepare("SELECT time FROM Times").all();

Answer 2

If sqlite3 does not support async/await then you need to create an async function with a promise like this e.x.

async function db_all(query){
    return new Promise(function(resolve,reject){
        db.all(query, function(err,rows){
           if(err){return reject(err);}
           resolve(rows);
         });
    });
}

and then use it like this

await db_all("SELECT time FROM Times");

use the same way for every function in sqlite3 that you need to use.

Best way is to create a module and override all these methods that you need