sqlite

123 notes in this subject, read out of this brain and free to use. This is page 2 of 3.

Execute multiple statements with .simple()

Use .simple() on a query to execute multiple SQL statements in one request: await sql`SELECT 1; SELECT 2;`.simple(). Simple queries do not support parameters (${value}). This is useful for database migrations and setup scripts.

Execute query from file with sql.file()

Execute SQL from a file: const result = await sql.file("query.sql", [1, 2, 3]). If the file uses placeholders like $1 and $2, pass parameters. Without parameters, the file can contain multiple commands. SQLite also supports named parameters (:name, $name, @name placeholders).

Execute unsafe raw SQL with sql.unsafe()

Execute raw SQL strings with sql.unsafe(): const result = await sql.unsafe("SELECT * FROM users WHERE id = $1", [id]). This does not escape user input, so use with caution. Without parameters, the string can contain multiple commands. With parameters, only one command is allowed.

Query lazy execution and cancellation

Queries are lazy and only execute when awaited or run with .execute(). To cancel a running query, call cancel() on the query object: const query = sql`SELECT * FROM users`.execute(); setTimeout(() => query.cancel(), 100); await query;

PostgreSQL is default database adapter

PostgreSQL is the default adapter when a connection string does not match MySQL or SQLite patterns, or when no connection string is provided and environment variables point to PostgreSQL.

MySQL connection string formats

MySQL accepts: "mysql://user:••••@localhost:3306/database", "mysql://user:••••@localhost/database" (default port 3306), "mysql2://user:pass@localhost:3306/database", "mysql://user:••••@localhost/db?ssl=true", and "mysql://user:••••@/database?socket=/var/run/mysqld/mysqld.sock" for Unix socket connections.

MySQL prepared statements and binary protocol

MySQL automatically uses server-side prepared statements for parameterized queries with statement caching. The binary protocol is used for better performance and accurate type handling.

MySQL multiple result sets from stored procedures

MySQL supports stored procedures returning multiple result sets and OUT parameters.

MySQL authentication plugins supported

MySQL supports mysql_native_password, caching_sha2_password (default in MySQL 8.0+), and sha256_password. The client automatically handles authentication plugin switching when the server requests it.

MySQL 8 caching_sha2_password over plain TCP

MySQL 8 accounts default to caching_sha2_password plugin. The first connection requires full authentication. Over unencrypted connections, the client must download the server's RSA public key. Bun refuses this by default with error ERR_MYSQL_PUBLIC_KEY_RETRIEVAL_NOT_ALLOWED. Connect with TLS or set allowPublicKeyRetrieval: true in the connection options to opt in.

SQLite connection string formats

SQLite accepts: "sqlite://path/to/database.db", "sqlite:path/to/database.db" (without slashes), "file://path/to/database.db", ":memory:" or "sqlite://:memory:" for in-memory, "sqlite://./local.db" for relative paths, and query parameters like "sqlite://data.db?mode=ro", "?mode=rw", "?mode=rwc" (default).

SQLite options for readonly, create, readwrite

