SQL

SQLite is the default dialect. Your app can use MySQL instead. See SQL dialect for the differences.

db.sql("CREATE TABLE IF NOT EXISTS todos (id INT PRIMARY KEY AUTO_INCREMENT, title TEXT, done BOOLEAN)");
const r = db.sql("INSERT INTO todos (title, done) VALUES (?, ?)", ["write docs", false]);
r.changes;                          // 1
r.lastInsertId;                     // 1 (a BigInt past 2^53)
db.sql("SELECT * FROM todos WHERE done = ?", [false]);   // [{ id: 1, title: "write docs", done: 0 }]

db.sql(text, params?) returns rows for a SELECT and { changes, lastInsertId } for anything else.

Parameters and types

Parameters are an array, one per ?. A parameter is null, undefined (NULL), a boolean, a number, a BigInt, a string, a Uint8Array or an ArrayBuffer. Anything else throws a TypeError naming the parameter.

• An integral number inside int64 binds as an integer; any other number (a fraction, NaN, infinity, -0) as a double.
• A BigInt binds as int64, or as unsigned past INT64_MAX up to UINT64_MAX; outside that range it throws.
• Binary values bind as BLOB.
• One parameter is at most 16 MiB; a larger one throws an error naming the limit.

In results, integers past 2^53 return as BigInt, DECIMAL, DATE, TIME, and DATETIME return as SQL text, and BLOB returns as Uint8Array. A TEXT or BLOB column holds up to 65535 bytes. A longer value is refused with error 1406, Data too long, rather than truncated. Declare MEDIUMTEXT or LONGTEXT for larger values.

Prepared statements

const byDone = db.prepare("SELECT id, title FROM todos WHERE done = ?");
byDone.all(false);                  // every row
byDone.first(false);                // the first row or null

Transactions

Transaction statements (BEGIN, COMMIT, ROLLBACK, and SAVEPOINT) are refused. As with other statements the engine does not run, an Error named SQL_FAILED explains why. Use db.transaction instead.

const moved = db.transaction((tx) => {
  tx.sql("UPDATE accounts SET balance = balance - ? WHERE id = ?", [10, 1]);
  tx.sql("UPDATE accounts SET balance = balance + ? WHERE id = ?", [10, 2]);
  return tx.sql("SELECT balance FROM accounts WHERE id = ?", [1])[0].balance;
});

db.transaction(fn) runs fn(tx) as one SQL transaction and returns fn's return value. Rules:

• fn is synchronous. A returned promise rolls back and throws a TypeError.
• A throw rolls back and rethrows the same error.
• The app's write lock is held until fn returns: other writers wait, so keep it short. A call killed at its CPU limit inside fn is rolled back.
• Collection writes join the transaction. tx.<collection> is db.<collection>, and its inserts, updates, upserts, deletes, insertMany, updateWhere, chain update and delete, row save(), and other writes commit or roll back with the tx.sql writes. A throw, returned promise, nested transaction rollback, or failed commit causes a rollback. Reads through collections and chains, and tx.sql over a collection, see the transaction's writes. Collection change events are published in write order when the outermost transaction commits. Oplog entries are also written then, one per collection. A rolled-back transaction publishes and records nothing. Although the transaction holds the write lock, another thread's collection write is not held off by it yet.
• Refused inside the callback, with an error naming the problem: kv and graph writes ("kv and graph writes are not transactional yet: use tx.sql or collection writes, or write outside db.transaction"), a db.transaction from script that runs inside a collection upsert, and the services that write outside the transaction or run another function (substrio.api writes, substrio.functions.<name> calls, substrio.webhooks.create/remove/emit, substrio.plans.set, substrio.usage.consume, substrio.lua.eval, substrio.db writes). substrio.request is allowed for GET and HEAD only.
• A nested db.transaction is a savepoint: it rolls back alone.

db.transaction((tx) => {
  tx.sql("INSERT INTO todos (title) VALUES (?)", ["outer"]);
  try {
    db.transaction((inner) => {
      inner.sql("INSERT INTO todos (title) VALUES (?)", ["inner"]);
      throw new Error("undo only the inner insert");
    });
  } catch (e) { /* the outer insert is kept */ }
});

SQL over documents

A document collection is also a SQL table of the same name. SQL reads it and never writes it: write documents through the collection (collections-and-chains.md).

db.sql("SELECT name FROM people WHERE age < ?", [30]);      // [{ name: "bob" }]
db.sql("SELECT p.name, r.rank FROM people p JOIN ranks r ON r.who = p.name");   // joins a SQL table
db.people.query("SELECT name WHERE age > ?", 30);           // no FROM: reads the collection

A field reads as a typed column, a missing field is NULL, and a nested value is JSON text. INSERT, REPLACE, UPDATE, DELETE, DROP TABLE, ALTER TABLE, and CREATE TABLE using a collection's name are refused. The documents remain unchanged, and the error reads:

people is a document collection: SQL reads it; write documents with db.people.insert/update/delete

db.<collection>.query(sql, ...args) and .prepare(sql) are described in collections-and-chains.md, "SQL over a collection".

A collection is a SQL table of its documents' fields, and its text compares byte for byte in SQL, as it does everywhere else on documents: equality, IN, GROUP BY, DISTINCT, ORDER BY and LIKE keep case apart (Paid, paid and PAID are three values), ignore no trailing spaces ('paid ' is not 'paid') and order by bytes. lower(col) gives a folded comparison: WHERE lower(status) = 'paid' (an expression compares under the default collation, so it also ignores trailing spaces). A text equality or IN on a collection field that holds only text is answered from the collection's own value keys, with no scan. A chain query (eq, in, startsWith) gives the same answer through SQL as through the chain.

D1 shim

env.<D1> has prepare(sql).bind(...).first/all/run/raw, batch and exec, all returning promises. batch runs the statements one after another, not as a transaction: use db.transaction when the writes must be atomic.