Setting up SQLite as a backend on a single VPS with `bun:sqlite` and WAL

Open the Bun runtime and write a single line import { Database } from "bun:sqlite" — no separate driver installation needed to talk to SQLite. You may already know that, but how to combine PRAGMAs to get stable throughput in practice, and how to handle concurrent write contention, rarely gets covered well in example code. This article focuses on filling that gap.

bun:sqlite statically links the SQLite C library into the runtime and does not cross the N-API boundary that Node.js native addons go through. The official Bun documentation and the Bun 1.2 release notes cover this architecture and benchmarks against better-sqlite3, so it is safer to refer to the official sources directly for the numbers themselves. Rather than repeating specific multiplier claims, this article covers the configuration and trade-offs involved in using that architecture in a real API server.

This is written with developers who rapidly prototype lightweight backends or CLI tools in TypeScript in mind, as well as teams that want to start with a single VPS and hold out there for a while even as traffic grows.

Where bun:sqlite differs from other SQLite clients

Node.js native addons carry a marshaling cost of round-tripping between the JavaScript runtime and C++ code via N-API. Because Bun's runtime is written in Zig and bundles the SQLite C library, it does not cross a separate addon boundary.

Concrete benchmark numbers vary significantly by workload, so measuring with your own query patterns is far more useful in practice. The conditions and results presented by Bun officially can be found in the benchmark section of the 1.2 release notes.

What WAL mode changes

In the default journal mode (rollback journal), a write transaction locks the DB file and blocks readers. WAL (Write-Ahead Logging) first writes changes to a separate -wal file; readers check the -shm shared memory index to find out whether the latest version of a given page is in the WAL, then read from either the WAL or the main DB file.

The key property summarized in the SQLite WAL documentation is "readers do not block writers, and writers do not block readers." The magnitude of performance improvement varies greatly depending on the read/write ratio, number of concurrent readers, page size, and transaction length, so it is hard to generalize as a fixed multiplier. However, the qualitative benefit is clear: with writer-reader mutual blocking eliminated, the conditions under which SQLITE_BUSY occurs become much narrower.

An easy point to miss here is that even in WAL mode, only one concurrent write transaction is allowed at a time. Reader-writer interference disappears, but writer-writer contention remains. This is covered again later.

query.as(Class) — mapping results to type-safe instances

bun:sqlite has an .as() method that maps query results to instances of a specific class. The official Bun SQLite documentation has examples, so check the docs for the latest API signature.

import { Database } from "bun:sqlite";
 
class User {
  id!: number;
  name!: string;
  email!: string;
  createdAt!: string;
}
 
const db = new Database("app.db");
const getUser = db.query("SELECT * FROM users WHERE id = $id").as(User);
const user = getUser.get({ $id: 1 });

Even before attaching Drizzle or Prisma, this alone lets you handle result types with a good degree of safety.

The baseline PRAGMA set

To use this in production, you need a minimal PRAGMA combination. The following is a reasonable starting point for a workload with many reads and few writes.

import { Database } from "bun:sqlite";
 
function openDatabase(path: string) {
  const db = new Database(path);
 
  db.run("PRAGMA journal_mode = WAL");
  db.run("PRAGMA synchronous = NORMAL");
  db.run("PRAGMA cache_size = -64000");
  db.run("PRAGMA foreign_keys = ON");
  db.run("PRAGMA busy_timeout = 5000");
 
  return db;
}
 
export const db = openDatabase("app.db");

• synchronous = NORMAL carries a risk of losing the last transaction on a power loss, but there is no risk of DB file corruption. For financial data, FULL is safer. OFF can corrupt the DB file itself on power loss, so avoid it unless you are using it as a temporary cache.
• busy_timeout = 5000 tells SQLite to retry internally for up to 5 seconds when writers contend. Without this value, SQLITE_BUSY is returned immediately.
• cache_size = -64000 allocates 64 MB for the page cache. Adjust based on available RAM and your working set.

Prepared statement caching

db.query(sql) internally caches the compiled Statement for the same SQL string. The same Statement is reused even when parameters differ, so repeatedly calling db.query(...) inside a loop does not recompile the SQL bytecode each time. That said, for code clarity it is better to extract the Statement once and reference it.

const findProduct = db.query("SELECT * FROM products WHERE id = $id");
 
for (const item of items) {
  findProduct.get({ $id: item.id });
}

Batch inserts and transactions

Inserting rows one by one without a transaction accumulates the overhead of an implicit transaction being opened and closed on every insert. Wrapping with db.transaction() lets Bun handle BEGIN/COMMIT.

import { Database } from "bun:sqlite";
 
const db = new Database("app.db");
db.run("PRAGMA journal_mode = WAL");
db.run("PRAGMA synchronous = NORMAL");
db.run("PRAGMA busy_timeout = 5000");
 
