Claude Code for SQLite in Production: Patterns, Performance, and When to Use It

SQLite is the most deployed database in the world — every smartphone, browser, and embedded system runs it. In production web applications, it’s underutilized. For read-heavy workloads, edge deployments, and single-server applications, SQLite with WAL mode outperforms PostgreSQL significantly while eliminating operational complexity. Claude Code configures SQLite correctly for production use cases.

When SQLite is the Right Choice

Good fit:

• Read-heavy workloads (news sites, documentation, product catalogs)
• Edge deployments (Cloudflare Workers via Turso, embedded apps)
• Single-server applications with < 1k concurrent connections
• Development/test environments (embedded, no setup)
• Electron/desktop apps and mobile backends

Poor fit:

• High write concurrency (SQLite has writer lock — use PostgreSQL)
• Multi-server deployments without Turso/libSQL replication
• Complex OLAP queries (use DuckDB or data warehouse)

Production Configuration

CLAUDE.md for SQLite Projects

## SQLite Configuration
- SQLite 3.45+ via better-sqlite3 (Node) or rusqlite (Rust)
- WAL mode enabled: allows concurrent reads during writes
- Connection: single shared connection instance — never create per-request
- Migrations: sequential numbered files in db/migrations/, applied at startup
- Backup: WAL checkpointing + nightly VACUUM to separate file
- Pragma caching: set once at startup, not per query

## Critical Settings (set at startup)
- PRAGMA journal_mode=WAL (concurrent reads + writes)
- PRAGMA synchronous=NORMAL (safe with WAL, 2x faster than FULL)
- PRAGMA cache_size=-64000 (64MB cache)
- PRAGMA foreign_keys=ON (not default in SQLite!)
- PRAGMA temp_store=MEMORY

// src/lib/database.ts — production SQLite setup
import Database from 'better-sqlite3';
import path from 'path';

const DB_PATH = process.env.DATABASE_PATH ?? path.join(process.cwd(), 'data', 'app.db');

// Singleton — one connection per process
let _db: Database.Database | null = null;

export function getDb(): Database.Database {
  if (_db) return _db;
  
  _db = new Database(DB_PATH, {
    // Verbose logging in dev
    verbose: process.env.NODE_ENV === 'development' ? console.log : undefined,
  });
  
  // Production pragmas — set once at startup
  _db.pragma('journal_mode = WAL');        // Concurrent reads during writes
  _db.pragma('synchronous = NORMAL');      // Safe with WAL, 2x faster than FULL
  _db.pragma('cache_size = -64000');       // 64MB query cache
  _db.pragma('foreign_keys = ON');         // IMPORTANT: not default!
  _db.pragma('temp_store = MEMORY');       // Temp tables in memory
  _db.pragma('mmap_size = 134217728');     // 128MB memory-mapped I/O
  _db.pragma('journal_size_limit = 67108864'); // 64MB WAL limit before checkpoint
  
  // Run pending migrations
  runMigrations(_db);
  
  // Graceful shutdown — close on process exit
  process.on('exit', () => _db?.close());
  process.on('SIGTERM', () => { _db?.close(); process.exit(0); });
  process.on('SIGINT', () => { _db?.close(); process.exit(0); });
  
  return _db;
}

Migration System

Build a simple migration system that runs pending migrations
on startup and tracks which have been applied.

// src/lib/migrations.ts
import Database from 'better-sqlite3';
import fs from 'fs';
import path from 'path';

export function runMigrations(db: Database.Database): void {
  // Create migrations table if it doesn't exist
  db.exec(`
    CREATE TABLE IF NOT EXISTS _migrations (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      filename TEXT NOT NULL UNIQUE,
      applied_at TEXT NOT NULL DEFAULT (datetime('now'))
    )
  `);
  
  const migrationsDir = path.join(process.cwd(), 'db', 'migrations');
  
  if (!fs.existsSync(migrationsDir)) {
    console.log('No migrations directory found, skipping');
    return;
  }
  
  // Get all migration files, sorted by filename (001_, 002_, etc.)
  const files = fs.readdirSync(migrationsDir)
    .filter(f => f.endsWith('.sql'))
    .sort();
  
  // Get already-applied migrations
  const applied = new Set(
    (db.prepare('SELECT filename FROM _migrations').all() as Array<{filename: string}>)
      .map(r => r.filename)
  );
  
  // Apply pending migrations in a single transaction
  const pending = files.filter(f => !applied.has(f));
  
  if (pending.length === 0) return;
  
  console.log(`Applying ${pending.length} migration(s)...`);
  
  const applyMigration = db.transaction((filename: string, sql: string) => {
    db.exec(sql);
    db.prepare('INSERT INTO _migrations (filename) VALUES (?)').run(filename);
  });
  
  for (const filename of pending) {
    const sql = fs.readFileSync(path.join(migrationsDir, filename), 'utf-8');
    applyMigration(filename, sql);
    console.log(`  Applied: ${filename}`);
  }
}

