skip to content

With a SQLite-backed Durable Object, how do you run a parameterised query through ctx.storage.sql.exec(), and what does its cursor give you?

level: middleimportance: should knowfreq 50%

answer

  1. synchronous, no await
  2. question-mark placeholders, extra arguments
  3. toArray, one, raw, next
  4. rowsRead and rowsWritten
  5. consume before the next await

basics

~10 s

ctx.storage.sql.exec(query, ...bindings) runs SQL synchronously, fills each ? placeholder from the extra arguments, and returns a SqlStorageCursor you iterate or read with toArray(), one() or raw(), plus rowsRead and rowsWritten counts.

solid answer

~40 s

`ctx.storage.sql.exec()` takes a query string with `?` placeholders and one extra argument per placeholder, so request values never get spliced into the SQL. It runs synchronously and returns a `SqlStorageCursor`. You can iterate it with `for...of`, call `toArray()` for every row as an object, `one()` when exactly one row must match (it throws on zero or several), or `raw()` for rows as arrays alongside `columnNames`. `rowsRead` and `rowsWritten` count the work that is billed. Several statements separated by semicolons may share one call, but the bindings and the returned cursor belong to the last statement. `exec()` refuses `BEGIN TRANSACTION` and `SAVEPOINT`; use `ctx.storage.transactionSync()` instead. Consume a cursor before the next `await`, because a cursor read later gets no snapshot isolation.

code

javascript · 46 lines
javascript
import { DurableObject } from "cloudflare:workers";

export class TicketEvent extends DurableObject {
  constructor(ctx, env) {
    super(ctx, env);
    ctx.storage.sql.exec(`CREATE TABLE IF NOT EXISTS holds (
      seat TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      expires_at INTEGER NOT NULL
    )`);
  }

  addHold(seat, userId, minutes) {
    const cursor = this.ctx.storage.sql.exec(
      "INSERT INTO holds (seat, user_id, expires_at) VALUES (?, ?, ?)",
      seat, userId, Date.now() + minutes * 60_000,
    );
    return cursor.rowsWritten;
  }

  holdsFor(userId) {
    const cursor = this.ctx.storage.sql.exec(
      "SELECT seat, expires_at FROM holds WHERE user_id = ? AND expires_at > ?",
      userId, Date.now(),
    );
    const rows = cursor.toArray(); // consume before any await
    return { rows, rowsRead: cursor.rowsRead };
  }

  seatOwner(seat) {
    // one() throws unless exactly one row matches
    return this.ctx.storage.sql
      .exec("SELECT user_id FROM holds WHERE seat = ?", seat)
      .one().user_id;
  }
}

export default {
  async fetch(request, env) {
    const stub = env.TICKETS.getByName("gig-1");
    const written = await stub.addHold("A1", "u-42", 15);
    const mine = await stub.holdsFor("u-42");
    const owner = await stub.seatOwner("A1");
    return Response.json({ written, mine, owner });
  },
};

go deeper

for a junior

Recall the shape: exec(sql, ...values) with question-mark placeholders, then toArray() or one() on the result, with no await because the call is synchronous.

for a middle

Explain the cursor members, the last-statement rule for multi-statement calls, why exec() rejects BEGIN TRANSACTION, and how rowsRead and rowsWritten relate to billing and indexes.

for a senior

Show the production habits: bindings for every value, cursors consumed before any await, the 100-parameter and 100 KB statement limits, and schema versions tracked in a table.

for a principal

Weigh per-object SQL against a shared database: queries stay local and cheap inside one object, but nothing joins across objects, so the data model must follow how objects are split.

