SQLite

使用 better-sqlite3 操作 SQLite

借助 better-sqlite3 Node.js 驱动，获取 SQLite 数据库操作方面的专家协助。

概述

SQLite 是一款轻量级嵌入式 SQL 数据库引擎：

• 零配置：无需设置服务器，数据库存储在单个文件中
• 符合 ACID：全面支持事务和回滚
• 高性能：非常适合读取密集型工作负载
• 便携：单个文件，易于备份和分发
• better-sqlite3：同步 Node.js 驱动，速度快于异步替代方案

安装

# Install better-sqlite3
npm install better-sqlite3
npm install --save-dev @types/better-sqlite3

# Optional: SQLite CLI tools
# Ubuntu/Debian
sudo apt-get install sqlite3

# macOS
brew install sqlite3

基本设置

初始化数据库

import Database from 'better-sqlite3';

// Create or open database
const db = new Database('mydb.sqlite');

// In-memory database (for testing)
const memDb = new Database(':memory:');

// Read-only mode
const readDb = new Database('mydb.sqlite', { readonly: true });

// Enable WAL mode for better concurrency
db.pragma('journal_mode = WAL');

// Close database
db.close();

数据库配置

import Database from 'better-sqlite3';

const db = new Database('mydb.sqlite', {
  verbose: console.log, // Log every SQL statement
  fileMustExist: false, // Create if doesn't exist
});

// Recommended pragmas
db.pragma('journal_mode = WAL'); // Write-Ahead Logging
db.pragma('synchronous = NORMAL'); // Balance safety/performance
db.pragma('foreign_keys = ON'); // Enable foreign keys
db.pragma('temp_store = MEMORY'); // Use memory for temp tables

创建表

创建基本表

// Create table
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at INTEGER NOT NULL DEFAULT (unixepoch())
  )
`);

// Create index
db.exec(`
  CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)
`);

复杂架构

db.exec(`
  CREATE TABLE IF NOT EXISTS certificate_authorities (
    id TEXT PRIMARY KEY,
    subject_dn TEXT NOT NULL,
    serial_number TEXT NOT NULL UNIQUE,
    not_before INTEGER NOT NULL,
    not_after INTEGER NOT NULL,
    kms_key_id TEXT NOT NULL,
    certificate_pem TEXT NOT NULL,
    is_root BOOLEAN NOT NULL DEFAULT 0,
    parent_ca_id TEXT REFERENCES certificate_authorities(id),
    status TEXT NOT NULL CHECK(status IN ('active', 'revoked', 'expired')),
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
  );

  CREATE TABLE IF NOT EXISTS certificates (
    id TEXT PRIMARY KEY,
    ca_id TEXT NOT NULL REFERENCES certificate_authorities(id) ON DELETE CASCADE,
    subject_dn TEXT NOT NULL,
    serial_number TEXT NOT NULL UNIQUE,
    not_before INTEGER NOT NULL,
    not_after INTEGER NOT NULL,
    certificate_pem TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('active', 'revoked', 'expired')),
    revocation_date INTEGER,
    revocation_reason TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
  );

  CREATE INDEX IF NOT EXISTS idx_certificates_ca_id ON certificates(ca_id);
  CREATE INDEX IF NOT EXISTS idx_certificates_status ON certificates(status);
  CREATE INDEX IF NOT EXISTS idx_certificates_serial ON certificates(serial_number);
`);

查询

预处理语句

// SELECT query
const getUser = db.prepare('SELECT * FROM users WHERE id = ?');
const user = getUser.get('user-123');

// SELECT all
const getAllUsers = db.prepare('SELECT * FROM users');
const users = getAllUsers.all();

// SELECT with multiple parameters
const findUsers = db.prepare('SELECT * FROM users WHERE name LIKE ? AND created_at > ?');
const results = findUsers.all('%John%', 1640000000);

// Named parameters
const getUserByEmail = db.prepare('SELECT * FROM users WHERE email = @email');
const user = getUserByEmail.get({ email: '[email protected]' });

插入操作

// Single insert
const insertUser = db.prepare(`
  INSERT INTO users (id, name, email)
  VALUES (?, ?, ?)
