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?

Rollerskate

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

Dao

Does this happens with .run and other methods too in sqlite3? – Rollerskate

It's been a while since I used sqlite3 itself, but I think .run will execute synchronously & you only need the callback to catch errors. As you can read in the link, it too returns the database instance. (I prefer to use the sqlite package that wraps everything in Promises, myself.) – Dao

Okay thanks, I would too prefer sqlite now. But, I can't find any ref. for that. If you can share any one of them, it would be helpful! – Rollerskate

I've added links to the packages on NPM. From there, you can click through to the respective Github pages. Their Readme files contain documentation and links to where you can find more reference info / help. – Dao

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

Bordeaux

how do you handle error here if the db.all promise got rejected? – Tiphani

you can wrap it in a try/catch block. e.x. try{ const results = await db_all("query"); }catch(e){ console.log(e);} – Bordeaux

Answer 3

Here's how I did it using both the sqlite3 and sqlite packages:

const sqlite3 = require('sqlite3');
const { open } = require('sqlite');

async function openDb (dbName) {
  return open({
    filename: dbName,
    driver: sqlite3.Database
  })
}

(async function () {
    try {
        const db = await openDb('./db_folder/db_name.db');
        const query = 'SELECT * from table_name'
        const result = await db.all(query)
        console.log(JSON.stringify(result))
        db.close()
    } catch (error) {
        console.error(error);
    }
})()

Phosphoresce

Answer 4

none of above is option for me :(

'better-sqlite' is python-ballasted package that just grows container 'sqlite' looks like M$ typescript mastering training

I use this nice&simple solution https://www.scriptol.com/sql/sqlite-async-await.php

or when transaction support needed and no problem with extra tiny package https://www.npmjs.com/package/sqlite-async

Conley

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – Ploch