skip to content

In Cloudflare D1, how do you query from a Worker with prepare() and bind(), and when do you choose first(), all(), run() or raw()?

level: juniorimportance: must knowfreq 70%

answer

  1. a statement object before any query
  2. placeholders, never template strings
  3. one row, or null
  4. rows as arrays of values

basics

~10 s

Write env.DB.prepare(sql) with ? or ?NNN placeholders and pass values through bind(), never string concatenation. first() returns one row or null, all() and run() return rows plus meta, and raw() returns rows as arrays.

solid answer

~40 s

A D1 binding (`env.DB`) offers `prepare(sql)`, which returns a `D1PreparedStatement`; `bind(...values)` fills its `?` (anonymous) or `?1`, `?2` (ordered) placeholders. Binding keeps user input out of the SQL text, so the query is safe from injection and the statement can be reused. Then pick the method by the shape you need: `first()` returns the first row as an object, or `null` when nothing matches, and `first("email")` returns one column's value; `all()` returns a `D1Result` with `results` and `meta`; `run()` is documented as functionally the same as `all()` and is the usual call for writes, whose `results` is empty unless the SQL has `RETURNING`; `raw()` returns an array of arrays, and `raw({ columnNames: true })` adds the column names as the first row. Bind `null`, not `undefined`, which fails with `D1_TYPE_ERROR`.

code

javascript · 27 lines
javascript
export default {
  async fetch(request, env) {
    const url = new URL(request.url);
    const userId = Number(url.searchParams.get("user"));

    if (request.method === "POST") {
      const { body } = await request.json();
      const { meta } = await env.DB
        .prepare("INSERT INTO notes (user_id, body) VALUES (?1, ?2)")
        .bind(userId, body)
        .run();
      return Response.json({ id: meta.last_row_id, rowsWritten: meta.rows_written }, { status: 201 });
    }

    const user = await env.DB
      .prepare("SELECT id, email FROM users WHERE id = ?")
      .bind(userId)
      .first();
    if (user === null) return new Response("No such user", { status: 404 });

    const { results } = await env.DB
      .prepare("SELECT id, body FROM notes WHERE user_id = ?1 ORDER BY id DESC LIMIT ?2")
      .bind(userId, 20)
      .all();
    return Response.json({ user, notes: results });
  },
};

go deeper

for a junior

Recall prepare(), bind() and the four run methods, and say plainly that request values always go through bind() so they cannot change the SQL.

for a middle

Explain ? versus ?NNN, what first() returns with no rows or with a column name, what a D1Result carries, and why undefined fails where null works.

for a senior

Show that you read meta: changes to see whether an update matched, last_row_id after an insert, rows_read and rows_written when a query costs more than its result suggests.

for a principal

Set the team rule that no SQL string is ever assembled from input, and decide where typed row shapes and query helpers live so request handlers stay thin.

