Bun SQLite 数据库

Bun 内置了高性能的 SQLite 驱动，无需安装额外依赖。本章介绍 Bun SQLite 的使用方法。

快速开始

创建数据库

import { Database } from "bun:sqlite";

// 创建/打开数据库文件
const db = new Database("mydb.sqlite");

// 创建内存数据库
const memoryDb = new Database(":memory:");

// 只读模式
const readonlyDb = new Database("mydb.sqlite", { readonly: true });

基本操作

import { Database } from "bun:sqlite";

const db = new Database("users.db");

// 创建表
db.run(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// 插入数据
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["张三", "zhangsan@example.com"]);

// 查询数据
const users = db.query("SELECT * FROM users").all();
console.log(users);

// 关闭数据库
db.close();

查询方法

query() 方法

const db = new Database(":memory:");

// 创建表
db.run("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)");

// 插入数据
db.run("INSERT INTO products (name, price) VALUES (?, ?)", ["苹果", 5.5]);
db.run("INSERT INTO products (name, price) VALUES (?, ?)", ["香蕉", 3.0]);

// 获取所有结果
const allProducts = db.query("SELECT * FROM products").all();
console.log(allProducts);
// [{ id: 1, name: "苹果", price: 5.5 }, { id: 2, name: "香蕉", price: 3.0 }]

// 获取第一条结果
const firstProduct = db.query("SELECT * FROM products WHERE id = ?").get(1);
console.log(firstProduct);
// { id: 1, name: "苹果", price: 5.5 }

// 获取特定列的值
const names = db.query("SELECT name FROM products").values();
console.log(names);
// [["苹果"], ["香蕉"]]

run() 方法

// 执行修改操作
const result = db.run(
  "INSERT INTO products (name, price) VALUES (?, ?)",
  ["橙子", 4.0]
);

console.log(result.changes);      // 影响的行数: 1
console.log(result.lastInsertRowid); // 最后插入的 ID

预处理语句

创建语句

// 预处理语句（更高效）
const insertStmt = db.prepare(
  "INSERT INTO users (name, email) VALUES ($name, $email)"
);

// 多次使用
insertStmt.run({ $name: "张三", $email: "zhangsan@example.com" });
insertStmt.run({ $name: "李四", $email: "lisi@example.com" });
insertStmt.run({ $name: "王五", $email: "wangwu@example.com" });

参数绑定

// 位置参数 (?)
const stmt1 = db.prepare("SELECT * FROM users WHERE id = ?");
stmt1.get(1);

// 命名参数 ($name)
const stmt2 = db.prepare("SELECT * FROM users WHERE name = $name");
stmt2.get({ $name: "张三" });

// 命名参数 (:name)
const stmt3 = db.prepare("SELECT * FROM users WHERE name = :name");
stmt3.get({ name: "张三" });

语句复用

const selectByEmail = db.prepare(
  "SELECT * FROM users WHERE email = ?"
);

// 多次使用同一语句
const user1 = selectByEmail.get("zhangsan@example.com");
const user2 = selectByEmail.get("lisi@example.com");

// 释放语句
selectByEmail.finalize();

事务

基本事务

// 使用 transaction() 方法
const insertUsers = db.transaction((users: Array<{name: string, email: string}>) => {
  const insert = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
  
  for (const user of users) {
    insert.run(user.name, user.email);
  }
  
  return users.length;
});

// 执行事务
const count = insertUsers([
  { name: "用户1", email: "user1@example.com" },
  { name: "用户2", email: "user2@example.com" },
  { name: "用户3", email: "user3@example.com" },
]);

console.log(`插入了 ${count} 条记录`);

事务回滚

