SQLite in der Produktion: WAL-Modus, Litestream, Turso und D1

SQLite has evolved from a simple embedded database to a serious production contender. In 2026, tools like Litestream, LiteFS, Turso, and Cloudflare D1 have eliminated many of SQLite's traditional limitations. Companies like Expensify, Tailscale, and Fly.io run SQLite in production serving millions of requests. This guide covers when SQLite is the right choice, how to configure it for production, and the tools that make it viable at scale.

Why SQLite for Production?

SQLite is the most deployed database engine in the world, with over one trillion active databases. It is embedded in every smartphone, browser, and most operating systems. Its simplicity, reliability, and zero-administration nature make it increasingly attractive for server-side applications.

Key Advantages

• Zero administration: no separate server process, no configuration, no user management
• Single file database: easy to backup, copy, move, and version control
• Extremely fast reads: up to 10x faster than PostgreSQL for simple queries because there is no network round-trip
• ACID compliant: full transaction support with WAL mode for concurrent reads
• Battle-tested: used in production on billions of devices for over 20 years
• Tiny footprint: the library is under 1MB, the binary is a few hundred KB

Honest Limitations

• Single writer: only one write transaction at a time (though reads are concurrent)
• No built-in replication: requires external tools like Litestream or LiteFS
• No network access: the database must be on the same machine as the application
• Limited concurrent writes: not suitable for write-heavy multi-user applications
• No stored procedures: business logic must live in the application layer

When to Use SQLite in Production

Production Configuration

Default SQLite settings are optimized for compatibility, not performance. For production, you need to configure WAL mode, memory-mapped I/O, and connection pooling.

WAL Mode (Write-Ahead Logging)

WAL mode is the most important configuration for production SQLite. It allows concurrent reads while a write is in progress and significantly improves performance.

-- Enable WAL mode (only needs to be set once, persists across connections)
PRAGMA journal_mode = WAL;

-- WAL mode benefits:
-- 1. Readers don't block writers
-- 2. Writers don't block readers
-- 3. Better performance for most workloads
-- 4. Crash-safe (WAL file is replayed on recovery)

-- Check current journal mode
PRAGMA journal_mode;  -- should return "wal"

Essential PRAGMA Settings

PRAGMA statements configure SQLite behavior. These settings should be applied when the database connection is opened.

-- Production PRAGMA settings (apply on each connection open)
PRAGMA journal_mode = WAL;          -- write-ahead logging
PRAGMA synchronous = NORMAL;        -- safe with WAL mode, faster than FULL
PRAGMA busy_timeout = 5000;         -- wait 5s on lock instead of failing
PRAGMA cache_size = -64000;         -- 64MB page cache (negative = KB)
PRAGMA foreign_keys = ON;           -- enforce foreign key constraints
PRAGMA auto_vacuum = INCREMENTAL;   -- reclaim space incrementally
PRAGMA temp_store = MEMORY;         -- store temp tables in memory
PRAGMA mmap_size = 268435456;       -- 256MB memory-mapped I/O
PRAGMA wal_autocheckpoint = 1000;   -- checkpoint every 1000 pages

-- For read-only connections, also add:
PRAGMA query_only = ON;

Connection Pooling

While SQLite does not need connection pooling in the traditional sense (no network), using a pool of connections allows concurrent reads while serializing writes.

// Node.js: better-sqlite3 with connection pattern
import Database from "better-sqlite3";

// Single write connection
const writeDb = new Database("app.db");
writeDb.pragma("journal_mode = WAL");
writeDb.pragma("synchronous = NORMAL");
writeDb.pragma("busy_timeout = 5000");
writeDb.pragma("cache_size = -64000");
writeDb.pragma("foreign_keys = ON");

// Multiple read connections (pool)
const readPool = Array.from({ length: 4 }, () => {
  const db = new Database("app.db", { readonly: true });
  db.pragma("cache_size = -64000");
  db.pragma("mmap_size = 268435456");
  return db;
});

