SQL

db.ts複製

import { sql, SQL } from "bun";

// PostgreSQL（預設）
const users = await sql`
  SELECT * FROM users
  WHERE active = ${true}
  LIMIT ${10}
`;

// 使用 MySQL
const mysql = new SQL("mysql://user:pass@localhost:3306/mydb");
const mysqlResults = await mysql`
  SELECT * FROM users 
  WHERE active = ${true}
`;

// 使用 SQLite
const sqlite = new SQL("sqlite://myapp.db");
const sqliteResults = await sqlite`
  SELECT * FROM users 
  WHERE active = ${1}
`;

db.ts複製

import { sql } from "bun";
// 當未設定 DATABASE_URL 或為 PostgreSQL URL 時使用 PostgreSQL
await sql`SELECT ...`;

import { SQL } from "bun";
const pg = new SQL("postgres://user:pass@localhost:5432/mydb");
await pg`SELECT ...`;

db.ts複製

import { SQL } from "bun";

// MySQL 連線
const mysql = new SQL("mysql://user:password@localhost:3306/database");
const mysql2 = new SQL("mysql2://user:password@localhost:3306/database"); // mysql2 協議同樣支援

// 使用選項物件
const mysql3 = new SQL({
  adapter: "mysql",
  hostname: "localhost",
  port: 3306,
  database: "myapp",
  username: "dbuser",
  password: "secretpass",
});

// 支援引數 — 會自動使用預處理語句
const users = await mysql`SELECT * FROM users WHERE id = ${userId}`;

// 事務和 PostgreSQL 使用方法相同
await mysql.begin(async tx => {
  await tx`INSERT INTO users (name) VALUES (${"Alice"})`;
  await tx`UPDATE accounts SET balance = balance - 100 WHERE user_id = ${userId}`;
});

// 批次插入
const newUsers = [
  { name: "Alice", email: "alice@example.com" },
  { name: "Bob", email: "bob@example.com" },
];
await mysql`INSERT INTO users ${mysql(newUsers)}`;

import { SQL } from "bun";

// 記憶體資料庫
const memory = new SQL(":memory:");
const memory2 = new SQL("sqlite://:memory:");

// 檔案資料庫
const sql1 = new SQL("sqlite://myapp.db");

// 使用選項物件
const sql2 = new SQL({
  adapter: "sqlite",
  filename: "./data/app.db",
});

// 簡單檔名必須明確告知介面卡
const sql3 = new SQL("myapp.db", { adapter: "sqlite" });

import { sql } from "bun";

// 基礎插入，直接傳值
const [user] = await sql`
  INSERT INTO users (name, email) 
  VALUES (${name}, ${email})
  RETURNING *
`;

// 使用物件輔助，語法更簡潔
const userData = {
  name: "Alice",
  email: "alice@example.com",
};

const [newUser] = await sql`
  INSERT INTO users ${sql(userData)}
  RETURNING *
`;
// 展開為：INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')

const users = [
  { name: "Alice", email: "alice@example.com" },
  { name: "Bob", email: "bob@example.com" },
  { name: "Charlie", email: "charlie@example.com" },
];

await sql`INSERT INTO users ${sql(users)}`;

const user = {
  name: "Alice",
  email: "alice@example.com",
  age: 25,
};

await sql`INSERT INTO users ${sql(user, "name", "email")}`;
// 僅插入 name 和 email 列，其餘欄位忽略

const rows = await sql`SELECT * FROM users`.values();
console.log(rows);

[
  ["Alice", "alice@example.com"],
  ["Bob", "bob@example.com"],
];

const rows = await sql`SELECT * FROM users`.raw();
console.log(rows); // [[Buffer, Buffer], [Buffer, Buffer], [Buffer, Buffer]]

// 安全動態引用表名
await sql`SELECT * FROM ${sql("users")}`;

// 帶模式限定
await sql`SELECT * FROM ${sql("public.users")}`;