const transfer = db.transaction((from: number, to: number, amount: number) => {
  // 减少发送者余额
  const sender = db.query("SELECT balance FROM accounts WHERE id = ?").get(from);
  if (!sender || sender.balance < amount) {
    throw new Error("余额不足");
  }
  
  db.run("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, from]);
  db.run("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, to]);
  
  return { from, to, amount };
});

try {
  const result = transfer(1, 2, 100);
  console.log("转账成功:", result);
} catch (error) {
  console.log("转账失败，已回滚:", error.message);
}

嵌套事务（保存点）

const outer = db.transaction(() => {
  db.run("INSERT INTO logs (message) VALUES (?)", ["外层事务"]);
  
  const inner = db.transaction(() => {
    db.run("INSERT INTO logs (message) VALUES (?)", ["内层事务"]);
  });
  
  inner(); // 保存点
});

outer();

类型安全

定义接口

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

// 类型化查询
const users = db.query("SELECT * FROM users").all() as User[];

// 类型化单条查询
const user = db.query("SELECT * FROM users WHERE id = ?").get(1) as User | null;

if (user) {
  console.log(user.name); // 有类型提示
}

使用泛型

// 创建类型化的查询函数
function queryAll<T>(sql: string, params?: any[]): T[] {
  return db.query(sql).all(...(params || [])) as T[];
}

function queryOne<T>(sql: string, params?: any[]): T | null {
  return db.query(sql).get(...(params || [])) as T | null;
}

// 使用
const users = queryAll<User>("SELECT * FROM users");
const user = queryOne<User>("SELECT * FROM users WHERE id = ?", [1]);

高级特性

自定义函数

// 注册自定义 SQL 函数
db.function("upper_cn", (str: string) => {
  return str.toUpperCase();
});

// 使用自定义函数
const result = db.query("SELECT upper_cn(name) as name FROM users").all();

聚合函数

// 注册聚合函数
db.aggregate("json_array_agg", {
  start: () => [],
  step: (acc: any[], value: any) => {
    acc.push(value);
    return acc;
  },
  finalize: (acc: any[]) => JSON.stringify(acc),
});

// 使用聚合函数
const result = db.query("SELECT json_array_agg(name) FROM users").get();

WAL 模式

// 启用 WAL 模式（更好的并发性能）
const db = new Database("mydb.sqlite");
db.run("PRAGMA journal_mode = WAL");
db.run("PRAGMA synchronous = NORMAL");

外键约束

// 启用外键约束
db.run("PRAGMA foreign_keys = ON");

// 创建带外键的表
db.run(`
  CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
  )
`);

数据库迁移

简单迁移

// migrations.ts
const migrations = [
  // 版本 1
  `CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
  )`,
  
  // 版本 2
  `ALTER TABLE users ADD COLUMN email TEXT`,
  
  // 版本 3
  `CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)`,
];

function migrate(db: Database) {
  // 创建迁移表
  db.run(`
    CREATE TABLE IF NOT EXISTS migrations (
      id INTEGER PRIMARY KEY,
      version INTEGER UNIQUE,
      applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);
  
  // 获取当前版本
  const current = db.query("SELECT MAX(version) as version FROM migrations").get();
  const currentVersion = current?.version ?? 0;
  
  // 应用新迁移
  for (let i = currentVersion; i < migrations.length; i++) {
    console.log(`应用迁移 ${i + 1}...`);
    db.run(migrations[i]);
    db.run("INSERT INTO migrations (version) VALUES (?)", [i + 1]);
  }
  
  console.log("迁移完成");
}

const db = new Database("app.db");
migrate(db);

实际应用示例

ORM 风格封装

// orm.ts
import { Database } from "bun:sqlite";

class Model<T extends Record<string, any>> {
  constructor(
    private db: Database,
    private table: string
  ) {}

  all(): T[] {
    return this.db.query(`SELECT * FROM ${this.table}`).all() as T[];
  }

  find(id: number): T | null {
    return this.db.query(`SELECT * FROM ${this.table} WHERE id = ?`).get(id) as T | null;
  }

  where(conditions: Partial<T>): T[] {
    const keys = Object.keys(conditions);
    const where = keys.map(k => `${k} = ?`).join(" AND ");
    const values = Object.values(conditions);
    
    return this.db.query(`SELECT * FROM ${this.table} WHERE ${where}`).all(...values) as T[];
  }

  create(data: Omit<T, "id">): T {
    const keys = Object.keys(data);
    const placeholders = keys.map(() => "?").join(", ");
    const values = Object.values(data);
    
    const result = this.db.run(
      `INSERT INTO ${this.table} (${keys.join(", ")}) VALUES (${placeholders})`,
      values
    );
    
    return this.find(Number(result.lastInsertRowid))!;
  }

  update(id: number, data: Partial<Omit<T, "id">>): T | null {
    const keys = Object.keys(data);
    const set = keys.map(k => `${k} = ?`).join(", ");
    const values = [...Object.values(data), id];
    
    this.db.run(`UPDATE ${this.table} SET ${set} WHERE id = ?`, values);
    return this.find(id);
  }

  delete(id: number): boolean {
    const result = this.db.run(`DELETE FROM ${this.table} WHERE id = ?`, [id]);
    return result.changes > 0;
  }
}

// 使用
interface User {
  id: number;
  name: string;
  email: string;
}

const db = new Database("app.db");
const users = new Model<User>(db, "users");

// CRUD 操作
const newUser = users.create({ name: "张三", email: "zhangsan@example.com" });
const allUsers = users.all();
const user = users.find(1);
const updated = users.update(1, { name: "张三丰" });
const deleted = users.delete(1);

API 服务器示例

// api-with-db.ts
import { Database } from "bun:sqlite";

const db = new Database("api.db");

// 初始化数据库
db.run(`
  CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    completed INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

interface Todo {
  id: number;
  title: string;
  completed: number;
  created_at: string;
}

const server = Bun.serve({
  port: 3000,
  
  async fetch(request) {
    const url = new URL(request.url);
    const method = request.method;
    
    // GET /api/todos
    if (url.pathname === "/api/todos" && method === "GET") {
      const todos = db.query("SELECT * FROM todos ORDER BY created_at DESC").all();
      return Response.json(todos);
    }
    
    // POST /api/todos
    if (url.pathname === "/api/todos" && method === "POST") {
      const body = await request.json();
      const result = db.run(
        "INSERT INTO todos (title) VALUES (?)",
        [body.title]
      );
      
      const todo = db.query("SELECT * FROM todos WHERE id = ?")
        .get(Number(result.lastInsertRowid));
      
      return Response.json(todo, { status: 201 });
    }
    
    // PUT /api/todos/:id
    const putMatch = url.pathname.match(/^\/api\/todos\/(\d+)$/);
    if (putMatch && method === "PUT") {
      const id = parseInt(putMatch[1]);
      const body = await request.json();
      
      db.run(
        "UPDATE todos SET title = ?, completed = ? WHERE id = ?",
        [body.title, body.completed ? 1 : 0, id]
      );
      
      const todo = db.query("SELECT * FROM todos WHERE id = ?").get(id);
      
      if (!todo) {
        return Response.json({ error: "Not found" }, { status: 404 });
      }
      
      return Response.json(todo);
    }
    
    // DELETE /api/todos/:id
    const deleteMatch = url.pathname.match(/^\/api\/todos\/(\d+)$/);
    if (deleteMatch && method === "DELETE") {
      const id = parseInt(deleteMatch[1]);
      const result = db.run("DELETE FROM todos WHERE id = ?", [id]);
      
      if (result.changes === 0) {
        return Response.json({ error: "Not found" }, { status: 404 });
      }
      
      return new Response(null, { status: 204 });
    }
    
    return Response.json({ error: "Not found" }, { status: 404 });
  },
});

console.log(`服务器运行在 http://localhost:${server.port}`);

性能优化

批量插入

// 使用事务批量插入
const insertMany = db.transaction((items: any[]) => {
  const stmt = db.prepare("INSERT INTO items (name, value) VALUES (?, ?)");
  
  for (const item of items) {
    stmt.run(item.name, item.value);
  }
});

// 一次性插入 1000 条记录
const items = Array.from({ length: 1000 }, (_, i) => ({
  name: `Item ${i}`,
  value: i * 10,
}));

insertMany(items);

索引优化

// 为常用查询创建索引
db.run("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)");
db.run("CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id)");

// 复合索引
db.run("CREATE INDEX IF NOT EXISTS idx_orders_user_date ON orders(user_id, created_at)");

小结

本章介绍了：

• ✅ 创建和管理数据库
• ✅ 查询和修改数据
• ✅ 预处理语句
• ✅ 事务处理
• ✅ 类型安全
• ✅ 高级特性和自定义函数
• ✅ 实际应用示例

下一步

继续阅读 FFI 外部接口 了解如何调用原生代码。