Issue with async SQLite statement execution wrapper functions

Question

I am having an issue executing an SQL statement immediately after creating and populating a table.

I think the record does not exist, because the creation query does not finish in time.

If I pass false for my clean flag (in createDatabaseFromSQL), after a few attempts,

I get the expected:

$ node ./setup.js 
CREATE database
Executing: DROP TABLE IF EXISTS `accounts`
Executing: CREATE TABLE IF NOT EXISTS `accounts` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `username` VARCHAR(50) NOT NULL, `password` VARCHAR(255) NOT NULL, `email` VARCHAR(100) NOT NULL )
Executing: INSERT INTO `accounts` (`username`, `password`, `email`) VALUES ('admin', 'admin', 'admin@admin.com'), ('test', 'test', 'test@test.com')
FETCH test account
Connected to the 'auth' database.
Connected to the 'auth' database.
true
Close the 'auth' database connection.
Close the 'auth' database connection.

If I force-clean, the selection fails, because the accounts table does not exist.

$ node ./setup.js 
CREATE database
Executing: DROP TABLE IF EXISTS `accounts`
Executing: CREATE TABLE IF NOT EXISTS `accounts` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `username` VARCHAR(50) NOT NULL, `password` VARCHAR(255) NOT NULL, `email` VARCHAR(100) NOT NULL )
Executing: INSERT INTO `accounts` (`username`, `password`, `email`) VALUES ('admin', 'admin', 'admin@admin.com'), ('test', 'test', 'test@test.com')
FETCH test account
Connected to the 'auth' database.
Connected to the 'auth' database.
node:internal/process/esm_loader:97
    internalBinding('errors').triggerUncaughtException(
                              ^

[Error: SQLITE_ERROR: no such table: accounts] {
  errno: 1,
  code: 'SQLITE_ERROR'
}

Node.js v18.14.0

Files

Here are the associated files.

setup.js

import { createDatabaseFromSql, executeQuery } from "./query-utils.js";

console.log("CREATE database");
await createDatabaseFromSql("auth", "./setup.sql", true);

console.log("FETCH test account");
const row = await executeQuery(
  "auth",
  "SELECT * FROM `accounts` where `username` = ?",
  ["test"]
);
console.log(row?.id === 2);

query-utils.js

import fs from "fs";
import sqlite3 from "sqlite3";

const SQL_DEBUG = true;

const loadSql = (filename, delimiter = ";") =>
  fs
    .readFileSync(filename)
    .toString()
    .replace(/(\r\n|\n|\r)/gm, " ")
    .replace(/\s+/g, " ")
    .split(delimiter)
    .map((statement) => statement.trim())
    .filter((statement) => statement.length);

const executeSerializedQueries = async (databaseName, callback) => {
  let db;
  try {
    db = new sqlite3.Database(`./${databaseName}.db`, (err) => {
      if (err) console.error(err.message);
      console.log(`Connected to the '${databaseName}' database.`);
    });
    db.serialize(() => {
      callback(db);
    });
  } catch (e) {
    throw Error(e);
  } finally {
    if (db) {
      db.close((err) => {
        if (err) console.error(err.message);
        console.log(`Close the '${databaseName}' database connection.`);
      });
    }
  }
};

const createDatabaseFromSql = async (databaseName, sqlFilename, clean) =>
  new Promise((resolve, reject) => {
    if (clean) {
      fs.rmSync(`./${databaseName}.db`, { force: true }); // Remove existing
    }
    try {
      executeSerializedQueries(databaseName, (db) => {
        loadSql(sqlFilename).forEach((statement) => {
          if (SQL_DEBUG) {
            console.log("Executing:", statement);
          }
          db.run(statement);
        });
        resolve();
      });
    } catch (e) {
      reject(e);
    }
  });

const executeQuery = async (databaseName, query, params = []) =>
  new Promise((resolve, reject) => {
    try {
      executeSerializedQueries(databaseName, (db) => {
        db.get(query, params, (error, row) => {
          if (error) reject(error);
          else resolve(row);
        });
      });
    } catch (e) {
      reject(e);
    }
  });

const executeQueryAll = async (databaseName, query, params = []) =>
  new Promise((resolve, reject) => {
    try {
      executeSerializedQueries(databaseName, (db) => {
        db.all(query, params, (error, rows) => {
          if (error) reject(error);
          else resolve(rows);
        });
      });
    } catch (e) {
      reject(e);
    }
  });

export {
  createDatabaseFromSql,
  executeSerializedQueries,
  executeQuery,
  executeQueryAll,
  loadSql,
};

setup.sql

DROP TABLE IF EXISTS `accounts`;

CREATE TABLE IF NOT EXISTS `accounts` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `email` VARCHAR(100) NOT NULL
);

