SQL Databases

Bun provides a unified SQL API for connecting to PostgreSQL, MySQL, and SQLite databases via the Bun.SQL class.

Quick Start

PostgreSQL

const sql = new Bun.SQL("postgres://user:pass@localhost:5432/mydb");

const users = await sql`SELECT * FROM users WHERE id = ${1}`;
console.log(users);

await sql.close();

MySQL

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

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

await sql.close();

SQLite

const sql = new Bun.SQL("sqlite://./mydb.db");
// or
const sql = new Bun.SQL({ adapter: "sqlite", filename: "./mydb.db" });

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

await sql.close();

Creating Connections

From Connection String

const sql = new Bun.SQL("postgres://localhost/mydb");

With Options

const sql = new Bun.SQL({
  adapter: "postgres",
  hostname: "localhost",
  port: 5432,
  database: "mydb",
  username: "user",
  password: "pass",
  max: 20, // Connection pool size
  idleTimeout: 30, // seconds
  connectionTimeout: 10, // seconds
});

With URL and Options

const sql = new Bun.SQL(
  "postgres://localhost/mydb",
  {
    max: 10,
    idleTimeout: 60,
  }
);

Querying with Tagged Templates

Basic Query

const users = await sql`SELECT * FROM users`;

With Parameters

const id = 1;
const users = await sql`SELECT * FROM users WHERE id = ${id}`;

Multiple Parameters

const name = "Alice";
const email = "alice@example.com";
const user = await sql`
  SELECT * FROM users 
  WHERE name = ${name} AND email = ${email}
`;

Inserting Data

Single Insert

const result = await sql`
  INSERT INTO users (name, email) 
  VALUES (${"Alice"}, ${"alice@example.com"})
  RETURNING *
`;

Insert Helper

const user = { name: "Bob", email: "bob@example.com" };

const result = await sql`
  INSERT INTO users ${sql(user)} 
  RETURNING *
`;

Bulk Insert

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

const result = await sql`
  INSERT INTO users ${sql(users)} 
  RETURNING *
`;

Insert Specific Columns

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

const result = await sql`
  INSERT INTO users ${sql(user, "name", "email")} 
  RETURNING *
`;

Transactions

Basic Transaction

const result = await sql.begin(async (tx) => {
  const user = await tx`
    INSERT INTO users (name) VALUES (${'Alice'})
    RETURNING *
  `;
  
  await tx`
    INSERT INTO posts (user_id, title) 
    VALUES (${user[0].id}, ${'Hello World'})
  `;
  
  return user;
});

With Transaction Options

await sql.begin("read write", async (tx) => {
  // Transaction with read write mode
});

Savepoints

await sql.begin(async (tx) => {
  await tx.savepoint("sp1", async (sp) => {
    await sp`INSERT INTO users (name) VALUES ('Alice')`;
    // Can rollback to here
  });
});

Connection Management

Reserve a Connection

const reserved = await sql.reserve();

try {
  await reserved`SELECT * FROM users`;
  await reserved`UPDATE users SET active = true`;
} finally {
  await reserved.release();
}

// Or with using
using reserved = await sql.reserve();
await reserved`SELECT * FROM users`;
// Automatically released

Connect and Close

// Wait for connection
await sql.connect();

// Close connection
await sql.close();

// Close with timeout
await sql.close({ timeout: 5 }); // seconds

// Or use end() alias
await sql.end();

Query Methods

Execute Query

const query = sql`SELECT * FROM users WHERE id = ${1}`;

// Execute and await results
const users = await query;

// Explicit execute
const users2 = await query.execute();

Raw Results

const query = sql`SELECT name, email FROM users`.raw();
const rows = await query;
// Returns array of arrays instead of objects

Values Only

const query = sql`SELECT name, email FROM users`.values();
const rows = await query;
// Returns just the values

Simple Query

// Execute multiple commands separated by semicolons
const result = await sql`
  CREATE TABLE test (id INT);
  INSERT INTO test VALUES (1);
`.simple();

Cancel Query

const query = sql`SELECT * FROM large_table`;

// Start query
const promise = query.execute();

// Cancel it
query.cancel();

PostgreSQL Arrays

// Create array parameter
const ids = sql.array([1, 2, 3], "INT");

const users = await sql`
  SELECT * FROM users WHERE id = ANY(${ids})
`;

// With type name
const tags = sql.array(["js", "ts"], "TEXT");

Connection Options

PostgreSQL

const sql = new Bun.SQL({
  adapter: "postgres",
  hostname: "localhost",
  port: 5432,
  username: "user",
  password: "pass",
  database: "mydb",
  
  // Connection pool
  max: 10,
  idleTimeout: 30,
  connectionTimeout: 10,
  maxLifetime: 3600,
  
  // TLS
  tls: true,
  
  // Runtime config
  connection: {
    application_name: "myapp",
    timezone: "UTC",
  },
  
  // BigInt for large numbers
  bigint: false,
  
  // Prepared statements
  prepare: true,
  
  // Callbacks
  onconnect: (err) => {
    if (err) console.error("Connection failed", err);
  },
  onclose: (err) => {
    console.log("Connection closed", err);
  },
});

MySQL

