Using node:sqlite in your app

We have been using node:sqlite in bits and pieces throughout the course. The migration runner opened a connection with it. The pragmas lesson ran its three essential settings through it. The transactions lesson wrapped it in a tx helper. All of that has been hanging out in separate code snippets. Now let’s pull it together into a single, shared database module that the rest of your app can import.

This is the lesson where the database stops being a collection of SQL snippets and becomes a proper module in your codebase. One connection, shared across your whole process. Pragmas set in one place. The tx helper co-located with the connection that uses it. Prepared statements cached where they are written. Migrations run before the server boots. Shutdown handled cleanly. It is all small stuff individually, but it adds up to the difference between a demo and a real app.

The problem a real app runs into

Until now we have opened the database inline in whatever script we happened to be writing. That is fine for learning. In a real app it falls apart within the first day.

Picture a server that handles HTTP requests. A handler for /books needs to query the books table. A handler for /reviews needs to insert a review. A handler for /auth needs to look up a session. If each one opens its own connection, you end up with a pile of connections that have to be configured identically and closed correctly. If any one of them forgets PRAGMA foreign_keys = ON, the constraints you so carefully added stop being enforced for that handler. If any one forgets PRAGMA journal_mode = WAL, that connection’s writes will block everyone else.

The fix is simple. Open the database exactly once, configure it once, and share the handle.

The db module

Here is the pattern, in a single file you can drop into any project:

// db.ts
import { DatabaseSync } from "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");

export function tx<T>(fn: () => T): T {
  db.exec("BEGIN");
  try {
    const result = fn();
    db.exec("COMMIT");
    return result;
  } catch (err) {
    db.exec("ROLLBACK");
    throw err;
  }
}

export default db;

Let’s walk through this carefully, because every line matters.

import { DatabaseSync } from "node:sqlite" pulls the SQLite class out of the built-in module. The node: prefix is how Node marks built-in modules as opposed to npm packages. You do not need to install anything. If your Node version is 22 or newer, this import just works. Deno 2 supports the same module, so the same file runs on both runtimes without changes.

new DatabaseSync("app.db") opens the file. Same behavior as every other time we have opened a database: SQLite creates the file if it does not exist, and connects to it if it does.

The three db.exec lines are the essential pragmas from the PRAGMAs lesson. Every connection needs them. Because we open the database exactly once in this module, we also set them exactly once, and every other part of the app that imports db inherits the configuration. No way to forget, no way to configure inconsistently.

The tx function is the helper from the transactions lesson. I like keeping it in the same file as the connection because tx needs the connection to work, and any caller who needs one almost always needs the other. Exporting it here means import db, { tx } from "./db" and you have everything.

export default db makes the connection the default export. The rest of your app reads like this:

// users.ts
import db from "./db";

export function getUser(id: number) {
  return db.prepare("SELECT * FROM users WHERE id = ?").get(id);
}

One import, and the rest just works.

[!NOTE] DatabaseSync is synchronous on purpose. SQLite queries finish in microseconds because there is no network round-trip and no separate process. A synchronous API is the natural fit, even inside a web server, and most code runs faster with it because there is no promise overhead. node:sqlite does also export async primitives if you genuinely need them, but DatabaseSync is the default.

Caching prepared statements

Prepared statements are fast because SQLite compiles them once and reuses the plan. If you call db.prepare(sql) inside a request handler, the SQL is reparsed on every request. For a hot path, that small amount of parsing is real overhead.

The fix is to prepare the statement at module scope, not inside the function:

// users.ts
import db from "./db";

const getUserStmt = db.prepare("SELECT * FROM users WHERE id = ?");
const createUserStmt = db.prepare("INSERT INTO users (email, name) VALUES (?, ?) RETURNING id");

export function getUser(id: number) {
  return getUserStmt.get(id);
}

export function createUser(email: string, name: string) {
  return createUserStmt.get(email, name) as { id: number };
}

Now each statement is compiled once, when the module loads. Every subsequent call just binds parameters and executes. This is the pattern you want for any query you run more than once.

What do you think happens to these prepared statements when the process exits? Nothing you need to worry about. SQLite cleans them up when the connection closes. You do not need to finalize them manually.

Boot order: migrate before serve

A server that starts serving traffic before the database schema is up to date is a bug generator. You will hit no such column errors on rows that exist but do not have the new shape yet, or worse, you will silently write old-shaped rows into a new-shaped table. The rule is simple: run migrations before the server takes its first request.

In practice, that means your process does this when it starts:

// server.ts
import "./scripts/migrate"; // runs on import, top to bottom
import { createServer } from "node:http";
import db from "./db";

// ... route handlers using db ...

createServer(handler).listen(3000);
console.log("Listening on 3000");

Importing ./scripts/migrate runs the migration runner from Section 3. When that import returns, every pending migration has been applied. Only then do we bind to the port and start serving.

This is a small detail that prevents a whole class of deploy bugs. You can also run node scripts/migrate.ts as a separate step in your entrypoint script (we will see that in the next lesson), but tying it to the server boot guarantees you cannot forget.

Closing on shutdown

SQLite with WAL mode is already crash-safe. Even if your process is killed mid-write, the database stays consistent. But there is a small benefit to closing cleanly when you can, because it gives SQLite a chance to checkpoint the WAL into the main database file. Smaller app.db-wal on restart, slightly faster first queries.

// server.ts (continued)
process.on("SIGTERM", () => {
  console.log("Shutting down...");
  db.close();
  process.exit(0);
});

SIGTERM is the signal most process managers send when they ask your process to shut down cleanly. Closing the database on that signal is good hygiene. If the process dies from a crash instead, WAL mode still has your back.

Running on Deno

If you run Deno instead of Node, literally the only change is how you start the script. The import, the DatabaseSync class, the exec calls, everything else is identical:

deno run --allow-read --allow-write --allow-net --allow-env server.ts

This is the quiet benefit of built-in modules. Your database code is not tied to any particular runtime. Node today, Deno tomorrow, back to Node the day after. Same module, same API.

Exercises

Exercise 1: Create a db.ts module exactly as shown above. Import it from a small script, run a SELECT count(*) FROM users query using db.prepare(...).get(), and print the result. Confirm it works without any npm install.

Exercise 2: Add two module-scoped prepared statements to your user module: one for looking up a user by id, one for creating a new user. Call each from a tiny CLI script and verify they both work. Convince yourself that the statements are compiled exactly once when the module loads.

Exercise 3: Wire your migration runner into your server boot. Write a trivial server (Node http or whatever you prefer) that imports the migration module at the top, then starts serving. Confirm that running the server on a fresh database applies every migration before the first request.

Exercise 4: Handle SIGTERM in your server and close the database cleanly on shutdown. Send SIGTERM to the process (for example with kill -TERM <pid>) and verify the shutdown log line appears and the process exits with code 0.

The app can now talk to its database through a single shared module, configured once, with prepared statements cached and migrations running on boot. But the database file still lives on one disk. That disk can fail. A bad migration can corrupt the file. An rm in the wrong directory can wipe it. In the next lesson we will add Litestream, which streams your database to object storage so losing a server does not mean losing your data.