INSERT INTO `accounts` (`username`, `password`, `email`)
  VALUES ('admin', 'admin', 'admin@admin.com'),
    ('test', 'test', 'test@test.com');

package.json

{
  "dependencies": {
    "sqlite3": "^5.1.4"
  }
}

Answer 1

I ended up going with better-sqlite3. This library does not rely on async or Promise logic.

setup.js

import { createDatabaseFromSql, query } from "./src/app/js/query-utils.js";

console.log("CREATE database");
createDatabaseFromSql("auth", "./data/setup.sql", true);

console.log("FETCH test account");
const result = query("auth", "SELECT * FROM `accounts` where `username` = ?", [
  "test",
]);
console.log(result.id === 2);

query-utils.js

import fs from "fs";
import Database from "better-sqlite3";

const openDatabase = (databaseName) =>
  new Database(`${databaseName}.db`, { verbose: console.log });

const dropDatabase = (databaseName) => {
  fs.rmSync(`./${databaseName}.db`, { force: true });
};

const createDatabaseFromSql = (databaseName, sqlFilename, clean) => {
  if (clean) dropDatabase(databaseName);
  const db = openDatabase(databaseName);
  db.exec(fs.readFileSync(sqlFilename, "utf8"));
  db.close();
};

const query = (databaseName, query, params = []) => {
  let db = null;
  try {
    db = openDatabase(databaseName);
    return db.prepare(query).get(...params);
  } finally {
    if (db) {
      db.close();
    }
  }
};

const queryAll = (databaseName, query, params = []) => {
  let db = null;
  try {
    db = openDatabase(databaseName);
    return db.prepare(query).all(...params);
  } finally {
    if (db) {
      db.close();
    }
  }
};

export { createDatabaseFromSql, openDatabase, query, queryAll };

Additional function suggested by @wood.wheels

const queryRun = (databaseName, query, params = []) => {
  let db = null;
  try {
    db = openDB(databaseName);
    return db.prepare(query).run(...params);
  } finally {
    if (db) {
      db.close();
    }
  }
};

Answer 2

db.serialize is hard to use in promise/async code as it doesn't follow the standard callback convention of "run this callback after completion". It also only serialises the queries for sqlite, it doesn't affect the the control flow in nodejs, which will continue on with more code immediately.

Essentially the final query in db.serialize is usually when you want a promise to resolve, something like:

function dbSerialize(db) {
  return new Promise((resolve, reject) => {
    const rejector = (err) => err && reject(err)
    db.serialize(() => {
      db.run(a, rejector)
      db.run(b, rejector)
      db.run(c, rejector)
      db.run(d, (err) => {
        if (err) return reject(err)
        resolve(true)
      })
    })
  })
}

This is also hard to generalise when passing around functions containing the database commands constructed elsewhere, as the in the example code.

Luckily promises provide their own control flow, so we can leave serialize for the moment.

Converting to promises

Here is a promisified "open database" function

import sqlite3 from 'sqlite3'

function openDatabase(databaseName) {
  return new Promise((resolve, reject) => {
    const db = new sqlite3.Database(`./${databaseName}.db`, (err) => {
      if (err) return reject(err)
      console.log(`Connected to the '${databaseName}' database.`);
      resolve(db)
    })
  })
}

The err from the callback is rejected, a value is resolved in the callback, the promise is returned from the function. The function has a single purpose, to turn a callback operation into a promise.

You can wrap larger callback chains in a promise, but the reason promises exist is to remove the mental load and edge cases of managing large async callback chains. The same basic rules apply, the err from all callbacks should reject or be handled. Then the last function in callback chain should resolve a value, if you want to wait for everything to complete.

This isn't a generalised solution like the OP, but a simple promisified version of setup could then be.

import { readFile, rm } from 'node:fs/promises'
import { promisify } from 'node:util'

// Now open can be awaited.
const db = await openDatabase('myfile')

// Use node built in to promisify sqlite3's regular callback functions
dbRun = promisify(db.run)
dbGet = promisify(db.get)
dbAll = promisify(db.all)
dbClose = promisify(db.close)

The database API can now be accessed via promisified functions, so all your functions don't need to repeatedly wrap callbacks in promises.

The serial queries can be awaited in a for loop (sequentially like db.serialize), but nodejs control flow won't move on until the queries are completed. All callback errors are handled by the promisified functions for you.

try {
  const sql_statements = await loadFile(sqlFilename)
  for (const statement of sql_statements) {
    await dbRun(statement)
  }
  const rows = await dbGet("SELECT * FROM `accounts` where `username` = ?", ["test"])
  console.log(rows)
}
finally {
  if (db) {
    await dbClose()
    console.log(`Close the '${databaseName}' database connection.`);
  }
}