SQLite configuration options: readonly: false (open in read-only mode), create: true (create database if it doesn't exist), readwrite: true (open for reading and writing). URL parameters map: ?mode=ro → readonly: true, ?mode=rw → readonly: false, create: false, ?mode=rwc → readonly: false, create: true (default).

SQLite strict mode and safeIntegers options

SQLite options: strict: true (enable strict mode), safeIntegers: false (use JavaScript numbers for integers instead of BigInt for values exceeding safe integer range).

SQLite PRAGMA statements

Use PRAGMA statements to configure SQLite: await sqlite`PRAGMA foreign_keys = ON` (enable foreign keys), await sqlite`PRAGMA journal_mode = WAL` (set journal mode to WAL for better concurrency), await sqlite`PRAGMA integrity_check` (check integrity).

SQLite data type storage classes

SQLite has 5 storage classes: NULL, INTEGER, REAL, TEXT, BLOB. SQLite is more lenient with types than PostgreSQL. JavaScript values are automatically converted.

Database environment variables - AUTO-DETECTION

When using sql() without arguments or new SQL() with a connection string, Bun auto-detects the database type. MySQL: "mysql://..." or "mysql2://..." patterns. SQLite: ":memory:", "sqlite://...", "sqlite:...", "file://...", "file:..." patterns. PostgreSQL: everything else is the default.

MySQL environment variables for connection

MySQL environment variables: MYSQL_URL (primary connection URL), DATABASE_URL (alternative with MySQL protocol), MYSQL_HOST (default: localhost), MYSQL_PORT (default: 3306), MYSQL_USER (default: root), MYSQL_PASSWORD (default: empty), MYSQL_DATABASE (default: mysql), TLS_MYSQL_DATABASE_URL (SSL/TLS-enabled connection URL).

PostgreSQL environment variables for connection

PostgreSQL environment variables: POSTGRES_URL (primary), DATABASE_URL (alternative, auto-detected), PGURL, PG_URL, TLS_POSTGRES_DATABASE_URL, TLS_DATABASE_URL. Individual parameters: PGHOST (default: localhost), PGPORT (default: 5432), PGUSERNAME (fallback: PGUSER, USER, USERNAME; default: postgres), PGPASSWORD, PGDATABASE (default: username), PGSSLMODE (default: disable; values: disable, allow, prefer, require, verify-ca, verify-full).

SQLite environment variables for connection

SQLite is configured with DATABASE_URL when it contains a SQLite-compatible URL (":memory:", "sqlite://./app.db", "file:///absolute/path/to/db.sqlite"). Bun ignores PostgreSQL-specific environment variables like POSTGRES_URL and PGHOST when using SQLite.

PostgreSQL preconnection at startup

Enable PostgreSQL preconnection before application code runs with the --sql-preconnect flag: bun --sql-preconnect index.js. Works with DATABASE_URL environment variable: DATABASE_URL=postgres://user:••••@localhost:5432/db bun --sql-preconnect index.js. Can be combined with other runtime flags like --hot.

MySQL connection options object

MySQL connection options: adapter (required): "mysql", hostname, port (default: 3306), database, username, password, socket (Unix socket alternative to hostname/port), max (pool size, default: 10), idleTimeout (close idle connections, default: 30s), maxLifetime (connection lifetime in seconds, 0 = forever), connectionTimeout (default: 30s), ssl ("disable", "prefer", "require", "verify-ca", "verify-full"), tls (object with rejectUnauthorized, ca, key, cert), onconnect (callback), onclose (callback).

PostgreSQL connection options object

PostgreSQL connection options: url, hostname, port (default: 5432), database, username, password, max (pool size), idleTimeout (default: 30s), maxLifetime (0 = forever), connectionTimeout (default: 30s), tls (boolean or object with rejectUnauthorized, requestCert, ca, key, cert, checkServerIdentity callback), onconnect (callback), onclose (callback).

SQLite connection options object

SQLite connection options: adapter (required): "sqlite", filename (path or ":memory:" for in-memory), readonly, create, readwrite, strict, safeIntegers, onconnect (callback), onclose (callback).

SQLite connection pooling and transactions

SQLite doesn't use connection pooling as it's a file-based database. Each SQL instance represents a single connection. SQLite supports nested transactions through savepoints. Concurrent access is handled through file locking; WAL mode provides better concurrency. In-memory databases (:memory:) exist only for the connection lifetime.

Dynamic password function for authentication

Set password to a synchronous or asynchronous function for dynamic authentication: const sql = new SQL(url, { password: async () => await signer.getAuthToken() }). Bun calls the function at connection time to resolve the password. Useful for access tokens or rotating passwords.

Basic transaction with sql.begin()

Start a transaction: await sql.begin(async tx => { await tx`INSERT INTO users (name) VALUES (${ "Alice"})`; await tx`UPDATE accounts SET balance = balance - 100 WHERE user_id = 1`; }). All queries run in a transaction. Commits automatically if no errors. Rolls back on error. Bun sends BEGIN automatically and issues ROLLBACK on error.

Pipeline queries in transaction by returning array

Pipeline queries in a transaction by returning an array: 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 for nested rollback in transactions

Create savepoints for intermediate checkpoints: await sql.begin(async tx => { await tx`INSERT INTO users (name) VALUES (${ "Alice"})`; await tx.savepoint(async sp => { await sp`UPDATE users SET status = 'active'`; if (someCondition) throw new Error("Rollback to savepoint"); }); await tx`INSERT INTO audit_log (action) VALUES ('user_created')`; }). Savepoints allow part of a transaction to roll back without aborting the whole transaction.

Distributed transactions with Two-Phase Commit

Two-Phase Commit (2PC) protocol: Phase 1 coordinator prepares each node; Phase 2 nodes commit or roll back. PostgreSQL uses prepared transactions; MySQL uses XA Transactions. Begin 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"). Uncaught exceptions roll back all changes. Distributed transactions persist beyond the original session.

PostgreSQL authentication methods - SASL, MD5, Clear Text

Bun supports SCRAM-SHA-256 (SASL), MD5, and Clear Text authentication. SASL is recommended for better security. See PostgreSQL SASL Authentication documentation.

PostgreSQL SSL modes

PostgreSQL SSL/TLS modes: "disable" (no SSL, default), "prefer" (tries SSL, falls back to non-SSL), "require" (requires SSL without cert verification), "verify-ca" (verifies server certificate is signed by trusted CA), "verify-full" (most secure, verifies certificate and hostname). Set in options: ssl: "mode" or in URL query parameter: sslmode=mode.

Connection pooling configuration

Connection pool options: max (maximum concurrent connections, default varies by database), idleTimeout (close idle connections in seconds, default: 30), maxLifetime (connection lifetime in seconds, 0 = forever), connectionTimeout (timeout when establishing new connections in seconds). Pool reuses connections across queries and caps concurrent connections. Bun doesn't open connections until a query runs.

Close SQL connection pool with sql.close()

Close all connections from the pool: await sql.close() (waits for all queries to finish), await sql.close({ timeout: 5 }) (waits 5 seconds before closing), await sql.close({ timeout: 0 }) (closes immediately).

Reserve connection from pool with sql.reserve()

Get exclusive connection from pool: const reserved = await sql.reserve(); try { await reserved`INSERT INTO users (name) VALUES (${ "Alice"})`;} finally { reserved.release(); }. Or using Symbol.dispose: { using reserved = await sql.reserve(); await reserved`SELECT 1`; }. Pass AbortSignal to stop waiting: await sql.reserve({ signal: AbortSignal.timeout(5000) }).

PostgreSQL LISTEN/NOTIFY for pub-sub

PostgreSQL LISTEN/NOTIFY allows inter-process communication. Subscribe: const subscription = await sql.listen("orders", payload => { console.log("new order", JSON.parse(payload)); }). Publish: await sql.notify("orders", JSON.stringify({ id: 42 })). Unsubscribe: await subscription.unlisten(). Using async disposal: { await using subscription = await sql.listen("orders", handleOrder); await doWork(); }. Channel names are quoted as identifiers, limited to 63 bytes. Payloads limited to 8000 bytes by default.

PostgreSQL LISTEN with catch-up callback

Pass a third argument to listen() that runs on initial subscribe and after reconnects for catching up: await sql.listen("orders", handleOrder, async () => { for (const order of await sql`SELECT * FROM orders WHERE processed = false`) handleOrder(order); }). This recovers missed notifications after reconnection.

LISTEN/NOTIFY behavior and reconnection

All subscriptions on a client share one dedicated connection. First listen() opens it; removing the last subscription closes it. If the connection drops, Bun re-establishes with exponential backoff (250ms doubling to 32s with jitter) and re-subscribes channels. PostgreSQL only delivers to connected listeners; notifications sent during disconnection are lost. Each listen() is its own subscription; several on one channel share a single server-side LISTEN. All callbacks receive every notification. Each handle's unlisten() removes only what its own call registered. A throwing callback is reported as uncaught exception and stays subscribed.

NOTIFY behavior in transactions

notify() is an ordinary query on whichever handle you call it. On sql it uses the pool. Inside sql.begin() it runs in the transaction, so PostgreSQL delivers on COMMIT and drops on ROLLBACK. Calling notify() inside a transaction announces a change only once it is visible. Example: await sql.begin(async tx => { const [order] = await tx`INSERT INTO orders ${sql(data)} RETURNING id`; await tx.notify("orders", String(order.id)); }).

Prepared statements configuration

By default, Bun creates named prepared statements for static queries. Disable with prepare: false in connection options: const sql = new SQL({ prepare: false }). When disabled, queries use unnamed prepared statements on the extended protocol, which last only until the next Parse statement. Parameter binding is still safe, but server parses and plans each query from scratch, no pipelining, only one command per query (unless using sql.simple()).

SQL error classes and types

Catch typed errors: SQL.PostgresError (PostgreSQL-specific, has code, detail, hint properties), SQL.SQLiteError (SQLite-specific, has code, errno, byteOffset properties), SQL.SQLError (generic base class with message property).

PostgreSQL connection error codes

PostgreSQL connection errors: ERR_POSTGRES_CONNECTION_CLOSED (established connection terminated), ERR_POSTGRES_CONNECTION_FAILED (connection closed before handshake, retried with backoff), ERR_POSTGRES_CONNECTION_REFUSED (nothing listening, fails immediately), ERR_POSTGRES_CONNECTION_TIMEOUT (failed within timeout), ERR_POSTGRES_IDLE_TIMEOUT (closed due to inactivity), ERR_POSTGRES_LIFETIME_TIMEOUT (exceeded max lifetime), ERR_POSTGRES_TLS_NOT_AVAILABLE, ERR_POSTGRES_TLS_UPGRADE_FAILED.

PostgreSQL authentication error codes

PostgreSQL authentication errors: ERR_POSTGRES_AUTHENTICATION_FAILED_PBKDF2, ERR_POSTGRES_UNKNOWN_AUTHENTICATION_METHOD, ERR_POSTGRES_UNSUPPORTED_AUTHENTICATION_METHOD, ERR_POSTGRES_INVALID_SERVER_KEY, ERR_POSTGRES_INVALID_SERVER_SIGNATURE, ERR_POSTGRES_SASL_SIGNATURE_INVALID_BASE64, ERR_POSTGRES_SASL_SIGNATURE_MISMATCH.

PostgreSQL query error codes

PostgreSQL query errors: ERR_POSTGRES_SYNTAX_ERROR (extends SyntaxError), ERR_POSTGRES_SERVER_ERROR, ERR_POSTGRES_INVALID_QUERY_BINDING, ERR_POSTGRES_QUERY_CANCELLED, ERR_POSTGRES_NOT_TAGGED_CALL.

PostgreSQL data type error codes

PostgreSQL data type errors: ERR_POSTGRES_INVALID_BINARY_DATA, ERR_POSTGRES_INVALID_BYTE_SEQUENCE, ERR_POSTGRES_INVALID_BYTE_SEQUENCE_FOR_ENCODING, ERR_POSTGRES_INVALID_CHARACTER, ERR_POSTGRES_OVERFLOW, ERR_POSTGRES_UNSUPPORTED_BYTEA_FORMAT, ERR_POSTGRES_UNSUPPORTED_INTEGER_SIZE, ERR_POSTGRES_MULTIDIMENSIONAL_ARRAY_NOT_SUPPORTED_YET, ERR_POSTGRES_NULLS_IN_ARRAY_NOT_SUPPORTED_YET.

PostgreSQL protocol error codes

PostgreSQL protocol errors: ERR_POSTGRES_EXPECTED_REQUEST, ERR_POSTGRES_EXPECTED_STATEMENT, ERR_POSTGRES_INVALID_BACKEND_KEY_DATA, ERR_POSTGRES_INVALID_MESSAGE, ERR_POSTGRES_INVALID_MESSAGE_LENGTH, ERR_POSTGRES_UNEXPECTED_MESSAGE.

PostgreSQL transaction error codes

PostgreSQL transaction errors: ERR_POSTGRES_UNSAFE_TRANSACTION, ERR_POSTGRES_INVALID_TRANSACTION_STATE.

SQLite error codes and errno values

SQLite common error codes: SQLITE_CONSTRAINT (errno 19, constraint violation), SQLITE_BUSY (errno 5, database locked), SQLITE_LOCKED (errno 6, table locked), SQLITE_READONLY (errno 8, readonly database), SQLITE_IOERR (errno 10, disk I/O error), SQLITE_CORRUPT (errno 11, malformed database), SQLITE_FULL (errno 13, database or disk full), SQLITE_CANTOPEN (errno 14, unable to open file), SQLITE_PROTOCOL (errno 15, lock protocol error), SQLITE_SCHEMA (errno 17, schema changed), SQLITE_TOOBIG (errno 18, string/BLOB exceeds limit), SQLITE_MISMATCH (errno 20, type mismatch), SQLITE_MISUSE (errno 21, library misuse), SQLITE_AUTH (errno 23, authorization denied).

Large integer handling - strings by default

Bun returns PostgreSQL bigint (int8) and MySQL BIGINT values outside the 32-bit range as strings: const [{ x, y }] = await sql`SELECT 9223372036854777 as x, 12345 as y`; // x is string, y is number.

BigInt option for large integers

To get large numbers as BigInt instead of strings, set bigint: true in the SQL constructor: const sql = new SQL({ bigint: true }); const [{ x }] = await sql`SELECT 9223372036854777 as x`; // x is BigInt: 9223372036854777n

MySQL type conversions JavaScript

MySQL to JavaScript type mapping: INT, TINYINT, MEDIUMINT → number (within safe range), BIGINT → string/number/BigInt (depending on bigint option), DECIMAL, NUMERIC → string (preserve precision), FLOAT, DOUBLE → number, DATE → Date object, DATETIME, TIMESTAMP → Date (UTC), TIME → string (HH:MM:SS or HHH:MM:SS for >99 hours), YEAR → number, CHAR, VARCHAR, VARSTRING, STRING → string, TEXT types → string, BLOB types → Buffer, JSON → object/array (auto-parsed), BIT(1) → boolean, GEOMETRY → Buffer (4-byte SRID + WKB).

MySQL DATETIME and TIMESTAMP timezone handling

MySQL DATETIME and TIMESTAMP values have no timezone on the wire, so Bun reads them as UTC. The returned Date has the same UTC wall-clock that was stored, regardless of machine timezone. Reading as UTC matches how Bun writes (bound Date stores UTC components). The same applies to PostgreSQL timestamp (without timezone); timestamptz carries explicit offset and is unaffected. The value 0000-00-00 becomes an Invalid Date.

MySQL character set and collation

Bun.SQL uses utf8mb4 character set for MySQL connections, which covers all of Unicode including emoji.

MySQL connection attributes for monitoring

Bun sends client information to MySQL automatically: _client_name: "Bun", _client_version: <bun version>. These appear in MySQL's performance_schema.session_connect_attrs.

MySQL differences from PostgreSQL API

API is unified but behavior differs: MySQL parameter placeholders are ? internally but Bun converts $1, $2 style automatically. MySQL doesn't support RETURNING; use result.lastInsertRowid or separate SELECT. MySQL doesn't have native array types like PostgreSQL.

MySQL query pipelining

MySQL supports query pipelining - execute multiple prepared statements without waiting for responses: 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}`, ]);

MySQL result object lastInsertRowid

After INSERT in MySQL, get the insert ID: const result = await mysql`INSERT INTO users (name) VALUES (${ "Alice"})`; console.log(result.lastInsertRowid);

MySQL result object affectedRows

Get number of rows affected by UPDATE/DELETE: const updated = await mysql`UPDATE users SET active = ${ false} WHERE age < ${ 18}`; console.log(updated.affectedRows);

MySQL stored procedures with OUT parameters

MySQL stored procedures are supported including OUT parameters: const results = await mysql`CALL GetUserStats(${ userId}, @total_orders)`; const outParam = await mysql`SELECT @total_orders as total`;

MySQL multi-statement queries with .simple()

MySQL multi-statement queries return multiple result sets: const multiResults = await mysql`SELECT * FROM users WHERE id = 1; SELECT * FROM orders WHERE user_id = 1;`.simple();

MySQL-specific SQL syntax support

MySQL-specific syntax is supported: SET @user_id = ${userId}, SHOW TABLES, DESCRIBE users, EXPLAIN SELECT * FROM users WHERE id = ${id}.

Give your agent this brain