// 可選 WHERE 子句
const filterAge = true;
const minAge = 21;
const ageFilter = sql`AND age > ${minAge}`;
await sql`
  SELECT * FROM users
  WHERE active = ${true}
  ${filterAge ? ageFilter : sql``}
`;

await sql`UPDATE users SET ${sql(user, "name", "email")} WHERE id = ${user.id}`;
// 使用物件所有鍵更新
await sql`UPDATE users SET ${sql(user)} WHERE id = ${user.id}`;

await sql`SELECT * FROM users WHERE id IN ${sql([1, 2, 3])}`;

const users = [
  { id: 1, name: "Alice" },
  { id: 2, name: "Bob" },
  { id: 3, name: "Charlie" },
];
await sql`SELECT * FROM users WHERE id IN ${sql(users, "id")}`;

// PostgreSQL 陣列字面量
await sql`INSERT INTO tags (items) VALUES (${sql.array(["red", "blue", "green"])})`;
// 生成：INSERT INTO tags (items) VALUES (ARRAY['red', 'blue', 'green'])

// 也適用於數值陣列
await sql`SELECT * FROM products WHERE ids = ANY(${sql.array([1, 2, 3])})`;
// 生成：SELECT * FROM products WHERE ids = ANY(ARRAY[1, 2, 3])

// 一次執行多條語句
await sql`
  SELECT 1;
  SELECT 2;
`.simple();

const result = await sql.file("query.sql", [1, 2, 3]);

// 多條命令無引數
const result = await sql.unsafe(`
  SELECT ${userColumns} FROM users;
  SELECT ${accountColumns} FROM accounts;
`);

// 帶引數（只支援單條命令）
const result = await sql.unsafe("SELECT " + dangerous + " FROM users WHERE id = $1", [id]);

const sql = new SQL({ adapter: "sqlite", filename: "myapp.db", strict: true });
const result = await sql.unsafe("SELECT * FROM users WHERE id = :id", { id: 1 });

const query = sql`SELECT * FROM users`.execute();
setTimeout(() => query.cancel(), 100);
await query;

// 以下均自動使用 MySQL（無需指定介面卡）
const sql1 = new SQL("mysql://user:pass@localhost/mydb");
const sql2 = new SQL("mysql2://user:pass@localhost:3306/mydb");

// 也支援通過環境變數 DATABASE_URL
DATABASE_URL="mysql://user:pass@localhost/mydb" bun run app.js
DATABASE_URL="mysql2://user:pass@localhost:3306/mydb" bun run app.js

// 以下均自動使用 SQLite
const sql1 = new SQL(":memory:");
const sql2 = new SQL("sqlite://app.db");
const sql3 = new SQL("file://./database.db");

// 也支援環境變數 DATABASE_URL
DATABASE_URL=":memory:" bun run app.js
DATABASE_URL="sqlite://myapp.db" bun run app.js
DATABASE_URL="file://./data/app.db" bun run app.js

# PostgreSQL 檢測範例
DATABASE_URL="postgres://user:pass@localhost:5432/mydb" bun run app.js
DATABASE_URL="postgresql://user:pass@localhost:5432/mydb" bun run app.js

# 或者任何其他不匹配 MySQL 和 SQLite 的 URL
DATABASE_URL="localhost:5432/mydb" bun run app.js

# 優先使用的連線 URL
MYSQL_URL="mysql://user:pass@localhost:3306/mydb"

# 相容 DATABASE_URL 格式（MySQL 協議）
DATABASE_URL="mysql://user:pass@localhost:3306/mydb"
DATABASE_URL="mysql2://user:pass@localhost:3306/mydb"

# SQLite 連線範例
DATABASE_URL=":memory:"
DATABASE_URL="sqlite://./app.db"
DATABASE_URL="file:///absolute/path/to/db.sqlite"

# 啟用 PostgreSQL 預連線
bun --sql-preconnect index.js

# 配合 DATABASE_URL 環境變數使用
DATABASE_URL=postgres://user:pass@localhost:5432/db bun --sql-preconnect index.js

