skip to content

Edge Data Stores

Cloudflare's stores each fit one access pattern: KV for read-heavy keys, R2 for objects, D1 for SQL, Hyperdrive for an existing database. Interviewers ask which one fits.

on this pageshow

explore

questions

27

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().
open as a page

In Workers KV, how does a Worker read and write a feature flag through its binding, and what does get() return for a missing key?

level: juniorimportance: must knowfreq 70%

basics

~20 s

A Worker reads with env.FLAGS.get(key), passing type "json" to get a parsed object, and writes with env.FLAGS.put(key, value, options). Both return promises to await, and get() resolves to null when the key does not exist.

open as a page

In Cloudflare Workers, how do you write, read and list objects in an R2 bucket through its binding, and what does each call return?

level: juniorimportance: must knowfreq 65%

basics

~10 s

Bind the bucket under r2_buckets and call env.BUCKET.put(), get(), head(), list() and delete(). put() returns an R2Object, get() an R2ObjectBody with a stream, head() metadata only, and a missing key gives null.

open as a page

In Cloudflare D1, how does env.DB.batch() make several statements atomic, and why does a Worker not send BEGIN TRANSACTION instead?

level: middleimportance: must knowfreq 55%

basics

~20 s

env.DB.batch() runs an array of prepared statements in order as one SQL transaction in a single call; if one fails, all roll back and the call rejects. D1 already wraps each query in a transaction, so BEGIN is rejected.

open as a page

In Workers KV, why can a feature flag an admin just switched off still read as on in other locations, and what does cacheTtl change?

level: middleimportance: must knowfreq 65%

basics

~20 s

Workers KV writes go to central stores while each location serves its cached copy, so other locations may see the old value for 60 seconds or more. cacheTtl sets that cache time: 60 seconds by default, 30 at minimum.

open as a page

When browsers upload large videos straight into a Cloudflare R2 bucket with presigned URLs, how are the URLs issued and what must the bucket allow?

level: middleimportance: must knowfreq 50%

basics

~20 s

A Worker signs a short-lived PUT URL for R2's S3-compatible endpoint using an R2 API token kept as a secret, and the browser uploads directly. The bucket needs a CORS rule allowing your origin, PUT and the Content-Type header.

open as a page

How does a Cloudflare Worker query an existing PostgreSQL database through Hyperdrive, from creating the config to running a query?

level: juniorimportance: should knowfreq 32%

basics

~10 s

Create a Hyperdrive config with wrangler hyperdrive create and the database's connection string, bind its ID under hyperdrive in the Wrangler file, then pass env.HYPERDRIVE.connectionString to node-postgres or Postgres.js inside the handler.

open as a page

With Wrangler 4 and Cloudflare D1, how do wrangler d1 migrations create and apply work, and how do changes reach local and remote databases?

level: middleimportance: should knowfreq 45%

basics

~20 s

wrangler d1 migrations create writes a numbered .sql file into migrations/. wrangler d1 migrations apply runs every file not yet recorded in the database's d1_migrations table: on the local database by default, and on the real one only with --remote.

open as a page

In Cloudflare D1, how does env.DB.withSession() let a Worker read from read replicas, and what do first-primary and first-unconstrained change?

level: middleimportance: should knowfreq 42%

basics

~20 s

D1 serves reads from replicas only for queries made through a session from withSession(); queries on the plain binding all run on the primary. first-unconstrained, the default, lets the first query hit any instance; first-primary sends it to the primary.

open as a page

In Cloudflare D1, how do you use Time Travel to recover from an UPDATE run without a WHERE clause, and what does a restore do?

level: middleimportance: should knowfreq 40%

basics

~20 s

Time Travel keeps D1's history for 30 days on Workers Paid (7 on Free). wrangler d1 time-travel restore with --timestamp or --bookmark overwrites the whole database in place, cancels in-flight queries and prints the previous bookmark for undoing it.

open as a page

With Hyperdrive, why should a Cloudflare Worker create a new database client in every request handler instead of keeping one global connection pool?

level: middleimportance: should knowfreq 28%

basics

~20 s

Workers cannot do I/O at module scope or reuse a socket from another request, so a global driver pool fails. Hyperdrive makes a per-request client cheap: the handshake finishes near the Worker and Hyperdrive reuses its own pooled connections near the database.

open as a page

In Workers KV, how do you seed thousands of site-configuration keys at once when the Worker binding offers no bulk write?

level: middleimportance: should knowfreq 30%

basics

~20 s

Write them outside the Worker: wrangler kv bulk put with a JSON array file, or the REST API's bulk endpoint, which takes up to 10,000 pairs per request. In Wrangler 4 the command writes to local storage unless it is given --remote.

open as a page

In Workers KV, how do you page through every key under a prefix with list(), and when should small values live in metadata instead?

level: middleimportance: should knowfreq 40%

basics

~20 s

Call env.FLAGS.list({ prefix, cursor }) in a loop, passing the returned cursor and the same prefix each time, until list_complete is true. list() returns key names, expirations and metadata but not values, so small values kept in metadata avoid one get() per key.

open as a page

For an R2 bucket that serves public thumbnails, why is the r2.dev URL wrong for production, and what does a custom domain change?

level: middleimportance: should knowfreq 45%

basics

~20 s

The r2.dev URL is a rate-limited testing endpoint that returns 429 under load and cannot use caching, WAF or Access. A custom domain on a zone in the same account gives a production hostname that Cloudflare's cache and security rules can front.