db.run(`
  CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT NOT NULL,
    payload TEXT,
    created_at TEXT DEFAULT (datetime('now'))
  )
`);
 
const insertEvent = db.query(
  "INSERT INTO events (type, payload) VALUES ($type, $payload)"
);
 
const batchInsert = db.transaction(
  (rows: Array<{ type: string; payload: string }>) => {
    for (const row of rows) {
      insertEvent.run({ $type: row.type, $payload: row.payload });
    }
  }
);
 
const events = Array.from({ length: 10_000 }, (_, i) => ({
  type: "click",
  payload: JSON.stringify({ index: i }),
}));
 
const start = performance.now();
batchInsert(events);
console.log(`Insert complete: ${(performance.now() - start).toFixed(1)}ms`);

If multiple writers may contend, it is safer to start with db.run("BEGIN IMMEDIATE") to acquire the write lock immediately. BEGIN (=DEFERRED) upgrades the lock at the first write, and if another writer overlaps during that upgrade, SQLITE_BUSY_SNAPSHOT can occur.

REST API combined with Hono

The following is a conceptual example with minimal validation. In a production service, adding schema validation with Zod or similar is the safer approach.

import { Hono } from "hono";
import { Database, SQLiteError } from "bun:sqlite";
 
const db = new Database("tasks.db");
db.run("PRAGMA journal_mode = WAL");
db.run("PRAGMA synchronous = NORMAL");
db.run("PRAGMA foreign_keys = ON");
db.run("PRAGMA busy_timeout = 5000");
 
db.run(`
  CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    done INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now'))
  )
`);
 
const queries = {
  list: db.query("SELECT * FROM tasks ORDER BY created_at DESC"),
  get: db.query("SELECT * FROM tasks WHERE id = $id"),
  insert: db.query("INSERT INTO tasks (title) VALUES ($title) RETURNING *"),
  update: db.query("UPDATE tasks SET done = $done WHERE id = $id RETURNING *"),
  delete: db.query("DELETE FROM tasks WHERE id = $id"),
};
 
const app = new Hono();
 
app.get("/tasks", (c) => c.json(queries.list.all()));
 
app.get("/tasks/:id", (c) => {
  const task = queries.get.get({ $id: Number(c.req.param("id")) });
  if (!task) return c.json({ error: "Not found" }, 404);
  return c.json(task);
});
 
app.post("/tasks", async (c) => {
  let body: unknown;
  try {
    body = await c.req.json();
  } catch {
    return c.json({ error: "Invalid JSON" }, 400);
  }
  const title = (body as { title?: unknown })?.title;
  if (typeof title !== "string" || title.trim() === "") {
    return c.json({ error: "title required" }, 400);
  }
  const task = queries.insert.get({ $title: title });
  return c.json(task, 201);
});
 
app.patch("/tasks/:id", async (c) => {
  const { done } = await c.req.json<{ done: boolean }>();
  const task = queries.update.get({
    $id: Number(c.req.param("id")),
    $done: done ? 1 : 0,
  });
  if (!task) return c.json({ error: "Not found" }, 404);
  return c.json(task);
});
 
app.delete("/tasks/:id", (c) => {
  queries.delete.run({ $id: Number(c.req.param("id")) });
  return c.body(null, 204);
});
 
app.onError((err, c) => {
  if (err instanceof SQLiteError && err.code === "SQLITE_BUSY") {
    return c.json({ error: "DB busy, retry" }, 503);
  }
  throw err;
});
 
export default { port: 3000, fetch: app.fetch };

The only external dependency is hono. No DB client, connection pool, or ORM is attached. In-process access means no network round-trips, which tends to keep latency low — but actual values depend on query complexity and the presence of indexes, so numbers are only meaningful when measured against your own workload.

Synchronous API in CLI tools

The bun:sqlite API is synchronous, which makes it a natural fit for CLI scripts.

#!/usr/bin/env bun
import { Database } from "bun:sqlite";
 
const db = new Database(`${process.env.HOME}/.mytool/history.db`);
db.run("PRAGMA journal_mode = WAL");
db.run(`
  CREATE TABLE IF NOT EXISTS history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    command TEXT NOT NULL,
    ran_at TEXT DEFAULT (datetime('now'))
  )
`);
 
const command = process.argv.slice(2).join(" ");
 
if (command === "list") {
  const rows = db
    .query("SELECT command, ran_at FROM history ORDER BY ran_at DESC LIMIT 20")
    .all() as { command: string; ran_at: string }[];
  for (const row of rows) console.log(`[${row.ran_at}] ${row.command}`);
} else if (command) {
  db.query("INSERT INTO history (command) VALUES ($cmd)").run({ $cmd: command });
  console.log(`Recorded: ${command}`);
}

