Chapter 12: D1: SQLite at the Edge
Can D1 replace my PostgreSQL/MySQL, and when should I use it?
D1 is a Durable Object you didn't have to build, and this single fact explains everything about the service: why databases limit at 10 GB, why query performance matters so acutely, and why horizontal scaling is the intended architecture rather than a limitation to work around. If you read Chapter 6 and understood Durable Objects, you already understand D1's operational model: D1 databases are single-threaded actors running in one location, strongly consistent, with writes replicated for durability before external effects become visible.
This architecture makes D1 radically simpler to operate than traditional databases. No connection pools to tune, no read replicas to provision, no failover to configure. But simplicity comes with constraints that differ fundamentally from PostgreSQL or MySQL. The question isn't whether D1 is "good enough" to replace your existing database. The question is whether your workload fits D1's model. If it does, D1 offers something no traditional database can: a globally distributed relational store with zero operational overhead.
The decision: D1, Hyperdrive, or something else
Before exploring how D1 works, establish whether it fits your needs. Decide early to focus on your application rather than working around limitations.
Choose D1 when your data has natural boundaries. Multi-tenant SaaS where each customer's data is independent. Consumer applications where each user has their own dataset. Microservices where each service owns its domain. The multi-database model D1 encourages aligns with how you'd structure these applications anyway. D1 shines when you're building new on Cloudflare and want zero database operations.
Choose Hyperdrive with external PostgreSQL when you need what D1 can't provide. If your largest logical data partition exceeds 10 GB and genuinely cannot be subdivided, you need a larger database. If you depend on PostgreSQL-specific features such as stored procedures, LISTEN/NOTIFY, advanced JSON operators, or PostGIS, D1's SQLite foundation won't provide them. If you have an existing PostgreSQL database that works well and migration offers no compelling benefit, Hyperdrive lets you keep it while dramatically reducing edge-to-database latency. The question isn't how to migrate; it's whether to migrate at all. Hyperdrive to existing PostgreSQL is a valid long-term architecture, not a temporary crutch.
Choose something else entirely when relational queries aren't the right model. Analytics workloads spanning hundreds of gigabytes belong in a purpose-built analytics store. Document storage with infrequent queries belongs in R2. High-frequency counters and coordination belong in Durable Objects directly. D1 is a relational database; use it for relational problems.
The serverless database landscape evolves rapidly. Cloudflare's current position, SQLite-based D1 for native workloads and Hyperdrive for PostgreSQL and MySQL connectivity, may not be permanent. A native serverless PostgreSQL offering would fill an obvious gap. Until then, developers requiring serverless PostgreSQL can consider alternatives that pair well with Workers through Hyperdrive: Neon for PostgreSQL with branching capabilities, PlanetScale for MySQL with horizontal scaling, Supabase for PostgreSQL with integrated auth and storage, CockroachDB Serverless for distributed SQL with strong consistency. Hyperdrive accelerates connections to all of them, making the choice about database capabilities rather than edge latency.
Serverless database comparison
For teams evaluating D1 against hyperscaler and third-party alternatives, the differences extend beyond features to fundamental architecture.
| Aspect | D1 | Aurora Serverless v2 | Neon | PlanetScale |
|---|---|---|---|---|
| Engine | SQLite | PostgreSQL/MySQL | PostgreSQL | MySQL (Vitess) |
| Max database size | 10 GB | 128 TB | 50GB (free), unlimited (paid) | Unlimited |
| Cold start | None (always warm) | 500ms-15s | 500ms-2s | Near-zero (Metal) |
| Scale to zero | Yes (no compute cost) | Yes (since late 2024) | Yes | No |
| Geographic distribution | Single location + read replicas | Single region | Single region | Single region |
| Connection pooling | Built-in | RDS Proxy (additional cost) | Built-in (PgBouncer) | Built-in |
| Branching/cloning | Time Travel (restore) | Cloning (slow for large DBs) | Instant branching | Branching |
| Pricing model | Rows read/written | ACU-hours | Compute units | Rows read/written |
| Approximate cost (light workload) | $5-10/month | $50-100/month minimum | $19/month (Launch) | $29/month (Scaler) |
The architecture distinction that matters: D1's single-location Durable Object model with read replicas differs fundamentally from Aurora's regional architecture with optional global databases. Aurora provides a larger, more feature-complete PostgreSQL/MySQL, but at higher cost and without true edge distribution. Neon and PlanetScale offer modern developer experience with branching workflows, but remain regionally deployed.
When Aurora wins: Large datasets exceeding 10 GB that cannot be partitioned. Complex PostgreSQL-specific features (PostGIS, advanced JSON, full-text search with language support). Existing AWS infrastructure with IAM, CloudWatch, and established operational practices.
When D1 wins: Multi-tenant workloads where database-per-tenant is natural. Applications where zero cold starts matter for user experience. Cost-sensitive projects where pay-per-query beats minimum monthly spend. Teams wanting zero database operations.
When Neon wins: PostgreSQL compatibility is non-negotiable but you want modern developer workflows. Instant database branching for preview environments and testing. Teams comfortable with eventually consistent edge caching via Hyperdrive.
The durable object foundation
D1 databases run as Durable Objects within Cloudflare's infrastructure. This fundamental architecture determines D1's behaviour, constraints, and optimal usage patterns.
The naive approach to building an edge database: put replicas at every edge location. Writes propagate globally; reads are local. This is KV's model, and it works for key-value data where last-write-wins is acceptable. But for relational databases, global replication creates intractable problems. Two users in different regions update the same row simultaneously. Both writes succeed locally, then propagate globally. Now you have conflicting versions. Resolving these conflicts requires complex algorithms; CRDTs, vector clocks, application-level merge logic. Some databases attempt this; the complexity is immense, and operations like auto-increment or unique constraints become nearly impossible to implement correctly.
Cloudflare's solution is not to distribute writes. Each D1 database is a single Durable Object in a single location. All writes route to that location. This eliminates distributed consensus entirely because there is no replication conflict. Strong consistency is trivial because all operations serialise through one actor. The trade-off is latency for users distant from the database location, which read replicas address.
This architecture shapes how you should think about D1. Each database is a single actor with one location and one thread, so your query routes to wherever that database lives, executes against SQLite, and returns. Queries execute sequentially, not in parallel; a database processing 10ms queries can handle roughly 100 queries per second, while one processing 100ms queries can handle roughly 10 per second. Slow queries don't just delay individual requests; they queue everything behind them. Every millisecond costs throughput.
Consistency is strong by default, meaning D1 reads from the primary always reflect the latest committed write. Durability follows the Durable Object model: writes replicate to multiple locations before the response returns. The output gating mechanism from Chapter 6 applies here too. If your Worker writes to D1 and then makes an external API call, that call blocks until the D1 write is confirmed durable, so you cannot observe a state where the external call succeeded but the database write was lost.
The 10 GB model
Each D1 database has a hard 10 GB limit, and this constraint is architectural, not temporary. Fighting it means fighting the design.
The limit exists because D1 databases are single-location actors. A database approaching 10 GB has longer query times, slower backups, more complex recovery. More fundamentally, a single actor can only scale so far. Rather than allowing databases to grow until they become operationally problematic, Cloudflare constrains size and expects horizontal scaling. Many small databases isn't a workaround; it's the design.
Horizontal scaling means many small databases rather than one large. A SaaS application with 10,000 customers doesn't have one 100 GB database; it has 10,000 databases averaging 10 MB each. A consumer application with a million users doesn't struggle with database size: each user's data lives in their own database, and most users have far less than 10 GB of personal data. Paid accounts can create 50,000 databases. At 10 GB each, that's 500 TB of addressable storage. The scale is available; the model is different.
This model provides benefits beyond satisfying Cloudflare's architectural constraints. Tenant isolation becomes complete: one customer's runaway queries cannot affect another's performance. Compliance boundaries are natural: a European customer's data can live in a European database with no possibility of commingling. Backup and recovery operate per-tenant.
The model creates challenges too. Cross-tenant queries are impossible by design. To answer "how many total orders did all customers place yesterday," you'll query each database and aggregate in application code, or maintain a separate analytics store. Schema migrations must apply to all databases, requiring tooling to iterate through thousands. The multi-database model trades database complexity for application complexity. You're managing a fleet, not one database.
When should you use a single database despite this philosophy? Small internal tools with predictable, limited data are fine as single databases. Fewer than a hundred users, total data under a gigabyte, no tenant isolation requirements, no trajectory suggesting significant growth; the overhead of multi-database architecture isn't justified. But if any of those conditions might change, start with multiple databases. Retrofitting multi-database architecture onto a monolithic database requires significant effort; designing for it from the start is straightforward.
The monolithic database trap
A team building a SaaS application creates one D1 database for all customers. Single schema, single connection, straightforward queries. Early on, everything works.
As the product grows, the database approaches 10 GB. Query latency increases as tables grow. A slow query from one customer blocks all customers; the single-threaded model means everyone waits. The team considers aggressive archiving, more indexes, query optimisation. They're fighting the architecture.
The fix: embrace the model. One database per customer. Each customer's data lives in isolation. A customer with 50 MB and a customer with 500 MB operate independently. One customer's slow query cannot affect another. The 10 GB limit becomes irrelevant because no single customer approaches it. Migration requires effort: extracting data, partitioning by tenant, updating routing. Teams who start with the multi-database model avoid this entirely. The 10 GB limit isn't a constraint to optimise around; it's a design signal pointing toward horizontal architecture.
Geographic placement and global applications
D1 databases are created in a region based on where the creating request originates. Provision a database from a Worker in London and it's placed in or near Europe. This automatic placement works well when provisioning location matches users' locations, but it can create problems when it doesn't.
The most common mistake: provisioning all databases from a CI/CD pipeline in a single region. Your deployment runs in us-east-1, creates databases for customers worldwide, and every database ends up in North America regardless of where customers actually are. European customers experience 100-150ms latency on every query instead of the 10-20ms they'd see from a nearby database.
For globally distributed applications, two architectural options exist. Regional provisioning: create databases from Workers in representative regions, routing creation requests to edge locations near intended users. A European customer signs up, their account creation runs on a European edge node, and their database is placed in Europe. This requires coordination between signup flow and provisioning but ensures databases live near users.
The second option is database-per-user taken to its logical conclusion. When each user has their own database created during their first interaction, the database naturally places itself near them. A user in Tokyo makes their first request, it runs on a Tokyo edge node, creates the database, and the database lives in Asia. Subsequent requests find their database nearby. This requires embracing fine-grained multi-tenancy; not just database-per-customer but database-per-user; but it solves geographic distribution elegantly.
Neither approach works well for applications with a single shared database. A monolithic database must live somewhere, and that somewhere will be far from most global users. The multi-database model allows data to live near the users who access it.
Read replication and the consistency trade-off
D1 can replicate reads to edge locations, reducing latency for read-heavy workloads with globally distributed users. Understanding when to use replicas, and when to avoid them, is essential for correct behaviour.
Each D1 database has a primary location where all writes occur. Cloudflare automatically creates read replicas in other regions based on query traffic; you don't provision them explicitly. Read queries can be served from a nearby replica rather than routing to the primary. Replicas are eventually consistent: writes take time to propagate, typically under a second but not instantaneous and not guaranteed.
Replicas buy latency at the cost of certainty. For most reads, that's a good trade. For reads following writes, it isn't.
The practical difference is substantial. A read from a nearby replica typically completes in 5-20ms. A read from a distant primary might take 50-200ms, dominated by network latency. For a dashboard displaying data that needn't reflect the last few seconds, replica reads provide dramatically better user experience. For a page displaying a user's profile immediately after they've edited it, replica reads might show stale data.
The Sessions API solves this by ensuring reads within a session reflect all writes within that session.
const session = env.DB.withSession();
// Writes and reads in the same session are consistent
await session.prepare(
"UPDATE users SET name = ? WHERE id = ?"
).bind(newName, userId).run();
// This read will see the write above
const user = await session.prepare(
"SELECT * FROM users WHERE id = ?"
).bind(userId).first();
Under the hood, session reads route to the primary or a replica known to have received the session's writes. The mechanism isn't documented, but the guarantee is clear: reads within a session are consistent with writes within that session. The trade-off: session reads may route to distant locations to maintain consistency.
The decision framework is straightforward. Use sessions when a write is followed by an immediate read, when users expect to see their own changes, or when correctness requires the latest data. Use non-session reads when staleness of a few seconds is acceptable, when displaying infrequently changing data, or for analytics, reporting, and background processing. Default to sessions for authenticated user requests; use non-session reads for read-only dashboards and background jobs.
Cross-request staleness is subtler. Sessions don't solve it. User A writes data. User B, in a different region, reads immediately and sees old data because B's read went to a replica that hasn't received A's write. This isn't a bug; it's eventual consistency working as designed. If your application assumes all users see consistent data; collaborative editing, real-time dashboards; you need the primary for all reads or a different architecture: Durable Objects for real-time coordination with D1 as the persistence layer.
Query performance and cost
D1's single-threaded execution makes query performance existentially important. Slow queries don't just slow individual requests; they bottleneck the entire database, affecting every request waiting in the queue.
Indexing is your primary lever. Without indexes, queries scan entire tables. With proper indexes, queries find data directly. The difference can be 100x: a 500ms table scan versus a 5ms index lookup. Use EXPLAIN QUERY PLAN to verify your queries use indexes. If you see "SCAN TABLE" instead of "SEARCH TABLE USING INDEX," add an index. For a database receiving meaningful traffic, unindexed queries aren't merely slow; they're a scaling ceiling.
D1 charges based on rows read, rows written, and storage. As of early 2026, pricing is approximately $0.001 per million rows read and $1.00 per million rows written, with storage at $0.75 per GB per month after the free tier. Numbers will change; the principle won't. Query efficiency directly affects cost; a query scanning a million rows to find ten matches costs far more than a query using an index to find those ten directly.
A concrete example: a SaaS application with 1,000 tenants, each averaging 10,000 rows, running 100 queries per day per tenant. With proper indexes, each query reads 10-50 rows on average. That's roughly 100,000 queries per day reading 2 million rows total; about $0.06 per day, or roughly $2 per month. Add writes for user activity, perhaps 10,000 rows written per day, adding $0.30 per month. Storage for 10 million rows might be 2-3 GB, adding $1.50-2.25 per month. Total: roughly $4-5 per month for a thousand-tenant application with moderate activity.
Now consider the same application with poor indexing. Queries scan entire tables. Each query reads 10,000 rows instead of 50. Read costs increase 200x. That $2 per month becomes $400; a meaningful difference that compounds as you scale. The application with good indexes scales to 10,000 tenants for $40-50 per month; the application with poor indexes pays $4,000 per month. Index your tables.
Batching provides both performance and transactional benefits. Multiple queries in a batch execute atomically with lower total latency than executing each separately.
const results = await env.DB.batch([
env.DB.prepare("SELECT * FROM users WHERE id = ?").bind(userId),
env.DB.prepare("SELECT * FROM orders WHERE user_id = ?").bind(userId),
env.DB.prepare("SELECT * FROM preferences WHERE user_id = ?").bind(userId)
]);
D1 doesn't expose explicit transaction control beyond batching. If multiple operations must succeed or fail together, batch them. Design around this constraint.
D1's concurrency model is optimistic: it allows concurrent operations and detects conflicts at commit time rather than preventing them upfront. If two Workers execute conflicting operations simultaneously, one succeeds and the other may fail with a conflict error. This contrasts with Durable Objects' conservative concurrency, where single-threaded execution prevents conflicts by making concurrent access impossible. Optimistic concurrency provides higher throughput when conflicts are rare but requires retry logic when they occur. For frequent write contention on the same rows, either add retry handling or consider Durable Objects where serialisation eliminates conflicts entirely.
The SQLite foundation
D1 runs actual SQLite; the same database engine in every smartphone and browser. Not a SQLite-compatible reimplementation; SQLite itself, with the same query syntax, limitations, and decades of battle-tested reliability.
Standard SQL works as expected. Joins, subqueries, common table expressions, window functions: SQLite supports them, so D1 supports them. FTS5 provides full-text search without external services.
SQLite Features D1 Lacks
Certain features don't exist because SQLite doesn't have them:
- No stored procedures or triggers
- No user-defined functions beyond SQLite's built-ins
- No materialised views
- No LISTEN/NOTIFY or pub/sub patterns
If your application depends on these, D1 requires reworking that logic into Worker code. Moving logic from database to application often improves testability and portability, but it's work you should account for.
Data types are simpler than PostgreSQL. SQLite has five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. Most PostgreSQL types map naturally, but you lose some precision and validation. UUID columns become TEXT. TIMESTAMP becomes TEXT or INTEGER. JSONB becomes TEXT with JSON functions for querying. Application code must compensate for validation PostgreSQL would have enforced.
Multi-database operations
Managing a fleet of databases requires tooling a single database doesn't. Schema migrations, lifecycle management, and cross-database queries all become application concerns.
Schema migrations must apply to every database. Adding a column or creating an index must propagate to all 10,000 tenant databases. The naive approach; iterate through databases and apply changes; fails at scale because some migrations fail due to unanticipated data constraints. Robust migration tooling tracks which databases have migrated, handles failures gracefully with retry logic, and provides visibility into migration state across the fleet. Build this tooling before you need it.
Lifecycle management means knowing which databases exist, which tenants they serve, and their state. A tenant signs up; you create their database. They churn; you should delete it but might forget. They return; you might create a new database instead of reusing the old one. Orphaned databases accumulate. Robust lifecycle management requires explicit tracking: a registry of databases and tenants, automated cleanup for churned tenants, reactivation logic for returning ones.
Cross-database queries don't exist. For aggregate data across tenants; total orders yesterday, active users this month, revenue by region; either iterate through databases and aggregate in application code (slower as you add databases) or maintain a separate analytics store that receives events from all tenants. The second option adds complexity but scales better and separates operational queries from analytical ones.
Time travel: point-in-time recovery without backups
Traditional database backups are scheduled snapshots. If your backup runs nightly and disaster strikes at 5pm, you lose a day of data. More sophisticated setups reduce this window but never eliminate it.
D1's Time Travel eliminates this gap entirely. Restore any database to any minute within the last 30 days on paid plans (7 days on free). Not the last backup; any minute. Bad deployment corrupts data at 2:47pm? Restore to 2:46pm. Someone runs DELETE without a WHERE clause? Rewind to before the mistake.
Time Travel is always on. You don't enable, schedule, or configure it. Every write to every D1 database is automatically captured in a way that allows reconstruction to any point in time. Cost: zero; no additional storage charges, no restore fees.
How time travel works
D1 stores a write-ahead log (WAL) of all changes alongside your database, recording every modification before it's applied. Recovery works by replaying changes. Changes are indexed by bookmarks; opaque identifiers corresponding to specific points in time. Restoration reconstructs database state by replaying the log up to the specified bookmark.
Bookmarks have useful properties: lexicographically sortable (sorting orders them chronologically), deterministically derived from timestamps (the same timestamp always produces the same bookmark), and they survive restores (bookmarks from before a restore remain valid after).
Query current bookmarks through Wrangler:
# Get the current bookmark
wrangler d1 time-travel info YOUR_DATABASE
# Get the bookmark for a specific timestamp
wrangler d1 time-travel info YOUR_DATABASE --timestamp="2024-01-15T14:30:00Z"
Restoring a database
Restoration is a single command:
wrangler d1 time-travel restore YOUR_DATABASE --timestamp="2024-01-15T14:30:00Z"
The command converts timestamp to bookmark, confirms you want to proceed (restoration overwrites current state), and performs the restore. Completion takes seconds to minutes depending on database size and change history to replay.
Crucially, restoration preserves pre-restore state as a bookmark. Restore to 2:46pm and realise you needed 2:44pm? Restore again. Realise the original state was fine? Restore to the bookmark the previous restore gave you. Time Travel makes recovery from recovery mistakes possible.
# Initial restore
wrangler d1 time-travel restore YOUR_DATABASE --timestamp="2024-01-15T14:46:00"
# Output: ↩️ To undo this operation, restore to bookmark: 00000085-ffffffff-00004c6d-...
# Oops, went too far back. Restore to just before the first restore
wrangler d1 time-travel restore YOUR_DATABASE --bookmark="00000085-ffffffff-00004c6d-..."
Operational patterns
Time Travel changes how you approach database operations.
Failed migrations become recoverable. Run a migration, discover it broke something, restore to before the migration. Test your fix, try again. Feedback loop: minutes, not hours.
Data corruption investigations become tractable. Restore to various points in time to identify when corruption began. Binary search through time to find the exact minute.
Compliance queries about historical state become answerable. What did this record contain last Tuesday? Restore to last Tuesday (in a test environment), query, restore back.
For retention beyond 30 days, compliance requirements, or independent backup copies, D1 provides export capabilities. Export to R2 using Workflows for automated, scheduled backups that persist indefinitely:
export class DatabaseBackup extends WorkflowEntrypoint {
async run(event: WorkflowEvent) {
const timestamp = new Date().toISOString();
await this.step.do('export-database', async () => {
const dump = await this.env.DB.dump();
await this.env.BACKUPS.put(
`db-backup-${timestamp}.sql`,
dump
);
});
}
}
Time Travel handles operational recovery; R2 handles long-term archival. Use both.
Migration considerations
Moving from PostgreSQL or MySQL to D1 is an architectural change, not just a database swap. Evaluate whether it's worthwhile before investing.
Schema translation is usually straightforward. SQLite's type system is simpler; most types map naturally, though you lose some validation. Auto-increment uses INTEGER PRIMARY KEY. Foreign keys work but must be explicitly enabled. Sequences don't exist; use auto-increment or generate identifiers in application code. The mechanical translation matters less than the architectural shift: logic from stored procedures moves to Workers, validation from database constraints moves to application code, and pub/sub patterns using LISTEN/NOTIFY need different solutions.
Data migration for small datasets can use SQL export and import. For larger datasets, stream through Workers in batches to avoid memory limits. Strategy matters more than specific tooling: move data incrementally, verify consistency, maintain the ability to roll back.
Running databases in parallel during migration provides safety. Write to both, read from legacy, gradually shift reads to D1, compare results, complete cutover when confident. Hyperdrive can accelerate legacy database access during transition, reducing the performance penalty of maintaining two data paths.
The more important question: whether to migrate at all. If your existing database works well and Hyperdrive provides acceptable latency, migration may not be worth the effort. If you're building new features that would benefit from D1's model, migrating those while leaving legacy data in place may be the pragmatic middle path. New applications should default to D1; existing applications with working databases should evaluate migration skeptically.
What comes next
D1 handles structured data with relational queries. The next chapters complete the data layer picture: R2 for object storage, KV for caching, Hyperdrive for accelerating access to external databases.
The key insight from this chapter: D1 is a Durable Object with a SQL interface. That architecture determines everything; its strengths, constraints, and intended usage patterns. If the 10 GB limit feels constraining, consider whether the multi-database model might be a better fit. Many small databases isn't a workaround; it's the design.