SQL

You write queries as tagged template literals, and the client supports connection pooling, transactions, and prepared statements.

import { sql, SQL } from "bun";

// PostgreSQL (default)
const users = await sql`
  SELECT * FROM users
  WHERE active = ${true}
  LIMIT ${10}
`;

// With MySQL
const mysql = new SQL("mysql://user:pass@localhost:3306/mydb");
const mysqlResults = await mysql`
  SELECT * FROM users
  WHERE active = ${true}
`;

// With SQLite
const sqlite = new SQL("sqlite://myapp.db");
const sqliteResults = await sqlite`
  SELECT * FROM users
  WHERE active = ${1}
`;

Features#

• Tagged template literals to protect against SQL injection
• Transactions
• Positional parameters, and named parameters on SQLite
• Connection pooling
• BigInt support
• SASL (SCRAM-SHA-256, SCRAM-SHA-256-PLUS), MD5, and Clear Text authentication
• Connection timeouts
• Returning rows as data objects, arrays of arrays, or Buffer
• Binary protocol support makes it faster
• TLS support (and auth mode)
• Automatic configuration with environment variables
• PostgreSQL LISTEN / NOTIFY
• PostgreSQL COPY as web streams

Database Support#

Bun.SQL provides a unified API for multiple database systems:

PostgreSQL#

Bun uses PostgreSQL when:

• The connection string uses the postgres:// or postgresql:// protocol
• The connection string has no protocol, like localhost:5432/mydb
• You pass no connection string and no environment variable selects another adapter

import { sql } from "bun";
// Uses PostgreSQL if DATABASE_URL is not set or is a PostgreSQL URL
await sql`SELECT ...`;

import { SQL } from "bun";
const pg = new SQL("postgres://user:pass@localhost:5432/mydb");
await pg`SELECT ...`;

MySQL and MariaDB#

MySQL support is built into Bun.SQL, with the same tagged template literal interface, and is compatible with MySQL 5.7+, MySQL 8.0+, and MariaDB:

import { SQL } from "bun";

// MySQL connection
const mysql = new SQL("mysql://user:password@localhost:3306/database");
const mysql2 = new SQL("mysql2://user:password@localhost:3306/database"); // mysql2 protocol also works

// MariaDB connection
const mariadb = new SQL("mariadb://user:password@localhost:3306/database");

// Using options object
const mysql3 = new SQL({
  adapter: "mysql",
  hostname: "localhost",
  port: 3306,
  database: "myapp",
  username: "dbuser",
  password: "secretpass",
});

// Works with parameters - automatically uses prepared statements
const users = await mysql`SELECT * FROM users WHERE id = ${userId}`;

// Transactions work the same as PostgreSQL
await mysql.begin(async tx => {
  await tx`INSERT INTO users (name) VALUES (${"Alice"})`;
  await tx`UPDATE accounts SET balance = balance - 100 WHERE user_id = ${userId}`;
});

// Bulk inserts
const newUsers = [
  { name: "Alice", email: "alice@example.com" },
  { name: "Bob", email: "bob@example.com" },
];
await mysql`INSERT INTO users ${mysql(newUsers)}`;

mariadb:// and adapter: "mariadb" use the same driver as MySQL. They read the MARIADB_* environment variables where MySQL reads MYSQL_*.

Without a database in the URL or the options, Bun selects none: SELECT DATABASE() returns NULL, and table names need a database. prefix.

// Standard mysql:// protocol
new SQL("mysql://user:pass@localhost:3306/database");
new SQL("mysql://user:pass@localhost/database"); // Default port 3306

// mysql2:// protocol (compatibility with mysql2 npm package)
new SQL("mysql2://user:pass@localhost:3306/database");

// mariadb:// protocol
new SQL("mariadb://user:pass@localhost:3306/database");

// With query parameters
new SQL("mysql://user:pass@localhost/db?ssl=true");

// Unix socket connection
new SQL("mysql://user:pass@/database?socket=/var/run/mysqld/mysqld.sock");

const mysql = new SQL({
  adapter: "mysql",
  hostname: "localhost",
  username: "root",
  password: "secret",
  database: "myapp",
  allowPublicKeyRetrieval: true, // permit RSA public key retrieval without TLS
});

SQLite#

SQLite support is built into Bun.SQL, with the same tagged template literal interface:

import { SQL } from "bun";

// In-memory database
const memory = new SQL(":memory:");
const memory2 = new SQL("sqlite://:memory:");

// File-based database
const sql1 = new SQL("sqlite://myapp.db");

// Using options object. `filename` selects SQLite, so `adapter` is optional
const sql2 = new SQL({
  adapter: "sqlite",
  filename: "./data/app.db",
});

// A connection string that is a plain filename needs the adapter
const sql3 = new SQL("myapp.db", { adapter: "sqlite" });

// Standard sqlite:// protocol
new SQL("sqlite://path/to/database.db");
new SQL("sqlite:path/to/database.db"); // Without slashes

// file:// protocol (also recognized as SQLite)
new SQL("file://path/to/database.db");
new SQL("file:path/to/database.db");

// Special :memory: database
new SQL(":memory:");
new SQL("sqlite://:memory:");
new SQL("file://:memory:");

// Relative and absolute paths
new SQL("sqlite://./local.db"); // Relative to current directory
new SQL("sqlite://../parent/db.db"); // Parent directory
new SQL("sqlite:///absolute/path.db"); // Absolute path

// With query parameters
new SQL("sqlite://data.db?mode=ro"); // Read-only mode
new SQL("sqlite://data.db?mode=rw"); // Read-write mode (no create)
new SQL("sqlite://data.db?mode=rwc"); // Read-write-create mode (default)

const sql = new SQL({
  adapter: "sqlite",
  filename: "app.db",

  // SQLite-specific options
  readonly: false, // Open in read-only mode
  create: true, // Create database if it doesn't exist
  readwrite: true, // Open for reading and writing

  // Additional Bun:sqlite options
  strict: true, // Enable strict mode
  safeIntegers: false, // Use JavaScript numbers for integers
});

Inserting data#

Pass JavaScript values directly to the SQL template literal; Bun handles the escaping.

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 ($1, $2)

Bulk Insert#