## The call On a **SQLite-backed Durable Object**, `ctx.storage.sql` is a `SqlStorage` object and `exec()` is its main method: ```javascript const cursor = this.ctx.storage.sql.exec( "SELECT seat, expires_at FROM holds WHERE user_id = ? AND expires_at > ?", userId, Date.now(), ); ``` - The first argument is the SQL text. Each `?` is a **placeholder**. - Every following argument is a **binding**, matched to the placeholders in order. A count mismatch throws `Wrong number of parameter bindings for SQL query.` - The call is **synchronous**. The database is embedded in the object, so there is no network round trip and nothing to `await`. Binding values instead of building strings keeps user input out of the SQL text, which is the defence against SQL injection. It also keeps statements short: one statement may be at most **100 KB** and carry at most **100** bound parameters. ## Reading the cursor | Member | What it gives you | | --- | --- | | `for (const row of cursor)` / `next()` | rows one at a time, each as an object keyed by column name | | `toArray()` | every remaining row as an array of objects | | `one()` | the single row; throws `Expected exactly one result from SQL query, but got no results.` on zero rows, and also throws on more than one | | `raw()` | an iterator over the same rows as arrays of values, with names in `columnNames` | | `rowsRead`, `rowsWritten` | counters that grow as you iterate; their final values are what SQL billing uses | `raw()` and the object view walk the same result, so mixing them continues where the other stopped. TypeScript users can write `exec<Hold>(...)` for typed rows, but the type parameter does not validate anything at runtime. ## Consume before you await A cursor can technically be kept across an `await`, but it is **not a snapshot**. If the object awaits a `fetch()` and then keeps reading, the remaining rows may reflect writes made in the meantime, including writes from a later implicit transaction that might still roll back. The safe habit is to finish the cursor (`toArray()`, `one()` or a loop) in the same synchronous stretch that created it. ## What exec() refuses - `BEGIN TRANSACTION` and `SAVEPOINT` throw, with a message pointing to `state.storage.transaction()` or `state.storage.transactionSync()`. Those APIs roll back on exceptions and cooperate with the object's automatic write coalescing. - `PRAGMA user_version` is not supported, so schema versions are tracked in a table of your own. - On a class created with the legacy key-value backend, any `exec()` throws `SQL is not enabled for this Durable Object class`. ## Several statements in one call `exec()` accepts several statements separated by semicolons, such as a `CREATE TABLE IF NOT EXISTS` followed by an `INSERT`. Two details follow from the API contract: 1. Bindings apply to the **last** statement only, so placeholders belong there. 2. The returned cursor describes only the **last** statement's result. This is convenient for schema setup in the constructor, and a trap if you expect results from an earlier statement. ## Reading the code example The example's constructor creates the `holds` table with `CREATE TABLE IF NOT EXISTS`, which is safe to repeat because the constructor runs on every start. `addHold()` binds three values and returns `rowsWritten`, which comes back as 2: one table row plus one entry in the index behind the `TEXT PRIMARY KEY`. `holdsFor()` passes the user and the current time as bindings, turns the cursor into an array straight away, and reports `rowsRead` for the caller to log. `seatOwner()` uses `one()`, so asking about a seat nobody holds raises an error instead of quietly returning nothing; the calling Worker should catch it and answer 404. ## Rows, billing and numbers - Every row an index has to update counts as an extra row written, so an `INSERT` into a table with a `TEXT PRIMARY KEY` can report `rowsWritten` of 2. - Reads are billed by `rowsRead`, so an index that turns a full scan into a lookup lowers the bill as well as the latency. - Integers come back as JavaScript numbers, so a 64-bit value beyond the safe-integer range can come back less precise than it went in; store large identifiers as text. - `ctx.storage.sql.databaseSize` reports the database's current size in bytes, useful for watching growth toward the per-object limit.

  • Why might one INSERT into a Durable Object table report rowsWritten of 2?
    Index maintenance is billed as rows written. A table whose primary key is a `TEXT` column is backed by an index, so one `INSERT` writes the table row and an index entry, and `rowsWritten` reports 2. Indexes still usually pay for themselves on read-heavy objects, because they cut `rowsRead`.
  • How do you roll one SQLite-backed Durable Object back to how it looked an hour ago?
    Use the point-in-time recovery API: `await ctx.storage.getBookmarkForTime(Date.now() - 3_600_000)` returns a bookmark (any time in the last 30 days), `await ctx.storage.onNextSessionRestoreBookmark(bookmark)` schedules the restore, and `ctx.abort()` restarts the object so it comes back at that point. The call returns a bookmark that undoes the restore. It covers SQL and key-value data, and does not work in local development.
  • Should a Durable Object build SQL text from request parameters when the values are validated?
    No. Pass every value as a binding to a `?` placeholder, even after validation. Bindings keep data out of the SQL text entirely, so a crafted value cannot change the statement. Only identifiers you choose from a fixed list, such as a column to sort by, may be placed into the text, and only from that allow-list.

  • Concatenating request values into the SQL string is fine once they are validated.
  • ctx.storage.sql.exec() returns a Promise that must be awaited.
  • With several statements in one exec() call, the bindings fill the first statement.
  • Related statements belong between BEGIN TRANSACTION and COMMIT inside exec().
  • cursor.one() returns the first row when several rows match.