SQL

Bun provides native bindings for working with PostgreSQL databases with a modern, Promise-based API. The interface is designed to be simple and performant, using tagged template literals for queries and offering features like connection pooling, transactions, and prepared statements.

import { sql } from "bun";

const users = await sql`
  SELECT * FROM users
  WHERE active = ${true}
  LIMIT ${10}
`;

// Select with multiple conditions
const activeUsers = await sql`
  SELECT * 
  FROM users 
  WHERE active = ${true} 
  AND age >= ${18}
`;

Features

Inserting data

You can pass JavaScript values directly to the SQL template literal and escaping will be handled for you.

import { sql } from "bun";

// Basic insert with direct values
const [user] = await sql`
  INSERT INTO users (name, email) 
  VALUES (${name}, ${email})
  RETURNING *
`;

// Using object helper for cleaner syntax
const userData = {
  name: "Alice",
  email: "alice@example.com",
};

const [newUser] = await sql`
  INSERT INTO users ${sql(userData)}
  RETURNING *
`;
// Expands to: INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')

Bulk Insert

You can also pass arrays of objects to the SQL template literal and it will be expanded to a INSERT INTO ... VALUES ... statement.

const users = [
  { name: "Alice", email: "alice@example.com" },
  { name: "Bob", email: "bob@example.com" },
  { name: "Charlie", email: "charlie@example.com" },
];

await sql`INSERT INTO users ${sql(users)}`;

Picking columns to insert

You can use sql(object, Array<string>) to pick which columns to insert. Each of the columns must be defined on the object.

const user = {
  name: "Alice",
  email: "alice@example.com",
  age: 25,
};

await sql`INSERT INTO users ${sql(user, ["name", "email"])}`;
// Only inserts name and email columns, ignoring other fields

Query Results

By default, Bun's SQL client returns query results as arrays of objects, where each object represents a row with column names as keys. However, there are cases where you might want the data in a different format. The client provides two additional methods for this purpose.

sql``.values() format

The sql``.values() method returns rows as arrays of values rather than objects. Each row becomes an array where the values are in the same order as the columns in your query.

const rows = await sql`SELECT * FROM users`.values();
console.log(rows);

This returns something like:

[
  ["Alice", "alice@example.com"],
  ["Bob", "bob@example.com"],
];

sql``.values() is especially useful if duplicate column names are returned in the query results. When using objects (the default), the last column name is used as the key in the object, which means duplicate column names overwrite each other — but when using sql``.values(), each column is present in the array so you can access the values of duplicate columns by index.

sql``.raw() format

The .raw() method returns rows as arrays of Buffer objects. This can be useful for working with binary data or for performance reasons.

const rows = await sql`SELECT * FROM users`.raw();
console.log(rows); // [[Buffer, Buffer], [Buffer, Buffer], [Buffer, Buffer]]

SQL Fragments

A common need in database applications is the ability to construct queries dynamically based on runtime conditions. Bun provides safe ways to do this without risking SQL injection.

Dynamic Table Names

When you need to reference tables or schemas dynamically, use the sql() helper to ensure proper escaping:

// Safely reference tables dynamically
await sql`SELECT * FROM ${sql("users")}`;

// With schema qualification
await sql`SELECT * FROM ${sql("public.users")}`;

Conditional Queries

You can use the sql() helper to build queries with conditional clauses. This allows you to create flexible queries that adapt to your application's needs:

// Optional WHERE clauses
const filterAge = true;
const minAge = 21;
const ageFilter = sql`AND age > ${minAge}`;
await sql`
  SELECT * FROM users
  WHERE active = ${true}
  ${filterAge ? ageFilter : sql``}
`;

Unsafe Queries

You can use the sql.unsafe function to execute raw SQL strings. Use this with caution, as it will not escape user input.

const result = await sql.unsafe(
  "SELECT " + columns + " FROM users WHERE id = " + id,
);

What is SQL Injection?

Execute and Cancelling Queries

Bun's SQL is lazy that means its will only start executing when awaited or executed with .execute(). You can cancel a query that is currently executing by calling the cancel() method on the query object.

const query = await sql`SELECT * FROM users`.execute();
setTimeout(() => query.cancel(), 100);
await query;

Database Environment Variables

sql connection parameters can be configured using environment variables. The client checks these variables in a specific order of precedence.

