interface

ReservedSQL

interface ReservedSQL

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

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

    Creates a SQL array parameter

    @param values

    Array values to bind

    @param typeNameOrTypeID

    Element type name or type ID; defaults to JSON when omitted

    @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})`;
  • 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 database connection. With timeout: 0 it closes immediately; with no timeout it waits for all queries to finish first.

    @param options

    Optional timeout in seconds

    await sql.close({ timeout: 1 });
  • 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

    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. Pass values if the file uses positional parameters ($1, $2, ...). With the SQLite adapter, values may also be an object of named parameters (:name, $name, or @name placeholders); keys keep the prefix unless the connection sets strict: true.

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

    Flushes any pending operations

    sql.flush();
  • 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(): void;

    Releases the client back to the connection 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.

    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.

    sql.unsafe can be nested inside a safe sql expression, for example when only part of the query is unsafe.

    With the SQLite adapter, values may also be an object of named parameters (:name, $name, or @name placeholders). Object keys keep the prefix unless the connection sets strict: true.

    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 })

Referenced types

interface Query<T>

A pending SQL query. Extends Promise, so it can be awaited, and adds methods to control how it runs.

  • readonly [Symbol.toStringTag]: string
  • active: boolean

    True while the query is executing

  • cancelled: boolean

    True if the query has been cancelled

  • cancel(): Query<T>;

    Cancels the executing query

  • catch<TResult = never>(
    onrejected?: null | (reason: any) => TResult | PromiseLike<TResult>
    ): Promise<T | TResult>;

    Attaches a callback for only the rejection of the Promise.

    @param onrejected

    The callback to execute when the Promise is rejected.

    @returns

    A Promise for the completion of the callback.

  • execute(): Query<T>;

    Starts executing the query. Queries are lazy: they only run when awaited or executed with this method.

  • onfinally?: null | () => void
    ): Promise<T>;

    Attaches a callback that is invoked when the Promise is settled (fulfilled or rejected). The resolved value cannot be modified from the callback.

    @param onfinally

    The callback to execute when the Promise is settled (fulfilled or rejected).

    @returns

    A Promise for the completion of the callback.

  • raw(): Query<T>;

    Returns rows as arrays of Buffer objects instead of objects

  • simple(): Query<T>;

    Executes the query as a simple query. Parameters are not allowed, but the query can contain multiple commands separated by semicolons.

  • then<TResult1 = T, TResult2 = never>(
    onfulfilled?: null | (value: T) => TResult1 | PromiseLike<TResult1>,
    onrejected?: null | (reason: any) => TResult2 | PromiseLike<TResult2>
    ): Promise<TResult1 | TResult2>;

    Attaches callbacks for the resolution and/or rejection of the Promise.

    @param onfulfilled

    The callback to execute when the Promise is resolved.

    @param onrejected

    The callback to execute when the Promise is rejected.

    @returns

    A Promise for the completion of which ever callback is executed.

  • values(): Query<T>;

    Returns each row as an array of values, in the same order as the columns in the query

interface Helper<T>

A parameter or serializable value interpolated into a query.

const helper = sql(users, 'id');
await sql`insert into users ${helper}`;