# 可與其他執行環境引數結合
bun --sql-preconnect --hot index.js

import { SQL } from "bun";

const sql = new SQL({
  // MySQL 必須指定
  adapter: "mysql",

  // 連線設定
  hostname: "localhost",
  port: 3306,
  database: "myapp",
  username: "dbuser",
  password: "secretpass",

  // Unix 套接字連線（替代 hostname/port）
  // socket: "/var/run/mysqld/mysqld.sock",

  // 連線池設定
  max: 20, // 最大連線數（預設10）
  idleTimeout: 30, // 空閒連線 30 秒後關閉
  maxLifetime: 0, // 連線最大存活時間（秒），0 表示無限
  connectionTimeout: 30, // 建立連線超時（秒）

  // SSL/TLS 選項
  ssl: "prefer", // 可設 "disable", "require", "verify-ca", "verify-full"
  // tls: {
  //   rejectUnauthorized: true,
  //   ca: "path/to/ca.pem",
  //   key: "path/to/key.pem",
  //   cert: "path/to/cert.pem",
  // },

  // 回撥函式
  onconnect: client => {
    console.log("已連線到 MySQL");
  },
  onclose: (client, err) => {
    if (err) {
      console.error("MySQL 連線錯誤:", err);
    } else {
      console.log("MySQL 連線關閉");
    }
  },
});

import { SQL } from "bun";

const sql = new SQL({
  // 連線設定（介面卡自動預設為 PostgreSQL）
  url: "postgres://user:pass@localhost:5432/dbname",

  // 可替代設定
  hostname: "localhost",
  port: 5432,
  database: "myapp",
  username: "dbuser",
  password: "secretpass",

  // 連線池設定
  max: 20, // 最大連線數
  idleTimeout: 30, // 空閒連線 30 秒關閉
  maxLifetime: 0, // 連線最大存活時間，0 表示無限
  connectionTimeout: 30, // 建立連線超時

  // SSL/TLS 選項
  tls: true,
  // tls: {
  //   rejectUnauthorized: true,
  //   requestCert: true,
  //   ca: "path/to/ca.pem",
  //   key: "path/to/key.pem",
  //   cert: "path/to/cert.pem",
  //   checkServerIdentity(hostname, cert) {
  //     ...
  //   },
  // },

  // 回撥函式
  onconnect: client => {
    console.log("已連線到 PostgreSQL");
  },
  onclose: client => {
    console.log("PostgreSQL 連線關閉");
  },
});

import { SQL } from "bun";

const sql = new SQL({
  // 必須指定介面卡
  adapter: "sqlite",
  filename: "./data/app.db", // 或 ":memory:" 表示記憶體資料庫

  // SQLite 檔案訪問模式
  readonly: false, // 只讀開啟
  create: true, // 不存在則建立
  readwrite: true, // 支援讀寫

  // SQLite 資料處理設定
  strict: true, // 開啟嚴格模式提升型別安全
  safeIntegers: false, // 超出 JS 數字範圍的整數使用 BigInt

  // 回撥函式
  onconnect: client => {
    console.log("SQLite 資料庫已開啟");
  },
  onclose: client => {
    console.log("SQLite 資料庫已關閉");
  },
});

import { SQL } from "bun";

const sql = new SQL(url, {
  // 其他連線設定
  ...
  // 用於獲取資料庫使用者密碼的函式
  password: async () => await signer.getAuthToken(),
});

const sqlite = new SQL("sqlite://app.db");

// 使用體驗和 PostgreSQL 一樣，但底層同步執行
const users = await sqlite`SELECT * FROM users`;

// 引數傳遞方式一致
const user = await sqlite`SELECT * FROM users WHERE id = ${userId}`;

const sqlite = new SQL("sqlite://app.db");

// 啟用外部索引鍵支援
await sqlite`PRAGMA foreign_keys = ON`;

// 設定 WAL 日誌模式提升並行
await sqlite`PRAGMA journal_mode = WAL`;