`);

const info = insertUser.run('user-123', 'John Doe', '[email protected]');
console.log(`Inserted ${info.changes} rows, last ID: ${info.lastInsertRowid}`);

// Insert with RETURNING (SQLite 3.35+)
const insertUserReturning = db.prepare(`
  INSERT INTO users (id, name, email)
  VALUES (?, ?, ?)
  RETURNING *
`);

const newUser = insertUserReturning.get('user-456', 'Jane Doe', '[email protected]');
console.log('Created user:', newUser);

// Bulk insert (fast)
const insert = db.prepare('INSERT INTO users (id, name, email) VALUES (?, ?, ?)');
const insertMany = db.transaction((users) => {
  for (const user of users) {
    insert.run(user.id, user.name, user.email);
  }
});

insertMany([
  { id: '1', name: 'Alice', email: '[email protected]' },
  { id: '2', name: 'Bob', email: '[email protected]' },
  { id: '3', name: 'Charlie', email: '[email protected]' },
]);

更新操作

// Update
const updateUser = db.prepare(`
  UPDATE users
  SET name = ?, email = ?
  WHERE id = ?
`);

const info = updateUser.run('John Smith', '[email protected]', 'user-123');
console.log(`Updated ${info.changes} rows`);

// Update with RETURNING
const updateReturning = db.prepare(`
  UPDATE users
  SET name = ?
  WHERE id = ?
  RETURNING *
`);

const updatedUser = updateReturning.get('New Name', 'user-123');

删除操作

// Delete
const deleteUser = db.prepare('DELETE FROM users WHERE id = ?');
const info = deleteUser.run('user-123');
console.log(`Deleted ${info.changes} rows`);

// Delete with condition
const deleteOldUsers = db.prepare(`
  DELETE FROM users
  WHERE created_at < ?
`);

const info = deleteOldUsers.run(Date.now() - 86400000); // 24 hours ago

事务

基本事务

// Define transaction
const transferFunds = db.transaction((fromId, toId, amount) => {
  const debit = db.prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?');
  const credit = db.prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?');

  debit.run(amount, fromId);
  credit.run(amount, toId);
});

// Execute transaction (atomic)
transferFunds('account-1', 'account-2', 100);

复杂事务

const createOrder = db.transaction((order, items) => {
  // Insert order
  const insertOrder = db.prepare(`
    INSERT INTO orders (id, user_id, total)
    VALUES (?, ?, ?)
    RETURNING *
  `);

  const newOrder = insertOrder.get(order.id, order.userId, order.total);

  // Insert order items
  const insertItem = db.prepare(`
    INSERT INTO order_items (order_id, product_id, quantity, price)
    VALUES (?, ?, ?, ?)
  `);

  for (const item of items) {
    insertItem.run(newOrder.id, item.productId, item.quantity, item.price);
  }

  // Update inventory
  const updateInventory = db.prepare(`
    UPDATE products
    SET stock = stock - ?
    WHERE id = ?
  `);

  for (const item of items) {
    updateInventory.run(item.quantity, item.productId);
  }

  return newOrder;
});

// Use transaction
const order = createOrder(
  { id: 'order-1', userId: 'user-1', total: 150.00 },
  [
    { productId: 'prod-1', quantity: 2, price: 50.00 },
    { productId: 'prod-2', quantity: 1, price: 50.00 },
  ]
);

事务选项

// Immediate transaction (lock immediately)
const immediateTransaction = db.transaction((data) => {
  // Operations
});
immediateTransaction.immediate(); // Optional: make it immediate

// Deferred transaction (default)
const deferredTransaction = db.transaction((data) => {
  // Operations
});
deferredTransaction.deferred(); // Optional: make it deferred

// Exclusive transaction
const exclusiveTransaction = db.transaction((data) => {
  // Operations
});
exclusiveTransaction.exclusive(); // Lock database exclusively

高级查询

联接

const getUsersWithOrders = db.prepare(`
  SELECT
    u.id, u.name, u.email,
    COUNT(o.id) as order_count,
    SUM(o.total) as total_spent
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id
`);

const results = getUsersWithOrders.all();

子查询

const getTopCustomers = db.prepare(`
  SELECT *
  FROM users
  WHERE id IN (
    SELECT user_id
    FROM orders
    GROUP BY user_id
    HAVING SUM(total) > ?
  )
