Distributed transaction example
Example distributed transaction: await sql.beginDistributed("tx1", async tx => { await tx`INSERT INTO users (name) VALUES (${"Alice"})`; }); await sql.commitDistributed("tx1");
database apis
67 notes in this subject, read out of this brain and free to use. This is page 1 of 2.
Bun.sql and Bun.SQL import paths
Import sql and SQL from "bun". The sql object is the default export for direct queries; SQL is the constructor for creating client instances with custom connection strings or options.
SQL client return value - default format
By default, SQL query results are returned as arrays of objects, where each object represents a row with column names as keys.
SQL queries use tagged template literals
Queries are written as tagged template literals with parameter binding using ${value} syntax. This protects against SQL injection.
sql.values() result format
The sql``.values() method returns each row as an array of values in the same order as the columns in the query, useful for handling duplicate column names.
sql.raw() result format
The sql``.raw() method returns rows as arrays of Buffer objects. Use it for binary data or for performance.
SQL helper function for dynamic table names and fragments
Use the sql() helper function to safely reference tables dynamically: sql("tablename") escapes identifiers. Can be used for dynamic columns: sql(object, "col1", "col2") picks specific columns for INSERT or UPDATE.
sql.array helper for PostgreSQL arrays
The sql.array() helper creates PostgreSQL array literals from JavaScript arrays: sql.array(["red", "blue", "green"]) generates ARRAY['red', 'blue', 'green']. PostgreSQL-only feature.
sql.simple() for multiple statements
The sql``.simple() method runs multiple SQL statements in a single query. Simple queries do not support parameters (${value}). Useful for database migrations and setup scripts.
sql.file() to execute queries from files
The sql.file("query.sql", [1, 2, 3]) method reads a query from a file and executes it. If the file uses placeholders like $1 and $2, pass parameters as an array. With SQLite, parameters can be an object of named parameters.
sql.unsafe() for raw SQL strings
The sql.unsafe() method executes raw SQL strings without escaping. Use with caution: it does not escape user input. Without parameters, can contain multiple commands. With parameters, only one command is allowed.
Query execution is lazy
Queries only start executing when awaited or run with .execute(). To cancel a running query, call cancel() on the query object.
PostgreSQL connection string formats
PostgreSQL uses postgres:// or postgresql:// protocols. Example: new SQL("postgres://user:••••@localhost:5432/mydb"). If no connection string matches MySQL or SQLite patterns, it defaults to PostgreSQL.
MySQL connection string formats
MySQL uses mysql:// or mysql2:// protocols. Examples: new SQL("mysql://user:••••@localhost:3306/database"), new SQL("mysql2://user:pass@localhost:3306/database"). Supports unix socket: mysql://user:••••@/database?socket=/var/run/mysqld/mysqld.sock
MySQL connection via options object
MySQL can be configured with an options object: new SQL({ adapter: "mysql", hostname: "localhost", port: 3306, database: "myapp", username: "dbuser", password: "secretpass" }). Socket option: socket: "/var/run/mysqld/mysqld.sock"
SQLite connection string formats
SQLite uses :memory: for in-memory database, sqlite:// protocol, or file:// protocol. Examples: new SQL(":memory:"), new SQL("sqlite://myapp.db"), new SQL("file://path/to/database.db"). Supports query parameters like ?mode=ro for read-only.
SQLite with simple filename requires adapter option
When using a simple filename without protocol like "myapp.db", must explicitly specify { adapter: "sqlite" } to avoid ambiguity with PostgreSQL.
SQLite connection via options object
SQLite can be configured with an options object: new SQL({ adapter: "sqlite", filename: "./data/app.db", readonly: false, create: true, readwrite: true, strict: true, safeIntegers: false }). The strict option enables strict mode for better type safety.
SQLite mode query parameters
SQLite connection strings support mode query parameters: ?mode=ro (readonly: true), ?mode=rw (readonly: false, create: false), ?mode=rwc (readonly: false, create: true) - the default.
SQL client connection pooling options
Connection pooling is configured with: max (maximum concurrent connections, default varies), idleTimeout (close idle connections after N seconds), maxLifetime (max connection lifetime in seconds, 0 = forever), connectionTimeout (timeout when establishing new connections).
SQL client close() method
Call sql.close() to close all connections from the pool and await all queries to finish. Accepts options object: { timeout: 5 } to wait 5 seconds, { timeout: 0 } to close immediately.
sql.reserve() for exclusive connection
The sql.reserve() method takes a connection from the pool and returns a client for an isolated connection. Call release() to return it to the pool. Works with Symbol.dispose for automatic release: using reserved = await sql.reserve();
Dynamic password function for authentication
Set password to a synchronous or asynchronous function that Bun calls at connection time to resolve the password. Useful for access tokens or rotating passwords: password: async () => await signer.getAuthToken()
SQL transactions with sql.begin()
Start a transaction with sql.begin(async tx => { ... }). All queries in the callback run in a transaction. Transaction automatically commits if no errors; rolls back if any error occurs.
SQL savepoints in transactions
Use tx.savepoint(async sp => { ... }) within a transaction to create intermediate checkpoints. Allows rolling back part of the transaction without aborting the whole thing.
Distributed transactions with Two-Phase Commit
Use sql.beginDistributed("txid", async tx => { ... }) to begin a distributed transaction. Later call sql.commitDistributed("txid") or sql.rollbackDistributed("txid") to commit or roll back. Available for PostgreSQL and MySQL.
PostgreSQL SSL modes
PostgreSQL ssl option accepts: "disable" (default, no SSL), "prefer" (tries SSL, falls back), "require" (requires SSL without cert verification), "verify-ca" (verifies server cert by CA), "verify-full" (most secure, verifies cert and hostname).
MySQL connection pooling settings
MySQL options: max (default 10), idleTimeout (seconds), maxLifetime (seconds, 0 = forever), connectionTimeout (seconds). Also supports ssl: "prefer", "disable", "require", "verify-ca", "verify-full" and TLS options with ca, key, cert paths.
PostgreSQL options object configuration
PostgreSQL can use options: url, hostname, port, database, username, password, max, idleTimeout, maxLifetime, connectionTimeout, tls (boolean or object with rejectUnauthorized, requestCert, ca, key, cert, checkServerIdentity), onconnect callback, onclose callback.
Prepared statements can be disabled
Set prepare: false in connection options to disable persisting named prepared statements. Queries still use the extended protocol as unnamed statements but are parsed and planned from scratch each time. Slower for frequently-run queries.
SQL error classes
Three error classes are provided: SQL.PostgresError (PostgreSQL-specific with code, detail, hint properties), SQL.SQLiteError (SQLite-specific with code, errno, byteOffset properties), SQL.SQLError (generic base class).
Large numbers handling in SQL results
Numbers exceeding 53-bit integer range are returned as strings by default. Set bigint: true in SQL constructor options to get large numbers as BigInt instead of strings.
PostgreSQL environment variables for connection
PostgreSQL checks: POSTGRES_URL, DATABASE_URL, PGURL, PG_URL (connection URLs), TLS_POSTGRES_DATABASE_URL, TLS_DATABASE_URL (SSL/TLS URLs). Individual parameters: PGHOST (default localhost), PGPORT (default 5432), PGUSERNAME with fallbacks PGUSER/USER/USERNAME (default postgres), PGPASSWORD, PGDATABASE (default username), PGSSLMODE (default disable).
MySQL environment variables for connection
MySQL checks: MYSQL_URL, DATABASE_URL with mysql:// protocol, TLS_MYSQL_DATABASE_URL. Individual parameters: MYSQL_HOST (default localhost), MYSQL_PORT (default 3306), MYSQL_USER (default root), MYSQL_PASSWORD, MYSQL_DATABASE (default mysql).
SQLite with DATABASE_URL environment variable
SQLite recognizes DATABASE_URL when it contains SQLite-compatible patterns: :memory:, sqlite://..., file://... . PostgreSQL-specific env vars like POSTGRES_URL are ignored when using SQLite.
PostgreSQL preconnection with bun CLI flag
The bun --sql-preconnect flag establishes a PostgreSQL connection at startup before application code runs, so the first query doesn't pay connection latency. Works with DATABASE_URL environment variable. Can be combined with other runtime flags like --hot.
SQLite pragmas for configuration
SQLite supports PRAGMA statements for configuration: PRAGMA foreign_keys = ON enables foreign key constraints; PRAGMA journal_mode = WAL sets WAL mode for better concurrency; PRAGMA integrity_check checks database integrity.
SQLite synchronous query execution
SQLite queries execute synchronously, unlike PostgreSQL which uses asynchronous I/O. The API still returns Promises for compatibility.
MySQL authentication plugins
MySQL supports multiple authentication plugins, automatically negotiated: mysql_native_password (traditional), caching_sha2_password (MySQL 8.0+ default), sha256_password. Client handles authentication plugin switching and secure password exchange.
MySQL character set and collation
Bun.SQL uses utf8mb4 character set for MySQL connections, which covers all Unicode including emoji.
MySQL type conversions to JavaScript
MySQL type mappings: INT/TINYINT/MEDIUMINT → number; BIGINT → string/number/BigInt (depending on value and bigint option); DECIMAL/NUMERIC → string; FLOAT/DOUBLE → number; DATE → Date; DATETIME/TIMESTAMP → Date (as UTC); TIME → number (microseconds); YEAR → number; CHAR/VARCHAR/TEXT → string; BLOB → string; JSON → object/array (auto-parsed); BIT(1) → boolean.
Connection pooling close example
Example closing connections: const sql = new SQL({ max: 20, idleTimeout: 30, maxLifetime: 0, connectionTimeout: 30 }); await sql.close(); or await sql.close({ timeout: 5 });
DATETIME and TIMESTAMP timezone handling
MySQL DATETIME and TIMESTAMP values have no timezone on the wire. Bun reads them as UTC - the Date object has the same UTC wall-clock that was stored, regardless of machine timezone. Same applies to PostgreSQL's timestamp without time zone.
MySQL multi-statement queries
MySQL can return multiple result sets from multi-statement queries using the simple() method: await mysql`SELECT ...; SELECT ...`.simple()
MySQL lastInsertRowid after INSERT
After an INSERT query in MySQL, access result.lastInsertRowid to get the auto-increment ID (MySQL's LAST_INSERT_ID()).
MySQL affectedRows for UPDATE/DELETE
After UPDATE or DELETE queries in MySQL, access result.affectedRows to get the number of rows affected.
MySQL bulk insert syntax
For bulk inserts, pass an array of objects: await mysql`INSERT INTO users ${mysql(newUsers)}` where newUsers is an array of objects. Automatically expands to INSERT INTO ... VALUES ... statement.
SQL INSERT with RETURNING clause
INSERT queries can use RETURNING * to get the inserted row: const [user] = await sql`INSERT INTO users (name, email) VALUES (${name}, ${email}) RETURNING *`
SQL bulk insert with object helper
Use the sql() helper for cleaner insert syntax: await sql`INSERT INTO users ${sql(userData)}` where userData is an object. Expands to INSERT with appropriate columns and values.
SQL array bulk insert
Pass an array of objects to sql(): await sql`INSERT INTO users ${sql([{name: "Alice", email: "alice@example.com"}, ...])}`. Bun expands into INSERT INTO ... VALUES ... statement.
SQL selective column insert/update
Use sql(object, "col1", "col2") to pick specific columns: await sql`INSERT INTO users ${sql(user, "name", "email")}` only inserts those columns. Same for UPDATE: await sql`UPDATE users SET ${sql(user, "name", "email")} WHERE id = ${user.id}`
SQL conditional query building
Build queries dynamically with conditions: const ageFilter = sql`AND age > ${minAge}`; await sql`SELECT * FROM users WHERE active = ${true} ${filterAge ? ageFilter : sql``}`
SQL WHERE IN with dynamic values
Create WHERE IN queries dynamically: await sql`SELECT * FROM users WHERE id IN ${sql([1, 2, 3])}`; or with object array: await sql`SELECT * FROM users WHERE id IN ${sql(users, "id")}`
SQL transaction query pipelining
Within sql.begin(), return an array of queries to pipeline them: await sql.begin(async tx => { return [tx`INSERT ...`, tx`UPDATE ...`]; });
PostgreSQL SASL authentication
Bun supports SCRAM-SHA-256 (SASL), MD5, and Clear Text authentication for PostgreSQL. SASL is recommended for better security.
PostgreSQL error code categories
PostgreSQL errors include connection errors (ERR_POSTGRES_CONNECTION_CLOSED, ERR_POSTGRES_CONNECTION_FAILED, ERR_POSTGRES_CONNECTION_REFUSED, ERR_POSTGRES_CONNECTION_TIMEOUT, ERR_POSTGRES_IDLE_TIMEOUT, ERR_POSTGRES_LIFETIME_TIMEOUT, ERR_POSTGRES_TLS_NOT_AVAILABLE, ERR_POSTGRES_TLS_UPGRADE_FAILED), authentication errors, query errors (ERR_POSTGRES_SYNTAX_ERROR, ERR_POSTGRES_SERVER_ERROR, ERR_POSTGRES_INVALID_QUERY_BINDING, ERR_POSTGRES_QUERY_CANCELLED, ERR_POSTGRES_NOT_TAGGED_CALL), data type errors, protocol errors, and transaction errors.
SQLite error code examples
Common SQLite error codes include: SQLITE_CONSTRAINT (19, constraint violation), SQLITE_BUSY (5, database locked), SQLITE_LOCKED (6, table locked), SQLITE_READONLY (8, read-only), SQLITE_IOERR (10, I/O error), SQLITE_CORRUPT (11, malformed), SQLITE_FULL (13, disk full), SQLITE_CANTOPEN (14, cannot open), SQLITE_PROTOCOL (15, lock protocol error), SQLITE_SCHEMA (17, schema changed), SQLITE_TOOBIG (18, string/BLOB too large), SQLITE_MISMATCH (20, type mismatch), SQLITE_MISUSE (21, library misuse), SQLITE_AUTH (23, authorization denied).
SQL constructor with URL parameter
The SQL constructor accepts a URL string as first parameter: new SQL("postgres://..."), new SQL("mysql://..."), new SQL("sqlite://..." or ":memory:"). Can also accept options object as first parameter.
SQL query example with PostgreSQL
Example PostgreSQL query: const users = await sql`SELECT * FROM users WHERE active = ${true} LIMIT ${10}`;
SQL query example with MySQL
Example MySQL query: const mysql = new SQL("mysql://user:••••@localhost:3306/mydb"); const mysqlResults = await mysql`SELECT * FROM users WHERE active = ${true}`;