D1: SQLite at the Edge

If R2 is "the bytes," D1 is "the rows." Cloudflare D1 is a SQLite database wrapped in a Workers binding — full SQL, full ACID transactions, no connection pool to manage, no driver to install. You declare a binding in wrangler.toml, and env.DB.prepare(...) becomes a method call on the runtime. That single design choice — no network handshake between your Worker and the database — is why D1 fits so cleanly into the edge.

It's also why D1 is not Postgres, and why a clear-eyed view of what D1 is and isn't will save you a week of refactoring.

The mental model

D1 is SQLite. Same SQL dialect, same type affinity rules, same PRAGMAs, same INTEGER PRIMARY KEY = ROWID semantics, same lack of BOOLEAN as a real type. If you've ever written a SQLite query, you already know D1's surface.

Where D1 diverges from local SQLite:

• It runs as a service. Your database lives in Cloudflare's network with a primary in one region and read-only replicas in others. The Worker talks to it over the Cloudflare backbone, not over an open Postgres-style connection.
• No long-lived connections. Every Worker invocation gets a fresh "session." There's no connection pool — there's nothing to pool.
• Statements are prepared, then executed. The shape — env.DB.prepare(sql).bind(...args).first() — is the same for every query.

The binding in wrangler.toml looks like this (lifted from saas/wrangler.toml):

[[d1_databases]]
binding = "DB"
database_name = "simpleappshipper-db"
database_id = "680bd509-4a31-4a80-97dd-2e4cdb10129f"

That's the whole "driver installation." From now on, env.DB is a D1Database inside any handler.

Pricing and limits

D1 has a generous free tier: 5 million reads/day, 100,000 writes/day, and 5 GB of total storage across all your databases — comfortably enough to ship a real product to a real audience. Paid (Workers Paid plan) is $0.001 per 1k reads, $1.00 per 1M writes, and $0.75/GB-month. There's a row-read accounting model under the hood — a query that touches 1,000 rows counts as 1,000 reads, not 1 — so indexed queries pay off both in latency and in dollars.

Per-database limits to remember: 10 GB per database (you can have many databases per account) and a 1 MB max response size per query. The 1 MB ceiling is the most common production gotcha — a SELECT * over a wide table will silently truncate. Always project the columns you actually need.

The five query methods

The whole D1 surface is five methods on a prepared statement. Here they are, each with a real example from the SAS backend.

.first() — return a single row

const u = await env.DB.prepare(
  'SELECT subscription_tier, subscription_expires_at FROM users WHERE device_id = ?'
).bind(deviceId).first();

Returns the first row as an object, or null. Optionally takes a column name (.first('subscription_tier')) to return just that value. Use it any time you expect at most one row — primary-key lookups, "does this exist", "give me the latest". It's the single most common shape in the SAS codebase.

.all() — return every row

const { results } = await env.DB.prepare(query).bind(...params).all();

Returns { results, success, meta } where results is an array of row objects. The meta block carries duration and rows_read / rows_written — useful for slow-query logging and for the row-read pricing calculation.

If your query could conceivably return tens of thousands of rows, always pair .all() with LIMIT and an explicit OFFSET or a cursor column. The 1 MB response cap is unforgiving.

.run() — execute, return only metadata

await env.DB.prepare(
  'INSERT INTO users (device_id, points) VALUES (?, ?)'
).bind(deviceId, 0).run();

Use .run() for INSERT / UPDATE / DELETE / CREATE TABLE / anything where you don't need the rows back. The return value has meta.last_row_id and meta.changes, which is how you grab the auto-incremented primary key after an insert.

.raw() — return arrays of values, not objects

.raw() returns [col1, col2, ...] per row instead of { col1: ..., col2: ... }. Marginally faster (no per-row column name allocation) and rarely needed unless you're hot-pathing a large result set into JSON.

.batch() — multiple statements atomically

await env.DB.batch(stmts);

batch takes an array of prepared statements and runs them in a single atomic operation against the primary. Either they all succeed or none do. The SAS backend uses this for any "transactional" operation — for example, inserting a captured screenshot row + bumping the user's screens_uploaded counter must happen together or not at all:

const stmts = [
  env.DB.prepare(
    'INSERT INTO screens (id, app_id, user_id, image_hash, image_url) VALUES (?, ?, ?, ?, ?)'
  ).bind(screenId, appId, userId, hash, r2Url),
  env.DB.prepare(
    'UPDATE users SET screens_uploaded = screens_uploaded + 1 WHERE id = ?'
  ).bind(userId),
];
await env.DB.batch(stmts);

Worth knowing: batch is not a general transaction primitive. You can't read a row, decide what to do, then write inside the same transaction. For that, see "where D1 still loses to Postgres" below.

Prepared statements and SQL injection

prepare(...).bind(...) is the only safe shape. The placeholders (?) are positional, and .bind(...) is the substitution. Never string-template a user value into the SQL. This is the same advice you've heard for every SQL surface ever invented; D1 doesn't change it.

// SAFE
await env.DB.prepare('SELECT * FROM users WHERE email = ?').bind(email).first();
 
// UNSAFE — concatenated user input
await env.DB.prepare(`SELECT * FROM users WHERE email = '${email}'`).first();

D1 lets you re-bind the same prepared statement with different parameters — useful inside loops — but for typical Worker code, building a fresh prepare(...).bind(...) chain per call is clearer and the perf cost is invisible.

Real schema, real product

Here's a slice of the actual users table SAS runs in production, from saas/schema.sql:

CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE,
    display_name TEXT,
    device_id TEXT,
    points INTEGER DEFAULT 0,
    tier TEXT DEFAULT 'free',
    screens_uploaded INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    last_active_at TEXT DEFAULT (datetime('now')),
    stripe_customer_id TEXT,
    stripe_subscription_id TEXT,
    subscription_tier TEXT DEFAULT 'free',
    subscription_expires_at TEXT,
    credits INTEGER DEFAULT 1000,
    is_banned INTEGER DEFAULT 0,
    is_admin INTEGER DEFAULT 0,
    google_sub TEXT
);

Three SQLite-isms worth calling out, because they trip up Postgres veterans:

• No native BOOLEAN. We store is_banned INTEGER DEFAULT 0 and treat 0/1 as false/true. Filters become WHERE is_banned = 1, not WHERE is_banned IS TRUE.
• Dates as ISO 8601 strings. SQLite's datetime('now') produces '2026-05-19 12:34:56'. We never use the integer-Unix or REAL-Julian formats; the string form sorts correctly and is human-readable.
• UNIQUE and the default NULL-skipping rule. SQLite lets multiple rows have email IS NULL even when the column is UNIQUE. Useful here — anonymous (device-only) users have no email but share the constraint.

Migrations

D1 uses a forward-only, file-based migration system. Sequentially numbered SQL files live in migrations/:

saas/migrations/
├── 001_add_google_sub.sql
├── 002_add_webapp_research.sql
├── 003_add_rich_ai_metadata.sql
├── 004_add_viral_tweets.sql
├── 005_add_subscription_columns.sql
├── 006_add_admin_flags.sql
└── 007_add_courses_and_videos.sql

wrangler d1 migrations apply <database> runs the unapplied ones in order, tracking what's been applied in an internal d1_migrations table. The migrations themselves are plain SQL — ALTER TABLE ... ADD COLUMN ..., CREATE TABLE, CREATE INDEX. Two practical rules:

1. Migrations are forward-only. There's no down migration. If you need to undo something, you write a new migration that does the undoing.
2. SQLite ALTER TABLE is limited. You can add columns and rename them, but you can't drop a column, change a type, or add a CHECK constraint to an existing column in one statement. The textbook workaround is "create a new table, copy rows, drop the old, rename" — annoying but rarely needed if you treat columns as append-only from day one.

Indexes — the only knob that matters

D1 charges per row-read. An unindexed query that scans 100k rows costs 100k reads and takes ~1s. The same query against a well-chosen index reads ~1 row and takes ~10ms. The economic incentive to index aggressively is much sharper than on Postgres.