`);

const topCustomers = getTopCustomers.all(1000);

全文搜索（FTS5）

// Create FTS table
db.exec(`
  CREATE VIRTUAL TABLE documents_fts USING fts5(
    title,
    content,
    content=documents,
    content_rowid=id
  );

  -- Populate FTS index
  INSERT INTO documents_fts(rowid, title, content)
  SELECT id, title, content FROM documents;
`);

// Search
const search = db.prepare(`
  SELECT *
  FROM documents d
  JOIN documents_fts fts ON d.id = fts.rowid
  WHERE documents_fts MATCH ?
  ORDER BY rank
`);

const results = search.all('security AND encryption');

JSON 操作（SQLite 3.38+）

// Store JSON
const insertWithJson = db.prepare(`
  INSERT INTO users (id, name, metadata)
  VALUES (?, ?, json(?))
`);

insertWithJson.run('user-1', 'John', JSON.stringify({ role: 'admin', age: 30 }));

// Query JSON
const getAdmins = db.prepare(`
  SELECT *
  FROM users
  WHERE json_extract(metadata, '$.role') = 'admin'
`);

const admins = getAdmins.all();

TypeScript 集成

类型安全的查询

import Database from 'better-sqlite3';

interface User {
  id: string;
  name: string;
  email: string;
  created_at: number;
}

const db = new Database('mydb.sqlite');

// Type-safe prepared statements
const getUserById = db.prepare<[string], User>('SELECT * FROM users WHERE id = ?');
const user: User | undefined = getUserById.get('user-123');

const getAllUsers = db.prepare<[], User>('SELECT * FROM users');
const users: User[] = getAllUsers.all();

// Insert with types
interface InsertUser {
  id: string;
  name: string;
  email: string;
}

const insertUser = db.prepare<[string, string, string]>(`
  INSERT INTO users (id, name, email)
  VALUES (?, ?, ?)
`);

function createUser(user: InsertUser) {
  return insertUser.run(user.id, user.name, user.email);
}

数据库类封装

import Database from 'better-sqlite3';

export class DatabaseClient {
  private db: Database.Database;

  constructor(filename: string) {
    this.db = new Database(filename);
    this.db.pragma('journal_mode = WAL');
    this.db.pragma('foreign_keys = ON');
  }

  getUserById(id: string): User | undefined {
    const stmt = this.db.prepare<[string], User>('SELECT * FROM users WHERE id = ?');
    return stmt.get(id);
  }

  createUser(user: InsertUser): User {
    const stmt = this.db.prepare<[string, string, string]>(`
      INSERT INTO users (id, name, email)
      VALUES (?, ?, ?)
      RETURNING *
    `);
    return stmt.get(user.id, user.name, user.email)!;
  }

  close() {
    this.db.close();
  }
}

迁移

手动迁移

const migrations = [
  // Migration 1
  `CREATE TABLE users (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
  )`,

  // Migration 2
  `ALTER TABLE users ADD COLUMN created_at INTEGER DEFAULT (unixepoch())`,

  // Migration 3
  `CREATE INDEX idx_users_email ON users(email)`,
];

function migrate(db: Database.Database) {
  // Create migrations table
  db.exec(`
    CREATE TABLE IF NOT EXISTS migrations (
      id INTEGER PRIMARY KEY,
      applied_at INTEGER NOT NULL DEFAULT (unixepoch())
    )
  `);

  const getCurrentVersion = db.prepare('SELECT MAX(id) as version FROM migrations');
  const currentVersion = (getCurrentVersion.get() as any).version || 0;

  const insertMigration = db.prepare('INSERT INTO migrations (id) VALUES (?)');

  // Run pending migrations
  const runMigrations = db.transaction(() => {
    for (let i = currentVersion; i < migrations.length; i++) {
      console.log(`Running migration ${i + 1}`);
      db.exec(migrations[i]);
      insertMigration.run(i + 1);
    }
  });

  runMigrations();
}

// Run migrations
migrate(db);

性能优化

索引

// Create indexes for frequently queried columns
db.exec(`
  CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
  CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
  CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);

  -- Composite index
  CREATE INDEX IF NOT EXISTS idx_orders_user_status
    ON orders(user_id, status);

  -- Partial index (SQLite 3.8+)
  CREATE INDEX IF NOT EXISTS idx_active_users
    ON users(email) WHERE status = 'active';
