Cloudflare D1 correlated subquery pitfalls and how to avoid silent wrong results

You wrote a query that counts related rows per parent, your tests are green, and in production every count is zero. The query never errored. It runs fine on your laptop. This is the Cloudflare D1 correlated subquery trap, and it is the worst kind of bug: silent, value-only, and invisible to a test suite that uses local SQLite.

The dangerous part is not that D1 is a different database. It is that D1 is almost the same database. It is SQLite, your ORM speaks SQLite, your local test driver is SQLite, and 99% of your queries behave identically. So you stop thinking about portability. Then one query class, the correlated subquery used as a projected column, diverges, and because the divergence is in the values and not in an error, nothing in your pipeline flags it.

This is a reference for that specific bug and the D1 gotchas that share its shape: valid SQL, no error, wrong answer. Each one comes with the rewrite that behaves the same on D1 and on local SQLite.

What is the Cloudflare D1 correlated subquery bug?

A correlated subquery placed in a SELECT column list can resolve to a constant (commonly 0 or null) on Cloudflare D1 while returning the correct per-row value on local SQLite drivers like better-sqlite3. The SQL is valid and runs without error on both, so the failure is silent.

Here is the shape that bites. You have inboxes and threads, and you want each inbox row to carry a count of its threads and its unread threads for a sidebar badge:

SELECT
  inboxes.id,
  inboxes.name,
  (SELECT COUNT(*) FROM threads
     WHERE threads.inbox_id = inboxes.id) AS thread_count,
  (SELECT COUNT(*) FROM threads
     WHERE threads.inbox_id = inboxes.id
       AND threads.has_unread = 1) AS unread_count
FROM inboxes
WHERE inboxes.is_active = 1
ORDER BY inboxes.name;

Each subquery is correlated: it references inboxes.id from the outer row. Under better-sqlite3 (the in-process SQLite most Node test setups use) this returns the right counts. On Cloudflare D1, the outer-row reference can fail to bind per row, and thread_count / unread_count come back as 0 for every inbox. No exception. No log line. Just zeros where there should be counts.

We hit this exact case in Tinbox's inbox list query. The unit tests ran against better-sqlite3 and asserted on the shape, so they stayed green. The first time the query ran against a real local D1 (under wrangler dev / miniflare), every sidebar badge read zero. The SQL was correct by the SQL standard and correct under the test driver. It was only wrong on the driver that ships to production.

How do I rewrite a correlated subquery to work on Cloudflare D1?

Replace the per-row subquery with a LEFT JOIN to the related table and a GROUP BY on the parent's primary key, then aggregate with COUNT() and SUM(CASE WHEN ...). This stays a single query, avoids N+1, and evaluates identically on D1 and local SQLite.

SELECT
  inboxes.id,
  inboxes.name,
  COUNT(threads.id) AS thread_count,
  COALESCE(SUM(
    CASE WHEN threads.has_unread = 1
          AND threads.status = 'open'
         THEN 1 ELSE 0 END
  ), 0) AS unread_count
FROM inboxes
LEFT JOIN threads ON threads.inbox_id = inboxes.id
WHERE inboxes.is_active = 1
GROUP BY inboxes.id
ORDER BY inboxes.name;

Three things make this robust:

1. LEFT JOIN, not INNER. An inbox with zero threads still appears, with COUNT(threads.id) evaluating to 0 because COUNT ignores nulls from the unmatched side.
2. SUM(CASE WHEN ...) for conditional counts. You cannot COUNT(*) only the unread rows in a grouped query, so encode the predicate as 1/0 and sum it. COALESCE guards the all-null group.
3. GROUP BY on the parent primary key. One row out per inbox, counts attached, in a single round trip.

In Drizzle (which is how Tinbox writes it), the same query reads:

import { and, asc, eq, inArray, sql } from "drizzle-orm";

const rows = await ctx.db
  .select({
    id: inboxes.id,
    name: inboxes.name,
    threadCount: sql<number>`COUNT(${threads.id})`,
    unreadCount: sql<number>`COALESCE(SUM(CASE
      WHEN ${threads.hasUnread} = 1 AND ${threads.status} = 'open'
      THEN 1 ELSE 0 END), 0)`,
  })
  .from(inboxes)
  .leftJoin(threads, eq(threads.inboxId, inboxes.id))
  .where(and(eq(inboxes.isActive, 1), inArray(inboxes.id, allowedIds)))
  .groupBy(inboxes.id)
  .orderBy(asc(inboxes.name));

// D1 returns aggregate columns as numbers, but normalize anyway:
return rows.map((r) => ({
  ...r,
  threadCount: Number(r.threadCount ?? 0),
  unreadCount: Number(r.unreadCount ?? 0),
}));

The Number(...) normalization is not paranoia. D1 and better-sqlite3 occasionally disagree on whether an aggregate comes back as a number or a numeric string, so coerce at the boundary and stop thinking about it.

How do I catch this bug before it ships?

