TransactionSQL

interface TransactionSQL

• 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>;
• array(

  values: any[] | TypedArray<ArrayBufferLike>,

  typeNameOrTypeID?: number | ArrayType

  ): SQLArrayParameter;

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

• beginDistributed<T>(

  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");

• close(

  options?: { timeout: number }

  ): Promise<void>;

  Rolls the transaction back. SQL.begin then rejects with ERR_POSTGRES_CONNECTION_CLOSED (or the MYSQL / SQLITE code).

  @param options

  timeout is how many seconds to wait for the running queries of the transaction before Bun sends ROLLBACK. The default is 0.

• commitDistributed(

  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();

• distributed<T>(

  name: string,

  fn: TransactionContextCallback<T>

  ): Promise<ContextCallbackResult<T>>;

  Begins a distributed transaction. Alias of beginDistributed.
• end(

  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();

• json(

  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"

• listen(

  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();

• notify(

  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");

• reserve(

  options?: { signal: AbortSignal }

  ): Promise<ReservedSQL>;

  Reserves a connection from the pool and returns a client that wraps that single connection.

  Inside a transaction, reserve() returns a brand new connection, not one related to the transaction. This matches the behaviour of 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.

• rollbackDistributed(

  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");

• savepoint<T>(

  name: string,

  fn: SavepointContextCallback<T>

  ): Promise<ContextCallbackResult<T>>;

  Creates a savepoint within the current transaction.

  The callback gets a client of its own, which ends with the savepoint. savepoint resolves with the callback's return value. If the callback throws, Bun rolls back to the savepoint and savepoint rejects with the error: catch it to continue the transaction.

  Run the savepoints of one transaction one after the other. A savepoint holds every statement that the connection runs while it is open, so a savepoint that fails also rolls back the work of one that runs next to it.

  @param name

  A label. Bun appends it to the name it generates, for example SAVEPOINT s0_audit for "audit". Each character that is not an ASCII letter, a digit or _ becomes _.

  savepoint<T>(

  fn: SavepointContextCallback<T>

  ): Promise<ContextCallbackResult<T>>;
• transaction<T>(

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

  transaction<T>(

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