SQLite in Production: WAL Mode, Concurrent Writes, and Scaling Strategies

SQLite in Production Is Underrated

SQLite runs 1+ trillion queries per day across all devices. With modern tools, it's viable for production web services.

Why SQLite for Production?

• No separate server process — simpler deployment
• Reads scale infinitely — files can be replicated
• Write throughput: 100k+ writes/second with WAL
• Zero configuration — no connection pooling needed
• 35% smaller than equivalent PostgreSQL data

WAL Mode (Essential)

import sqlite3

conn = sqlite3.connect('app.db')
cursor = conn.cursor()

# Enable WAL mode (critical for concurrent access)
cursor.execute('PRAGMA journal_mode=WAL')

# Tune for performance
cursor.execute('PRAGMA synchronous=NORMAL')  # Faster, still safe with WAL
cursor.execute('PRAGMA cache_size=10000')    # 10MB page cache
cursor.execute('PRAGMA temp_store=MEMORY')
cursor.execute('PRAGMA mmap_size=268435456') # 256MB memory-mapped I/O

conn.commit()

WAL allows:

• Multiple concurrent readers while a writer is active
• One writer at a time (serialized)
• Reader performance unaffected by writers

Better SQLite (better-sqlite3 for Node.js)

import Database from 'better-sqlite3'

const db = new Database('app.db', { verbose: console.log })

// WAL mode setup
db.pragma('journal_mode = WAL')
db.pragma('synchronous = NORMAL')
db.pragma('foreign_keys = ON')

// Prepared statements are compiled once, reused
const getUser = db.prepare('SELECT * FROM users WHERE id = ?')
const insertUser = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)')
const updateUser = db.prepare('UPDATE users SET name = ? WHERE id = ?')

// Synchronous API — no async/await needed!
const user = getUser.get(42)
const result = insertUser.run('Alice', 'alice@example.com')
console.log(result.lastInsertRowid)  // Auto-increment ID

// Transactions (atomic, fast — wraps multiple statements)
const transferPoints = db.transaction((fromId: number, toId: number, points: number) => {
  db.prepare('UPDATE users SET points = points - ? WHERE id = ?').run(points, fromId)
  db.prepare('UPDATE users SET points = points + ? WHERE id = ?').run(points, toId)
})

transferPoints(1, 2, 100) // Atomic!

Litestream: Continuous Replication

# litestream.yml
dbs:
  - path: /app/data/app.db
    replicas:
      - url: s3://my-bucket/app.db
        sync-interval: 1s
      - url: gcs://my-bucket/app.db  # Google Cloud Storage

# Replicate to S3 in background
litestream replicate -config litestream.yml

# Restore from S3 (disaster recovery)
litestream restore -config litestream.yml -replica s3 /app/data/app.db

# Docker: run alongside your app
CMD ["litestream", "replicate", "-exec", "node server.js"]

Turso: Distributed SQLite

import { createClient } from '@libsql/client'

const db = createClient({
  url: 'libsql://my-db-username.turso.io',
  authToken: process.env.TURSO_AUTH_TOKEN,
})

// Same SQLite API, globally distributed
const result = await db.execute('SELECT * FROM users WHERE id = ?', [42])
const users = result.rows

// Batch operations
await db.batch([
  { sql: 'INSERT INTO users (name) VALUES (?)', args: ['Alice'] },
  { sql: 'INSERT INTO users (name) VALUES (?)', args: ['Bob'] },
])

When SQLite vs PostgreSQL

Performance Benchmark

// Insert 100k rows
const stmt = db.prepare('INSERT INTO events (type, data) VALUES (?, ?)')
const insert100k = db.transaction(() => {
  for (let i = 0; i < 100_000; i++) {
    stmt.run('click', JSON.stringify({ x: i, y: i }))
  }
})

const start = performance.now()
insert100k()
console.log(`100k inserts: ${performance.now() - start}ms`)
// ~200ms — ~500k inserts/second!