You can also pass an array of objects, which Bun expands into an 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)}`;

The columns are the keys of the first row. Bun ignores a key that only a later row has, so that column gets its default in every row. A key that a later row lacks, or whose value is undefined, is NULL in that row. Bun leaves out a column that is undefined in every row, so the column's default applies, unless transform.undefined is set.

A value can also be a fragment:

await sql`INSERT INTO users ${sql({ name: "Alice", created_at: sql`now()` })}`;

Picking columns to insert#

Use sql(object, ...string) to pick which columns to insert.

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

Parameter types#

Bun converts a parameter by its JavaScript type. On PostgreSQL the type that the server expects for the parameter also counts.

See Numbers and BigInt for numbers and Dates and time zones for Date.

A JavaScript array works wherever PostgreSQL expects an array:

await sql`SELECT * FROM users WHERE id = ANY(${[1, 2, 3]})`;
await sql`INSERT INTO posts ${sql({ title: "Hello", tags: ["bun", "sql"] })}`; // tags text[]

Use sql.array for an array of a user-defined type such as an enum, because Bun cannot tell that it is an array type, and for a Float32Array, Float64Array or BigInt64Array:

await sql`INSERT INTO people ${sql({ name: "Alice", moods: sql.array(["ok", "happy"], "mood") })}`; // moods mood[]
await sql`INSERT INTO samples (points) VALUES (${sql.array(new Float32Array([1.5, 2.5]))})`; // points float4[]

sql.json#

PostgreSQL stores a string in a json or jsonb column as a JSON string. Pass the object itself, not JSON.stringify(object):

await sql`INSERT INTO settings (data) VALUES (${{ theme: "dark" }})`; // {"theme": "dark"}
await sql`INSERT INTO settings (data) VALUES (${"dark"})`; // "dark"
await sql`INSERT INTO settings (data) VALUES (${'{"theme":"dark"}'})`; // "{\"theme\":\"dark\"}", a string

sql.json(value) stores any value as JSON, on every adapter. Use it for a number, a boolean or null:

await sql`INSERT INTO settings (data) VALUES (${sql.json(42)})`; // 42
await sql`INSERT INTO settings (data) VALUES (${sql.json(null)})`; // JSON null. A bare null is SQL NULL
await sql`INSERT INTO settings (data) VALUES (${sql.json("dark")})`; // "dark"

MySQL, MariaDB, SQLite, and PostgreSQL with prepare: false send a string as it is, so the string must already be JSON text. There, ${"dark"} is not JSON, and ${sql.json("dark")} stores "dark".

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. Two other formats are available.

sql``.values() format#

The sql``.values() method returns each row as an array of values, in the same order as the columns in your query.

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

The rows look like:

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

sql``.values() is useful when a query returns duplicate column names. With objects (the default), the last column wins because the column name is the key. With sql``.values(), every column is present in the array, so you can read duplicates by index.

sql``.raw() format#

The .raw() method returns rows as arrays of Buffer objects. Use it for binary data or for performance.

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

• On PostgreSQL, each Buffer holds the bytes the server sent. A prepared query gets bool, int4, float4, float8, numeric, bytea, time, timestamp, timestamptz, int4[] and float4[] in PostgreSQL's binary format, so a bytea column is its bytes. Every other type is text. All types are text on the first run of a query without parameters, with .simple(), and with prepare: false.
• On MySQL, each Buffer holds the bytes the server sent. They are binary for a prepared query and text for .simple().
• SQLite returns Uint8Array objects.

Call .raw() before the query starts, that is before await, .then() or .execute(). After that, Bun may already have asked PostgreSQL for the formats that it decodes. Then numeric and time arrive as text, and int2[] and float8[] in binary. On SQLite, .execute() has already run the query when it returns, so .raw() and .values() have no effect after it.

Result metadata#

The array that a query resolves to has six more properties. console.log, Object.keys and spreading list count, command, lastInsertRowid and affectedRows next to the rows. columns and statement are not enumerable, and JSON.stringify only sees the rows.

const result = await sql`UPDATE users SET active = ${false} WHERE age < ${18}`;
console.log(result.count); // rows updated, on every adapter

• A query with several statements resolves to a plain array of results. Each inner array has its own metadata.
• PostgreSQL tags CREATE TABLE AS, and a SELECT whose CTE writes, as SELECT. affectedRows is 0 for them.
• MariaDB reports INSERT ... RETURNING as a result set: the rows are in count, and affectedRows is 0.

Column metadata#

On PostgreSQL and MySQL, result.columns describes each column of the result, as the server reported it. Use type to tell apart columns that give the same JavaScript value, such as jsonb and text[].

const result = await sql`SELECT id, tags, data FROM posts WHERE id = ${1}`;

result.columns;
// [
//   { name: "id", type: 23, table: 16388, number: 1 }, // int4
//   { name: "tags", type: 1009, table: 16388, number: 2 }, // text[]
//   { name: "data", type: 3802, table: 16388, number: 3 }, // jsonb
// ]

result.statement.string; // the text that Bun sent, with $1 for the parameter
result.statement.types; // [23], the parameter types that the server reported

• columns is also there for a result without rows, and with .values() and .raw(). It is [] for a statement that returns no rows, such as a plain INSERT.
• columns lists every column, including the duplicate names that a row object cannot hold.
• The runs of one prepared statement share one statement object, so treat it as read-only.

Column name transforms#

The transform option renames columns between the style of the database and the style of your code. SQL.camel, SQL.pascal and SQL.kebab are presets for a database that uses snake_case. They work on every adapter.

import { SQL } from "bun";

const sql = new SQL({ transform: SQL.camel });

await sql`INSERT INTO users ${sql({ firstName: "Alice", createdAt: new Date() })}`;
// INSERT INTO users ("first_name", "created_at") VALUES ($1, $2)

const [user] = await sql`SELECT first_name, created_at FROM users`;
console.log(user.firstName, user.createdAt);

For a rule of your own, pass the functions:

const sql = new SQL({
  transform: {
    column: {
      from: name => name.toLowerCase(), // a name that comes from the database
      to: name => name.toUpperCase(), // a name that goes to the database
    },
    undefined: null, // the value that Bun sends for `undefined`
  },
});

• column.from renames the keys of the row objects and the name of each entry in result.columns. A function as column means from.
• column.to renames the keys of sql(object), the columns that you pick in sql(object, "a", "b"), and every sql("name"), table names included.
• Bun calls from once for each column of a statement, not once for each row, so it must return the same name every time.
• There is no transform.value or transform.row as in postgres.js, so the presets do not rename the keys inside json and jsonb values.
• With transform.undefined, every undefined parameter is that value, and the insert and update helpers leave no column out. Without it, undefined is NULL.

SQL Fragments#

Bun can build queries dynamically from runtime conditions without risking SQL injection.

Dynamic Table Names#

To reference tables, schemas or columns dynamically, use the sql() helper, which quotes the name as an identifier:

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

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

A dot separates schema, table and column, and Bun quotes each part: sql("public.users") becomes "public"."users", or `public`.`users` on MySQL.

Quoting prevents SQL injection, but it does not limit which table or column the name reaches. Check a name that comes from a user against a list of allowed names.

Conditional Queries#

Use the sql() helper to build queries with conditional clauses:

// 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``}
`;

// Bun joins an array of fragments with spaces
const filters = [sql`AND age > ${minAge}`, sql`AND name LIKE ${"A%"}`];
await sql`SELECT * FROM users WHERE active = ${true} ${filters}`;

Dynamic columns in updates#

Use sql(object, ...string) to pick which columns to update. If you don't list any columns, Bun uses all keys on the object. Bun skips a column whose value is undefined, unless transform.undefined is set.

await sql`UPDATE users SET ${sql(user, "name", "email")} WHERE id = ${user.id}`;
// uses all keys from the object to update the row
await sql`UPDATE users SET ${sql(user)} WHERE id = ${user.id}`;

Dynamic values and where in#

Value lists can also be created dynamically, for WHERE IN queries. You can also pass an array of objects and name the key to build the list from.