The following environment variables can be used to define the connection URL:

If no connection URL is provided, the system checks for the following individual parameters:

Connection Options

You can configure your database connection manually by passing options to the SQL constructor:

import { SQL } from "bun";

const db = new SQL({
  // Required
  url: "postgres://user:pass@localhost:5432/dbname",

  // Optional configuration
  hostname: "localhost",
  port: 5432,
  database: "myapp",
  username: "dbuser",
  password: "secretpass",

  // Connection pool settings
  max: 20, // Maximum connections in pool
  idleTimeout: 30, // Close idle connections after 30s
  maxLifetime: 0, // Connection lifetime in seconds (0 = forever)
  connectionTimeout: 30, // Timeout when establishing new connections

  // SSL/TLS options
  tls: true,
  // tls: {
  //   rejectUnauthorized: true,
  //   requestCert: true,
  //   ca: "path/to/ca.pem",
  //   key: "path/to/key.pem",
  //   cert: "path/to/cert.pem",
  //   checkServerIdentity(hostname, cert) {
  //     ...
  //   },
  // },

  // Callbacks
  onconnect: client => {
    console.log("Connected to database");
  },
  onclose: client => {
    console.log("Connection closed");
  },
});

Transactions

To start a new transaction, use sql.begin. This method reserves a dedicated connection for the duration of the transaction and provides a scoped sql instance to use within the callback function. Once the callback completes, sql.begin resolves with the return value of the callback.

The BEGIN command is sent automatically, including any optional configurations you specify. If an error occurs during the transaction, a ROLLBACK is triggered to release the reserved connection and ensure the process continues smoothly.

Basic Transactions

await sql.begin(async tx => {
  // All queries in this function run in a transaction
  await tx`INSERT INTO users (name) VALUES (${"Alice"})`;
  await tx`UPDATE accounts SET balance = balance - 100 WHERE user_id = 1`;

  // Transaction automatically commits if no errors are thrown
  // Rolls back if any error occurs
});

It's also possible to pipeline the requests in a transaction if needed by returning an array with queries from the callback function like this:

await sql.begin(async tx => {
  return [
    tx`INSERT INTO users (name) VALUES (${"Alice"})`,
    tx`UPDATE accounts SET balance = balance - 100 WHERE user_id = 1`,
  ];
});

Savepoints

Savepoints in SQL create intermediate checkpoints within a transaction, enabling partial rollbacks without affecting the entire operation. They are useful in complex transactions, allowing error recovery and maintaining consistent results.

await sql.begin(async tx => {
  await tx`INSERT INTO users (name) VALUES (${"Alice"})`;

  await tx.savepoint(async sp => {
    // This part can be rolled back separately
    await sp`UPDATE users SET status = 'active'`;
    if (someCondition) {
      throw new Error("Rollback to savepoint");
    }
  });

  // Continue with transaction even if savepoint rolled back
  await tx`INSERT INTO audit_log (action) VALUES ('user_created')`;
});

Distributed Transactions

Two-Phase Commit (2PC) is a distributed transaction protocol where Phase 1 has the coordinator preparing nodes by ensuring data is written and ready to commit, while Phase 2 finalizes with nodes either committing or rolling back based on the coordinator's decision. This process ensures data durability and proper lock management.

In PostgreSQL and MySQL, distributed transactions persist beyond their original session, allowing privileged users or coordinators to commit or rollback them later. This supports robust distributed transactions, recovery processes, and administrative operations.

Each database system implements distributed transactions differently:

PostgreSQL natively supports them through prepared transactions, while MySQL uses XA Transactions.

If any exceptions occur during the distributed transaction and aren't caught, the system will automatically rollback all changes. When everything proceeds normally, you maintain the flexibility to either commit or rollback the transaction later.

// Begin a distributed transaction
await sql.beginDistributed("tx1", async tx => {
  await tx`INSERT INTO users (name) VALUES (${"Alice"})`;
});

// Later, commit or rollback
await sql.commitDistributed("tx1");
// or
await sql.rollbackDistributed("tx1");

Authentication

Bun supports SCRAM-SHA-256 (SASL), MD5, and Clear Text authentication. SASL is recommended for better security. Check Postgres SASL Authentication for more information.

SSL Modes Overview

