method

ReservedSQL.reserve

options?: { signal: AbortSignal }
): Promise<ReservedSQL>;

Reserves a connection from the pool and returns a client that wraps that single connection. Use it to run queries on an isolated connection.

Calling reserve() on a reserved client returns a new reserved connection, not the same one (behavior matches the postgres package).

@param options

signal aborts the reservation while it is still waiting for a connection; the returned promise rejects with signal.reason and no connection is taken from the pool. Aborting after the promise resolved has no effect: the caller owns the connection and must release() it.

Rejects if the adapter does not support connection pooling (SQLite).

const reserved = await sql.reserve();
await reserved`select * from users`;
await reserved.release();

// In production, release in a finally block
const reserved = await sql.reserve();
try {
  // ... queries
} finally {
  await reserved.release();
}

// Bun supports Symbol.dispose and Symbol.asyncDispose,
// so `using` releases the connection at the end of the scope
using reserved = await sql.reserve()
await reserved`select * from users`

// Give up on the reservation if no connection frees up in time
const reserved = await sql.reserve({ signal: AbortSignal.timeout(5000) });

Referenced types

interface ReservedSQL

A connection reserved from the pool with SQL.reserve. Call release to return it to the pool.

After release(), a query on this client that has not started rejects with ERR_POSTGRES_CONNECTION_CLOSED or ERR_MYSQL_CONNECTION_CLOSED. Queries are lazy, so this includes a query that a using block returns without await.

  • options: Merge<SQLiteOptions, PostgresOrMySQLOptions> | Merge<PostgresOrMySQLOptions, SQLiteOptions>

    The resolved settings of the client, after the URL, the options and the environment are merged.

    Treat it as output. Timeouts are in milliseconds here, and only the long option names are set (hostname, not host), so new SQL(sql.options) does not make an equal client. console.log prints [REDACTED] for the password and the TLS key.

  • [Symbol.asyncDispose](): PromiseLike<void>;
  • values: any[] | TypedArray<ArrayBufferLike>,
    typeNameOrTypeID?: number | ArrayType

    Creates a PostgreSQL array parameter. null elements are SQL NULL, and nested arrays make a multi-dimensional array. Throws on MySQL and SQLite.

    @param values

    Array values to bind

    @param typeNameOrTypeID

    Element type name, or the OID of the array type. Bun casts the parameter to it: $1::INT[]. When omitted, Bun adds no cast and the server infers the type from the column, the compared value or a cast in the query. Pass it when the query gives no context: a bare SELECT ${sql.array([1])} returns the text {"1"}. Pass "JSON" or "JSONB" to encode each element as JSON: there null is the JSON value null, undefined is SQL NULL, and sql.json([1, 2]) stores a JSON array as one element.

    @returns

    The array parameter, ready to interpolate into a query

    const array = sql.array([1, 2, 3], "INT");
await sql`CREATE TABLE users_posts (user_id INT, posts_id INT[])`;
await sql`INSERT INTO users_posts (user_id, posts_id) VALUES (${user.id}, ${array})`;

