我在创建和填充表后立即执行SQL语句时遇到问题。
我认为记录不存在，因为创建查询没有及时完成。
如果我为clean标志（在createDatabaseFromSQL中）传递false，经过几次尝试后，
我得到了预期的结果：

$ 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.

如果强制清除，选择将失败，因为accounts表不存在。

$ 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

文件
以下是相关文件。

设置. 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);

查询实用程序. 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,
};

设置. 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');

软件包. json

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