`);

// Analyze query performance
db.exec('ANALYZE');

查询优化

// Use EXPLAIN QUERY PLAN
const plan = db.prepare('EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = ?');
console.log(plan.all('[email protected]'));

// Batch operations in transactions
const insertMany = db.transaction((users) => {
  const insert = db.prepare('INSERT INTO users (id, name, email) VALUES (?, ?, ?)');
  for (const user of users) {
    insert.run(user.id, user.name, user.email);
  }
});

// This is ~1000x faster than individual inserts
insertMany(largeUserArray);

连接设置

// Optimize for performance
db.pragma('cache_size = -64000'); // 64MB cache
db.pragma('temp_store = MEMORY');
db.pragma('mmap_size = 30000000000'); // 30GB memory-mapped I/O
db.pragma('page_size = 4096'); // Match OS page size

// Check settings
console.log(db.pragma('cache_size', { simple: true }));
console.log(db.pragma('page_size', { simple: true }));

备份与恢复

备份数据库

import fs from 'fs';

// Simple file copy (database must be closed or in WAL mode)
function backupDatabase(source: string, dest: string) {
  fs.copyFileSync(source, dest);
  // Also copy WAL and SHM files if they exist
  if (fs.existsSync(`${source}-wal`)) {
    fs.copyFileSync(`${source}-wal`, `${dest}-wal`);
  }
  if (fs.existsSync(`${source}-shm`)) {
    fs.copyFileSync(`${source}-shm`, `${dest}-shm`);
  }
}

// Online backup using VACUUM INTO (SQLite 3.27+)
function vacuumBackup(db: Database.Database, dest: string) {
  db.prepare(`VACUUM INTO ?`).run(dest);
}

// Export to SQL
function exportToSql(db: Database.Database, filename: string) {
  const tables = db.prepare(`
    SELECT name FROM sqlite_master
    WHERE type='table' AND name NOT LIKE 'sqlite_%'
  `).all() as { name: string }[];

  let sql = '';
  for (const { name } of tables) {
    // Get CREATE statement
    const createStmt = db.prepare(`
      SELECT sql FROM sqlite_master WHERE name = ?
    `).get(name) as { sql: string };
    sql += createStmt.sql + ';\n\n';

    // Get data
    const rows = db.prepare(`SELECT * FROM ${name}`).all();
    for (const row of rows) {
      const values = Object.values(row).map(v =>
        typeof v === 'string' ? `'${v.replace(/'/g, "''")}'` : v
      ).join(', ');
      sql += `INSERT INTO ${name} VALUES (${values});\n`;
    }
    sql += '\n';
  }

  fs.writeFileSync(filename, sql);
}

