D1 SQLite on the Edge: What the Docs Don't Tell You

D1 is Cloudflare's SQLite-backed database, available as a binding in Workers. You get a real SQL database — full SQLite syntax, transactions, indexes — accessible from the edge with no TCP connection overhead.

For a blog's view counter, it's perfect. For a financial system, you'd want to understand the tradeoffs first.

The Migration Problem#

D1 has no built-in migration runner. There's no equivalent of db migrate up, no schema versioning, no rollback. If you need the table to exist, you create it yourself.

The pattern that works: CREATE TABLE IF NOT EXISTS on every relevant request.

javascriptasync function initDb(env) {
  try {
    await env.DB.prepare(
      "CREATE TABLE IF NOT EXISTS post_views (slug TEXT PRIMARY KEY, views INTEGER DEFAULT 0)"
    ).run();
  } catch(e) {}
}

This looks wasteful, but D1 makes CREATE TABLE IF NOT EXISTS a near no-op when the table already exists. The first request creates the table; every subsequent request checks and moves on in microseconds. No deployment step, no migration file to maintain.

The catch block swallows errors silently. If D1 is unavailable, the page still loads — just without the view count.

await vs ctx.waitUntil: It Changes What the User Sees#

This is the non-obvious one.

When a user visits a post, I want to increment the view counter and display the updated count on the same page. The naive approach is to fire the increment with ctx.waitUntil (non-blocking, runs after the response is sent) and read the count separately. The problem: you'd read the pre-increment value.

javascript// Wrong: reads count before increment completes
ctx.waitUntil(db.prepare("INSERT OR REPLACE ...").run());
const views = await db.prepare("SELECT views ...").first();
// views is the old count

javascript// Correct: await the increment, then read
await db.prepare("INSERT INTO post_views (slug, views) VALUES (?, 1) ON CONFLICT(slug) DO UPDATE SET views = views + 1").bind(slug).run();
const r = await db.prepare("SELECT views FROM post_views WHERE slug = ?").bind(slug).first();
return r ? r.views : 1;
// views is the updated count

Awaiting costs maybe 5-10ms. For a view counter, that's acceptable. The user sees 1 VIEW on their first visit, not 0 VIEWS with a delayed update.

The Upsert Pattern#

SQLite's INSERT OR REPLACE and ON CONFLICT DO UPDATE are your friends. For a view counter, you want: insert with views=1 if the slug doesn't exist, or increment if it does. One query, atomic.

sqlINSERT INTO post_views (slug, views) VALUES (?, 1)
ON CONFLICT(slug) DO UPDATE SET views = views + 1

This is safer than SELECT + conditional INSERT/UPDATE, which would require a transaction to be race-free.

D1 is Not for High Write Throughput#

D1 sits behind a single writer architecture — like SQLite itself, writes are serialized. For a blog with occasional readers, this is irrelevant. If you have hundreds of concurrent write requests per second, you'll hit limits.

Read throughput is fine. D1 uses read replicas across regions for SELECT queries, so reads scale horizontally without any configuration.

The practical limit for this use case: view counter increments are infrequent relative to reads. A post with 10,000 views per day averages 7 writes per minute — SQLite handles this without thinking.

Local Development#

D1 bindings work in wrangler dev with a local SQLite file. The schema is separate from prod, so you need to run your CREATE TABLE IF NOT EXISTS manually or let the first request do it.

bashwrangler d1 execute dwsa-db --local --command \
  "CREATE TABLE IF NOT EXISTS post_views (slug TEXT PRIMARY KEY, views INTEGER DEFAULT 0)"

Or just visit a post page in local dev — initDb runs on the first request and creates the table automatically. Same production behavior, no extra step.

What Works Well#

D1 is genuinely good for:

• Counters and simple aggregates
• User preferences or settings (key-value but queryable)
• Anything where you'd normally reach for a SQLite file

It's not a replacement for Postgres or MySQL in systems that need foreign keys enforced at the DB level, complex joins on large datasets, or streaming replication. For a blog, it's the right tool.