// 校驗資料庫完整性
const integrity = await sqlite`PRAGMA integrity_check`;

// SQLite 儲存 5 種基礎型別：NULL、INTEGER、REAL、TEXT 和 BLOB
const sqlite = new SQL("sqlite://app.db");

// SQLite 型別寬鬆
await sqlite`
  CREATE TABLE flexible (
    id INTEGER PRIMARY KEY,
    data TEXT,        -- 數字能存為字串
    value NUMERIC,    -- 可以是整數、浮點或文本
    blob BLOB         -- 二進位制資料
  )
`;

// JS 值會自動轉換
await sqlite`INSERT INTO flexible VALUES (${1}, ${"text"}, ${123.45}, ${Buffer.from("binary")})`;

await sql.begin(async tx => {
  // 該函式內所有查詢都運行於事務中
  await tx`INSERT INTO users (name) VALUES (${"Alice"})`;
  await tx`UPDATE accounts SET balance = balance - 100 WHERE user_id = 1`;

  // 沒有拋錯則自動提交
  // 拋錯則回滾
});

await sql.begin(async tx => {
  return [
    tx`INSERT INTO users (name) VALUES (${"Alice"})`,
    tx`UPDATE accounts SET balance = balance - 100 WHERE user_id = 1`,
  ];
});

await sql.begin(async tx => {
  await tx`INSERT INTO users (name) VALUES (${"Alice"})`;

  await tx.savepoint(async sp => {
    // 此處段落可獨立回滾
    await sp`UPDATE users SET status = 'active'`;
    if (someCondition) {
      throw new Error("Rollback to savepoint");
    }
  });

  // 儲存點回滾後繼續事務
  await tx`INSERT INTO audit_log (action) VALUES ('user_created')`;
});

// 開始分散式事務
await sql.beginDistributed("tx1", async tx => {
  await tx`INSERT INTO users (name) VALUES (${"Alice"})`;
});

// 後續提交或回滾
await sql.commitDistributed("tx1");
// 或
await sql.rollbackDistributed("tx1");

const sql = new SQL({
  hostname: "localhost",
  username: "user",
  password: "password",
  ssl: "disable", // 可選 "prefer" | "require" | "verify-ca" | "verify-full"
});

// prefer 模式
const sql = new SQL("postgres://user:password@localhost/mydb?sslmode=prefer");

// verify-full 模式
const sql = new SQL("postgres://user:password@localhost/mydb?sslmode=verify-full");

const sql = new SQL({
  max: 20, // 最大連線數 20
  idleTimeout: 30, // 空閒 30 秒關閉連線
  maxLifetime: 3600, // 連線最大存活 1 小時
  connectionTimeout: 10, // 連線超時 10 秒
});

const sql = Bun.SQL(); // 未連線

await sql`...`; // 連線池啟動，最多建立 max 連線，複用空閒連線
await sql`...`; // 複用已有連線

// 並行使用多個連線
await Promise.all([
  sql`INSERT INTO users ${sql({ name: "Alice" })}`,
  sql`UPDATE users SET name = ${user.name} WHERE id = ${user.id}`,
]);

await sql.close(); // 等待所有查詢完成並關閉連線池
await sql.close({ timeout: 5 }); // 最多等待 5 秒關閉
await sql.close({ timeout: 0 }); // 立即關閉所有連線

// 保留獨佔連線
const reserved = await sql.reserve();

try {
  await reserved`INSERT INTO users (name) VALUES (${"Alice"})`;
} finally {
  // 必須釋放連接回池
  reserved.release();
}

// 或使用 Symbol.dispose 自動釋放
{
  using reserved = await sql.reserve();
  await reserved`SELECT 1`;
} // 自動釋放

// Give up if no connection frees up within 5 seconds
using reserved = await sql.reserve({ signal: AbortSignal.timeout(5000) });

import { sql } from "bun";

const subscription = await sql.listen("orders", payload => {
  console.log("new order", JSON.parse(payload));
});

await sql.notify("orders", JSON.stringify({ id: 42 }));