Run at least one test against a real local D1, not only against your in-process SQLite, and assert on the actual numeric values rather than the row shape. The correlated-subquery bug is value-only, so a shape assertion (toHaveLength, "has a threadCount key") sails right past it.

The cheapest version of this is a wrangler/miniflare-backed integration test that seeds known data and checks the counts:

it("returns threadCount + unreadCount per inbox (against real D1)", async () => {
  // 3 threads in one inbox; mark one read so unread = 2, total = 3.
  await seedThread(db, { subject: "a" });
  await seedThread(db, { subject: "b" });
  const read = await seedThread(db, { subject: "c" });
  await db.update(threads).set({ hasUnread: 0 }).where(eq(threads.id, read.id));

  const list = await api.inboxes.list();

  expect(list).toHaveLength(1);
  // VALUE assertions. The correlated-subquery bug returns 0 here;
  // a shape-only check would not notice.
  expect(list[0]).toMatchObject({ threadCount: 3, unreadCount: 2 });
});

If your test runner uses better-sqlite3 for speed (most do, and that is fine), keep it for the bulk of your suite but tag a small set of value-correctness tests to run against wrangler dev --local or a miniflare D1 binding. You do not need to dual-run everything. You need to dual-run the queries that aggregate, join, or use anything fancier than a flat SELECT ... WHERE.

A blunt but effective rule for code review: any correlated subquery in a SELECT column list is a smell on D1. Flag it, and ask whether it can be a join. For the broader set of gotchas that bite before you ever hit this one, the things to know before you put email in D1 is worth a pass.

What other Cloudflare D1 query pitfalls return wrong or surprising results?

The correlated-subquery bug is the loudest member of a family of D1 behaviors that are valid SQL but diverge from a server SQL engine or from local SQLite. Here are the ones worth keeping on a checklist.

Booleans are integers

D1 (and SQLite) have no boolean type. A column you think of as true/false is stored as 1/0. If your code does WHERE has_unread = true, behavior depends on your driver's coercion, and a JS === true check against a returned 1 will fail. Store and compare 1/0 explicitly, and coerce on read (r.isPrimary === 1) rather than trusting the type.

No long-running transactions across awaits

D1 does not give you an open transaction you hold across multiple awaits the way a Postgres connection does. You get db.batch([...]), which runs statements together, but it is not a server-side transaction with the rollback-on-app-error semantics you may expect. Do not architect a multi-step "read, decide in JS, conditionally write" flow assuming the read and write are isolated under a lock. Push the decision into SQL (a conditional UPDATE ... WHERE) or design for idempotency.

The per-query response limits

A single D1 query result must fit within D1's response size and row-count limits. A SELECT * over a large table with big text columns (full email bodies, say) can succeed in your tests with three seeded rows and fail in production at scale. Paginate with LIMIT/OFFSET (or keyset pagination), and select only the columns you render. This is also just good D1 schema design hygiene: keep large blobs out of list queries.

NOT IN with a nullable subquery

WHERE id NOT IN (SELECT other_id FROM t) behaves unintuitively if the subquery can yield NULL, because NOT IN against a set containing NULL returns no rows (a SQL-standard footgun, not D1-specific, but it bites edge apps that lean on subqueries). Prefer NOT EXISTS or LEFT JOIN ... WHERE x.id IS NULL, both of which are null-safe and join-shaped.

Counts that ignore filters

Not a D1 quirk, but the most common pagination bug: a total count that does not apply the same WHERE as the page query, so the total disagrees with the rows. When you compute a count for pagination, run it with the identical filter (and run the two queries with Promise.all so they go in parallel).

Why does Tinbox care about this so much?

Because Tinbox runs entirely on Cloudflare D1, R2, and Queues on the tenant's own account, and the inbox is, literally, a SQLite database the user owns. Every list view, badge, and search is a D1 query. The correlated-subquery bug was the first divergence we found the moment we ran against a real local D1 instead of better-sqlite3, and it will not be the last, so the engineering rule is fixed: aggregates use joins, value tests run against a real D1, and large bodies stay out of list queries.

If you are building your own edge email stack, you will rediscover every one of these the hard way, in roughly this order. That is genuinely useful learning, and the rewrites above are yours to keep whether or not you ever touch Tinbox. But if you would rather inherit a query layer where the D1-safe patterns are already baked in (the inbox list, thread views, FTS5 search, campaign aggregates), that is most of what the data layer is for.

The same instinct that makes you reach for a correlated subquery is the one that makes you reach for a Durable Object when D1 would do: the exotic primitive sounds like it fits. On D1, boring joins are the safe path. For the rest of the platform's rough edges, see whether Cloudflare is ready to run production email, and if you are coming from a server database, the Postgres-to-D1 migration notes cover the broader portability story.

Deploy this yourself: npx tinbox init <domain> stands up the D1-backed inbox with the join-based query layer already in place. The schema, indexes, and these query patterns are documented in the Tinbox docs under the data layer, and it runs on your own Cloudflare account, so the data and queries stay yours.