Designing per-tenant DB file isolation using Bun 1.2's built-in SQLite with WAL mode and connection pooling

When designing a multi-tenant DB for a SaaS backend, most people start with a single DB and a tenant_id column. I did too. The problems creep in over time. When one tenant runs a heavy aggregate query, overall response times spike, and forgetting a tenant_id in a WHERE clause means touching another tenant's data. Anyone who's been there knows — that afternoon was very long.

The Database-per-Tenant pattern — giving each tenant their own independent DB file — structurally eliminates the possibility of data leakage and achieves performance isolation at the same time. Implementing this with PostgreSQL, however, requires managing a separate connection pool per tenant, which actually increases complexity. This is where Bun's built-in bun:sqlite driver opens up a practical option. It runs SQLite natively without external packages, and with WAL mode enabled, multiple read connections and a single write connection operate concurrently without blocking each other. Applied independently per tenant, write contention between tenants disappears entirely.

In this post, we'll walk through separating .db files per tenant, configuring WAL + PRAGMAs, implementing an LRU-based instance pool, and schema migration strategies — all with real code. If you're migrating to Bun or seriously considering SQLite for production as a Node.js/TypeScript developer, this is directly applicable.

Why This Combination, Why Now

bun:sqlite — A Built-in Driver Shipped with the Runtime

bun:sqlite is a built-in module that has shipped with Bun since early versions. It integrates directly with the JavaScriptCore engine, calling SQLite natively without going through N-API. No separate npm package is needed, which simplifies the deployment pipeline, and there's no native binding build step like with better-sqlite3.

Rather than citing a specific benchmark multiplier — since performance varies greatly by workload — it's more honest to view the architecture of bypassing the N-API layer and connecting directly to the engine as the advantage. Actual gains in your workload should be verified through your own profiling.

The SQLite-as-Primary-DB Trend

Centered around Cloudflare D1, Turso (libSQL), and Litestream, the trend of using SQLite as a server production DB is visibly gaining traction. The "SQLite file per tenant" architecture is gaining attention as a realistic alternative, especially for small-to-medium SaaS, and the experimental introduction of node:sqlite in Node.js v22.5+ is a signal pointing in the same direction.

Since SQLite is an embedded DB, TCP connection pools are unnecessary, and inactive tenant files don't consume memory once their handles are closed. This is why operational overhead is far lower than PostgreSQL for SaaS handling hundreds of small tenants.

Architecture: Per-Tenant Files + Central Meta DB

The basic directory structure is simple.

data/
├── central.db            # Tenant registry, schema version management
└── tenants/
    ├── org-abc.db        # Independent file per tenant
    ├── org-abc.db-wal    # WAL file (auto-created and managed)
    ├── org-def.db
    └── org-def.db-wal

central.db serves as a meta store managing tenant registration info and schema versions. Actual tenant data is isolated in each tenant's own file. When an HTTP request comes in, the tenantId is extracted to open or retrieve the corresponding file from the pool.

Since the files themselves differ between tenants, data leakage is structurally impossible. Deleting a tenant is as simple as removing that tenant's .db and -wal files.

WAL Mode: A Structural Solution to Concurrency Issues

In SQLite's default journal mode (DELETE mode), reads are blocked while a write is in progress. This is fine for single-user environments, but in a server environment where multiple requests arrive simultaneously, SQLITE_BUSY errors appear.

WAL mode writes changes to a separate -wal file first instead of the main .db file, then merges at checkpoint time. This allows multiple read connections and a single write connection to operate concurrently without blocking each other.

busy_timeout is a setting that makes SQLite wait and retry for the specified duration instead of failing immediately when a lock occurs. It's not a root fix — it's a buffer that absorbs momentary contention. Under write-heavy workloads, SQLITE_BUSY can still occur, so retry logic at the application layer should also be considered. I once ran WAL without busy_timeout and immediately hit lock errors during concurrent writes — that one setting showed me just how much of a buffer it provides.

The Core PRAGMA Combination