await subscription.unlisten();

{
  await using subscription = await sql.listen("orders", handleOrder);
  await doWork();
} // 在此處取消監聽

await sql.listen("orders", handleOrder, async () => {
  for (const order of await sql`SELECT * FROM orders WHERE processed = false`) handleOrder(order);
});

await sql.begin(async tx => {
  const [order] = await tx`INSERT INTO orders ${sql(data)} RETURNING id`;
  await tx.notify("orders", String(order.id)); // 在事務提交時送達
});

const sql = new SQL({
  // 其他設定...
  prepare: false, // 停用伺服器端持久命名預處理語句
});

import { SQL } from "bun";

try {
  await sql`SELECT * FROM users`;
} catch (error) {
  if (error instanceof SQL.PostgresError) {
    // PostgreSQL 專用錯誤
    console.log(error.code); // PostgreSQL 錯誤碼
    console.log(error.detail); // 詳細錯誤資訊
    console.log(error.hint); // 提示
  } else if (error instanceof SQL.SQLiteError) {
    // SQLite 專用錯誤
    console.log(error.code); // SQLite 錯誤碼（如 "SQLITE_CONSTRAINT"）
    console.log(error.errno); // SQLite 錯誤號
    console.log(error.byteOffset); // SQL 語句中的位元組偏移（若有）
  } else if (error instanceof SQL.SQLError) {
    // 通用 SQL 錯誤（基類）
    console.log(error.message);
  }
}

import { sql } from "bun";

const [{ x, y }] = await sql`SELECT 9223372036854777 as x, 12345 as y`;

console.log(typeof x, x); // "字串" "9223372036854777"
console.log(typeof y, y); // "數字" 12345

const sql = new SQL({
  bigint: true,
});

const [{ x }] = await sql`SELECT 9223372036854777 as x`;

console.log(typeof x, x); // "bigint" 9223372036854777n

// 自動建立伺服器端預處理語句
const user = await mysql`SELECT * FROM users WHERE id = ${userId}`;

// 預處理語句快取並重復使用
for (const id of userIds) {
  await mysql`SELECT * FROM users WHERE id = ${id}`; // 重用同一預處理語句
}

// 查詢流水線 - 並行傳送多個語句
const [users, orders, products] = await Promise.all([
  mysql`SELECT * FROM users WHERE active = ${true}`,
  mysql`SELECT * FROM orders WHERE status = ${"pending"}`,
  mysql`SELECT * FROM products WHERE in_stock = ${true}`,
]);

const mysql = new SQL("mysql://user:pass@localhost/mydb");

// 使用 simple() 多語句查詢
const multiResults = await mysql`
  SELECT * FROM users WHERE id = 1;
  SELECT * FROM orders WHERE user_id = 1;
`.simple();

// 自動傳送以下屬性：
// _client_name: "Bun"
// _client_version: <bun 版本號>
// 可以在 MySQL performance_schema.session_connect_attrs 中檢視

// 獲取插入 ID
const result = await mysql`INSERT INTO users (name) VALUES (${"Alice"})`;
console.log(result.lastInsertRowid); // MySQL 的 LAST_INSERT_ID()

// 受影響的行數
const updated = await mysql`UPDATE users SET active = ${false} WHERE age < ${18}`;
console.log(updated.affectedRows); // 更新行數

// 使用 MySQL 專用函式
const now = await mysql`SELECT NOW() as current_time`;
const uuid = await mysql`SELECT UUID() as id`;

try {
  await mysql`INSERT INTO users (email) VALUES (${"duplicate@email.com"})`;
} catch (error) {
  if (error.code === "ER_DUP_ENTRY") {
    console.log("檢測到重複條目");
  } else if (error.code === "ER_ACCESS_DENIED_ERROR") {
    console.log("訪問被拒絕");
  } else if (error.code === "ER_BAD_DB_ERROR") {
    console.log("資料庫不存在");
  }
  // MySQL 錯誤碼與 mysql/mysql2 包相容
}