-- db/migrations/001_create_users.sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY,  -- SQLite's rowid alias — auto-increment, fast
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;  -- STRICT mode: enforces column types (SQLite 3.37+)

CREATE INDEX idx_users_email ON users(email);

-- Trigger to auto-update updated_at
CREATE TRIGGER users_updated_at
AFTER UPDATE ON users
BEGIN
  UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
END;

Optimizing Queries

This query runs in 200ms. Profile it and make it faster.

// Analyze query performance
const db = getDb();

// EXPLAIN QUERY PLAN — shows if SQLite uses an index
const plan = db.prepare(`
  EXPLAIN QUERY PLAN
  SELECT u.*, COUNT(o.id) as order_count
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  WHERE u.created_at > ?
  GROUP BY u.id
  ORDER BY order_count DESC
  LIMIT 20
`).all('2026-01-01');

console.log(plan);
// Bad: "SCAN users" (full table scan)
// Good: "SEARCH users USING INDEX idx_users_created_at"

// Add missing index
db.exec('CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at)');

Bulk inserts — the single biggest SQLite performance lever:

// ❌ Slow: 1000 individual INSERTs = 1000 transactions = ~1 second
for (const user of users) {
  db.prepare('INSERT INTO users VALUES (?, ?, ?)').run(user.id, user.email, user.name);
}

// ✅ Fast: 1 transaction = ~10ms
const insertUser = db.prepare('INSERT INTO users VALUES (?, ?, ?)');
const insertMany = db.transaction((users: User[]) => {
  for (const user of users) {
    insertUser.run(user.id, user.email, user.name);
  }
});

insertMany(users); // All 1000 in one transaction

Turso and libSQL for Edge

We want SQLite at the edge — Cloudflare Workers.
Set up Turso for replication.

// Turso: SQLite for the edge — replicated, works in Cloudflare Workers
import { createClient } from '@libsql/client';

// For Cloudflare Workers (HTTP client — no filesystem)
const db = createClient({
  url: process.env.TURSO_DATABASE_URL!,  // libsql://name-org.turso.io
  authToken: process.env.TURSO_AUTH_TOKEN!,
});

// Same SQL — Turso extends SQLite with replication
const users = await db.execute({
  sql: 'SELECT * FROM users WHERE email = ?',
  args: [email],
});

// Batch queries in a transaction
await db.batch([
  { sql: 'INSERT INTO users (email, name) VALUES (?, ?)', args: [email, name] },
  { sql: 'INSERT INTO audit_log (user_id, action) VALUES (last_insert_rowid(), ?)', args: ['signup'] },
], 'write');

// For Cloudflare Pages with embedded SQLite replica
// Each CF edge location gets a read replica — sub-millisecond local reads

Read-Only Replicas for High Traffic

Traffic is growing — reads are fast but we want to scale horizontally.

// Read/write split: primary for writes, replicas for reads
export class DatabasePool {
  private primary: Database.Database;
  private replicas: Database.Database[];
  private replicaIndex = 0;
  
  constructor(primaryPath: string, replicaPaths: string[]) {
    this.primary = new Database(primaryPath);
    configurePragmas(this.primary);
    
    this.replicas = replicaPaths.map(p => {
      const db = new Database(p, { readonly: true });
      db.pragma('journal_mode = WAL');
      db.pragma('cache_size = -128000'); // Larger cache on replicas (reads only)
      return db;
    });
  }
  
  // Round-robin across replicas for reads
  get reader(): Database.Database {
    if (this.replicas.length === 0) return this.primary;
    const db = this.replicas[this.replicaIndex % this.replicas.length];
    this.replicaIndex++;
    return db;
  }
  
  get writer(): Database.Database {
    return this.primary;
  }
}

For PostgreSQL patterns when SQLite’s single-writer model becomes a bottleneck, see the PostgreSQL advanced guide. For database migration patterns that work across both SQLite and PostgreSQL, the database migrations guide covers schema versioning and backward-compatible changes. The Claude Skills 360 bundle includes SQLite skill sets for production configuration, migration patterns, and Turso edge deployment. Start with the free tier to try SQLite optimization prompts.