Chapter 13: D1: SQLite at the Edge
Can D1 replace my PostgreSQL/MySQL, and when should I use it?
D1 gives you managed SQLite databases built on Durable Objects. Each database has one write authority, so expensive queries and concentrated writes constrain that database’s capacity. Read replicas can move suitable reads closer to users. These properties explain when one database is enough and when independent datasets should be split.
D1 manages connection handling, replication and failover. Your team still owns schema design, query performance, recovery procedures and, if you partition, the database fleet. The decision is whether those responsibilities fit the workload better than operating or retaining PostgreSQL or MySQL.
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 reduces database infrastructure work; query design, migrations and recovery remain yours.
Choose Hyperdrive with external PostgreSQL when you need what D1 can't provide. If your largest logical data partition exceeds 10 GB and 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.
Compare database services by the capability you need to keep: engine semantics, maximum coherent dataset, cross-record transactions, placement, recovery and operational tooling. A minimum monthly price says little without the workload’s rows scanned, write rate and availability requirements.
D1 fits independent SQLite datasets with managed operations. Hyperdrive keeps an external engine and its ecosystem available to Workers. Branching or cloning can make a third-party database attractive for development, but D1 Time Travel is recovery of an existing database, not an interchangeable preview-branch feature.
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.
A globally replicated database must decide how concurrent writes agree. Systems make different choices about consensus, conflict resolution and where latency is paid. D1 uses a primary for writes and asynchronous replicas for eligible reads. That makes the primary's location and workload central to the design.
D1 sends writes for a database to one primary. That avoids the need to reconcile independently accepted writes from multiple primaries. Replication is still required for durability, and distant callers still pay to reach the write authority; read replicas reduce that journey for suitable reads.
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.
Queries outside the Sessions API go to the primary. Await a successful D1 write before triggering an external effect that depends on it. That ordering prevents acting on an unconfirmed write, but it does not make the database update and the external API call atomic; retries still need idempotency or a recorded pending action.
The 10 GB model
Each Paid D1 database has a 10 GB limit. Design against that limit without assuming it predicts every future service limit. More importantly, check whether the largest dataset that must be queried or updated together fits inside one database.
A single database has finite execution capacity as well as a storage limit. Splitting independent tenant datasets can isolate query load and recovery. It also creates a fleet to provision, migrate and monitor, so choose a boundary that earns that overhead.
A SaaS application with 10,000 independent tenants could use 10,000 databases averaging 10 MB each. Capacity planning must also respect account limits: Paid accounts allow 50,000 databases with 1 TB of total D1 storage. Multiplying the per-database maximum by the database count does not give usable account capacity.
Separate databases give tenants distinct query and recovery boundaries. They still share account limits and may depend on the same Workers, queues or external services. Authorise tenant-to-database routing so isolation is not defeated by selecting the wrong database. Use documented jurisdiction controls when location is a requirement; separate databases alone do not establish compliance.
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.
A shared database is sensible when the workload fits its size and throughput limits and shared queries are valuable. Partition when tenant isolation, independent recovery or measured capacity warrants the fleet overhead. Preserve a tenant key and clear data ownership early so a later split is feasible; hypothetical growth alone does not justify thousands of databases.
Partition the constraint you actually have
Suppose a shared database is approaching its storage limit and one tenant's expensive reports delay other queries. Moving independent tenants to separate databases can distribute that query load, but it will not help if the largest tenant alone exceeds the limit. First establish which constraint is binding: query shape, one tenant's dataset, or the combined workload.
An index or a separate analytical read model may remove report contention without a database fleet. A tenant split is useful when transactions remain inside tenant boundaries and independent recovery matters. If the domain needs large cross-tenant transactions, retaining an external database through Hyperdrive may be simpler. Make the choice from the operation that must remain coherent, and test the largest partition before multiplying it.
Geographic placement and global applications
D1 chooses an initial location near the request that creates the database. Supply a location hint when provisioning from a CI pipeline or another place unrelated to the users. A hint expresses a placement preference; a jurisdiction is the stronger control for restricting where the database runs and stores data.
Place a partition near the people who write it, then consider replicas for distributed reads. Database-per-tenant helps when each tenant has a geographic centre. It does not solve a shared write workload whose users span continents, and creating a database during signup does not guarantee permanent proximity to that user.
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 for writes. With read replication enabled, Cloudflare creates replicas in available regions, subject to jurisdiction restrictions. Your application opts into replica routing through the Sessions API; queries outside it continue to use the primary.
Replica reads trade freshness against travel and catch-up time. A session bookmark establishes how recent the result must be; it does not require every replica to be current before any read can proceed.
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();
A session carries a bookmark marking its progress. A replica serving the next query must be at least that current, waiting to catch up if necessary. The session therefore preserves sequential consistency while allowing replica reads.
Use withSession("first-unconstrained"), the default, when the first read may use any replica. Use withSession("first-primary") when the first query must start from the primary’s latest state. Later queries maintain the session’s progress. Ordinary non-session reads use the primary; they are not the stale-but-fast option.
A logical session can span HTTP requests: return session.getBookmark() and pass it to withSession(bookmark) on the next request. This preserves that caller’s progress. An unrelated caller without the bookmark is not guaranteed to observe the write through a replica immediately; use the primary or an appropriate coordination design when that freshness is required.
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. Paid plans include 25 billion rows read, 50 million rows written, and 5 GB of storage each month. Beyond those allowances, reads cost $0.001 per million rows, writes $1.00 per million, and storage $0.75 per GB-month. Query efficiency affects latency and capacity even when usage fits within the included allowance.
A concrete example: a SaaS application with 1,000 tenants, each averaging 10,000 rows, running 100 queries per day per tenant. At 20 rows read per indexed query, that is 60 million rows read in a 30-day month. Add 10,000 rows written daily and an assumed 2–3 GB of storage. All three fit within the Paid plan's included D1 allowances, provided other databases in the account have not consumed them. Worker invocations and CPU are billed separately.
If each query instead scans 10,000 rows, the same workload reads 30 billion rows monthly: 500 times more work, with $5 in read overages after the included allowance. The immediate reason to index is avoiding slow scans and contention on a single-threaded database. At larger volumes, the billing consequence follows. Measure rows read and query latency rather than assuming a low bill means an efficient database.
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.
A D1 batch executes sequentially as a transaction and rolls back if a statement fails. Separate Worker-to-D1 calls do not share that transaction. Express a check-and-update in one SQL statement or batch when possible; use idempotency for effects outside the database. Single-threaded execution does not protect an application decision split across network round trips.
Read-only queries are retried for you. The D1 client detects queries that only read, those containing solely SELECT, WITH, or EXPLAIN, and automatically attempts up to two retries when it hits a transient, retryable error, exposing the count in the total_attempts field of the response metadata. This removes a layer of boilerplate that cross-region reads would otherwise need. Writes are deliberately excluded because they cannot be replayed safely, so anything that mutates state still needs your own idempotency and retry handling.
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.
Database capabilities D1 does not expose
D1 does not expose every capability of an embedded SQLite database or a database server:
- No stored procedures
- No application-defined SQL functions, although embedded SQLite supports registering them
- 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 and independent backups
D1 Time Travel can restore a database within its retention window: 30 days on Paid plans and seven on Free. It is always enabled and has no separate recovery charge. Use it for a bad migration or accidental deletion, but decide what should happen to legitimate writes made after the restore point.
Restoring rewinds the database, not the rest of the business. A payment already captured, a message delivered or a file deleted from another store will not be undone with it. Recovery therefore needs reconciliation as well as a restore command.
Keep the pre-restore bookmark returned by the restore operation: within the retention window, it lets you return to the state before a mistaken restore. Rehearse that recovery before an incident. Check that the restored schema matches the deployed code, that background consumers will not repeat completed effects, and that users can resume safely. A bookmark is an opaque recovery identifier; do not build application logic around its format.
Time Travel is not a historical-query interface or an independent backup copy. For longer retention and recovery outside that window, use the supported D1 export tooling or export API and retain the result separately, for example in R2. Test importing it into a separate database. Backup success is evidence of a file; a restore drill is evidence of recoverability.
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 are enabled by default for D1 queries and migrations. 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.
During migration, keep one database authoritative and replicate changes to the other through a replayable path. Compare reads and track replication lag before shifting traffic. Independent dual writes can leave the stores divergent when only one succeeds; plan reconciliation and rollback before cutover. Hyperdrive can retain the legacy access path during that work.
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. For new applications, test D1 against the largest coherent partition and the required queries. For existing applications, require a concrete benefit before accepting the migration and operating cost.
What comes next
D1 keeps related rows queryable, but large files belong elsewhere. Chapter 14 covers R2: direct versus Worker-mediated access, object lifecycle, and the workloads where transfer charges dominate the storage decision.