// the server infers the type from the compared column
await sql`SELECT * FROM users WHERE id = ANY(${sql.array([1, 2, 3])})`;
  • begin<T>(
    fn: TransactionContextCallback<T>
    ): Promise<ContextCallbackResult<T>>;

    Begins a new transaction.

    Reserves a connection for the transaction and passes a scoped sql instance to the callback. sql.begin resolves with the callback's return value. BEGIN is sent automatically, and if anything fails, ROLLBACK is sent so the connection can be released and execution can continue.

    const [user, account] = await sql.begin(async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
    returning *
  `
  const [account] = await sql`
    insert into accounts (
      user_id
    ) values (
      ${ user.user_id }
    )
    returning *
  `
  return [user, account]
})
    begin<T>(
    options: string,
    fn: TransactionContextCallback<T>
    ): Promise<ContextCallbackResult<T>>;

    Begins a new transaction with options.

    Reserves a connection for the transaction and passes a scoped sql instance to the callback. sql.begin resolves with the callback's return value. BEGIN is sent with the given options, and if anything fails, ROLLBACK is sent so the connection can be released and execution can continue.

    const [user, account] = await sql.begin("read write", async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
    returning *
  `
  const [account] = await sql`
    insert into accounts (
      user_id
    ) values (
      ${ user.user_id }
    )
    returning *
  `
  return [user, account]
})
  • name: string,
    fn: TransactionContextCallback<T>
    ): Promise<ContextCallbackResult<T>>;

    Begins a distributed transaction, also known as Two-Phase Commit. In phase 1 the coordinator prepares each node, making sure its data is written and ready to commit; in phase 2 the nodes commit or roll back based on the coordinator's decision, ensuring durability and releasing locks.

    beginDistributed rolls back automatically if an exception is not caught; otherwise you commit or roll back later with commitDistributed or rollbackDistributed.

    In PostgreSQL and MySQL, distributed transactions persist beyond the original session, so privileged users or coordinators can commit or roll them back later, which supports recovery and administrative tasks. PostgreSQL implements them with PREPARE TRANSACTION; MySQL uses XA Transactions. MSSQL also supports distributed/XA transactions, but ties them to the original session, the DTC coordinator, and the specific connection: they are committed or rolled back under the same rules as regular transactions, with no manual intervention from other sessions, and are used to coordinate transactions across Linked Servers.

    await sql.beginDistributed("numbers", async sql => {
  await sql`create table if not exists numbers (a int)`;
  await sql`insert into numbers values(1)`;
});
// later you can call
await sql.commitDistributed("numbers");
// or await sql.rollbackDistributed("numbers");
  • options?: { timeout: number }
    ): Promise<void>;

    Closes the connection instead of returning it to the pool.

    @param options

    timeout is how many seconds to wait for the running queries of this client. The default is 0.

  • name: string
    ): Promise<void>;

    Commits a distributed transaction, also known as a prepared transaction in PostgreSQL or an XA transaction in MySQL

    @param name

    Name of the distributed transaction

    await sql.commitDistributed("my_distributed_transaction");
  • connect(): Promise<SQL>;

    Waits for the database connection to be established. Opens one connection if the pool has none.

    await sql.connect();
  • name: string,
    fn: TransactionContextCallback<T>
    ): Promise<ContextCallbackResult<T>>;

    Begins a distributed transaction. Alias of beginDistributed.

  • options?: { timeout: number }
    ): Promise<void>;

    Closes the database connection. Alias of SQL.close.

    @param options

    Optional timeout in seconds

    await sql.end({ timeout: 1 });
  • file<T = any>(
    filename: string,
    values?: any[] | Record<string, any>
    ): Query<T>;

    Reads a file and runs its contents as a query. The query is lazy: Bun reads the file when the query starts. Pass values as an array if the file uses positional parameters ($1 on PostgreSQL, ? on MySQL). With the SQLite adapter, values may also be an object of named parameters (:name, $name, or @name placeholders). A key works with or without the prefix.

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

    Flushes any pending operations. Does nothing on SQLite, where queries run synchronously.

    sql.flush();
  • value: unknown
    ): undefined | string | SQLJSONParameter;

    Returns a parameter that stores value as JSON, to interpolate into a query. It works on every adapter.

    Bun sends an object or an array as JSON by itself, and PostgreSQL stores a string in a json or jsonb column as a JSON string. Use sql.json to store a JSON number, boolean or null, to store an object or an array on SQLite, and to store a JSON string where Bun sends a string as it is: MySQL, MariaDB, SQLite, and PostgreSQL with prepare: false.

    await sql`INSERT INTO settings (data) VALUES (${sql.json(42)})`; // 42
await sql`INSERT INTO settings (data) VALUES (${sql.json(null)})`; // JSON null, not SQL NULL
await sql`INSERT INTO settings (data) VALUES (${sql.json("dark")})`; // "dark"
  • channel: string,
    onnotify: (payload: string) => void,
    onlisten?: () => void
    ): Promise<ListenSubscription>;

    Subscribe to a PostgreSQL LISTEN channel. Resolves once the server has acknowledged the subscription, with a handle that removes it again.

    Every call is its own registration: several on one channel share a single server-side subscription and each receives every notification. All of them share one dedicated connection, opened by the first listen() and closed when the last registration is removed. If it drops, it is reconnected with exponential backoff and every channel is re-subscribed; onlisten runs again each time.

    A throwing onnotify or onlisten is reported as an uncaught exception.

    @param channel

    Channel name, quoted for you; at most 63 bytes, the PostgreSQL identifier limit

    @param onnotify

    Receives each notification's payload

    @param onlisten

    Runs once the LISTEN is acknowledged, initially and after every reconnect

    const subscription = await sql.listen("events", payload => console.log(payload));