## The binding and the prepared statement A Worker reaches a D1 database through a **binding**: an entry in the `d1_databases` array of the Wrangler configuration file whose `binding` name (for example `DB`) becomes a property of `env`. The binding object has the type `D1Database`, and its everyday method is `prepare(sql)`. It returns a **`D1PreparedStatement`**, an object that holds the SQL text and, once bound, its parameter values. Nothing reaches the database until you call one of the statement's run methods, so preparing is cheap, and one statement can be prepared once and bound many times with different values. In a notes API with `users` and `notes` tables, a read handler typically looks up one user and then that user's newest notes. Each lookup is one prepared statement, bound with values taken from the request. ## Placeholders and bind() D1 follows SQLite's convention for parameters, and its documentation describes two forms: - **`?` (anonymous)**: each `?` takes the next value passed to `bind()`, left to right. - **`?NNN` (ordered)**: `?1`, `?2` and so on name a position, so `bind(userId, 20)` fills every `?1` with `userId` and every `?2` with `20`, and one value can be reused in several places. `bind()` returns a statement with the values attached. The values travel separately from the SQL text, so an input such as `x' OR '1'='1` is compared as a literal string instead of being parsed as SQL. That is the defence against **SQL injection**, and it is why building SQL with template literals from request data is the classic mistake: the input that a bound query treats as an odd email address becomes an extra `OR` condition once it is pasted into the string. Static statements with hard-coded literals are allowed for constant queries, but anything that came from a user goes through `bind()`. A few binding rules surprise people: - Bind `null` for SQL `NULL`. A JavaScript `undefined` is not converted; the query fails with `D1_TYPE_ERROR`. - D1 converts values one way on write: a boolean is stored as the integer `1` or `0` and comes back as a number, and an `ArrayBuffer` is stored as a `BLOB` and comes back as an array of numbers. - A query can carry at most 100 bound parameters. - JavaScript numbers are exact only up to `Number.MAX_SAFE_INTEGER`, so very large 64-bit integers can come back less precise than they went in. ## Choosing the run method | Method | Returns | Typical use | |---|---|---| | `first()` | The first row as an object, or `null` when there are no rows | One record by key | | `first("column")` | That column's value from the first row | A count or one field | | `all()` | A `D1Result`: `success`, `meta` and `results` (an array of row objects) | Lists | | `run()` | A `D1Result`, documented as functionally equivalent to `all()` | Writes, where `meta` matters | | `raw()` | An array of arrays, one per row, with no metadata | Compact output, joins with repeated column names | Some details matter in practice: - `first()` does not add `LIMIT 1` to the SQL, so a query that matches many rows can still read them all; add the `LIMIT` yourself. - When the query returns rows but the named column does not exist, `first("column")` throws a `D1_COLUMN_NOTFOUND` error. - `raw({ columnNames: true })` puts the column names in the first row of the array. - Because `all()` builds objects keyed by column name, a join that selects two columns called `id` keeps only one of them; `raw()` keeps both by position. - In TypeScript, `run()`, `raw()` and `first()` accept a type parameter describing the row. ## What the result's meta reports The `meta` object on every `D1Result` describes what the statement did: - `changes`: the number of rows inserted, updated or deleted. - `last_row_id`: the rowid of the last inserted row, for tables defined without `WITHOUT ROWID`. - `rows_read` and `rows_written`: the counts D1 bills by. An insert into a table that has an index can report more than one row written, because the index entry is written too. - `duration`, `changed_db` and `size_after`: timing, whether anything changed, and the database size after the statement. For a plain `INSERT`, `UPDATE` or `DELETE`, `results` is an empty array. Add a `RETURNING` clause when you want the written row back. ## Common mistakes 1. Interpolating request values into SQL instead of binding them. 2. Passing `undefined` for an optional field instead of `null`. 3. Expecting `first()` to throw when nothing matches; it returns `null`, and the handler must check for it. 4. Calling `all()` and taking `results[0]` for a single record, when `first()` states the intent. 5. Looking at `results` after an `UPDATE` to learn whether it matched; the answer is in `meta.changes`.

  • Why use ?1 and ?2 when a plain ? already works in D1?
    Ordered placeholders name a position, so one value can fill several places and reordering the SQL does not silently swap arguments. With anonymous `?`, each mark takes the next `bind()` value, which is easy to miscount in a long statement; SQLite's own documentation discourages bare `?` for that reason, and D1 follows SQLite's convention.
  • How do you get the id of a note you just inserted?
    Read `meta.last_row_id` from the `D1Result` that `run()` returns, or add `RETURNING id` to the `INSERT` and read it from `results` (or call `first()` on that statement). `last_row_id` applies to ordinary rowid tables; `RETURNING` also hands back other columns of the new row.
  • When is raw() a better choice than all() in D1?
    When a join selects two columns with the same name: `all()` builds objects keyed by column name, so only one `id` survives, while `raw()` keeps every column by position. `raw()` also skips building objects, and `raw({ columnNames: true })` adds a header row when you still want the names.

  • Building the SQL with a template literal is fine if quotes in the input are escaped by hand.
  • first() throws an error when the query matches no rows.
  • Passing undefined to bind() stores NULL in the column.
  • run() never returns rows, so a SELECT must always use all().
  • raw() returns objects keyed by column name, the same as all().