Every time you deploy an app to Cloudflare Workers and need a database, you hit the same wall: your Worker runs in 300 PoPs globally, your Postgres instance sits in us-east-1, and suddenly your "edge" app is making a 150ms round-trip to Virginia on every single query. You’ve replaced latency from the user’s connection with latency inside your own infrastructure. Congratulations, you’ve shuffled the problem.
D1 is Cloudflare’s answer to this. SQLite, globally replicated, accessed directly from your Worker runtime with zero connection pooling nonsense. No TCP connections, no PgBouncer, no /var/run/postgresql. Just SQL. This article walks through how it actually works under the hood, sets you up with a real project, covers the replication model honestly (including the rough edges), and shows you how to implement sharding when a single database won’t cut it anymore.
Official repo and Wrangler CLI (the tool you’ll use for everything D1): https://github.com/cloudflare/workers-sdk
Why SQLite and Not Something "Real"
The instinct to distrust SQLite for production is understandable. For years the rule was: SQLite is for embedded apps, test suites, and local dev. Don’t put it on a server. That rule made sense in 2010. It aged poorly.
SQLite is a single-file database with zero dependencies, no client-server protocol, and read throughput that embarrasses most managed Postgres instances on commodity hardware. The constraint was always writes — only one writer at a time, no network replication, no high availability. Those constraints still exist at the storage layer, but Cloudflare abstracts them away: they run the SQLite engine close to the user, replicate the data across their infrastructure, and expose it to your Worker as a simple binding. You’re not managing the file, the replication chain, or the failover. You write SQL, Cloudflare does the rest.
The architecture is similar in spirit to Litestream or LiteFS — SQLite with a replication layer bolted on — but run at Cloudflare’s scale across their global edge.
Setting Up Your First D1 Database
You need Wrangler installed. If you don’t have it:
npm install -g wrangler
wrangler login
Create the database:
wrangler d1 create my-app-db
Wrangler will spit out something like this:
✅ Successfully created DB 'my-app-db' in region WEUR (Western Europe)
[[d1_databases]]
binding = "DB"
database_name = "my-app-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
That [[d1_databases]] block goes directly into your wrangler.toml. The database_id is yours — keep it. Add it:
# wrangler.toml
name = "my-app"
main = "src/index.ts"
compatibility_date = "2024-09-23"
[[d1_databases]]
binding = "DB" # How you reference it in Worker code
database_name = "my-app-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
Now define your schema. D1 uses the SQLite dialect — same types, same functions, same quirks (and SQLite has quirks, we’ll get there).
-- schema/001_init.sql
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
tier TEXT NOT NULL DEFAULT 'free', -- 'free' | 'pro' | 'enterprise'
created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE TABLE IF NOT EXISTS events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id),
type TEXT NOT NULL,
payload TEXT, -- JSON blob
created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX IF NOT EXISTS idx_events_user_id ON events(user_id);
CREATE INDEX IF NOT EXISTS idx_events_type ON events(type);
Apply to remote (your actual D1 instance on Cloudflare):
wrangler d1 execute my-app-db --remote --file=schema/001_init.sql
Apply locally for dev (D1 runs a local SQLite instance via Wrangler):
wrangler d1 execute my-app-db --local --file=schema/001_init.sql
That --local flag is useful. Local dev uses a real SQLite file under .wrangler/state/, so you can iterate fast without hitting the network.
Querying D1 from a Worker
The D1 binding gives you a D1Database object on your env. The API is straightforward:
// src/index.ts
export interface Env {
DB: D1Database;
}
export default {
async fetch(request: Request, env: Env): Promise<Response> {
const url = new URL(request.url);
if (url.pathname === "/users" && request.method === "GET") {
return getUsers(env.DB);
}
if (url.pathname === "/users" && request.method === "POST") {
return createUser(request, env.DB);
}
return new Response("Not Found", { status: 404 });
},
};
async function getUsers(db: D1Database): Promise<Response> {
const { results } = await db
.prepare("SELECT id, email, name, tier FROM users ORDER BY created_at DESC LIMIT 50")
.all();
return Response.json(results);
}
async function createUser(request: Request, db: D1Database): Promise<Response> {
const body = await request.json<{ email: string; name: string }>();
if (!body.email || !body.name) {
return new Response("email and name required", { status: 400 });
}
const result = await db
.prepare("INSERT INTO users (email, name) VALUES (?1, ?2) RETURNING id")
.bind(body.email, body.name)
.first<{ id: number }>();
return Response.json({ id: result?.id }, { status: 201 });
}
A few things to note here:
- Use
?1,?2positional parameters (SQLite-style), not$1,$2. This trips people up coming from Postgres. .all()returns{ results, meta, success }.resultsis your rows array..first()returns the first row ornull..run()is for statements that don’t return rows (UPDATE, DELETE, INSERT without RETURNING).
Batching multiple statements in one round-trip:
// Execute multiple statements atomically — all succeed or all fail
const [usersResult, eventsResult] = await db.batch([
db.prepare("SELECT COUNT(*) as count FROM users"),
db.prepare("SELECT COUNT(*) as count FROM events"),
]);
const userCount = (usersResult.results[0] as { count: number }).count;
const eventCount = (eventsResult.results[0] as { count: number }).count;
db.batch() is not a transaction by default — statements run sequentially but independently unless you wrap them in explicit BEGIN/COMMIT. To use a real transaction:
await db.batch([
db.prepare("BEGIN"),
db.prepare("UPDATE users SET tier = ?1 WHERE id = ?2").bind("pro", userId),
db.prepare("INSERT INTO events (user_id, type) VALUES (?1, ?2)").bind(userId, "upgrade"),
db.prepare("COMMIT"),
]);
The Replication Model: What D1 Actually Does
This is the part most tutorials skip, and it’s the part that bites you in production.
D1 is not a distributed database in the CockroachDB sense. There is one primary node that accepts writes. Reads can be served from replicas close to the requesting Worker instance. Cloudflare replicates the SQLite write-ahead log to read replicas, so you get global read performance, but write throughput is ultimately bounded by a single writer.
Consistency modes: D1 exposes two read consistency modes via the Sessions API:
first-available— Cloudflare routes your read to the nearest replica. It may return data that’s a few milliseconds stale (replica hasn’t caught up yet). Fastest, but you can read your own writes out of order.strongest— Forces the read to go to the primary. You always get the latest data, but you pay the latency of routing to wherever the primary lives.
For most apps, first-available is fine for reads and you just have to be explicit about when you need strongest. The Sessions API gives you a token to control this:
export default {
async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
// Create a session — reuse across requests if you have a user session
const session = env.DB.withSession("first-available");
// After a write, use the returned bookmark to ensure subsequent reads
// from this session see your own write — even from a different replica
const writeResult = await session
.prepare("INSERT INTO users (email, name) VALUES (?1, ?2) RETURNING id")
.bind("[email protected]", "Alice")
.first<{ id: number }>();
// This read is guaranteed to see the insert above, because we're using
// the same session object — D1 tracks the replication bookmark internally
const user = await session
.prepare("SELECT * FROM users WHERE id = ?1")
.bind(writeResult?.id)
.first();
return Response.json(user);
},
};
The session maintains an internal bookmark (a WAL position). Even if the subsequent read gets routed to a different replica, D1 will wait until that replica has applied the WAL up to the bookmark before returning results. You get read-your-writes without forcing everything to the primary.
Write latency is the practical gotcha. If your Worker is running in Frankfurt and your D1 primary is in the US (which can happen — primary placement is automatic, not necessarily near you), writes will be slower than you expect. The dashboard shows you where your primary lives. You can’t force primary placement directly, but Cloudflare’s internal routing tries to place it near where writes originate most frequently.
Migrations Without a Framework
Production databases need versioned migrations. Don’t just run schema files ad-hoc. Wrangler supports migrations natively:
# Generate a new migration file
wrangler d1 migrations create my-app-db add_api_keys_table
This creates migrations/0002_add_api_keys_table.sql. Edit it:
-- migrations/0002_add_api_keys_table.sql
CREATE TABLE IF NOT EXISTS api_keys (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
key_hash TEXT NOT NULL UNIQUE, -- store a hash, never the raw key
label TEXT NOT NULL,
last_used INTEGER,
created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id);
Apply all pending migrations:
# Remote
wrangler d1 migrations apply my-app-db --remote
# Local
wrangler d1 migrations apply my-app-db --local
Wrangler tracks applied migrations in a d1_migrations table it creates automatically. It won’t re-apply migrations that have already run. Bake this into your deploy pipeline:
# In your CI/CD pipeline (GitHub Actions, etc.)
wrangler d1 migrations apply my-app-db --remote
wrangler deploy
Run migrations before deploying the Worker, not after. If you deploy a Worker that references a column that doesn’t exist yet, you’ll have downtime.
Sharding: When One Database Isn’t Enough
D1’s free tier caps at 5 million rows per database and 100k write rows per day. The paid tier is much more generous, but there’s a deeper reason you might want sharding: write throughput. A single D1 primary is a single SQLite writer. If you’re hammering it with thousands of writes per second, you’ll saturate it.
The solution is application-level sharding — multiple D1 databases, each owning a partition of your data. Cloudflare doesn’t do this for you. You do it in your Worker.
Here’s a practical tenant-based sharding implementation:
# wrangler.toml — multiple D1 databases bound to the same Worker
[[d1_databases]]
binding = "DB_SHARD_0"
database_name = "my-app-shard-0"
database_id = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
[[d1_databases]]
binding = "DB_SHARD_1"
database_name = "my-app-shard-1"
database_id = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb"
[[d1_databases]]
binding = "DB_SHARD_2"
database_name = "my-app-shard-2"
database_id = "cccccccc-cccc-cccc-cccc-cccccccccccc"
[[d1_databases]]
binding = "DB_SHARD_3"
database_name = "my-app-shard-3"
database_id = "dddddddd-dddd-dddd-dddd-dddddddddddd"
// src/sharding.ts
export interface Env {
DB_SHARD_0: D1Database;
DB_SHARD_1: D1Database;
DB_SHARD_2: D1Database;
DB_SHARD_3: D1Database;
}
const SHARD_COUNT = 4;
// Simple hash-based shard routing — deterministic by tenant ID
function getShardIndex(tenantId: string): number {
let hash = 0;
for (let i = 0; i < tenantId.length; i++) {
// djb2-style hash — fast, good distribution, no crypto overhead
hash = (hash << 5) - hash + tenantId.charCodeAt(i);
hash |= 0; // convert to 32-bit int
}
return Math.abs(hash) % SHARD_COUNT;
}
export function getShard(tenantId: string, env: Env): D1Database {
const index = getShardIndex(tenantId);
const shards: D1Database[] = [
env.DB_SHARD_0,
env.DB_SHARD_1,
env.DB_SHARD_2,
env.DB_SHARD_3,
];
return shards[index];
}
Usage in your Worker:
import { getShard, Env } from "./sharding";
export default {
async fetch(request: Request, env: Env): Promise<Response> {
const tenantId = request.headers.get("X-Tenant-ID");
if (!tenantId) return new Response("Missing tenant", { status: 400 });
const db = getShard(tenantId, env);
const users = await db
.prepare("SELECT * FROM users LIMIT 100")
.all();
return Response.json(users.results);
},
};
Each shard is a completely isolated SQLite database. Queries never cross shard boundaries. Cross-shard queries — like "give me all users across all tenants" — require you to fan out to every shard and merge results in the Worker. That’s fine for admin queries that run rarely; it’s a design smell if it’s on your hot path.
Don’t reshard lightly. Once you’ve hashed tenantId to shard 2, that tenant’s data lives on shard 2 forever — or until you build a migration tool to move rows between databases. Pick your shard key carefully. Tenant ID, user ID, region — all valid. Anything that changes over time is a trap.
Gotchas
SQLite type affinity will surprise you. SQLite doesn’t enforce column types strictly. INSERT INTO users (id) VALUES ('not-a-number') won’t throw an error if id is INTEGER. The value gets stored as text. This is SQLite’s "type affinity" system. Use STRICT tables (SQLite 3.37+) if you want proper enforcement — D1 supports this:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL
) STRICT;
No RETURNING on db.run(). If you use .run() for an INSERT but want the generated ID, you need .first() with RETURNING id appended to the query, or you query last_insert_rowid() in a follow-up. .run() gives you meta.last_row_id though — so result.meta.last_row_id works in practice.
Connections aren’t persistent. Each Worker invocation gets a fresh D1 binding. There’s no connection object to manage or pool, but it also means there’s no persistent server-side state. Don’t try to hold transactions across requests — they won’t survive.
Free tier writes are per-row, not per-query. A single INSERT that writes 1000 rows counts as 1000 write operations against your quota. Bulk inserts are efficient in terms of roundtrips but each row counts. Plan accordingly.
Local dev isn’t identical to remote. The local Wrangler SQLite instance runs the exact same SQLite version, but the replication behavior, primary/replica routing, and latency characteristics are all absent locally. Don’t assume your consistency assumptions hold locally — test against remote before ship.
D1’s batch() is ordered but not transactional by default. If statement 3 of 5 fails in a batch, statements 1 and 2 have already committed. If you need all-or-nothing, use explicit BEGIN/COMMIT as shown earlier.
Production Checklist
Before you ship:
-
Apply migrations in CI before deploying the Worker. Always. The schema the Worker expects must exist before the Worker starts serving traffic.
-
Store JSON in TEXT columns, not as a denormalized schema. SQLite’s JSON functions (
json_extract,json_each) are powerful and available in D1. Apayload TEXTcolumn with JSON is often better than adding 15 nullable columns for semi-structured data. -
Use
EXPLAIN QUERY PLANto verify your indexes are being hit:wrangler d1 execute my-app-db --local \ --command="EXPLAIN QUERY PLAN SELECT * FROM events WHERE user_id = 42"If you see
SCAN eventsinstead ofSEARCH events USING INDEX, add an index. -
Set
compatibility_dateinwrangler.tomlto a recent date. This controls which Workers runtime APIs you get. Stale compatibility dates can lock you out of D1 features. -
Use
ctx.waitUntil()for fire-and-forget analytics writes. Don’t block the response on low-priority inserts:ctx.waitUntil( env.DB.prepare("INSERT INTO events (user_id, type) VALUES (?1, ?2)") .bind(userId, "page_view") .run() ); return new Response("ok"); -
Monitor your D1 metrics in the Cloudflare dashboard — query count, read/write rows, storage. Set up budget alerts on your account before you forget.
D1 is a genuinely useful tool once you understand what it is: a globally replicated SQLite database with a single writer and read replicas served from Cloudflare’s edge. It’s not Spanner, it’s not Postgres, and trying to use it like one of those will frustrate you. Use it for what it’s good at — lightweight, consistent, globally fast reads with modest write loads — and it’ll serve you well without a single database server to maintain.
The sharding approach above scales further than most apps will ever need. Start with one database, shard when you have a real reason to, and keep your shard key stable. That’s the whole game.