技能檔案

Jutsu Bunbun Sqlite

Name: Jutsu Bunbun Sqlite
Author: majiayu000

Use when working with SQLite databases in Bun. Covers Bun's built-in SQLite driver, database operations, prepared statements, and transactions with high performance.

majiayu000200 星標2026年1月22日

職業
分類: SQL 數據庫

技能內容

Bun SQLite

Use this skill when working with SQLite databases using Bun's built-in, high-performance SQLite driver.

Key Concepts

Opening a Database

Bun includes a native SQLite driver:

import { Database } from "bun:sqlite";

// Open or create database
const db = new Database("mydb.sqlite");

// In-memory database
const memDb = new Database(":memory:");

// Read-only database
const readOnlyDb = new Database("mydb.sqlite", { readonly: true });

Basic Queries

Execute SQL queries:

import { Database } from "bun:sqlite";

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

// Create table
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
  )
`);

// Insert data
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "[email protected]"]);

// Query data
const users = db.query("SELECT * FROM users").all();
console.log(users);

// Close database
db.close();

相關技能

Jutsu Bunbun Sqlite | Skills Pool

import { Database } from "bun:sqlite";

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

// Prepare statement
const insertUser = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");

// Execute multiple times
insertUser.run("Alice", "[email protected]");
insertUser.run("Bob", "[email protected]");

// Prepared query
const findUser = db.prepare("SELECT * FROM users WHERE email = ?");
const user = findUser.get("[email protected]");

console.log(user);

// Good - Prepared statement
const stmt = db.prepare("SELECT * FROM users WHERE id = ?");
const user = stmt.get(userId);

// Bad - String interpolation (SQL injection risk)
const user = db.query(`SELECT * FROM users WHERE id = ${userId}`).get();

import { Database } from "bun:sqlite";

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

// Transaction with automatic rollback on error
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: "[email protected]" },
    { name: "Bob", email: "[email protected]" },
  ]);
  console.log("All users inserted");
} catch (error) {
  console.error("Transaction failed:", error);
}

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

// .all() - Get all rows
const allUsers = db.query("SELECT * FROM users").all();

// .get() - Get first row
const firstUser = db.query("SELECT * FROM users").get();

// .values() - Get array of arrays
const userValues = db.query("SELECT name, email FROM users").values();

// .run() - Execute without returning rows
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", "[email protected]");

  db.close();
} catch (error) {
  if (error instanceof Error) {
    console.error("Database error:", error.message);
  }
}

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

// Usage
const users = new UserRepository("mydb.sqlite");
const newUser = users.create({ name: "Alice", email: "[email protected]" });
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);
  }
});

// Insert 1000 users atomically
const users = Array.from({ length: 1000 }, (_, i) => ({
  name: `User ${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(`Migration ${name} already applied`);
      return;
    }

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

    migration();
    console.log(`Migration ${name} applied successfully`);
  }

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

// Usage
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;
  }
}

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

// Bad - SQL injection vulnerability
const userId = "1 OR 1=1";
const user = db.query(`SELECT * FROM users WHERE id = ${userId}`).get();

// Good - Use prepared statements
const stmt = db.prepare("SELECT * FROM users WHERE id = ?");
const user = stmt.get(userId);

// Bad - Database remains open
const db = new Database("mydb.sqlite");
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "[email protected]"]);

// Good - Close when done
const db = new Database("mydb.sqlite");
try {
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "[email protected]"]);
} finally {
  db.close();
}

// Bad - Unnecessary transaction
const insert = db.transaction(() => {
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "[email protected]"]);
});
insert();

// Good - Direct execution
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "[email protected]"]);

// Bad - Reparsing query each iteration
for (let i = 0; i < 1000; i++) {
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", [`User ${i}`, `user${i}@example.com`]);
}

// Good - Prepare once, execute many times
const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
for (let i = 0; i < 1000; i++) {
  stmt.run(`User ${i}`, `user${i}@example.com`);
}

Jutsu Bunbun Sqlite

Bun SQLite

Key Concepts

Opening a Database

Basic Queries

Jutsu Bunbun Sqlite

Bun SQLite

Key Concepts

Opening a Database

Basic Queries

Prepared Statements

Best Practices

Use Prepared Statements

Transactions

Query Methods

Error Handling

Common Patterns

CRUD Operations

Bulk Inserts with Transaction

Migrations

Query Builder Pattern

Anti-Patterns

Don't Use String Interpolation

Don't Forget to Close Database

Don't Use Transactions for Single Operations

Don't Reparse Queries

Postgres Patterns

Postgres Patterns

Database Migrations

Postgres Patterns

Postgres Patterns

Jpa Patterns

Jutsu Bunbun Sqlite

Bun SQLite

Key Concepts

Opening a Database

Basic Queries

Jutsu Bunbun Sqlite

Bun SQLite

Key Concepts

Opening a Database

Basic Queries

Prepared Statements

Best Practices

Use Prepared Statements

Transactions

Query Methods

Error Handling

Common Patterns

CRUD Operations

Bulk Inserts with Transaction

Migrations

Query Builder Pattern

Anti-Patterns

Don't Use String Interpolation

Don't Forget to Close Database

Don't Use Transactions for Single Operations

Don't Reparse Queries

Related Skills

Postgres Patterns

Postgres Patterns

Database Migrations

Postgres Patterns

Postgres Patterns

Jpa Patterns