SQLite 生产环境指南：WAL 模式、Litestream、Turso 和 D1

SQLite 已从简单的嵌入式数据库发展为严肃的生产级选择。在 2026 年，Litestream、LiteFS、Turso 和 Cloudflare D1 等工具消除了 SQLite 的许多传统限制。本指南介绍何时 SQLite 是正确的选择以及如何为生产环境配置它。

为什么在生产环境使用 SQLite？

SQLite 是世界上部署最广泛的数据库引擎。它的简单性、可靠性和零管理特性使其越来越适合服务端应用。

主要优势

• 零管理：无需单独的服务器进程、配置或用户管理
• 单文件数据库：易于备份、复制、移动和版本控制
• 极快的读取速度：简单查询比 PostgreSQL 快 10 倍
• ACID 兼容：WAL 模式下支持完整事务和并发读取
• 久经考验：20 多年来在数十亿设备上生产使用
• 极小的占用：库不到 1MB

真实局限

• 单写入者：一次只能有一个写事务
• 无内置复制：需要 Litestream 或 LiteFS 等外部工具
• 无网络访问：数据库必须与应用在同一台机器上
• 有限的并发写入：不适合写入密集型多用户应用
• 无存储过程：业务逻辑必须在应用层

何时在生产环境使用 SQLite

生产环境配置

默认 SQLite 设置针对兼容性而非性能进行了优化。生产环境需要配置 WAL 模式和内存映射 I/O。

WAL 模式（预写日志）

WAL 模式是生产 SQLite 最重要的配置。它允许写入进行时并发读取。

-- 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"

必要的 PRAGMA 设置

PRAGMA 语句配置 SQLite 行为。这些设置应在打开数据库连接时应用。

-- 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;

连接池

使用连接池允许并发读取同时序列化写入。

// 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);

SQLite 生产工具

Litestream：连续复制

Litestream 是开源工具，将 SQLite 数据库持续复制到 S3 兼容存储。

# 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

Turso 是基于 libSQL 构建的托管 SQLite 服务，提供边缘复制和无服务器定价。

# 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",
});

架构模式

单服务器 + Litestream

最简单的生产模式：在单服务器上运行 SQLite，使用 Litestream 持续备份到 S3。

# 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"]

每租户一个数据库

为每个客户创建单独的 SQLite 数据库。提供自然隔离和便捷的数据迁移。

// 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();

性能调优

正确配置后，SQLite 可以处理令人印象深刻的工作负载。

常见问题