Two indexes from the SAS schema that earn their keep:

CREATE INDEX IF NOT EXISTS idx_rate_buckets_window ON rate_buckets(window_start);
CREATE INDEX IF NOT EXISTS idx_screens_app ON screens(app_id, flow_index);

The first lets rate-limit lookups by current-window scan one bucket per identity instead of the whole table. The second is a covering index for "give me this app's screens in flow order" — the most common library query. Both follow the rule of thumb: every column that ever appears in a WHERE, JOIN, or ORDER BY clause is a candidate for an index, and the cost of a redundant index in D1 is tiny.

Sessions API and read replicas

D1 replicates reads to nearby regions. By default, every query goes to the primary for consistency — which means a Worker in Sydney talking to a primary in San Jose pays a ~150ms RTT per query.

The Sessions API opts you into bounded staleness: you get a session token after a write, and subsequent reads on the same session can be served from a nearby replica as long as the replica is at least as fresh as your last write. The shape, conceptually:

const session = env.DB.withSession();
await session.prepare('INSERT INTO users (...)').bind(...).run();
const me = await session.prepare('SELECT * FROM users WHERE id = ?').bind(id).first();
// Re-uses the same session — guaranteed to read your own write.

For an indie product running globally with mostly small queries, the default routing is usually fine; reach for Sessions when you've measured the cross-region latency cost and decided it matters.

Where D1 still loses to Postgres

D1 has come a long way in three years and the team is shipping fast, but it isn't Postgres. Honest list:

The single-writer point is the one that bites at scale. Every INSERT/UPDATE/DELETE ultimately serialises against the primary. For an indie app's write rate (a handful of writes per second) this is invisible. For a write-heavy analytics ingest, it's the wrong database.

The 10 GB per-database ceiling looks small but is genuinely fine for "one product, one D1." If you outgrow it, the idiomatic move is to shard by tenant (one DB per workspace / per account), not to migrate.

The pros and cons cheat sheet

Pros

• Zero infrastructure surface. No connection pool, no driver, no failover plan. The Worker binding is the entire integration.
• Real SQL. Joins, subqueries, CTEs, window functions, FTS, real transactions inside batch. You don't have to give up SQL to live at the edge.
• Free tier you can ship on. 5M reads/day and 5 GB storage is enough for a real product, not a toy demo.
• Migrations baked in. wrangler d1 migrations is a serviceable schema-management story out of the box.
• Read replicas via Sessions API. Globally-distributed reads with bounded staleness, no replication topology to manage.

Cons

• Single writer per database. Write-heavy workloads need to shard or pick something else.
• 1 MB response cap. Project columns. Paginate everything. Don't SELECT * over wide rows.
• 10 GB per database. Plenty for one product, not for one shared multi-tenant database.
• No interactive transactions. Read-then-decide-then-write logic that needs to be atomic has to live inside a single batch (or move into a Durable Object).
• Postgres-isms you'll miss. JSONB indexing, pgvector, LISTEN/NOTIFY, materialised views. Each has a Cloudflare alternative, but they're not D1.

When to reach for D1

Use D1 when any of the following is true:

• You're running on Workers and want the lowest-friction relational store available.
• Your data is bounded by user / tenant — feature flags, user profiles, course progress, billing, audit log — and the per-DB 10 GB cap is comfortable.
• You want SQL semantics (joins, ACID batch, real WHERE clauses) without operating a Postgres cluster.

Use Postgres (Neon, Supabase, RDS, etc.) when any of the following is true:

• You need concurrent writers across multiple regions hitting the same dataset.
• Your largest table will exceed 10 GB and sharding by tenant isn't natural.
• You depend on JSONB-indexed queries, pgvector, or other Postgres-specific extensions.

For most products built on Cloudflare Workers, D1 is the right default for relational, metadata-shaped data, and R2 is the right default for bytes-shaped data. The next chapter covers KV, the third storage primitive — when KV beats D1, when KV is exactly wrong, and the 60-second consistency rule that decides which is which.