await sql`SELECT * FROM users WHERE id IN ${sql([1, 2, 3])}`;

const users = [
  { id: 1, name: "Alice" },
  { id: 2, name: "Bob" },
  { id: 3, name: "Charlie" },
];
await sql`SELECT * FROM users WHERE id IN ${sql(users, "id")}`;

An empty list matches no row. On PostgreSQL and MySQL it becomes IN (NULL), so NOT IN with an empty list matches no row either. On SQLite it becomes IN (), and NOT IN () matches every row.

sql.array helper#

The sql.array helper binds a JavaScript array as one PostgreSQL array parameter:

// PostgreSQL infers text[] from the column
await sql`INSERT INTO tags (items) VALUES (${sql.array(["red", "blue", "green"])})`;

// PostgreSQL infers the element type from the compared column
await sql`SELECT * FROM products WHERE id = ANY(${sql.array([1, 2, 3])})`;

// Pass the element type when the query gives PostgreSQL no context
await sql`SELECT ${sql.array([1, 2, 3], "INT")} AS ids`;
// Sends: SELECT $1::INT[] AS ids

A null element is SQL NULL, and nested arrays make a multi-dimensional array.

Pass "JSON" or "JSONB" for a json[] or jsonb[] column, so that Bun encodes each element as JSON. There, null is the JSON value null, and undefined is SQL NULL. A nested array is still a dimension. To store a JSON array as one element, wrap it in sql.json:

await sql`INSERT INTO events (payloads) VALUES (${sql.array([{ a: 1 }, null, sql.json([1, 2])], "JSONB")})`;
// three elements: {"a": 1}, null and [1, 2]

sql``.simple()#

The PostgreSQL wire protocol supports two types of queries: "simple" and "extended". Simple queries can contain multiple statements but don't support parameters, while extended queries (the default) support parameters but only allow one statement.

To run multiple statements in a single query, use sql``.simple():

// Multiple statements in one query
await sql`
  SELECT 1;
  SELECT 2;
`.simple();

Simple queries are useful for database migrations and setup scripts.

Simple queries cannot use parameters (${value}). If you need parameters, split your query into separate statements.

These rules apply to every string with several statements, also through sql.file and sql.unsafe:

• On PostgreSQL and MySQL, the query resolves to an array with one result per statement.
• On SQLite, every statement runs and the query resolves to one result. It holds the rows of the first statement if that statement returns rows. Otherwise its count is the number of rows that all the statements changed. Only the first statement can have parameters. Bun refuses a later statement that has one when it reaches it, like any other statement that fails.
• Only PostgreSQL runs the string as one implicit transaction. On MySQL and SQLite, the statements before a failing one stay applied. Wrap the call in sql.begin if you need all or nothing.

Queries in files#

sql.file reads a query from a file and executes it. If the file uses placeholders like $1 and $2, you can pass parameters to the query. Without parameters, the file can contain multiple commands.

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

sql.file returns a lazy query like any other: Bun reads the file when you await the query.

With the SQLite adapter, parameters can also be an object of named parameters (:name, $name, or @name placeholders), the same form sql.unsafe accepts below.

Unsafe Queries#

sql.unsafe executes raw SQL strings. Use it with caution: it does not escape user input. Without parameters, the string can contain more than one command.

// Multiple commands without parameters
const result = await sql.unsafe(`
  SELECT ${userColumns} FROM users;
  SELECT ${accountColumns} FROM accounts;
`);

// Using parameters (only one command is allowed)
const result = await sql.unsafe("SELECT " + dangerous + " FROM users WHERE id = $1", [id]);

The string goes to the database as written, so sql.unsafe and sql.file take the database's own placeholders:

Pass positional parameters as an array. Bun copies the array in the call, so a later change to it has no effect.

With the SQLite adapter, parameters can also be an object of named parameters. A key works with or without its prefix: { ":id": 1 } or { id: 1 }. A parameter without a key is NULL, or an error if the connection sets strict: true.

const sql = new SQL({ adapter: "sqlite", filename: "myapp.db" });
const result = await sql.unsafe("SELECT * FROM users WHERE id = :id", { id: 1 });

You can nest sql.unsafe in a tagged template, and Bun adds its text as written. On PostgreSQL, a nested sql.unsafe that has parameters cannot follow other parameters, because $1 in its text is the first parameter of the whole query. Such a query rejects with a SyntaxError. Use a sql fragment for a condition that has a value:

await sql`SELECT * FROM users WHERE active = ${true} ORDER BY ${sql.unsafe("name DESC")}`;
await sql`SELECT * FROM users WHERE active = ${true} AND ${sql`age > ${21}`}`;

Execute and Cancelling Queries#

Queries are lazy: they only start executing when awaited or run with .execute().

A query starts in the call to .then(), .catch(), .finally() or .execute(). await query calls .then(). A query that you never await or execute never runs. query.active is true while a connection runs the query.

To cancel a query, call cancel() on the query object.

const query = sql`SELECT pg_sleep(30)`.execute();
setTimeout(() => query.cancel(), 100);
await query; // rejects: canceling statement due to user request

What cancel() does depends on how far the query got:

• query.cancelled is true after cancel(), in every case.
• A PostgreSQL server only takes a cancel request on a second connection, so Bun opens one for the request, with the same TLS settings. A query that ends before the request arrives resolves as usual. The request stops whatever the connection runs when it arrives, so Bun does not send one while another query that it sent on that connection waits for its result. If Bun cannot deliver the request, the query rejects like one in the last row.
• An ERR_POSTGRES_QUERY_CANCELLED error without a hint means that the query never reached the server, so it is safe to run again.
• SQLite runs a query inside the call that starts it. cancel() only stops a query that has not started, with ERR_SQLITE_QUERY_CANCELLED.

Unhandled rejections#

.then() and .catch() return a promise like any other. When it rejects and nothing handles the rejection, Bun reports an unhandled rejection, which ends the process unless you listen for unhandledRejection. So does an error that your .finally() callback throws.

A query that you only start, with .execute() or .finally(), is the exception. Its own failure is not reported, and the error is lost, when:

• The query had a connection: the server refused it, or the connection was lost.
• close({ timeout: 0 }) closed the pool while the query waited for a connection.
• You called .cancel() before you started it.
• Its reservation was released, or its transaction or savepoint has ended.

After .execute(), Bun does report the failure when:

• It cannot connect: nothing listens, the login fails or the connection times out.
• The pool was already closed.
• The time of close({ timeout }), with a timeout above 0, runs out while the query waits for a connection.
• You call .cancel() while the query waits for a connection.
• It cannot build the query or read the file of sql.file().
• The adapter is SQLite and the query runs outside a transaction.

Add a .catch() to a query that you start and do not await:

sql`UPDATE stats SET hits = hits + 1`.catch(err => console.error(err));

When Bun reads parameters#

Bun reads the parameters of a tagged template when it sends the query, not when it evaluates the template. Leave a Buffer, TypedArray, Date or object alone until the query settles, or pass a copy.

const bytes = new Uint8Array([1, 2, 3, 4]);
const query = sql`INSERT INTO files (data) VALUES (${bytes})`;
bytes.fill(0); // the query has not read `bytes` yet
await query; // stores 00000000, not 01020304