let readIndex = 0;
function getReadDb() {
  readIndex = (readIndex + 1) % readPool.length;
  return readPool[readIndex];
}

// Usage
const user = getReadDb().prepare("SELECT * FROM users WHERE id = ?").get(userId);
writeDb.prepare("INSERT INTO users (name, email) VALUES (?, ?)").run(name, email);

Production Tools for SQLite

Litestream: Continuous Replication

Litestream is an open-source tool that continuously replicates SQLite databases to S3-compatible storage. It provides near-real-time backups without impacting database performance.

# Install Litestream
curl -fsSL https://github.com/benbjohnson/litestream/releases/download/v0.3.13/litestream-v0.3.13-linux-amd64.tar.gz | tar xz

# litestream.yml configuration
dbs:
  - path: /data/app.db
    replicas:
      - type: s3
        bucket: my-backup-bucket
        path: backups/app.db
        region: us-east-1
        retention: 72h
        sync-interval: 1s

# Start replication (runs alongside your app)
litestream replicate -config litestream.yml

# Restore from backup
litestream restore -config litestream.yml /data/app.db

Turso: SQLite at the Edge

Turso is a managed SQLite service built on libSQL (a fork of SQLite). It provides edge replication, built-in branching, and a serverless pricing model.

# Turso CLI
turso db create my-app
turso db show my-app    # get connection URL and token

# TypeScript client
import { createClient } from "@libsql/client";

const db = createClient({
  url: "libsql://my-app-user.turso.io",
  authToken: "your-token-here",
});

const result = await db.execute("SELECT * FROM users WHERE active = ?", [true]);
console.log(result.rows);

// Embedded replica (local SQLite + cloud sync)
const db = createClient({
  url: "file:local.db",
  syncUrl: "libsql://my-app-user.turso.io",
  authToken: "your-token-here",
});

Architecture Patterns

Single Server + Litestream

The simplest production pattern: run SQLite on a single server with Litestream for continuous backups to S3. If the server fails, restore from S3 and resume.

# Architecture: App + SQLite + Litestream
#
# [Your App] --> [SQLite WAL] --> [Litestream] --> [S3]
#     |              |
#     +-- reads -----+
#     +-- writes ----+
#
# Recovery: litestream restore -> start app
# RPO: ~1 second (continuous replication)
# RTO: ~2 minutes (restore + restart)

# Dockerfile
FROM node:20-slim

# Install Litestream
ADD https://github.com/benbjohnson/litestream/releases/download/v0.3.13/litestream-v0.3.13-linux-amd64.tar.gz /tmp/
RUN tar -xzf /tmp/litestream-*.tar.gz -C /usr/local/bin/

COPY . /app
WORKDIR /app

# Run with Litestream wrapper
CMD ["litestream", "replicate", "-exec", "node server.js"]

Database Per Tenant

Create a separate SQLite database for each customer or tenant. This provides natural isolation, easy data migration, and per-tenant backups.

// Database per tenant pattern
import Database from "better-sqlite3";
import { LRUCache } from "lru-cache";

const dbCache = new LRUCache<string, Database.Database>({
  max: 100,
  dispose: (db) => db.close(),
});

function getTenantDb(tenantId: string): Database.Database {
  let db = dbCache.get(tenantId);
  if (!db) {
    db = new Database(`./data/tenants/${tenantId}.db`);
    db.pragma("journal_mode = WAL");
    db.pragma("synchronous = NORMAL");
    db.pragma("busy_timeout = 5000");
    dbCache.set(tenantId, db);
  }
  return db;
}

// Each tenant has their own database file
// Easy to backup, migrate, or delete individual tenants
const db = getTenantDb("tenant-123");
const users = db.prepare("SELECT * FROM users").all();

Performance Tuning

With proper configuration, SQLite can handle impressive workloads. Here are benchmarks and tuning tips.

Frequently Asked Questions