const sql = new Bun.SQL({
  adapter: "mysql",
  hostname: "localhost",
  port: 3306,
  username: "user",
  password: "pass",
  database: "mydb",
  
  max: 10,
  idleTimeout: 30,
  connectionTimeout: 10,
});

SQLite

const sql = new Bun.SQL({
  adapter: "sqlite",
  filename: "./mydb.db",
  // or filename: ":memory:"
  
  readonly: false,
  create: true,
  safeIntegers: false,
  strict: true,
});

Error Handling

import { SQL } from "bun";

try {
  await sql`SELECT * FROM invalid_table`;
} catch (err) {
  if (err instanceof SQL.PostgresError) {
    console.error("Postgres error:", err.message);
    console.error("Code:", err.code);
    console.error("Detail:", err.detail);
    console.error("Hint:", err.hint);
  } else if (err instanceof SQL.MySQLError) {
    console.error("MySQL error:", err.message);
    console.error("Code:", err.code);
    console.error("SQL State:", err.sqlState);
  } else if (err instanceof SQL.SQLiteError) {
    console.error("SQLite error:", err.message);
    console.error("Code:", err.code);
    console.error("Errno:", err.errno);
  }
}

File Queries

// Load SQL from file
const users = await sql.file("./queries/users.sql", [1, 2]);

Unsafe Queries

// WARNING: SQL injection risk!
const table = "users";
const result = await sql.unsafe(`SELECT * FROM ${table}`);

Distributed Transactions

For PostgreSQL and MySQL only:

// Begin distributed transaction
await sql.beginDistributed("my_transaction", async (tx) => {
  await tx`INSERT INTO users (name) VALUES ('Alice')`;
});

// Later, commit or rollback
await sql.commitDistributed("my_transaction");
// or
await sql.rollbackDistributed("my_transaction");

Type Signatures

class SQL {
  constructor(url: string | URL);
  constructor(url: string | URL, options: SQL.Options);
  constructor(options: SQL.Options);
  
  <T = any>(strings: TemplateStringsArray, ...values: unknown[]): SQL.Query<T>;
  <T = any>(string: string): SQL.Query<T>;
  <T>(obj: T | T[]): SQL.Helper<T>;
  <T, Keys extends keyof T>(
    obj: T | T[],
    ...columns: Keys[]
  ): SQL.Helper<Pick<T, Keys>>;
  
  begin<T>(fn: SQL.TransactionContextCallback<T>): Promise<T>;
  begin<T>(options: string, fn: SQL.TransactionContextCallback<T>): Promise<T>;
  transaction<T>(fn: SQL.TransactionContextCallback<T>): Promise<T>;
  
  beginDistributed<T>(name: string, fn: SQL.TransactionContextCallback<T>): Promise<T>;
  commitDistributed(name: string): Promise<void>;
  rollbackDistributed(name: string): Promise<void>;
  
  reserve(): Promise<ReservedSQL>;
  connect(): Promise<SQL>;
  close(options?: { timeout?: number }): Promise<void>;
  end(options?: { timeout?: number }): Promise<void>;
  
  array(values: any[], typeNameOrTypeID?: number | string): SQLArrayParameter;
  file<T>(filename: string, values?: any[]): SQL.Query<T>;
  unsafe<T>(string: string, values?: any[]): SQL.Query<T>;
  
  readonly options: SQL.Options;
}

interface SQL.Query<T> extends Promise<T> {
  readonly active: boolean;
  readonly cancelled: boolean;
  
  cancel(): SQL.Query<T>;
  simple(): SQL.Query<T>;
  execute(): SQL.Query<T>;
  raw(): SQL.Query<T>;
  values(): SQL.Query<T>;
}

​Quick Start

​PostgreSQL

​MySQL

​SQLite

​Creating Connections

​From Connection String

​With Options

​With URL and Options

​Querying with Tagged Templates

​Basic Query

​With Parameters

​Multiple Parameters

​Inserting Data

​Single Insert

​Insert Helper

​Bulk Insert

​Insert Specific Columns

​Transactions

​Basic Transaction

​With Transaction Options

​Savepoints

​Connection Management

​Reserve a Connection

​Connect and Close

​Query Methods

​Execute Query

​Raw Results

​Values Only

​Simple Query

​Cancel Query

​PostgreSQL Arrays

​Connection Options

​PostgreSQL

​MySQL

​SQLite

​Error Handling

​File Queries

​Unsafe Queries

​Distributed Transactions

​Type Signatures

Quick Start

PostgreSQL

MySQL

SQLite

Creating Connections

From Connection String

With Options

With URL and Options

Querying with Tagged Templates

Basic Query

With Parameters

Multiple Parameters

Inserting Data

Single Insert

Insert Helper

Bulk Insert

Insert Specific Columns

Transactions

Basic Transaction

With Transaction Options

Savepoints

Connection Management

Reserve a Connection

Connect and Close

Query Methods

Execute Query

Raw Results

Values Only

Simple Query

Cancel Query

PostgreSQL Arrays

Connection Options

PostgreSQL

MySQL

SQLite

Error Handling

File Queries

Unsafe Queries

Distributed Transactions

Type Signatures