Database Environment Variables#

Bun.sql and new SQL() read connection settings from environment variables.

Precedence#

For each setting, Bun uses the first source that has a value:

1. An option, such as new SQL({ hostname: "db" }) or the second argument of new SQL(url, options)
2. The connection URL that you pass, including its query string
3. A connection URL from the environment, such as DATABASE_URL
4. A variable for one field, such as PGHOST or MYSQL_USER
5. The default

The database name is the exception: PGDATABASE, MYSQL_DATABASE and MARIADB_DATABASE take priority over the database in a URL.

An options object changes single fields of the URL from the environment. The rest of the URL still applies, including its user, password and sslmode:

// DATABASE_URL=postgres://app:secret@primary.internal:6543/shop?sslmode=verify-full
const replica = new SQL({ hostname: "replica.internal" });
// replica.internal:6543, user app, database shop, sslmode=verify-full

Bun does not read a URL from the environment when the first argument is a connection string. Next to the url option, only the name of the variable counts: MYSQL_URL selects MySQL for a url without a protocol, and a TLS_* variable asks for sslmode=require.

Automatic Database Detection#

Bun detects the adapter from the connection string:

An unknown protocol such as pg:// or sqlite3:// throws. The adapter option takes priority over the protocol, and an options object with filename selects SQLite.

DATABASE_URL="postgres://user:pass@localhost:5432/mydb" bun run app.js
DATABASE_URL="mysql://user:pass@localhost:3306/mydb" bun run app.js
DATABASE_URL="sqlite://./data/app.db" bun run app.js

Connection URL variables#

Bun uses the first of these variables that is set:

• A URL that Bun reads from a TLS_* variable uses sslmode=require unless the URL or an option names a mode. If you pass process.env.TLS_DATABASE_URL to new SQL() yourself, add ?sslmode=require or tls: true.
• With the adapter option, Bun reads DATABASE_URL and then only that adapter's variables. It does not connect to a DATABASE_URL whose protocol names another adapter. When no other URL applies, the connection still uses the TLS mode that URL asks for, unless tls: false or ssl says otherwise. A URL without a host names no other server, so its user, password, database and TLS mode apply.
• Next to hostname, host, path, socket or url, Bun skips a SQLite URL.

PostgreSQL Environment Variables#

PGSSLMODE also applies next to a connection URL. A URL from a TLS_* variable uses at least require; PGSSLMODE=verify-ca and PGSSLMODE=verify-full raise that.

An sslmode in the URL takes priority over both, and a mode in the tls or ssl option takes priority over the URL. tls: true and a tls object ask for at least require and never lower a stricter mode.

MySQL and MariaDB Environment Variables#

These variables do not select the adapter. Bun reads them for a client that is already MySQL or MariaDB, through its URL or the adapter option.

SQLite Environment Variables#

Set SQLITE_URL to a file name or a SQLite URL, or set DATABASE_URL to a SQLite URL:

SQLITE_URL="./app.db"
DATABASE_URL=":memory:"
DATABASE_URL="sqlite://./app.db"
DATABASE_URL="file:///absolute/path/to/db.sqlite"

A SQLite client reads no other variable. PGHOST and the like have no effect on it.

Connection URL parameters#

Bun reads these keys from the query string of a connection URL:

On PostgreSQL, Bun sends every other key to the server as a startup parameter, like the connection option: ?application_name=api&search_path=app. Bun leaves out the keys that only configure libpq, such as keepalives and target_session_attrs.

Runtime Preconnection#

Bun can preconnect to PostgreSQL at startup, before your application code runs, so the first query doesn't pay the connection latency.

# Enable PostgreSQL preconnection
bun --sql-preconnect index.js

# Works with DATABASE_URL environment variable
DATABASE_URL=postgres://user:pass@localhost:5432/db bun --sql-preconnect index.js

# Can be combined with other runtime flags
bun --sql-preconnect --hot index.js

The --sql-preconnect flag connects the default client, Bun.sql, at startup using your configured environment variables. It also works when DATABASE_URL is a MySQL or MariaDB URL. If the connection fails, Bun handles the error without crashing your application.

Connection Options#

You can configure the connection manually by passing options to the SQL constructor. Options vary by adapter:

PostgreSQL and MySQL options#

sql.options holds the resolved settings. Treat it as output: its timeouts are in milliseconds and sslMode is a number, so new SQL(sql.options) does not make an equal client. console.log(sql.options) prints [REDACTED] for the password and the TLS key.

MySQL Options#

import { SQL } from "bun";

const sql = new SQL({
  // Required for MySQL when using options object
  adapter: "mysql", // or "mariadb"

  // Connection details
  hostname: "localhost",
  port: 3306,
  database: "myapp",
  username: "dbuser",
  password: "secretpass",

  // Unix socket connection (alternative to hostname/port)
  // path: "/var/run/mysqld/mysqld.sock",

  // Connection pool settings
  max: 20, // Maximum connections in pool (default: 10)
  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: "prefer", // or "disable", "require", "verify-ca", "verify-full"
  // tls: {
  //   rejectUnauthorized: true,
  //   ca: Bun.file("path/to/ca.pem"),
  //   key: Bun.file("path/to/key.pem"),
  //   cert: Bun.file("path/to/cert.pem"),
  // },

  // Callbacks
  onconnect: () => {
    console.log("Connected to MySQL");
  },
  onclose: err => {
    // err.code is for example ERR_MYSQL_CONNECTION_CLOSED or ERR_MYSQL_IDLE_TIMEOUT
    console.log("MySQL connection closed:", err);
  },
});

PostgreSQL Options#

import { SQL } from "bun";

const sql = new SQL({
  // Connection details (adapter is auto-detected as PostgreSQL)
  url: "postgres://user:pass@localhost:5432/dbname",

  // Alternative connection parameters. They take priority over the URL
  hostname: "localhost",
  port: 5432,
  database: "myapp",
  username: "dbuser",
  password: "secretpass",

  // Unix socket connection (alternative to hostname/port)
  // path: "/var/run/postgresql",

  // 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: Bun.file("path/to/ca.pem"),
  //   key: Bun.file("path/to/key.pem"),
  //   cert: Bun.file("path/to/cert.pem"),
  //   checkServerIdentity(hostname, cert) {
  //     // return an Error to refuse the certificate
  //   },
  // },

  // Callbacks
  onconnect: () => {
    console.log("Connected to PostgreSQL");
  },
  onclose: err => {
    // err.code is for example ERR_POSTGRES_CONNECTION_CLOSED or ERR_POSTGRES_IDLE_TIMEOUT
    console.log("PostgreSQL connection closed:", err);
  },
});

SQLite Options#

import { SQL } from "bun";

const sql = new SQL({
  adapter: "sqlite", // Optional when `filename` is set
  filename: "./data/app.db", // or ":memory:" for in-memory database

  // SQLite-specific access modes
  readonly: false, // Open in read-only mode
  create: true, // Create database if it doesn't exist
  readwrite: true, // Allow read and write operations

  // SQLite data handling
  strict: true, // Throw when a named parameter is missing
  safeIntegers: false, // true returns every integer as a BigInt (`bigint: true` does the same)

  // Callbacks
  onconnect: err => {
    if (err) console.error("Failed to open the SQLite database:", err);
  },
  onclose: () => {
    console.log("SQLite database closed");
  },
});