await sql.notify("events", "hello");
await subscription.unlisten();
  • channel: string,
    payload?: string
    ): Promise<void>;

    Send a PostgreSQL NOTIFY via pg_notify. Runs as a normal query on this handle, so on a sql.begin() transaction it is delivered on commit and discarded on rollback. Omitting payload sends an empty one, like a bare NOTIFY channel.

    await sql.notify("events", JSON.stringify({ id: 1 }));
await sql.notify("cache-invalidated");
  • release(): Promise<void>;

    Releases the connection back to the pool.

    The client closes immediately: new queries and begin() calls reject. A transaction started with begin() on this client keeps the connection until it ends, and the pool gets the connection back after that. The returned promise resolves right away, so you can await release() inside such a transaction.

    Bun closes a connection that is released inside an open transaction, for example after a manual BEGIN, instead of returning it to the pool.

  • options?: { signal: AbortSignal }
    ): Promise<ReservedSQL>;

    Reserves a connection from the pool and returns a client that wraps that single connection. Use it to run queries on an isolated connection.

    Calling reserve() on a reserved client returns a new reserved connection, not the same one (behavior matches the postgres package).

    @param options

    signal aborts the reservation while it is still waiting for a connection; the returned promise rejects with signal.reason and no connection is taken from the pool. Aborting after the promise resolved has no effect: the caller owns the connection and must release() it.

    Rejects if the adapter does not support connection pooling (SQLite).

    const reserved = await sql.reserve();
await reserved`select * from users`;
await reserved.release();

// In production, release in a finally block
const reserved = await sql.reserve();
try {
  // ... queries
} finally {
  await reserved.release();
}

// Bun supports Symbol.dispose and Symbol.asyncDispose,
// so `using` releases the connection at the end of the scope
using reserved = await sql.reserve()
await reserved`select * from users`

// Give up on the reservation if no connection frees up in time
const reserved = await sql.reserve({ signal: AbortSignal.timeout(5000) });
  • name: string
    ): Promise<void>;

    Rolls back a distributed transaction, also known as a prepared transaction in PostgreSQL or an XA transaction in MySQL

    @param name

    Name of the distributed transaction

    await sql.rollbackDistributed("my_distributed_transaction");
  • fn: TransactionContextCallback<T>
    ): Promise<ContextCallbackResult<T>>;

    Begins a new transaction. Alias of begin.

    Reserves a connection for the transaction and passes a scoped sql instance to the callback. sql.transaction resolves with the callback's return value. BEGIN is sent automatically, and if anything fails, ROLLBACK is sent so the connection can be released and execution can continue.

    const [user, account] = await sql.transaction(async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
    returning *
  `
  const [account] = await sql`
    insert into accounts (
      user_id
    ) values (
      ${ user.user_id }
    )
    returning *
  `
  return [user, account]
})
    options: string,
    fn: TransactionContextCallback<T>
    ): Promise<ContextCallbackResult<T>>;

    Begins a new transaction with options. Alias of begin.

    Reserves a connection for the transaction and passes a scoped sql instance to the callback. sql.transaction resolves with the callback's return value. BEGIN is sent with the given options, and if anything fails, ROLLBACK is sent so the connection can be released and execution can continue.

    const [user, account] = await sql.transaction("read write", async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
    returning *
  `
  const [account] = await sql`
    insert into accounts (
      user_id
    ) values (
      ${ user.user_id }
    )
    returning *
  `
  return [user, account]
});
  • unsafe<T = any>(
    string: string,
    values?: any[] | Record<string, any>
    ): Query<T>;

    Executes any query string as-is. This can lead to SQL injection if the string contains untrusted input.

    The string takes the database's own placeholders: $1 on PostgreSQL, ? on MySQL, either on SQLite. Pass values as an array, which Bun copies in the call. Without values, the string can hold several statements.

    sql.unsafe can be nested inside a safe sql expression, for example when only part of the query is unsafe. Bun adds the nested text as written. On PostgreSQL, a nested sql.unsafe that has values cannot follow other parameters, because $1 in its text is the first parameter of the whole query: such a query rejects with a SyntaxError.

    With the SQLite adapter, values may also be an object of named parameters (:name, $name, or @name placeholders). A key works with or without the prefix. This form does not work in a nested sql.unsafe.

    const result = await sql.unsafe(`select ${danger} from users where id = ${dragons}`)
const row = await sql.unsafe("select * from users where id = :id", { id: 1 })