Why Bun’s Built‑in SQLite Module Lets You Drop PostgreSQL for a One‑File Local Service

Problem: Heavy Deployment Footprint for Small Services

When building a quick CLI, a desktop helper, or a single‑node microservice, developers often still reach for a full‑blown database like PostgreSQL or Redis. Those systems require a separate daemon, configuration files, and sometimes a Docker image. For a local tool that only needs a handful of tables, that extra weight feels excessive.

Enter bun:sqlite, a native, synchronous SQLite API bundled with Bun. It lets you ship a single binary plus a single data file—no external packages, no network server, no driver installation.

Thesis: Replace an External DB with bun:sqlite When

• Your workload is read‑heavy and can tolerate a single writer.
• You need a lightweight, zero‑config database for local tooling.
• Deployment simplicity outweighs high‑concurrency or multi‑node requirements.

Understanding the API

The API surface is intentionally small and familiar to anyone who has used the sqlite3 Node.js package:

• new Database(path) – open a file or use :memory: for an in‑memory DB.
• db.query(sql) – returns a prepared statement.
• Prepared statement methods: .get() (single row), .all() (array), .run() (write).
• Transactions: db.transaction(fn) wraps BEGIN/COMMIT and auto‑rolls back on throw.
• Pragma tweaks: db.exec('PRAGMA journal_mode=WAL') to enable Write‑Ahead Logging.

Because calls are synchronous, they block the event loop. That’s a deliberate trade‑off: you get zero async plumbing but you must keep queries fast and avoid long‑running workloads.

Worked Example – Tiny URL Shortener

Below is a ~90‑line Bun script that demonstrates routing, parameterized inserts, and lookups using bun:sqlite. It runs with bun run urlshortener.ts and requires no external dependencies.

// urlshortener.ts
import { serve } from "bun";
import { Database } from "bun:sqlite";

const db = new Database("urls.db");
// Ensure table exists
db.exec(
  `CREATE TABLE IF NOT EXISTS urls (
     id TEXT PRIMARY KEY,
     original TEXT NOT NULL,
     created_at INTEGER DEFAULT (strftime('%s','now'))
   );`
);

// Enable WAL for better read concurrency
try {
  db.exec("PRAGMA journal_mode=WAL");
} catch (_) { /* ignore if not supported */ }

const router = serve({
  fetch(request) {
    const url = new URL(request.url);
    if (url.pathname === "/shorten" && request.method === "POST") {
      return handleShorten(request);
    }
    if (url.pathname.startsWith("/")) {
      return handleRedirect(url.pathname.slice(1));
    }
    return new Response("Not Found", { status: 404 });
  },
});

async function handleShorten(request: Request) {
  const body = await request.json();
  const original = body.original as string;
  const id = crypto.randomUUID().slice(0, 6);
  try {
    db.transaction(() => {
      db.query("INSERT INTO urls (id, original) VALUES (?,?)").run(id, original);
    });
  } catch (e) {
    return new Response("Error", { status: 500 });
  }
  return new Response(JSON.stringify({ short: `${router.url}/${id}` }), {
    headers: { "Content-Type": "application/json" },
  });
}

function handleRedirect(id: string) {
  const row = db.query("SELECT original FROM urls WHERE id = ?").get(id);
  if (!row) return new Response("Not Found", { status: 404 });
  return Response.redirect(row.original, 302);
}

console.log(`Listening on ${router.url}`);

Key points to verify:

• Run bun run urlshortener.ts and POST to /shorten with JSON {"original":"https://example.com"}.
• Check the urls.db file appears in the working directory.
• Access the returned short URL and confirm it redirects.

Trade‑offs and Limitations

• Synchronous blocking – long queries freeze the event loop; keep SELECT and INSERT under a few milliseconds.
• Single writer – SQLite allows only one concurrent write. WAL improves read concurrency but not write concurrency.
• No network interface, roles, or replication – unsuitable for multi‑node or high‑availability scenarios.
• API surface is small; advanced features like custom collations or FTS5 require raw SQL.

If your service scales beyond a single writer or needs multi‑process access, consider a client‑server DB. For local tooling, tests, or prototypes, bun:sqlite is a pragmatic choice.

Actionable Checklist for Adoption

1. Verify your Bun version: bun --version and consult the official docs for bun:sqlite support.
2. Write a small script that:
   • Creates an in‑memory DB.
   • Inserts a row with parameters.
   • Queries the row back.
   Run with bun run test.ts and confirm no errors.
3. Benchmark against your current DB for typical workloads. Measure latency and CPU usage.
4. For production local services, add a try/catch around db.transaction to ensure rollbacks on failure.
5. Consider enabling WAL if you anticipate concurrent reads: db.exec("PRAGMA journal_mode=WAL").
6. Package your application as a single binary: bun build --compile urlshortener.ts --outfile urlshortener (requires Bun 1.0+).

With these steps, you can replace a heavy PostgreSQL dependency with a lightweight, zero‑config SQLite database, simplifying deployment and reducing runtime overhead.