Connection callbacks#

onconnect(err) and onclose(err) get one argument. Neither gets the connection.

• onconnect runs once for each connection that opens, with null. Only SQLite passes an error here, when it cannot open the file.
• onclose runs once for each connection that closes, and always gets an Error. After sql.close() its code is ERR_POSTGRES_CONNECTION_CLOSED or ERR_MYSQL_CONNECTION_CLOSED.
• On PostgreSQL and MySQL, Bun reports a failed connection attempt to onclose, not to onconnect.
• The callbacks also run for the connection that sql.listen() opens.

Dynamic passwords#

For alternative authentication schemes such as access tokens, or databases with rotating passwords, set password to a synchronous or asynchronous function. Bun calls it at connection time to resolve the password.

import { SQL } from "bun";

const sql = new SQL(url, {
  // Other connection config
  ...
  // Password function for the database user
  password: async () => await signer.getAuthToken(),
});

SQLite-Specific Features#

Query Execution#

SQLite executes queries synchronously, unlike PostgreSQL, which uses asynchronous I/O. The API still returns Promises:

const sqlite = new SQL("sqlite://app.db");

// Works the same as PostgreSQL, but executes synchronously under the hood
const users = await sqlite`SELECT * FROM users`;

// Parameters work identically
const user = await sqlite`SELECT * FROM users WHERE id = ${userId}`;

SQLite Pragmas#

Use PRAGMA statements to configure SQLite behavior:

const sqlite = new SQL("sqlite://app.db");

// Enable foreign keys
await sqlite`PRAGMA foreign_keys = ON`;

// Set journal mode to WAL for better concurrency
await sqlite`PRAGMA journal_mode = WAL`;

// Check integrity
const integrity = await sqlite`PRAGMA integrity_check`;

Data Type Differences#

SQLite has a more flexible type system than PostgreSQL:

// SQLite stores data in 5 storage classes: NULL, INTEGER, REAL, TEXT, BLOB
const sqlite = new SQL("sqlite://app.db");

// SQLite is more lenient with types
await sqlite`
  CREATE TABLE flexible (
    id INTEGER PRIMARY KEY,
    data TEXT,        -- Can store numbers as strings
    value NUMERIC,    -- Can store integers, reals, or text
    blob BLOB         -- Binary data
  )
`;

// JavaScript values are automatically converted
await sqlite`INSERT INTO flexible VALUES (${1}, ${"text"}, ${123.45}, ${Buffer.from("binary")})`;

Transactions#

To start a new transaction, use sql.begin. This method works on every adapter. For PostgreSQL and MySQL, it reserves a dedicated connection from the pool. For SQLite, it begins a transaction on the single connection.

Bun sends the BEGIN command automatically (START TRANSACTION on MySQL), including any optional configurations you specify. If an error occurs during the transaction, Bun issues a ROLLBACK and sql.begin rejects with the error.

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

To pipeline the queries in a transaction, return an array of queries from the callback:

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

The adapters differ in two ways:

• MySQL: DDL (CREATE, ALTER, DROP) commits the open transaction, so sql.begin cannot roll back past it. PostgreSQL and SQLite roll DDL back.
• SQLite: a second sql.begin waits until the first one ends. A plain sql query does not wait. It runs on the same connection, inside the open transaction.

After the transaction ends#

The tx client belongs to its transaction. Once Bun has sent COMMIT or ROLLBACK, a query on tx never reaches the database. It rejects with ERR_POSTGRES_INVALID_TRANSACTION_STATE, or the same code with MYSQL or SQLITE.

Queries are lazy, so this includes a query that you create inside the callback and first await later. Await every query inside the callback, or return the queries in an array. sql.begin resolves once the database has answered COMMIT. If the connection is lost while COMMIT is unanswered, sql.begin rejects and nobody can know whether the transaction was committed, so check before you run it again.

let late;
await sql.begin(async tx => {
  late = tx`INSERT INTO users (name) VALUES (${"Bob"})`; // created, not started
});
await late; // rejects, and inserts nothing

tx.close() rolls the transaction back. sql.begin then rejects with ERR_POSTGRES_CONNECTION_CLOSED, or the same code with MYSQL or SQLITE.

Savepoints#

Savepoints create intermediate checkpoints within a transaction, so part of it can roll back without aborting the whole thing.

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

  try {
    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");
      }
    });
  } catch (err) {
    // The savepoint rolled back, and the transaction continues
  }

  await tx`INSERT INTO audit_log (action) VALUES ('user_created')`;
});

When the callback throws, Bun rolls back to the savepoint and tx.savepoint rejects with the error. Catch the error to continue the transaction. An error that leaves the sql.begin callback rolls back everything.

• The callback gets its own client, sp. It ends with the savepoint, the way tx ends with its transaction.
• For a savepoint inside a savepoint, call sp.savepoint().
• tx.savepoint("audit", fn) adds a label to the name that Bun generates: SAVEPOINT s0_audit.
• Run the savepoints of one transaction one after the other, in a for loop rather than Promise.all. A savepoint holds every statement that its connection runs while it is open, so a failing savepoint also rolls back the rows of the savepoints that run next to it.

Distributed Transactions#

Two-Phase Commit (2PC) is a distributed transaction protocol. 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.

In PostgreSQL and MySQL, distributed transactions persist beyond their original session, so privileged users or coordinators can commit or roll them back later. PostgreSQL implements them as prepared transactions; MySQL uses XA Transactions.

An uncaught exception during the distributed transaction rolls back all changes. Otherwise, you can commit or roll back the transaction later.

On MariaDB, a prepared transaction stays bound to its connection while that connection is open. Commit it or roll it back on the same connection, with max: 1 or a reserved connection, or after that connection has closed.

// 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 and SCRAM-SHA-256-PLUS (SASL), MD5, and Clear Text authentication. We recommend SASL for better security. See Postgres SASL Authentication.

A non-ASCII password logs in whichever Unicode form you type it in. Bun first logs in with the password as you typed it. If the server refuses it, Bun tries once more with its SASLprep form, the form that psql uses. A wrong non-ASCII password can therefore count as two failed logins.

Channel binding#

Over TLS, PostgreSQL can tie a SCRAM login to the server's certificate (SCRAM-SHA-256-PLUS). Someone who terminates TLS between the client and the server then cannot pass the login on. Set channelBinding to control it:

const sql = new SQL("postgres://user:password@db.example.com/mydb?sslmode=verify-full", {
  channelBinding: "require",
});

You can also set it with ?channel_binding= in the URL or with PGCHANNELBINDING. Channel binding does not replace certificate verification, so keep verify-full.

A bound login fails behind a proxy that terminates TLS, which is what it is for. With "prefer" and sslmode=prefer, a connection that fell back to plaintext fails with ERR_POSTGRES_CHANNEL_BINDING_FAILED when the server offers SCRAM-SHA-256-PLUS: the server is on TLS, so something between the two removed it.

