Bun SQLite 数据库 #

import { Database } from "bun:sqlite";

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

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

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

// 创建文件（如果不存在）
const db = new Database("./mydb.sqlite", { create: true });

import { Database } from "bun:sqlite";

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

// 创建表
db.run(`
  CREATE TABLE 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 (?, ?)", ["Alice", "alice@example.com"]);
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Bob", "bob@example.com"]);

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

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

// 创建查询对象
const query = db.query("SELECT * FROM users WHERE id = ?");

// 获取所有结果
const all = query.all(1);

// 获取单条结果
const one = query.get(1);

// 获取第一个值
const value = query.values(1);

// 释放查询
query.finalize();

// 执行不返回结果的语句
db.run("CREATE TABLE test (id INTEGER, name TEXT)");

// 插入数据
const result = db.run("INSERT INTO users (name) VALUES (?)", ["Charlie"]);
console.log(result.lastInsertRowid);  // 最后插入的 ID
console.log(result.changes);          // 影响的行数

const users = db.query("SELECT * FROM users").all();
// [{ id: 1, name: "Alice", ... }, { id: 2, name: "Bob", ... }]

const user = db.query("SELECT * FROM users WHERE id = ?").get(1);
// { id: 1, name: "Alice", ... }

// 如果没有结果返回 null
const notFound = db.query("SELECT * FROM users WHERE id = ?").get(999);
// null

const names = db.query("SELECT name FROM users").values();
// [["Alice"], ["Bob"], ["Charlie"]]

// 使用 ? 占位符
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);

// 查询
const user = db.query("SELECT * FROM users WHERE id = ?").get(1);

// 使用 $name 或 :name 或 @name
db.run(
  "INSERT INTO users (name, email) VALUES ($name, $email)",
  { $name: "Alice", $email: "alice@example.com" }
);

// 查询
const user = db.query("SELECT * FROM users WHERE name = $name").get({ $name: "Alice" });

// 支持的类型
db.run("INSERT INTO test VALUES (?, ?, ?, ?, ?)", [
  123,              // INTEGER
  3.14,             // REAL
  "hello",          // TEXT
  new Uint8Array([1, 2, 3]),  // BLOB
  null,             // NULL
]);

// 预编译语句性能更好
const insertStmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
const selectStmt = db.prepare("SELECT * FROM users WHERE id = ?");

// 使用
insertStmt.run("Alice", "alice@example.com");
const user = selectStmt.get(1);

const stmt = db.prepare("SELECT * FROM users WHERE id = ?");

// 运行（不返回结果）
stmt.run(1);

// 获取单条
const user = stmt.get(1);

// 获取所有
const users = stmt.all();

// 获取值
const values = stmt.values();

// 迭代
for (const user of stmt.iterate()) {
  console.log(user);
}

// 手动事务
db.run("BEGIN TRANSACTION");

try {
  db.run("INSERT INTO users (name) VALUES (?)", ["User1"]);
  db.run("INSERT INTO users (name) VALUES (?)", ["User2"]);
  db.run("COMMIT");
} catch (error) {
  db.run("ROLLBACK");
  throw error;
}

// 使用 transaction 方法
const insertMany = db.transaction((users) => {
  const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
  for (const user of users) {
    stmt.run(user.name, user.email);
  }
});

// 执行事务
insertMany([
  { name: "User1", email: "user1@example.com" },
  { name: "User2", email: "user2@example.com" },
]);

const outer = db.transaction((data) => {
  const inner = db.transaction((items) => {
    for (const item of items) {
      db.run("INSERT INTO items (name) VALUES (?)", [item]);
    }
  });
  
  inner(data.items);
  db.run("UPDATE stats SET count = count + ?", [data.items.length]);
});

outer({ items: ["a", "b", "c"] });

const tx = db.transaction((data) => {
  // 事务代码
}, {
  // 事务类型
  type: "deferred",  // deferred, immediate, exclusive
});

tx(data);

// 存储日期
db.run("INSERT INTO events (created_at) VALUES (?)", [new Date().toISOString()]);

// 读取日期
const row = db.query("SELECT created_at FROM events").get();
const date = new Date(row.created_at as string);

// 存储 JSON
db.run("INSERT INTO config (data) VALUES (?)", [JSON.stringify({ key: "value" })]);

// 读取 JSON
const config = db.query("SELECT data FROM config").get();
const data = JSON.parse(config.data as string);

db.run(`
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    description TEXT,
    category_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
  )
`);

// 创建索引
db.run("CREATE INDEX IF NOT EXISTS idx_products_name ON products(name)");
db.run("CREATE INDEX IF NOT EXISTS idx_products_category ON products(category_id)");

// 添加列
db.run("ALTER TABLE users ADD COLUMN age INTEGER");

// 重命名表
db.run("ALTER TABLE users RENAME TO members");

// 重命名列
db.run("ALTER TABLE users RENAME COLUMN name TO username");

db.run("DROP TABLE IF EXISTS temp_table");
db.run("DROP INDEX IF EXISTS idx_name");

function getPaginated(page: number, limit: number) {
  const offset = (page - 1) * limit;
  
  const items = db.query(`
    SELECT * FROM products
    ORDER BY created_at DESC
    LIMIT ? OFFSET ?
  `).all(limit, offset);
  
  const total = db.query("SELECT COUNT(*) as count FROM products").get() as { count: number };
  
  return {
    items,
    total: total.count,
    page,
    limit,
    totalPages: Math.ceil(total.count / limit),
  };
}

function searchProducts(keyword: string) {
  return db.query(`
    SELECT * FROM products
    WHERE name LIKE ? OR description LIKE ?
    ORDER BY name
  `).all(`%${keyword}%`, `%${keyword}%`);
}

// 统计
const stats = db.query(`
  SELECT 
    COUNT(*) as total,
    AVG(price) as avg_price,
    MIN(price) as min_price,
    MAX(price) as max_price,
    SUM(price) as total_price
  FROM products
