name: bun-sqlite user-invocable: false description: 在Bun中使用SQLite数据库时使用。涵盖Bun的内置SQLite驱动程序、数据库操作、准备语句和高性能事务。 allowed-tools:

读取
写入
编辑
Bash
Grep
Glob

Bun SQLite数据库

使用这个技能来处理使用Bun内置的高性能SQLite驱动程序的SQLite数据库。

关键概念

打开数据库

Bun包含一个原生SQLite驱动程序：

import { Database } from "bun:sqlite";

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

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

// 只读数据库
const readOnlyDb = new Database("mydb.sqlite", { readonly: true });

基本查询

执行SQL查询：

import { Database } from "bun:sqlite";

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

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

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

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

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

准备语句

使用准备语句以获得更好的性能：

import { Database } from "bun:sqlite";

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

// 准备语句
const insertUser = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");

// 多次执行
insertUser.run("Alice", "alice@example.com");
insertUser.run("Bob", "bob@example.com");

// 准备查询
const findUser = db.prepare("SELECT * FROM users WHERE email = ?");
const user = findUser.get("alice@example.com");

console.log(user);

最佳实践

使用准备语句

准备语句更快且防止SQL注入：

// 好 - 准备语句
const stmt = db.prepare("SELECT * FROM users WHERE id = ?");
const user = stmt.get(userId);

// 坏 - 字符串插值（SQL注入风险）
const user = db.query(`SELECT * FROM users WHERE id = ${userId}`).get();

事务

使用事务进行原子操作：

import { Database } from "bun:sqlite";

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

// 事务，错误时自动回滚
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);
  }
});

try {
  insertUsers([
    { name: "Alice", email: "alice@example.com" },
    { name: "Bob", email: "bob@example.com" },
  ]);
  console.log("所有用户已插入");
} catch (error) {
  console.error("事务失败:", error);
}

查询方法

不同用例的不同方法：

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

// .all() - 获取所有行
const allUsers = db.query("SELECT * FROM users").all();

// .get() - 获取第一行
const firstUser = db.query("SELECT * FROM users").get();

// .values() - 获取数组的数组
const userValues = db.query("SELECT name, email FROM users").values();

// .run() - 执行但不返回行
db.run("DELETE FROM users WHERE id = ?", [userId]);

错误处理

正确处理数据库错误：

import { Database } from "bun:sqlite";

try {
  const db = new Database("mydb.sqlite");

  const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
  stmt.run("Alice", "alice@example.com");

  db.close();
} catch (error) {
  if (error instanceof Error) {
    console.error("数据库错误:", error.message);
  }
}

常见模式

CRUD操作

import { Database } from "bun:sqlite";

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

class UserRepository {
  private db: Database;

  constructor(dbPath: string) {
    this.db = new Database(dbPath);
    this.createTable();
  }

  private createTable() {
    this.db.run(`
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);
  }

  create(user: User): User {
    const stmt = this.db.prepare("INSERT INTO users (name, email) VALUES (?, ?) RETURNING *");
    return stmt.get(user.name, user.email) as User;
  }

  findById(id: number): User | null {
    const stmt = this.db.prepare("SELECT * FROM users WHERE id = ?");
    return (stmt.get(id) as User) || null;
  }

  findAll(): User[] {
    return this.db.query("SELECT * FROM users").all() as User[];
  }

  update(id: number, user: Partial<User>): User | null {
    const stmt = this.db.prepare(`
      UPDATE users
      SET name = COALESCE(?, name), email = COALESCE(?, email)
      WHERE id = ?
      RETURNING *
    `);
    return (stmt.get(user.name, user.email, id) as User) || null;
  }

  delete(id: number): boolean {
    const stmt = this.db.prepare("DELETE FROM users WHERE id = ?");
    const result = stmt.run(id);
    return result.changes > 0;
  }

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

// 使用
const users = new UserRepository("mydb.sqlite");
const newUser = users.create({ name: "Alice", email: "alice@example.com" });
console.log(newUser);

使用事务进行批量插入

import { Database } from "bun:sqlite";

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

const bulkInsert = db.transaction((items: Array<{ name: string; email: string }>) => {
  const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");

  for (const item of items) {
    stmt.run(item.name, item.email);
  }
});

// 原子性插入1000个用户
const users = Array.from({ length: 1000 }, (_, i) => ({
  name: `用户 ${i}`,
  email: `user${i}@example.com`,
}));

bulkInsert(users);

迁移

import { Database } from "bun:sqlite";

class DatabaseMigration {
  private db: Database;

  constructor(dbPath: string) {
    this.db = new Database(dbPath);
    this.initMigrationTable();
  }

  private initMigrationTable() {
    this.db.run(`
      CREATE TABLE IF NOT EXISTS migrations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);
  }