PostgreSQL supports different SSL/TLS modes to control how secure connections are established. These modes determine the behavior when connecting and the level of certificate verification performed.

const sql = new SQL({
  hostname: "localhost",
  username: "user",
  password: "password",
  ssl: "disable", // | "prefer" | "require" | "verify-ca" | "verify-full"
});

Using With Connection Strings

The SSL mode can also be specified in connection strings:

// Using prefer mode
const sql = new SQL("postgres://user:password@localhost/mydb?sslmode=prefer");

// Using verify-full mode
const sql = new SQL(
  "postgres://user:password@localhost/mydb?sslmode=verify-full",
);

Connection Pooling

Bun's SQL client automatically manages a connection pool, which is a pool of database connections that are reused for multiple queries. This helps to reduce the overhead of establishing and closing connections for each query, and it also helps to manage the number of concurrent connections to the database.

const db = new SQL({
  // Pool configuration
  max: 20, // Maximum 20 concurrent connections
  idleTimeout: 30, // Close idle connections after 30s
  maxLifetime: 3600, // Max connection lifetime 1 hour
  connectionTimeout: 10, // Connection timeout 10s
});

No connection will be made until a query is made.

const sql = Bun.sql(); // no connection are created

await sql`...`; // pool is started until max is reached (if possible), first available connection is used
await sql`...`; // previous connection is reused

// two connections are used now at the same time
await Promise.all([
  sql`INSERT INTO users ${sql({ name: "Alice" })}`,
  sql`UPDATE users SET name = ${user.name} WHERE id = ${user.id}`,
]);

await sql.close(); // await all queries to finish and close all connections from the pool
await sql.close({ timeout: 5 }); // wait 5 seconds and close all connections from the pool
await sql.close({ timeout: 0 }); // close all connections from the pool immediately

Reserved Connections

Bun enables you to reserve 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.

// Get exclusive connection from pool
const reserved = await sql.reserve();

try {
  await reserved`INSERT INTO users (name) VALUES (${"Alice"})`;
} finally {
  // Important: Release connection back to pool
  reserved.release();
}

// Or using Symbol.dispose
{
  using reserved = await sql.reserve();
  await reserved`SELECT 1`;
} // Automatically released

Error Handling

The client provides typed errors for different failure scenarios:

Connection Errors

Authentication Errors

Query Errors

Data Type Errors

Protocol Errors

Transaction Errors

Numbers and BigInt

Bun's SQL client includes special handling for large numbers that exceed the range of a 53-bit integer. Here’s how it works:

import { sql } from "bun";

const [{ x, y }] = await sql`SELECT 9223372036854777 as x, 12345 as y`;

console.log(typeof x, x); // "string" "9223372036854777"
console.log(typeof y, y); // "number" 12345

BigInt Instead of Strings

If you need large numbers as BigInt instead of strings, you can enable this by setting the bigint option to true when initializing the SQL client:

const sql = new SQL({
  bigint: true,
});

const [{ x }] = await sql`SELECT 9223372036854777 as x`;

console.log(typeof x, x); // "bigint" 9223372036854777n

Roadmap

There's still some things we haven't finished yet.

• Connection preloading via --db-preconnect Bun CLI flag
• MySQL support: we're working on it
• SQLite support: planned, but not started. Ideally, we implement it natively instead of wrapping bun:sqlite.
• Column name transforms (e.g. snake_case to camelCase). This is mostly blocked on a unicode-aware implementation of changing the case in C++ using WebKit's WTF::String.
• Column type transforms

Postgres-specific features

We haven't implemented these yet:

• COPY support
• LISTEN support
• NOTIFY support

We also haven't implemented some of the more uncommon features like:

• GSSAPI authentication
• SCRAM-SHA-256-PLUS support
• Point & PostGIS types
• All the multi-dimensional integer array types (only a couple of the types are supported)

Frequently Asked Questions

The plan is to add more database drivers in the future.

npm packages like postgres.js, pg, and node-postgres can be used in Bun too. They're great options.

Two reasons why:

1. We think it's simpler for developers to have a database driver built into Bun. The time you spend library shopping is time you could be building your app.
2. We leverage some JavaScriptCore engine internals to make it faster to create objects that would be difficult to implement in a library

Credits

Huge thanks to @porsager's postgres.js for the inspiration for the API interface.