Bundling with bun build --compile produces a single binary that includes the SQLite driver, making distribution straightforward.

Handling concurrent writes and SQLITE_BUSY

WAL mode eliminates reader-writer interference, but two or more write transactions cannot proceed simultaneously. When a Hono server receives multiple requests in parallel and each attempts a write, lock contention occurs; if the lock cannot be acquired within busy_timeout, SQLITE_BUSY is returned.

Practical mitigation can be combined at three layers.

1. PRAGMA busy_timeout: Absorbs brief contention via SQLite's internal retry. The 5-second value in the example above is a conservative buffer; in practice, setting it lower in line with your request SLA helps reduce response latency.
2. Application-level retry: On SQLITE_BUSY, retry a few times with exponential backoff. This can only be safely applied to idempotent writes.
3. Serialization queue: Funnel write requests through a single queue for sequential processing. This can be implemented with an in-process async queue (e.g., p-queue) or an in-memory channel; since it eliminates contention entirely, the resulting latency is predictable.

Read requests are not affected by this contention, so a simple split — "writes go through a serial queue, reads run freely" — is enough to organize most of the logic.

How to handle WAL checkpoints

SQLite automatically checkpoints the -wal file based on certain conditions, but the file can keep growing during traffic spikes. It is safer to schedule manual checkpoints, taking both mode selection and failure handling into account.

type CheckpointResult = { busy: number; log: number; checkpointed: number };
 
function checkpoint(mode: "PASSIVE" | "TRUNCATE" = "PASSIVE") {
  const row = db
    .query(`PRAGMA wal_checkpoint(${mode})`)
    .get() as CheckpointResult;
 
  if (row.busy !== 0) {
    console.warn(`Checkpoint incomplete (mode=${mode}): busy=${row.busy}`);
  }
  return row;
}
 
setInterval(() => checkpoint("PASSIVE"), 5 * 60 * 1000);
setInterval(() => checkpoint("TRUNCATE"), 60 * 60 * 1000);

• PASSIVE merges as much as possible without disturbing active readers or writers. If it fails, the next cycle retries, making it suitable for continuous scheduling.
• TRUNCATE truncates the WAL file after merging to reclaim disk space, but if active readers are present it either waits or returns busy > 0. Calling it on a time-based schedule during traffic peaks can cause long waits, so it is safer to run it during idle periods or check the busy field in the result and retry.
• Adjusting PRAGMA wal_autocheckpoint = N (default 1000 pages) in parallel lets you control the automatic checkpoint frequency.

When this stack fits, and when to move on

The term "local-first" refers to an architecture — as defined by Ink & Switch — where data is stored primarily on the client device and synchronized via CRDTs or similar. What this article covers is not that pattern, but rather a structure where an embedded DB lives on a single server, so that is the more accurate description going forward.

Common pitfalls

• A setup where multiple processes attempt concurrent writes to the same SQLite file. Sticking to the single-process principle is the safer approach.
• Trying to squeeze performance with synchronous = OFF. The file itself can become corrupted on a power loss.
• Pulling large result sets with query.all() and repeatedly calling JSON.parse in a loop. The bottleneck becomes the JS layer, not SQLite.

Decision criteria for operations

When running this stack in production, the decisions that come up repeatedly narrow down to roughly three.

When to turn on a replication tool like Litestream. From the moment data is exposed to users — that is, when it enters a state where losing it would be a problem. Even at the side-project stage, once user accounts exist, starting streaming replication to S3 with Litestream saves future regrets. The replication overhead, which tracks the WAL and uploads it, is imperceptible unless your write QPS is extremely high.

What determines the WAL checkpoint interval. Watch two signals. If the -wal file size persistently exceeds several hundred MB, or if the busy field in the return value of PRAGMA wal_checkpoint is consistently nonzero, the interval is too long or the mode choice is wrong. Starting with continuous PASSIVE plus TRUNCATE during idle periods, then adjusting based on the file-size graph, is the practical approach.

When to migrate to PostgreSQL. When write contention can no longer be absorbed by a queue, when the data has grown too large to restore within a backup window, or when the application needs to be split across multiple nodes. Until then, the simplicity gained from embedded SQLite generally wins.

What makes bun:sqlite attractive is not an extreme performance multiplier, but the fact that the point at which these three decisions become unavoidable turns out to be further away than expected — a "good enough" threshold. Rather than setting up PostgreSQL + a connection pool + an ORM from the start, you can begin with a few TypeScript files and a single SQLite file, and migrate when real load actually appears. It is not too late.

References

• Bun official SQLite documentation
• Bun 1.2 release notes
• SQLite WAL official documentation
• SQLite PRAGMA reference
• Ink & Switch — Local-first software
• Litestream official documentation
• Drizzle ORM — bun:sqlite integration