SQLite in production: the counter-intuitive SaaS database

Why SQLite works for single-server SaaS

SQLite's reputation as an "embedded toy" comes from its early days. Modern SQLite releases (3.46+) support WAL mode, concurrent readers with a single writer, savepoints, and up to 1 TB database files. The practical constraints are architectural, not theoretical: one process owns the write lock, and that process is your SvelteKit server.

For a multi-tenant SaaS where one server handles all requests, those constraints map cleanly onto the deployment model. There is no network hop to the database — the file is on the same disk. Reads are as fast as the filesystem. Writes contend only with other writes from the same process, which is exactly the concurrency model better-sqlite3 provides. The result: sub-millisecond reads, predictable latency, and zero connection-pool tuning.

The honest version: this works well for single-server deployments serving fewer than roughly 10,000 active users. Beyond that, you will likely need a networked database for horizontal scaling, connection pooling across multiple app instances, or write throughput that a single file lock cannot provide. The starter includes an adapter boundary (Drizzle) that makes that migration feasible — but that is a later decision, not a failing of the current design.

WAL mode: concurrent reads without blocking

The single most important production setting for SQLite is WAL (Write-Ahead Logging) mode. In the default journal mode, every write acquires an exclusive lock that blocks all readers. WAL mode decouples them: readers access a consistent snapshot while writers append to a separate log file, and readers never block writers and vice versa.

The starter enables WAL at connection time:

// src/lib/server/db/index.ts (abridged)
export function createDb(file: string): Db {
  const sqlite = new Database(file);
  sqlite.pragma('foreign_keys = ON');
  if (file !== ':memory:') sqlite.pragma('journal_mode = WAL');
  const db = drizzle(sqlite, { schema });
  migrate(db, { migrationsFolder: MIGRATIONS_DIR });
  return db;
}

Every pragma the starter ships is deliberate. foreign_keys = ONenforces referential integrity, so a cascade delete on a parent row cleans up its children. journal_mode = WAL enables the concurrent-read model — the starter applies it only to file-backed databases, never to :memory:.

Two more pragmas are commonly worth adding — but they are recommended additions, not shipped defaults:

// Recommended additions — NOT shipped by the starter
sqlite.pragma('busy_timeout = 5000');    // wait up to 5s for the write lock
sqlite.pragma('synchronous = NORMAL');   // durable on power loss in WAL, faster writes

busy_timeout = 5000 gives a writer up to five seconds to wait for the lock instead of failing immediately — essential when a background job and a request handler both try to write. synchronous = NORMAL trades one class of crash-recovery edge case for a meaningful write-speed improvement; in WAL mode, NORMAL still guarantees durability on power loss per SQLite's documentation. FULL is safer but measurably slower; use it only if your data is worth the cost.

Verify it worked after connecting:

const mode = db.pragma('journal_mode', { simple: true });
// mode === 'wal'

Tenant isolation: org_id on every table

SQLite has no row-level security. Tenant isolation is your application's job, enforced at every query boundary. The pattern is straightforward: every table that contains tenant data carries an org_id column, and every query filters on it.

Here is how the starter's Drizzle schema declares it, using the shipped invites table as the example:

export const invites = sqliteTable(
  'invites',
  {
    id: text('id').primaryKey(),
    orgId: text('org_id')
      .notNull()
      .references(() => organizations.id, { onDelete: 'cascade' }),
    tokenHash: text('token_hash').notNull(),
    role: text('role').notNull(),
    email: text('email'),
    invitedByUserId: text('invited_by_user_id').notNull(),
    expiresAtMs: integer('expires_at_ms').notNull(),
    acceptedAtMs: integer('accepted_at_ms'),
    acceptedByUserId: text('accepted_by_user_id'),
    revokedAtMs: integer('revoked_at_ms'),
    createdAtMs: integer('created_at_ms').notNull()
  },
  (t) => [index('invites_org_idx').on(t.orgId)]
);

The index on (org_id, ...) is not optional — it is what keeps a tenant-scoped query from scanning every row in the table. Drizzle's query builder accepts a .where(eq(table.orgId, orgId)) clause naturally, making the filter part of the query shape:

// src/lib/server/services/invites.ts (abridged) — services take orgId
export async function listPendingInvites(db: Db, orgId: string) {
  return db
    .select({
      id: invites.id,
      role: invites.role,
      email: invites.email,
      expiresAtMs: invites.expiresAtMs,
      createdAtMs: invites.createdAtMs
    })
    .from(invites)
    .where(and(eq(invites.orgId, orgId), isNull(invites.acceptedAtMs), isNull(invites.revokedAtMs), gt(invites.expiresAtMs, Date.now())))
    .orderBy(desc(invites.createdAtMs));
}

The discipline is: every public service function accepts orgId as a required parameter and applies it in the WHERE clause. There is no code path that queries the table without a tenant filter. If you want a safety net, write a test that asserts every Drizzle query on a tenant-scoped table includes an orgId equality check — it is a grep away.

Connection handling: one process, one file

Unlike Postgres or MySQL, better-sqlite3 does not use a connection pool. It opens the database file once, synchronously, and all operations run against that single handle. This is simpler and faster than pooled connections, but it means:

• One writer at a time. Better-sqlite3 serialises writes internally. Concurrent requests that write will queue, not conflict — handled by the busy_timeout pragma.
• No connection leaks. There is no pool to exhaust, no idle connections to reap. The database handle lives for the process lifetime.
• No network round-trips. Every query is a function call into the SQLite library, which reads and writes the file directly. Latency is disk speed, not network speed.

