Using SQLite from application code

Prepared statements, transactions in bulk, and the concurrency rules that keep an embedded database from throwing SQLITE_BUSY.

Drivers and prepared statements

Always bind parameters instead of building SQL strings. It removes injection risk and, more importantly here, lets the statement be prepared once and reused — preparing costs parsing and planning, which dominates the cost of a small query.

const { DatabaseSync } = require("node:sqlite");

const db = new DatabaseSync("app.db");
db.exec("PRAGMA journal_mode = WAL");
db.exec("PRAGMA foreign_keys = ON");
db.exec("PRAGMA busy_timeout = 5000");

const insert = db.prepare(
  "INSERT INTO measurements (sensor, value) VALUES (?, ?)"
);
const find = db.prepare(
  "SELECT id, value FROM measurements WHERE sensor = ? ORDER BY id DESC LIMIT ?"
);

const load = db.transaction((rows) => {          // one transaction, one fsync
  for (const [sensor, value] of rows) insert.run(sensor, value);
});
load([["s1", 21.4], ["s2", 19.8], ["s1", 21.9]]);

console.log(find.all("s1", 10));

• One transaction around a bulk insert is often a hundred times faster than autocommit per row, because each commit normally forces a durable write.
• Prepare statements once at startup and reuse them; the driver caches nothing for you if you keep passing new SQL text.
• run() returns the last inserted rowid and the number of changed rows — useful for optimistic checks.
• Keep large binary values out of the database; store a path or a hash and leave the file on disk.

Concurrency rules

• Any number of processes may read at the same time, but only one may write. A write lasting a millisecond is fine; a write lasting a minute blocks everyone.
• In rollback-journal mode a writer blocks readers too. WAL mode lets readers continue while a write is in progress.
• Use one connection per thread. Sharing a connection across threads or processes breaks transaction scoping.
• Wrap multi-statement writes in BEGIN IMMEDIATE so the write lock is taken up front rather than failing halfway through on upgrade.
• Batch writes into transactions and keep them short; do not hold a transaction open across a network call or user input.

import sqlite3

con = sqlite3.connect("app.db", timeout=5.0)      # busy timeout in seconds
con.execute("PRAGMA journal_mode = WAL")
con.execute("PRAGMA foreign_keys = ON")
con.row_factory = sqlite3.Row

with con:                                          # commits or rolls back
    con.execute("BEGIN IMMEDIATE")
    con.executemany(
        "INSERT INTO measurements (sensor, value) VALUES (?, ?)",
        [("s1", 21.4), ("s2", 19.8)],
    )

row = con.execute(
    "SELECT count(*) AS n, max(value) AS peak FROM measurements WHERE sensor = ?",
    ("s1",),
).fetchone()
print(dict(row))

Migrations and long-lived connections

const MIGRATIONS = [
  "CREATE TABLE IF NOT EXISTS measurements (id INTEGER PRIMARY KEY, sensor TEXT, value REAL)",
  "ALTER TABLE measurements ADD COLUMN taken TEXT",
];

let version = db.prepare("PRAGMA user_version").get().user_version;
while (version < MIGRATIONS.length) {
  db.exec("BEGIN IMMEDIATE");
  db.exec(MIGRATIONS[version]);
  db.exec("PRAGMA user_version = " + (version + 1));
  db.exec("COMMIT");
  version += 1;
}
db.exec("PRAGMA optimize");

• PRAGMA user_version is a free integer stored in the file header — enough to build a dependency-free migration runner.
• SQLite has limited ALTER TABLE: adding a column is cheap, dropping or retyping one means creating a new table and copying rows.
• Set PRAGMA optimize before a connection that has been open for a long time closes, so statistics are refreshed for the next startup.
• Do not share a database file over a network filesystem with concurrent writers: locking depends on primitives that are unreliable there.

FAQ

Related

The database in a file, and the sqlite3 CLI WAL mode, backups, and when not to use SQLite

Last refreshed 2026-09-18.