PRAGMA journal_mode = WAL;        -- Enable WAL mode
PRAGMA synchronous = NORMAL;      -- Balance between performance and durability
PRAGMA busy_timeout = 5000;       -- Max 5 seconds wait for lock (ms)
PRAGMA wal_autocheckpoint = 1000; -- Auto-checkpoint every 1000 pages (default)
PRAGMA cache_size = -64000;       -- 64MB cache per connection

synchronous = NORMAL is a trade-off that provides acceptable durability for most production environments in WAL mode. However, unlike FULL, it does not force an fsync on every commit, so there remains a possibility that some recent commits not yet checkpointed could be lost in a sudden power failure (see the synchronous entry in the SQLite PRAGMA documentation for detailed conditions). If complete durability of the last commit is an absolute requirement — as with a financial ledger — you should consider FULL.

wal_autocheckpoint = 1000 is actually the same as SQLite's default value. Explicitly setting it is more of a statement of intent — "our team is consciously maintaining this value" — and not setting it explicitly does not cause the WAL to grow indefinitely. The actual causes of abnormally bloated WAL files are covered separately later.

Connection Pool Implementation: LRU Instance Management

Unlike PostgreSQL/MySQL, SQLite has no TCP connections. Instead, in a file-based multi-tenant environment, you need to limit the number of open file handles. The OS ulimit default is typically 1024, and opening 1 writer + N readers per tenant consumes handles quickly. When tenant count exceeds a few hundred, it becomes impractical to keep all DBs open simultaneously, requiring LRU-based instance management.

JavaScript's Map preserves insertion order. By deleting and reinserting an entry on access, you can implement LRU behavior where the most recently used entry is always at the end.

1 Writer + N Readers Pattern

To maximize the benefits of WAL mode, it's best to separate a single write-only instance and multiple read-only instances per tenant. In bun:sqlite, the same file can be opened multiple times with the readonly: true option.

// tenant-db-pool.ts
import { Database } from "bun:sqlite";
import { mkdirSync } from "fs";
 
const PRAGMA_WRITER = `
  PRAGMA journal_mode = WAL;
  PRAGMA synchronous = NORMAL;
  PRAGMA busy_timeout = 5000;
  PRAGMA wal_autocheckpoint = 1000;
  PRAGMA cache_size = -64000;
`;
 
const PRAGMA_READER = `
  PRAGMA busy_timeout = 5000;
  PRAGMA cache_size = -64000;
`;
 