In a SvelteKit server, the database instance is typically created once at startup and imported by every route handler. The starter follows this pattern — a single getDb() call returns the shared handle, and every service function receives it as an argument rather than importing it globally, which keeps testing and dependency injection clean.

The consequence for deployment: your SvelteKit server must be a single process. If you run multiple app instances (e.g., behind a load balancer), each one opens its own file handle, and concurrent writes from different processes can corrupt the database. SQLite is not designed for multi-process write access. One process, one file — that is the contract.

Backup strategies for a file-based database

A SQLite database is a single file. This makes backups trivially simple and deceptively dangerous. The naive approach — copy the file — works only if no writes are happening during the copy. In WAL mode, the database state is split across the main .db file and the -wal file, and both must be consistent.

Two practical approaches:

-- Approach 1: Checkpoint the WAL, then copy
PRAGMA wal_checkpoint(TRUNCATE);
-- Now copy the .db file (the -wal file is empty after TRUNCATE)

-- Approach 2: Use SQLite's online backup API
-- better-sqlite3 exposes this via db.backup()
const backup = db.backup('backup.db');
await backup.step(-1);  // step(-1) copies the entire database
backup.close();

The online backup API is the safer choice: it copies the database while it remains open and writable, producing a consistent snapshot without checkpointing. The TRUNCATE checkpoint approach is faster but briefly blocks writers. For a production SaaS, schedule the backup API call during a low-traffic window and keep 7-14 days of retention.

One caveat that bites people: if your hosting platform (e.g., Cloudflare Workers, Vercel Edge Functions) does not give you filesystem access, the backup strategy must use an external export — push snapshots to R2, S3, or a similar object store via an HTTP API. The starter's single-server deployment model assumes filesystem access, which is appropriate for VPS and container deployments.

Drizzle ORM: type-safe queries over SQLite

Drizzle is schema-first: you declare your tables in TypeScript, generate the migration SQL, and the query builder is derived from that schema. For SQLite, the schema declaration uses sqliteTable and the query builder produces synchronous calls through better-sqlite3:

import { sqliteTable, text, integer, index } from 'drizzle-orm/sqlite-core';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

const sqlite = new Database('./data.db');
sqlite.pragma('journal_mode = WAL');
const db = drizzle(sqlite);

The practical benefit is not the query builder itself — it is the type safety that catches schema mismatches at compile time. If you rename a column in the schema file and forget to update a query, TypeScript catches it before you deploy. Drizzle's migration runner applies schema changes in order, and the drizzle-kit push command compares the schema file against the live database and generates the diff. The starter uses this workflow: edit schema, run drizzle-kit generate, review the SQL, apply.

For a multi-tenant app, Drizzle's eq() and and() helpers make tenant scoping explicit in the type system rather than buried in string interpolation. The org_id filter is part of the query shape, not a convention you hope someone remembers.

When to outgrow SQLite

SQLite is the right choice when your architecture matches its constraints. It stops being the right choice when any of the following become true:

• Multiple app instances. If you run two or more processes writing to the same database, you need a networked database with proper connection pooling.
• Write-heavy workloads. A single-writer model bottlenecks on write throughput. If your app does more than ~1,000 writes per second sustained, Postgres will serve it better.
• Horizontal scaling. SQLite is a file. You cannot shard it across servers or replicate it for read scaling without external tooling.
• Concurrent administrative access. If your ops team needs to query the database directly while the app is running, a networked database with proper permissions is easier to manage.
• Geo-distributed users. A single file on a single server means latency is tied to that server's location. Multi-region deployments need a replicated database.

The starter's Drizzle abstraction makes this transition feasible. The schema file, service functions, and query patterns are the same — you change the connection driver and add connection pooling. The honest assessment: for a solo founder's first SaaS serving fewer than 10k users, SQLite eliminates an entire category of operational complexity (database server, connection pooling, monitoring) while you validate the product. Migrate when the constraints are real, not theoretical.

Production checklist

• Enable WAL mode at connection time: PRAGMA journal_mode=WAL. Verify it stuck with SELECT journal_mode FROM pragma_journal_mode().
• Recommended, not shipped: set busy_timeout in createDb() (e.g. 5000 ms) so concurrent writers retry instead of failing immediately. The starter only sets foreign_keys and WAL.
• Scope every query by org_id. Never trust application logic alone — add a WHERE org_id = ? clause or a Drizzle eq() filter on every table access.
• Use a single-process connection pool (one Better-sqlite3 instance per SvelteKit server) rather than spawning multiple database handles.
• Snapshot the .db file for backups, not a logical dump — but checkpoint the WAL first (PRAGMA wal_checkpoint(TRUNCATE)) or use SQLite's online backup API. A raw file copy while in WAL mode is not a consistent image unless the WAL is checkpointed.
• Monitor the WAL file size. When -wal exceeds a few hundred MB, run PRAGMA wal_checkpoint(TRUNCATE) to reclaim space.
• Index (org_id, ...) on every table that serves tenant-scoped queries. The starter ships composite indexes for this purpose.
• Recommended, not shipped: set PRAGMA synchronous=NORMAL in WAL mode for a meaningful write-speed gain with acceptable durability. The starter leaves the default synchronous setting.
• Write a migration test that verifies your Drizzle schema matches the live database after every schema change.
• Know your ceiling: SQLite serves a single-writer workload well up to roughly 10k active users on a single server; plan a Postgres migration path before you outgrow it.

The full list of shipped decisions, from schema design to backup scheduling, runs through the guides on multi-tenant database design, Drizzle ORM migrations, and session management — all running in the multi-tenant SvelteKit starter.