> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/zhcndoc/bun/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Databases

> Connect to PostgreSQL, MySQL, and SQLite databases

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

## Quick Start

### PostgreSQL

```ts theme={null}
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

```ts theme={null}
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

```ts theme={null}
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

```ts theme={null}
const sql = new Bun.SQL("postgres://localhost/mydb");
```

### With Options

```ts theme={null}
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

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

## Querying with Tagged Templates

### Basic Query

```ts theme={null}
const users = await sql`SELECT * FROM users`;
```

### With Parameters

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

### Multiple Parameters

```ts theme={null}
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

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

### Insert Helper

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

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

### Bulk Insert

```ts theme={null}
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

```ts theme={null}
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

```ts theme={null}
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

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

### Savepoints

```ts theme={null}
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

```ts theme={null}
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

```ts theme={null}
// 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

```ts theme={null}
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

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

### Values Only

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

### Simple Query

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

### Cancel Query

```ts theme={null}
const query = sql`SELECT * FROM large_table`;

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

// Cancel it
query.cancel();
```

## PostgreSQL Arrays

```ts theme={null}
// 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

```ts theme={null}
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

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

### SQLite

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

## Error Handling

```ts theme={null}
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

```ts theme={null}
// Load SQL from file
const users = await sql.file("./queries/users.sql", [1, 2]);
```

## Unsafe Queries

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

## Distributed Transactions

For PostgreSQL and MySQL only:

```ts theme={null}
// 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

```ts theme={null}
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>;
}
```