`).get();

// 分组统计
const byCategory = db.query(`
  SELECT 
    category_id,
    COUNT(*) as count,
    AVG(price) as avg_price
  FROM products
  GROUP BY category_id
  HAVING count > 0
  ORDER BY count DESC
`).all();

// JOIN 查询
const orders = db.query(`
  SELECT 
    orders.id,
    orders.total,
    users.name as user_name,
    users.email as user_email
  FROM orders
  JOIN users ON orders.user_id = users.id
  WHERE orders.id = ?
`).get(1);

// 不推荐：每次都解析 SQL
for (const user of users) {
  db.run("INSERT INTO users (name) VALUES (?)", [user.name]);
}

// 推荐：使用预编译语句
const stmt = db.prepare("INSERT INTO users (name) VALUES (?)");
for (const user of users) {
  stmt.run(user.name);
}

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

const items = Array.from({ length: 10000 }, (_, i) => ({
  name: `Item ${i}`,
  value: i,
}));

bulkInsert(items);

// 启用 WAL 模式（Write-Ahead Logging）
db.run("PRAGMA journal_mode = WAL");

// 其他优化设置
db.run("PRAGMA synchronous = NORMAL");
db.run("PRAGMA cache_size = 10000");
db.run("PRAGMA temp_store = MEMORY");

// 创建索引
db.run("CREATE INDEX idx_users_email ON users(email)");
db.run("CREATE INDEX idx_orders_user_id ON orders(user_id)");
db.run("CREATE INDEX idx_orders_created_at ON orders(created_at)");

// 复合索引
db.run("CREATE INDEX idx_products_category_price ON products(category_id, price)");

// 分析查询计划
const plan = db.query("EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = ?").get("test@example.com");
console.log(plan);

try {
  db.run("INSERT INTO users (email) VALUES (?)", ["duplicate@example.com"]);
} catch (error) {
  if (error.message.includes("UNIQUE constraint failed")) {
    console.error("Email already exists");
  } else {
    throw error;
  }
}

import { Database, SQLiteError } from "bun:sqlite";

try {
  db.run("INVALID SQL");
} catch (error) {
  if (error instanceof SQLiteError) {
    console.error("SQLite Error:", error.message);
    console.error("Code:", error.code);
  }
}

// 备份到文件
import { copyFile } from "fs/promises";

await copyFile("./mydb.sqlite", "./mydb-backup.sqlite");

// 或使用 SQLite 命令
db.run("VACUUM INTO './backup.sqlite'");

// VACUUM - 重建数据库，释放空间
db.run("VACUUM");

// ANALYZE - 更新统计信息
db.run("ANALYZE");

// 检查完整性
const integrity = db.query("PRAGMA integrity_check").get();
console.log(integrity);

// 表列表
const tables = db.query("SELECT name FROM sqlite_master WHERE type='table'").all();

// 表结构
const schema = db.query("SELECT sql FROM sqlite_master WHERE name = ?").get("users");

// 数据库大小
const stats = require("fs").statSync("./mydb.sqlite");
console.log(`Database size: ${stats.size} bytes`);

import { Database } from "bun:sqlite";

class UserRepository {
  private db: Database;
  private insertStmt: ReturnType<Database["prepare"]>;
  private updateStmt: ReturnType<Database["prepare"]>;
  private deleteStmt: ReturnType<Database["prepare"]>;
  private findByIdStmt: ReturnType<Database["prepare"]>;
  private findAllStmt: ReturnType<Database["prepare"]>;

  constructor(dbPath: string = ":memory:") {
    this.db = new Database(dbPath);
    this.init();
    this.prepareStatements();
  }

  private init() {
    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,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);
  }

  private prepareStatements() {
    this.insertStmt = this.db.prepare(
      "INSERT INTO users (name, email) VALUES ($name, $email) RETURNING *"
    );
    this.updateStmt = this.db.prepare(
      "UPDATE users SET name = $name, email = $email, updated_at = CURRENT_TIMESTAMP WHERE id = $id RETURNING *"
    );
    this.deleteStmt = this.db.prepare("DELETE FROM users WHERE id = ?");
    this.findByIdStmt = this.db.prepare("SELECT * FROM users WHERE id = ?");
    this.findAllStmt = this.db.prepare("SELECT * FROM users ORDER BY created_at DESC");
  }

  create(name: string, email: string) {
    return this.insertStmt.get({ $name: name, $email: email });
  }

  update(id: number, name: string, email: string) {
    return this.updateStmt.get({ $id: id, $name: name, $email: email });
  }

  delete(id: number) {
    return this.deleteStmt.run(id);
  }

  findById(id: number) {
    return this.findByIdStmt.get(id);
  }

  findAll() {
    return this.findAllStmt.all();
  }

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

// 使用
const userRepo = new UserRepository();

const user = userRepo.create("Alice", "alice@example.com");
console.log(user);

const allUsers = userRepo.findAll();
console.log(allUsers);

userRepo.close();