open as a page

A Cloudflare D1 notes API reads a note with first(), then awaits an UPDATE, and concurrent edits silently overwrite each other; why, and how do you fix it with D1's API?

level: seniorimportance: should knowfreq 35%

basics

~20 s

Each awaited D1 query commits on its own, so another request can write between the read and the UPDATE. Make the write conditional, WHERE id = ? AND version = ?, and treat meta.changes of 0 as a conflict.

open as a page

A worldwide product catalogue on Cloudflare D1 has read replication on; a user saves a profile edit and the next page shows the old profile. Why, and how do bookmarks fix it?

level: seniorimportance: should knowfreq 38%

basics

~20 s

Each request opened a new D1 session with first-unconstrained, so the next read hit a replica that had not yet applied the write. Returning session.getBookmark() to the client and passing it into withSession() makes the replica wait until it has the edit.

open as a page

Why does a product listing read through Hyperdrive keep showing the old price for about a minute after an admin updates it, and how do you fix it?

level: seniorimportance: should knowfreq 30%

basics

~20 s

Hyperdrive never invalidates cached reads when a write happens, so the listing query returns its cached result until max_age (60 s by default) expires. Route reads that must be fresh through a second, cache-disabled Hyperdrive config, or shorten max_age.

open as a page

A team proposes keeping a per-user request counter in Workers KV, incremented on every request; what breaks in production, and where should the counter live instead?

level: seniorimportance: should knowfreq 50%

basics

~20 s

A Workers KV key accepts one write per second, so a busy user's increments start failing with 429 errors. A get-then-put is not atomic, so concurrent requests overwrite each other, and stale reads in other locations lose more. Counters belong in a Durable Object.

open as a page

In Cloudflare R2, two tabs save the same profile JSON through a Worker and one edit vanishes; how do onlyIf conditions on put() prevent it?

level: seniorimportance: should knowfreq 28%

basics

~20 s

R2 is last-writer-wins, so the second save replaced the first. Send back the etag that was read and put() with onlyIf etagMatches or If-Match; if the object changed, put() returns null without writing, and the Worker answers 412.

open as a page

In a Worker that proxies R2 multipart video uploads with createMultipartUpload() and uploadPart(), why would complete() fail for some clients?

level: seniorimportance: should knowfreq 35%

basics

~20 s

R2 requires non-final parts to share one size of at least 5 MiB, and complete() needs every part's partNumber and etag. Uploads are aborted seven days after creation by default, and each part must fit the Worker's request body limit.

open as a page

For a multi-tenant SaaS on Cloudflare D1, how would you decide between one shared database and one D1 database per tenant?

level: principalimportance: should knowfreq 30%

basics

~20 s

D1 is built to scale out across many small databases: each one is capped at 10 GB and runs queries one at a time. Per-tenant databases buy headroom, isolation and per-tenant restores, at the cost of fleet-wide migrations, routing and cross-tenant reporting.

open as a page

As a platform lead, what rule would you set for which data a team may keep in Workers KV, and what evidence would make you move a dataset out of it?

level: principalimportance: should knowfreq 30%

basics

~20 s

Allow Workers KV only for data read far more than written, with one writer well under once a second per key, that tolerates reads a minute or more old. Move it out on 429s, lost updates, stale-read bugs or query-shaped access.

open as a page

As the lead for an R2 bucket of user photos, thumbnails and videos, how would you set its storage-class and lifecycle policy?

level: principalimportance: should knowfreq 25%

basics

~20 s

Keep frequently read data such as thumbnails in Standard and move only long-tail originals to Infrequent Access, which stores for a third less but adds retrieval fees, a 30-day minimum and pricier operations. Abort stale multipart uploads and expire staging objects.

open as a page

How do you export a Cloudflare D1 database with wrangler d1 export and load a SQL file into D1, and what limits apply?

level: middleimportance: nice to knowfreq 24%

basics

~20 s

wrangler d1 export --remote --output writes the schema and data as a .sql file and blocks the database while it runs. Loading uses wrangler d1 execute --remote --file, with files up to 5 GB and statements up to 100 KB.

open as a page

Which read queries does Hyperdrive leave uncached even with query caching enabled, and how do you make a frequent listing query cacheable?

level: middleimportance: nice to knowfreq 22%

basics

~20 s

Hyperdrive caches only non-mutating reads. Writes, queries calling STABLE or VOLATILE PostgreSQL functions such as NOW() or RANDOM(), results over 50 MB and Postgres.js with prepare: false stay uncached; compute values in the Worker and pass them as parameters.

open as a page

Why does creating a Hyperdrive config for a PostgreSQL database with only a private network address fail with error 2009, and how do you connect it safely?

level: seniorimportance: nice to knowfreq 16%

basics

~20 s

Hyperdrive connects to public addresses from Cloudflare's network, and error 2009 means the host is not public. Run Cloudflare Tunnel in the private network and point Hyperdrive at it through a Workers VPC service or an Access-protected tunnel hostname.

open as a page

How would you set a team's Hyperdrive rules for query caching and origin connection limits when several Workers share one PostgreSQL database?

level: principalimportance: nice to knowfreq 14%

basics

~20 s

Classify reads by how stale they may be, give cache-tolerant reads a cached config with a deliberate max_age and route the rest through a cache-disabled one, then budget each config's soft origin connection limit so their sum stays under the database's cap.

open as a page