Only PostgreSQL has channel binding. On MySQL, MariaDB and SQLite, "require" throws when you create the client, and the other values have no effect.

SSL Modes Overview#

PostgreSQL's SSL/TLS modes control whether a secure connection is required and how much certificate verification is performed.

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

• NODE_TLS_REJECT_UNAUTHORIZED=0 turns certificate verification off, also for verify-ca and verify-full. tls: { rejectUnauthorized: true } verifies whatever the variable says.
• A tls object with ca, caFile, checkServerIdentity or rejectUnauthorized: true raises disable, prefer and require to verify-full.
• On a PostgreSQL unix socket, Bun ignores the SSL mode, like libpq.

Using With Connection Strings#

You can also set the SSL mode in the connection string:

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

Custom CA Certificates#

To verify the server against a CA that is not in the default trust store (for example an Amazon RDS or Google Cloud SQL bundle), pass the CA certificate as tls.ca, or pass the BunFile directly as tls. Both forms verify the certificate chain and the hostname (verify-full).

const sql = new SQL("postgres://user:password@db.example.com/mydb", {
  tls: { ca: Bun.file("./rds-ca-bundle.pem") },
});

// Shorthand for the same configuration
const sql = new SQL("postgres://user:password@db.example.com/mydb", {
  tls: Bun.file("./rds-ca-bundle.pem"),
});

Checking the server's identity#

tls.checkServerIdentity(hostname, cert) replaces the built-in check of the host name against the certificate, as in node:tls. It works on PostgreSQL, MySQL and MariaDB. Return undefined to accept the certificate. Return or throw an Error to fail the connection with that error.

const sql = new SQL("postgres://user:password@db.example.com/mydb", {
  tls: {
    ca: Bun.file("./ca.pem"),
    // Only accept one certificate
    checkServerIdentity: (hostname, cert) =>
      cert.fingerprint256 === process.env.DB_CERT_FINGERPRINT ? undefined : new Error("certificate pin mismatch"),
  },
});

• Bun calls it once for each connection, after the certificate chain verified and before it sends the user name or the password.
• hostname is tls.serverName, or else the host that Bun connects to. cert is the server's certificate in the form that node:tls uses.
• The function must be synchronous. A promise fails the connection with ERR_INVALID_RETURN_VALUE.
• To keep the host name check, call checkServerIdentity(hostname, cert) from node:tls inside the function.
• Bun does not call it with rejectUnauthorized: false.

Connection Pooling#

Bun's SQL client manages a connection pool. The pool reuses database connections across queries instead of opening and closing one for each query, and it caps the number of concurrent connections.

const sql = 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
});

Bun doesn't open a connection until you run a query.

