SQL

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: "[email protected]",
};

const [newUser] = await sql`
  INSERT INTO users ${sql(userData)}
  RETURNING *
`;
// 展开为：INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')

const users = [
  { name: "Alice", email: "[email protected]" },
  { name: "Bob", email: "[email protected]" },
  { name: "Charlie", email: "[email protected]" },
];

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

const user = {
  name: "Alice",
  email: "[email protected]",
  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", "[email protected]"],
  ["Bob", "[email protected]"],
];

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 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`;
} // 自动释放

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); // "string" "9223372036854777"
console.log(typeof y, y); // "number" 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 (${"[email protected]"})`;
} 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 包兼容
}