function applySchema(db: Database): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      email TEXT NOT NULL UNIQUE,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );
    CREATE TABLE IF NOT EXISTS events (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      type TEXT NOT NULL,
      data TEXT,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );
    CREATE INDEX IF NOT EXISTS idx_events_type ON events (type);
  `);
}
 
interface TenantConnections {
  writer: Database;
  readers: Database[];
  nextReader: number;
}
 
export class TenantDBPool {
  private connections = new Map<string, TenantConnections>();
  private readonly dbDir: string;
  private readonly maxTenants: number;
  private readonly readerCount: number;
 
  constructor(options: {
    dbDir: string;
    maxTenants?: number;
    readerCount?: number;
  }) {
    this.dbDir = options.dbDir;
    this.maxTenants = options.maxTenants ?? 100;
    this.readerCount = options.readerCount ?? 3;
    mkdirSync(this.dbDir, { recursive: true });
  }
 
  private openTenant(tenantId: string): TenantConnections {
    const path = `${this.dbDir}/${tenantId}.db`;
 
    const writer = new Database(path, { create: true });
    writer.exec(PRAGMA_WRITER);
    applySchema(writer);
 
    const readers = Array.from({ length: this.readerCount }, () => {
      const r = new Database(path, { readonly: true });
      r.exec(PRAGMA_READER);
      return r;
    });
 
    return { writer, readers, nextReader: 0 };
  }
 
  private getConnections(tenantId: string): TenantConnections {
    if (this.connections.has(tenantId)) {
      const conns = this.connections.get(tenantId)!;
      this.connections.delete(tenantId);
      this.connections.set(tenantId, conns);
      return conns;
    }
 
    if (this.connections.size >= this.maxTenants) {
      const oldestKey = this.connections.keys().next().value!;
      this.closeTenant(oldestKey);
    }
 
    const conns = this.openTenant(tenantId);
    this.connections.set(tenantId, conns);
    return conns;
  }
 
  getWriter(tenantId: string): Database {
    return this.getConnections(tenantId).writer;
  }
 
  getReader(tenantId: string): Database {
    const conns = this.getConnections(tenantId);
    const reader = conns.readers[conns.nextReader % conns.readers.length];
    conns.nextReader++;
    return reader;
  }
 
  closeTenant(tenantId: string): void {
    const conns = this.connections.get(tenantId);
    if (!conns) return;
    conns.writer.close();
    conns.readers.forEach((r) => r.close());
    this.connections.delete(tenantId);
  }
 
  closeAll(): void {
    for (const tenantId of [...this.connections.keys()]) {
      this.closeTenant(tenantId);
    }
  }
}

Usage Example

// app.ts
import { TenantDBPool } from "./tenant-db-pool";
 
const pool = new TenantDBPool({
  dbDir: "./data/tenants",
  maxTenants: 200,
  readerCount: 3,
});
 
// Read — use reader instance
const events = pool
  .getReader("org-abc")
  .query("SELECT * FROM events WHERE type = ? ORDER BY created_at DESC LIMIT 50")
  .all("login");
 
// Write — transaction wrapper is required
const writer = pool.getWriter("org-abc");
writer.transaction(() => {
  writer
    .query("INSERT INTO events (type, data) VALUES (?, ?)")
    .run("login", JSON.stringify({ userId: 42 }));
  writer
    .query("UPDATE users SET last_seen = ? WHERE id = ?")
    .run(Date.now(), 42);
})();

db.transaction(fn) returns a function. You can call it immediately with the trailing (), or store the returned function for reuse. If an exception is thrown, the transaction is automatically rolled back.

Start with readerCount based on your expected concurrent read requests and CPU core count, then adjust. The general community observation is that going far beyond the core count yields little practical gain due to SQLite's single-file nature.

Schema Migration Strategy

In a multi-tenant file-separated architecture, schema migrations must be applied to as many files as there are tenants, making management without automation difficult.

New tenants are automatically initialized when their file is first opened. In the code above, applySchema() is called inside openTenant(), so new tenants always start with the latest schema. The CREATE TABLE IF NOT EXISTS pattern ensures idempotency, so it's safe to run repeatedly.

When existing tenant DBs need changes, write a batch migration script. One caveat: ALTER TABLE ... ADD COLUMN is not idempotent in SQLite. If the column already exists, a duplicate column name error will stop the script. Tracking each tenant's schema version is the safest approach.

// migrate-all.ts
import { Database } from "bun:sqlite";
import { readdirSync } from "fs";
 
interface Migration {
  version: number;
  sql: string;
}
 
const MIGRATIONS: Migration[] = [
  { version: 1, sql: `ALTER TABLE events ADD COLUMN metadata TEXT;` },
];
 
function ensureMigrationTable(db: Database): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      version INTEGER PRIMARY KEY,
      applied_at INTEGER NOT NULL DEFAULT (unixepoch())
    );
  `);
}
 
function currentVersion(db: Database): number {
  const row = db
    .query("SELECT COALESCE(MAX(version), 0) AS v FROM schema_migrations")
    .get() as { v: number };
  return row.v;
}
 
function applyMigrations(db: Database): number[] {
  ensureMigrationTable(db);
  const from = currentVersion(db);
  const pending = MIGRATIONS.filter((m) => m.version > from);
  const applied: number[] = [];
 
  for (const m of pending) {
    db.transaction(() => {
      db.exec(m.sql);
      db.query("INSERT INTO schema_migrations (version) VALUES (?)").run(m.version);
    })();
    applied.push(m.version);
  }
  return applied;
}
 
const files = readdirSync("./data/tenants").filter((f) => f.endsWith(".db"));
 
for (const file of files) {
  const db = new Database(`./data/tenants/${file}`);
  db.exec("PRAGMA busy_timeout = 5000;");
  try {
    const applied = applyMigrations(db);
    console.log(`${file}: applied ${applied.length} migration(s) (${applied.join(", ") || "none"})`);
  } catch (err) {
    console.error(`Failed: ${file}`, err);
  } finally {
    db.close();
  }
}

