db

"bindings": { "DB": "db" }

const rows = await env.DB.query("SELECT * FROM users WHERE email = ?", email) // [{...}, ...]
const { changes, lastRowId } = await env.DB.run("UPDATE users SET name = ? WHERE id = ?", name, id)
const results = await env.DB.batch([ // one transaction: all or none
  ["INSERT INTO orders (user_id) VALUES (?)", userId],
  ["UPDATE stock SET n = n - 1 WHERE item = ?", item],
]) // [{rows, changes, lastRowId}, ...]

Parameters

Strings, numbers, booleans (stored as 0 and 1), null, Uint8Array or ArrayBuffer (blobs, returned as Uint8Array) and Date (stored as an ISO string).

Schema

Create the schema from the service: CREATE TABLE IF NOT EXISTS … on first use, or a version table you check. run takes several statements separated by ;, so a whole schema script runs at once.

Transactions

Transactions are only batch(). BEGIN, COMMIT, ATTACH and the pragmas that change how SQLite stores data are refused.

Statements run one at a time per database, up to 30 seconds each. A database holds up to 1 GB; a result up to 100,000 rows or 32 MB; a batch up to 1000 statements.

Locks

Reading a row, changing it in code and writing it back loses changes when requests do it at once. Do it under a lock: the function runs alone among those that lock the same key, in the order they asked.

const room = await env.DB.lock(`room:${code}`, async () => {
  const [row] = await env.DB.query("SELECT state FROM rooms WHERE code = ?", code)
  const room = JSON.parse(row.state)
  room.players.push(player)
  await env.DB.run("UPDATE rooms SET state = ? WHERE code = ?", JSON.stringify(room), code)
  return room // lock returns what the function returns
})

A key is up to 256 bytes, per binding. A lock is released when the function ends or throws, and after 30 seconds in any case; a caller waits up to 30 seconds for its turn, then lock throws.

From the API

POST /api/services/<name>/db/<BINDING>/query   {"sql": "...", "params": [...]} → [{rows, columns, changes, lastRowId}]
GET  /api/services/<name>/db/<BINDING>/tables  → [{name, sql, rows}]