CODEWITHSUNDEEP
javascriptnode.jssqliteexpressasync-await
vrintle
vrintle

Reputation: 5586

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

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?

Upvotes: 15

Views: 20741

Answers (4)

Brian Gonzalez
Brian Gonzalez

Reputation: 1354

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

Upvotes: 0

asida
asida

Reputation: 1

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

Upvotes: -4

RickN
RickN

Reputation: 13500

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

Upvotes: 23

gijoe
gijoe

Reputation: 1189

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

Upvotes: 18

Related Questions