错误处理

import Database from 'better-sqlite3';

try {
  const result = db.prepare('INSERT INTO users (id, email) VALUES (?, ?)').run('1', '[email protected]');
} catch (error) {
  if (error instanceof Database.SqliteError) {
    switch (error.code) {
      case 'SQLITE_CONSTRAINT_UNIQUE':
        console.error('Unique constraint violation');
        break;
      case 'SQLITE_CONSTRAINT_FOREIGNKEY':
        console.error('Foreign key constraint violation');
        break;
      default:
        console.error('Database error:', error.message);
    }
  }
}

测试

import Database from 'better-sqlite3';

// Use in-memory database for tests
let testDb: Database.Database;

beforeEach(() => {
  testDb = new Database(':memory:');
  testDb.pragma('foreign_keys = ON');

  // Setup schema
  testDb.exec(`
    CREATE TABLE users (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      email TEXT NOT NULL UNIQUE
    )
  `);
});

afterEach(() => {
  testDb.close();
});

test('creates user', () => {
  const insert = testDb.prepare('INSERT INTO users VALUES (?, ?, ?)');
  const info = insert.run('1', 'John', '[email protected]');

  expect(info.changes).toBe(1);

  const user = testDb.prepare('SELECT * FROM users WHERE id = ?').get('1');
  expect(user).toEqual({ id: '1', name: 'John', email: '[email protected]' });
});

最佳实践

1. 使用 WAL 模式：通过 journal_mode = WAL 提升并发性能
2. 启用外键：始终设置 foreign_keys = ON
3. 使用事务：将批量操作放入事务中以提升性能
4. 预处理语句：对频繁执行的查询重复使用预处理语句
5. 有策略地创建索引：为 WHERE、JOIN、ORDER BY 中使用的列创建索引
6. 定期执行 VACUUM：定期运行 VACUUM 进行碎片整理
7. 定期备份：实施自动备份策略
8. 监控大小：SQLite 在 1 TB 以下运行效果最佳
9. 连接池：每个进程使用单个连接（better-sqlite3 是同步的）
10. 错误处理：妥善处理约束冲突

常见模式

仓储模式

export class UserRepository {
  private db: Database.Database;

  private getByIdStmt: Database.Statement<[string]>;
  private getAllStmt: Database.Statement<[]>;
  private insertStmt: Database.Statement<[string, string, string]>;
  private updateStmt: Database.Statement<[string, string, string]>;
  private deleteStmt: Database.Statement<[string]>;

  constructor(db: Database.Database) {
    this.db = db;

    // Prepare statements once
    this.getByIdStmt = db.prepare('SELECT * FROM users WHERE id = ?');
    this.getAllStmt = db.prepare('SELECT * FROM users');
    this.insertStmt = db.prepare('INSERT INTO users (id, name, email) VALUES (?, ?, ?) RETURNING *');
    this.updateStmt = db.prepare('UPDATE users SET name = ?, email = ? WHERE id = ? RETURNING *');
    this.deleteStmt = db.prepare('DELETE FROM users WHERE id = ?');
  }

  findById(id: string): User | undefined {
    return this.getByIdStmt.get(id) as User | undefined;
  }

  findAll(): User[] {
    return this.getAllStmt.all() as User[];
  }

  create(user: Omit<User, 'created_at'>): User {
    return this.insertStmt.get(user.id, user.name, user.email) as User;
  }

  update(id: string, data: Partial<User>): User | undefined {
    return this.updateStmt.get(data.name, data.email, id) as User | undefined;
  }

  delete(id: string): boolean {
    const info = this.deleteStmt.run(id);
    return info.changes > 0;
  }
}

资源

• SQLite 文档：https://www.sqlite.org/docs.html
• better-sqlite3 文档：https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md
• SQLite 教程：https://www.sqlitetutorial.net/
• 性能提示：https://www.sqlite.org/performance.html