const sql = new SQL(); // no connection yet

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}`,
]);

• Bun starts the pool when a caller finds no connection ready: at the first query, and again after every connection has closed, for example at idleTimeout. It opens connections until the pool has max. Queries share the open connections.
• sql.begin and sql.reserve need a connection of their own, so they wait for an idle one.
• Bun serves the callers that wait for a connection oldest first.
• A pool that lost some of its connections opens new ones when queries have to wait.
• When Bun cannot open a new connection, callers wait for the connections that are open. They reject with the error of that attempt only when the pool has no connection left. A caller that finds every open connection reserved makes Bun try again at once. While an open connection can still take queries, Bun adds at most one connection at a time for the queries that wait, and after each failed attempt it pauses before the next: a few tens of milliseconds at first, then twice as long each time, up to 1 second.
• Bun closes a connection that goes back to the pool inside a transaction, and the server rolls that transaction back. That covers a reserved connection that you release after a manual BEGIN, and a query that leaves a transaction open or failed, such as SELECT 1; BEGIN. Queries of other callers that Bun already sent behind such a query are rolled back with it. With max: 1, where a manual BEGIN is allowed, a query can leave its transaction open.

Hot reloading#

With bun --hot, a new SQL() with the same connection settings takes over the open connections of the previous load. A reload opens no new connection, and the database sees the same sessions.

• onconnect runs again for each connection that the new client takes over.
• Session state, such as SET and temporary tables, stays on the connection.
• When you edit the settings, the old connections stay open for one more reload. At the first query of a load, Bun closes the connections of clients from earlier loads that ran no query during the whole previous load: idle ones at once, busy ones when they finish.
• A client that you keep on globalThis and use keeps its connections.
• A password or transform.column.from function that the file defines is a new function in each load. Bun treats it as a changed setting and connects again, so the database sees up to twice max sessions. Presets such as SQL.camel stay the same.

A sql.listen() subscription of an earlier load stays active, so a callback that you register at module level runs once for each load that registered it. Keep the subscription on globalThis, and remove it before you subscribe again:

declare global {
  var subscription: Bun.SQL.ListenSubscription | undefined;
}

await globalThis.subscription?.unlisten();
globalThis.subscription = await sql.listen("orders", handleOrder);

Timeouts#

idleTimeout and maxLifetime never interrupt work. A running query, a begin() transaction and a reserved connection keep their connection. A connection past maxLifetime finishes what it has, takes no new queries, and then closes.

Bun turns on TCP keepalive (60 seconds) for PostgreSQL and MySQL connections, so a query fails instead of waiting forever when the server disappears without closing the connection.

Closing the pool#

await sql.close(); // waits for the running queries, then closes every connection
await sql.close({ timeout: 5 }); // waits at most 5 seconds
await sql.close({ timeout: 0 }); // closes every connection immediately

• close() waits for the queries that started before the call. When the timeout ends, the queries that still run reject with ERR_POSTGRES_CONNECTION_CLOSED or ERR_MYSQL_CONNECTION_CLOSED.
• After close(), new queries, sql.begin and sql.reserve reject with the same code.
• You can call close() again. Every call resolves when the pool is closed, and the shortest timeout counts: close({ timeout: 0 }) after close() closes the pool immediately.
• await query starts the query in a microtask. A close() later in the same tick comes first, and the query rejects.

Reserved Connections#

sql.reserve() takes a connection from the pool and returns a client that wraps it, so you can run 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
  await reserved.release();
}

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

When every connection is busy, reserve() waits until one frees up. Pass an AbortSignal to stop waiting: the promise rejects with signal.reason and no connection is taken from the pool. Aborting after the promise resolved has no effect, so a connection you received must still be released.

// Give up if no connection frees up within 5 seconds
using reserved = await sql.reserve({ signal: AbortSignal.timeout(5000) });

After release()#

release() closes the reserved client immediately. A query on it that has not started rejects with ERR_POSTGRES_CONNECTION_CLOSED or ERR_MYSQL_CONNECTION_CLOSED, because the connection can already belong to another caller.

Queries are lazy, so await each query before the release:

async function countUsers() {
  using reserved = await sql.reserve();
  return await reserved`SELECT count(*) FROM users`; // without `await`, the query starts after the release and rejects
}

• A transaction that you start with reserved.begin() keeps the connection until it ends, even if you call release() meanwhile. A second reserved.begin() waits for the first.
• reserved.close() closes the connection rather than returning it to the pool.
• Bun also closes a connection that you release inside an open transaction, for example after a manual BEGIN. The next caller gets a new one.
• SQLite has a single connection, so sql.reserve() rejects.

LISTEN / NOTIFY (PostgreSQL)#

sql.listen() subscribes to a PostgreSQL notification channel and sql.notify() publishes to one. Processes sharing a database can use this as a lightweight message bus: cache invalidation, waking workers when rows are inserted (typically from a trigger calling pg_notify), fan-out of small events.

import { sql } from "bun";

const subscription = await sql.listen("orders", payload => {
  console.log("new order", JSON.parse(payload));
});

await sql.notify("orders", JSON.stringify({ id: 42 }));

await subscription.unlisten();

listen() resolves once PostgreSQL has acknowledged the LISTEN, so a notify() issued after it resolves is delivered. The subscription it resolves to is also an async disposable:

{
  await using subscription = await sql.listen("orders", handleOrder);
  await doWork();
} // unlistened here

How it works#

• All subscriptions on a client share one dedicated connection. The first listen() opens it, and removing the last subscription closes it. As a result, a client that never listens never pays for the connection, and unlistening everything lets the process exit without sql.close().
• While anything is subscribed, that connection keeps the process alive, like a listening server.
• If the connection drops, Bun re-establishes it with exponential backoff (250ms doubling to 32s, with jitter) and re-subscribes every channel. PostgreSQL only delivers to connected listeners, so notifications sent in between are lost. The optional third argument to listen() runs on the initial subscribe and after every reconnect, which is the place to catch up:

await sql.listen("orders", handleOrder, async () => {
  for (const order of await sql`SELECT * FROM orders WHERE processed = false`) handleOrder(order);
});

• A server that sets idle_session_timeout ends this connection at every limit, because a connection that only listens is idle. To keep it open, create the client with connection: { idle_session_timeout: 0 }.
• Every listen() call is its own subscription. Several on one channel share a single server-side LISTEN. Each callback receives every notification, and each handle's unlisten() removes only what its own call registered. A callback (either argument) that throws is reported as an uncaught exception and stays subscribed.
• Bun quotes channel names as identifiers for you. Like any PostgreSQL identifier, they are limited to 63 bytes, and Bun rejects longer names rather than silently truncating them. PostgreSQL limits payloads to 8000 bytes by default.

notify()#

notify() is an ordinary query (SELECT pg_notify($1, $2)) on whichever handle you call it through. On sql it uses the pool. Inside sql.begin() it runs in the transaction, so PostgreSQL delivers it on COMMIT and drops it on ROLLBACK. Calling notify() inside a transaction is how to announce a change only once it is visible:

await sql.begin(async tx => {
  const [order] = await tx`INSERT INTO orders ${sql(data)} RETURNING id`;
  await tx.notify("orders", String(order.id)); // delivered when the transaction commits
});

The payload is optional: sql.notify("cache-invalidated") is PostgreSQL's bare NOTIFY. Reserved and transaction handles also have listen(); it always uses the client's shared listen connection.

LISTEN/NOTIFY is PostgreSQL-only; on MySQL and SQLite these methods reject.

COPY (PostgreSQL)#

COPY moves rows in bulk. query.writable() returns a WritableStream that feeds COPY ... FROM STDIN, and query.readable() returns a ReadableStream with the output of COPY ... TO STDOUT.

// Load a CSV file into a table
await Bun.file("users.csv")
  .stream()
  .pipeTo(sql`COPY users (id, name) FROM STDIN (FORMAT csv)`.writable());

// Write a table to a CSV file
await Bun.write("users.csv", new Response(sql`COPY users TO STDOUT (FORMAT csv, HEADER)`.readable()));

// Stream a query as an HTTP response
Bun.serve({
  fetch: () => new Response(sql`COPY (SELECT * FROM users) TO STDOUT (FORMAT csv)`.readable()),
});

To write the rows yourself, use a writer. The query is still a promise, and resolves with the row count:

const query = sql`COPY users FROM STDIN`;
const writer = query.writable().getWriter();
await writer.write("1\tAlice\n");
await writer.write("2\tBob\n");
await writer.close(); // resolves when the server has stored the rows

const { count } = await query; // 2

• The streams carry bytes in the format that the statement names: text, csv or binary. Bun does not encode or parse rows, and a chunk can end in the middle of a row.
• close() and pipeTo() reject with the server's error, such as errno "22P02" for bad data. abort() stores nothing. The connection stays usable after both.
• Both streams apply backpressure, so memory use stays flat however large the table is.
• COPY takes no ${value} parameters. sql("name") identifiers work.
• Call writable() or readable() before the query starts. Both start it.
• The connection runs nothing else until the COPY ends, so always close or abort the WritableStream.
• Bun aborts a stream that is still open when its transaction, savepoint or reservation ends. Nothing of the COPY is stored, and sql.begin rolls back and rejects with the error of the COPY. Await writer.close() or pipeTo(), or read the stream to its end, before the callback returns or you call release().
• query.cancel(), reader.cancel() and leaving a for await loop end the COPY.
• For Node.js streams, wrap them with Readable.fromWeb() and Writable.fromWeb().

Prepared Statements#

By default, Bun's SQL client creates named prepared statements for queries it can infer are static, which is faster.

Each connection keeps the statements it used last: 1,024 on PostgreSQL, 256 on MySQL and MariaDB, where all connections share the server's max_prepared_stmt_count. When it prepares another one, it closes the least recently used statement on the server.

On PostgreSQL, a prepared statement can stop working: ALTER TABLE changes its result (cached plan must not change result type), and DEALLOCATE ALL or DISCARD ALL removes it. Bun then prepares the statement again and runs the query once more. The query rejects with the server's error instead if Bun has already sent another query behind it on that connection, which the server runs first, or inside a transaction, which is already aborted. The next run prepares the statement again.

To disable named prepared statements on PostgreSQL, set prepare: false in the connection options. MySQL and MariaDB have no such mode: the mysql adapter throws, and the mariadb adapter ignores the option.

const sql = new SQL({
  // ... other options ...
  prepare: false, // Disable persisting named prepared statements on the server
});

When prepare: false is set:

Queries still use the "extended" protocol, but run as unnamed prepared statements. An unnamed prepared statement lasts only until the next Parse statement specifying the unnamed statement as destination is issued.

• Parameter binding is still safe against SQL injection
• The server parses and plans each query from scratch
• Queries are not pipelined
• Only one command per query is supported (unless you use sql``.simple())
• A query takes one more round trip when a parameter is an array, an Int32Array, or an object with its own toString(). Bun asks the server for the parameter types first, so that it sends the same value as with prepared statements
• A Date does not work for a time, timetz, json or jsonb parameter, because Bun sends it as ISO 8601 text without asking for the type
• A string for a json or jsonb parameter is sent as it is, so it must already be JSON text. sql.json(value) stores the same value with and without prepared statements

You might want to use prepare: false when:

• Using PGBouncer in transaction mode (though since PGBouncer 1.21.0, protocol-level named prepared statements are supported when configured properly)
• Debugging query execution plans
• Working with dynamic SQL where query plans need to be regenerated frequently

Disabling prepared statements can slow down queries that run frequently with different parameters, since the server parses and plans each one from scratch.

Error Handling#

The client provides typed errors for different failure scenarios. Errors are database-specific and extend a base error class:

Error Classes#

import { SQL } from "bun";

try {
  await sql`SELECT * FROM users`;
} catch (error) {
  if (error instanceof SQL.PostgresError) {
    // PostgreSQL-specific error
    console.log(error.code); // Bun's error code, such as "ERR_POSTGRES_SERVER_ERROR"
    console.log(error.errno); // SQLSTATE from the server, such as "23505"
    console.log(error.detail); // Detailed error message
    console.log(error.hint); // Helpful hint from PostgreSQL
  } else if (error instanceof SQL.MySQLError) {
    // MySQL-specific error
    console.log(error.code); // Bun's error code, such as "ERR_MYSQL_SERVER_ERROR"
    console.log(error.errno); // MySQL error number, such as 1062
    console.log(error.sqlState); // SQLSTATE from the server, such as "23000"
  } else if (error instanceof SQL.SQLiteError) {
    // SQLite-specific error
    console.log(error.code); // SQLite error code (e.g., "SQLITE_CONSTRAINT_UNIQUE")
    console.log(error.errno); // SQLite error number
    console.log(error.byteOffset); // UTF-8 byte offset in the query text (-1 if unknown)
  } else if (error instanceof SQL.SQLError) {
    // Generic SQL error (base class)
    console.log(error.message);
  }
}

An error that the PostgreSQL or MySQL server reports has the code ERR_*_SERVER_ERROR, or ERR_*_SYNTAX_ERROR for a syntax error. The server's own code is in errno.

Some errors are not SQLErrors. A certificate that fails verification is a plain Error with a code such as DEPTH_ZERO_SELF_SIGNED_CERT. An invalid argument is a TypeError, and a misused helper or fragment is a SyntaxError.

PostgreSQL Connection Errors#

PostgreSQL Authentication Errors#

A wrong password is an error from the server: ERR_POSTGRES_SERVER_ERROR with errno "28P01".

PostgreSQL Query Errors#

PostgreSQL Data Type Errors#

The client raises these errors while it decodes a result, after the server ran the query. Only that query rejects. The connection stays open, and the other queries on it continue. An INSERT, UPDATE or DELETE ... RETURNING can already be stored at that point, so do not retry a write blindly. To read the value, cast the column to text or use .raw().

PostgreSQL Protocol Errors#

PostgreSQL Transaction Errors#

MySQL Errors#

MySQL and MariaDB errors are SQL.MySQLErrors.

A wrong password is an error from the server: ERR_MYSQL_SERVER_ERROR with errno 1045.

The remaining codes are protocol errors, usually a packet from the server that Bun cannot decode: ERR_MYSQL_MALFORMED_PACKET, ERR_MYSQL_UNEXPECTED_PACKET, ERR_MYSQL_OVERFLOW, ERR_MYSQL_UNSUPPORTED_COLUMN_TYPE, ERR_MYSQL_UNKNOWN_ERROR, and the other ERR_MYSQL_INVALID_* codes.

SQLite-Specific Errors#

SQLite errors carry SQLite's extended result codes and numbers. A constraint violation reports the specific constraint, such as SQLITE_CONSTRAINT_UNIQUE and 2067, not SQLITE_CONSTRAINT and 19. To match the whole family, use error.code.startsWith("SQLITE_CONSTRAINT") or (error.errno & 0xff) === 19.

Errors that Bun raises itself have errno 0: ERR_SQLITE_CONNECTION_CLOSED, ERR_SQLITE_INVALID_TRANSACTION_STATE, ERR_SQLITE_QUERY_CANCELLED and ERR_SQLITE_NOT_TAGGED_CALL.

Example error handling:

const sqlite = new SQL("sqlite://app.db");

try {
  await sqlite`INSERT INTO users (id, name) VALUES (1, 'Alice')`;
  await sqlite`INSERT INTO users (id, name) VALUES (1, 'Bob')`; // Duplicate ID
} catch (error) {
  if (error instanceof SQL.SQLiteError) {
    if (error.code === "SQLITE_CONSTRAINT_PRIMARYKEY") {
      console.log("Duplicate id:", error.message); // "UNIQUE constraint failed: users.id"
    } else if (error.code.startsWith("SQLITE_CONSTRAINT")) {
      console.log("Constraint violation:", error.message);
    }
  }
}

Numbers and BigInt#

Bun returns PostgreSQL bigint (int8) values, and MySQL BIGINT values outside the 32-bit range, as strings:

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

A number parameter is an integer (int4 or int8 on PostgreSQL, INT or BIGINT on MySQL) when Number.isSafeInteger() is true for it. Every other number is a double. The parameter's type carries over to a result that derives from it: on PostgreSQL, sql`SELECT ${2 ** 40} AS x` returns x as a string, because the column is int8.

BigInt Instead of Strings#

To get large numbers as BigInt instead of strings, set the bigint option to true when creating the SQL client:

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

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

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

What bigint: true changes depends on the adapter:

Dates and time zones#

PostgreSQL timestamp and MySQL DATETIME / TIMESTAMP values have no offset on the wire. Bun reads them as UTC, whatever the machine's time zone, and reads date / DATE as UTC midnight. Bun stores a Date parameter as its UTC wall clock, so it reads back as the same instant.

// created_at timestamp = 2024-01-15 12:00:00
const [row] = await sql`SELECT created_at FROM events`;
row.created_at.toISOString(); // "2024-01-15T12:00:00.000Z" on every host

pg, postgres.js, mysql2 and mariadb read timestamp / DATETIME as local time. If you migrate from one of them on a host that is not UTC, the same stored value becomes a different instant. timestamptz carries an offset and is the same in every driver.

Roadmap#

Things we haven't finished yet:

• Column type transforms

Database-Specific Features#

Authentication Methods#

MySQL supports multiple authentication plugins, which the client negotiates automatically:

• mysql_native_password - Traditional MySQL authentication, widely compatible
• caching_sha2_password - Default in MySQL 8.0+, more secure with RSA key exchange
• sha256_password - SHA-256 based authentication

The client automatically handles authentication plugin switching when the server requests it. For the two SHA-256 plugins, use TLS or allowPublicKeyRetrieval: true. caching_sha2_password also works over a unix socket.

Prepared Statements & Performance#

MySQL uses server-side prepared statements for all parameterized queries:

// This automatically creates a prepared statement on the server
const user = await mysql`SELECT * FROM users WHERE id = ${userId}`;

// Prepared statements are cached and reused for identical queries
for (const id of userIds) {
  // Same prepared statement is reused
  await mysql`SELECT * FROM users WHERE id = ${id}`;
}

// Query pipelining - multiple statements sent without waiting
const [users, orders, products] = await Promise.all([
  mysql`SELECT * FROM users WHERE active = ${true}`,
  mysql`SELECT * FROM orders WHERE status = ${"pending"}`,
  mysql`SELECT * FROM products WHERE in_stock = ${true}`,
]);