SQL

interface SQL

• constructor (connectionString: string | URL): SQL

  Creates a new SQL client instance

  const sql = new SQL("postgres://localhost:5432/mydb");
  const sql = new SQL(new URL("postgres://localhost:5432/mydb"));
  

  constructor (connectionString: string | URL, options: SQLOptions): SQL

  Creates a new SQL client instance with options

  const sql = new SQL("postgres://localhost:5432/mydb", { idleTimeout: 1000 });
  

  constructor (options?: SQLOptions): SQL

  Creates a new SQL client instance with options

  const sql = new SQL({ url: "postgres://localhost:5432/mydb", idleTimeout: 1000 });
  
• options: SQLOptions

  Current client options

• [Symbol.asyncDispose](): Promise<any>
• begin(fn: SQLTransactionContextCallback): Promise<any>

  Begins a new transaction Will reserve a connection for the transaction and supply a scoped sql instance for all transaction uses in the callback function. sql.begin will resolve with the returned value from the callback function. BEGIN is automatically sent with the optional options, and if anything fails ROLLBACK will be called 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(options: string, fn: SQLTransactionContextCallback): Promise<any>

  Begins a new transaction with options Will reserve a connection for the transaction and supply a scoped sql instance for all transaction uses in the callback function. sql.begin will resolve with the returned value from the callback function. BEGIN is automatically sent with the optional options, and if anything fails ROLLBACK will be called 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(name: string, fn: SQLTransactionContextCallback): Promise<any>

  Begins a distributed transaction Also know as Two-Phase Commit, in a distributed transaction, Phase 1 involves the coordinator preparing nodes by ensuring data is written and ready to commit, while Phase 2 finalizes with nodes committing or rolling back based on the coordinator's decision, ensuring durability and releasing locks. In PostgreSQL and MySQL distributed transactions persist beyond the original session, allowing privileged users or coordinators to commit/rollback them, ensuring support for distributed transactions, recovery, and administrative tasks. beginDistributed will automatic rollback if any exception are not caught, and you can commit and rollback later if everything goes well. PostgreSQL natively supports distributed transactions using PREPARE TRANSACTION, while MySQL uses XA Transactions, and MSSQL also supports distributed/XA transactions. However, in MSSQL, distributed transactions are tied to the original session, the DTC coordinator, and the specific connection. These transactions are automatically committed or rolled back following the same rules as regular transactions, with no option for manual intervention from other sessions, in MSSQL distributed transactions are used to coordinate transactions using 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>

  Closes the database connection with optional timeout in seconds. If timeout is 0, it will close immediately, if is not provided it will wait for all queries to finish before closing.

  await sql.close({ timeout: 1 });
  
• commitDistributed(name: string): Promise<void>

  Commits a distributed transaction also know as prepared transaction in postgres or XA transaction in MySQL

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

  Waits for the database connection to be established

  await sql.connect();
  
• distributed(name: string, fn: SQLTransactionContextCallback): Promise<any>

  Alternative method to begin a distributed transaction
• end(options?: { timeout: number }): Promise<void>

  Closes the database connection with optional timeout in seconds. If timeout is 0, it will close immediately, if is not provided it will wait for all queries to finish before closing.

  await sql.end({ timeout: 1 });
  
• file(filename: string, values?: any[]): SQLQuery

  Reads a file and uses the contents as a query. Optional parameters can be used if the file includes $1, $2, etc

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

  Flushes any pending operations

• reserve(): Promise<ReservedSQL>

  The reserve method pulls out a connection from the pool, and returns a client that wraps the single connection. This can be used for running queries on an isolated connection. Calling reserve in a reserved Sql will return a new reserved connection, not the same connection (behavior matches postgres package).

  const reserved = await sql.reserve();
  await reserved`select * from users`;
  await reserved.release();
  // with in a production scenario would be something more like
  const reserved = await sql.reserve();
  try {
    // ... queries
  } finally {
    await reserved.release();
  }
  //To make it simpler bun supportsSymbol.dispose and Symbol.asyncDispose
  {
  // always release after context (safer)
  using reserved = await sql.reserve()
  await reserved`select * from users`
  }
  
• rollbackDistributed(name: string): Promise<void>

  Rolls back a distributed transaction also know as prepared transaction in postgres or XA transaction in MySQL

  await sql.rollbackDistributed("my_distributed_transaction");
  
• transaction(fn: SQLTransactionContextCallback): Promise<any>

  Alternative method to begin a transaction Will reserve a connection for the transaction and supply a scoped sql instance for all transaction uses in the callback function. sql.transaction will resolve with the returned value from the callback function. BEGIN is automatically sent with the optional options, and if anything fails ROLLBACK will be called 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(options: string, fn: SQLTransactionContextCallback): Promise<any>

  Alternative method to begin a transaction with options Will reserve a connection for the transaction and supply a scoped sql instance for all transaction uses in the callback function. sql.transaction will resolve with the returned value from the callback function. BEGIN is automatically sent with the optional options, and if anything fails ROLLBACK will be called 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(string: string, values?: any[]): SQLQuery

  If you know what you're doing, you can use unsafe to pass any string you'd like. Please note that this can lead to SQL injection if you're not careful. You can also nest sql.unsafe within a safe sql expression. This is useful if only part of your fraction has unsafe elements.

  const result = await sql.unsafe(`select ${danger} from users where id = ${dragons}`)