  private hasRun(name: string): boolean {
    const stmt = this.db.prepare("SELECT COUNT(*) as count FROM migrations WHERE name = ?");
    const result = stmt.get(name) as { count: number };
    return result.count > 0;
  }

  private recordMigration(name: string) {
    this.db.run("INSERT INTO migrations (name) VALUES (?)", [name]);
  }

  migrate(name: string, sql: string) {
    if (this.hasRun(name)) {
      console.log(`迁移 ${name} 已经应用`);
      return;
    }

    const migration = this.db.transaction(() => {
      this.db.run(sql);
      this.recordMigration(name);
    });

    migration();
    console.log(`迁移 ${name} 应用成功`);
  }

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

// 使用
const migration = new DatabaseMigration("mydb.sqlite");

migration.migrate(
  "001_create_users",
  `
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
  )
`
);

migration.migrate(
  "002_add_timestamps",
  `
  ALTER TABLE users ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP
`
);

migration.close();

查询构建器模式

import { Database } from "bun:sqlite";

class QueryBuilder<T> {
  private db: Database;
  private tableName: string;
  private whereClause: string[] = [];
  private whereValues: any[] = [];
  private limitValue?: number;
  private offsetValue?: number;

  constructor(db: Database, tableName: string) {
    this.db = db;
    this.tableName = tableName;
  }

  where(column: string, value: any): this {
    this.whereClause.push(`${column} = ?`);
    this.whereValues.push(value);
    return this;
  }

  limit(n: number): this {
    this.limitValue = n;
    return this;
  }

  offset(n: number): this {
    this.offsetValue = n;
    return this;
  }

  getAll(): T[] {
    let sql = `SELECT * FROM ${this.tableName}`;

    if (this.whereClause.length > 0) {
      sql += ` WHERE ${this.whereClause.join(" AND ")}`;
    }

    if (this.limitValue) {
      sql += ` LIMIT ${this.limitValue}`;
    }

    if (this.offsetValue) {
      sql += ` OFFSET ${this.offsetValue}`;
    }

    const stmt = this.db.prepare(sql);
    return stmt.all(...this.whereValues) as T[];
  }

  getOne(): T | null {
    let sql = `SELECT * FROM ${this.tableName}`;

    if (this.whereClause.length > 0) {
      sql += ` WHERE ${this.whereClause.join(" AND ")}`;
    }

    sql += " LIMIT 1";

    const stmt = this.db.prepare(sql);
    return (stmt.get(...this.whereValues) as T) || null;
  }
}

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

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

const query = new QueryBuilder<User>(db, "users");
const users = query.where("name", "Alice").limit(10).getAll();
console.log(users);

反模式

不要使用字符串插值

// 坏 - SQL注入漏洞
const userId = "1 OR 1=1";
const user = db.query(`SELECT * FROM users WHERE id = ${userId}`).get();

// 好 - 使用准备语句
const stmt = db.prepare("SELECT * FROM users WHERE id = ?");
const user = stmt.get(userId);

不要忘记关闭数据库

// 坏 - 数据库保持打开
const db = new Database("mydb.sqlite");
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);

// 好 - 完成后关闭
const db = new Database("mydb.sqlite");
try {
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);
} finally {
  db.close();
}

不要对单个操作使用事务

// 坏 - 不必要的事务
const insert = db.transaction(() => {
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);
});
insert();

// 好 - 直接执行
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);

不要重复解析查询

// 坏 - 每次迭代都重新解析查询
for (let i = 0; i < 1000; i++) {
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", [`用户 ${i}`, `user${i}@example.com`]);
}

// 好 - 准备一次，执行多次
const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
for (let i = 0; i < 1000; i++) {
  stmt.run(`用户 ${i}`, `user${i}@example.com`);
}

BunSQLite数据库操作技能Skill bun-sqlite