When file counts grow into the thousands, moving to the migration system of drizzle-orm or kysely is a natural next step. The example above is the minimal version-tracking skeleton before heading in that direction.

Trade-offs

Pros and Cons at a Glance

Common Mistakes in Practice

1. Running multiple queries without transaction()

// Dangerous: partial state if an intermediate step fails
writer.query("UPDATE accounts SET balance = ? WHERE id = ?").run(newBalance, id);
writer.query("INSERT INTO ledger ...").run(...);
 
// Safe: atomicity is guaranteed
writer.transaction(() => {
  writer.query("UPDATE accounts SET balance = ? WHERE id = ?").run(newBalance, id);
  writer.query("INSERT INTO ledger ...").run(...);
})();

2. If the WAL file keeps growing, suspect long-running read transactions before suspecting auto-checkpoint

wal_autocheckpoint is already active at the default of 1000 pages. If the WAL file keeps growing despite this, the cause is usually elsewhere. If a long-running read transaction is holding an old snapshot, SQLite cannot recycle the WAL pages needed for that snapshot and defers checkpointing. Start by checking where transactions are left open due to batch queries or connection leaks. Periodically running PRAGMA wal_checkpoint(TRUNCATE) during low-traffic periods is also a useful mitigation.

3. Using the writer for read queries

To get the 1 Writer + N Readers benefit of WAL, reads must always use an instance retrieved via getReader(). Handling both reads and writes through a single writer significantly reduces concurrency gains.

4. Opening all tenant DBs at server startup

Opening all 200 tenants at startup increases initialization time and wastes file handles. The right approach is to open lazily when a request arrives and manage with an LRU pool.

Security Considerations: tenantId Validation

Since tenantId is placed directly into a file path segment, passing request values through without validation can allow path traversal attacks. Beyond worst-case inputs like ../../etc/passwd, there's also the abuse case of arbitrarily creating non-existent tenant files to fill up disk.

Always validate using a whitelist approach before entering the pool.

const TENANT_ID_PATTERN = /^[a-zA-Z0-9-]{1,64}$/;
 
function validateTenantId(tenantId: string): void {
  if (!TENANT_ID_PATTERN.test(tenantId)) {
    throw new Error("Invalid tenant ID");
  }
}
 
// Always run this first at the middleware entry point
validateTenantId(req.tenantId);
const db = pool.getReader(req.tenantId);

In addition, your authentication layer must separately verify that the requesting user has access rights to the given tenantId. Whether a string is valid in format and whether the user is allowed to access that tenant are two different questions.

Closing Thoughts

The bun:sqlite + WAL + LRU instance pool combination lets you build a robust multi-tenant architecture for small-to-medium SaaS without PostgreSQL. If your tenant count is in the tens to hundreds and a single server can handle the traffic, the value you get far outweighs the operational complexity.

From experience, the signals that it's time to tune are roughly as follows. If SQLITE_BUSY starts appearing repeatedly in server logs, before increasing busy_timeout, first check whether write transaction scopes are too broad. If you're doing heavy work like network calls or JSON serialization inside a transaction, removing that is more impactful than any other optimization. When file handle counts approach ulimit and errors start appearing, that's when to lower maxTenants or reduce readerCount. If WAL file size trends upward over several days, suspecting long-running read transactions first — as mentioned earlier — will get you to the answer faster.

It's also worth being aware of a few limitations upfront. When tenant count grows into the thousands, LRU pool and ulimit management becomes complex, and frequent LRU evictions accumulate file-open overhead. When horizontal scaling becomes necessary, distributed SQLite solutions like Litestream or Turso are the natural next step. If cross-tenant analytics queries are a core feature, it may be better to consider a single DB + schema separation approach from the start.

References

Official Documentation

• SQLite - Bun Official Docs
• bun:sqlite API Reference
• Pragma statements supported by SQLite
• Write-Ahead Logging (WAL) - SQLite Official Docs

Related Projects

• drizzle-orm
• kysely
• Litestream
• Turso

Community Cases & Essays

• Database-per-Tenant: Consider SQLite - Medium
• Multi-tenancy - High Performance SQLite
• SQLite concurrent writes and database is locked errors - Ten Thousand Meters