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. Retain PostgreSQL when the application depends on features such as stored procedures, PostGIS or engine-specific query behaviour. Assess the connection path separately: Hyperdrive does not support LISTEN/NOTIFY or session-level advisory locks, although transaction-level advisory locks work inside explicit transactions. Some operations therefore need a direct connection or an existing backend service. If an existing PostgreSQL database works well and migration offers no compelling benefit, Hyperdrive lets you retain it while pooling connections and caching suitable reads. Measure that path before replacing the database. 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.
Each D1 primary and read replica executes SQLite work through its own single-threaded instance. Queries targeting one instance compete for that capacity; eligible reads can be distributed across replicas. As a simplified capacity illustration, an instance occupied for 10ms per query could complete about 100 such queries per second, while 100ms of work leaves room for about ten. Real throughput also includes routing and storage work. A slow query delays other queries directed to that instance.
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 1 million databases with 1 TB of total D1 storage. The first 100,000 databases are included; additional databases carry a monthly charge. Multiplying the per-database maximum by the database count does not give usable account capacity, and an allowed resource count is not an included billing allowance.
The fleet must also fit the serving path. A Worker's script-metadata limit permits roughly 5,000 D1 bindings before accounting for other configuration. One Worker cannot directly bind the entire example fleet. Partition authorised routing and serving Workers deliberately, and verify that design before provisioning the databases. Chapter 25 develops the tenant-routing boundary.
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. D1 does not execute a query across tenant databases. 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.
Cloudflare applies a useful separation inside D1 itself: central PostgreSQL manages database metadata, while bound queries reach the database without consulting that central store. Apply the corresponding design test to your fleet: can an established tenant keep serving requests while provisioning and administration are unavailable? Keep its authorised database route available to the serving path, and make creation, migration and deletion recoverable operations with explicit states.
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 read replication, in public beta, can distribute reads across regions, reducing the journey to a primary for suitable workloads. 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.
A nearby replica can avoid the network trip to a distant primary, but query work and replica catch-up still affect latency. Measure the complete read with its required freshness constraint. A dashboard and the response following a profile edit may need different initial session constraints.
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
Query performance affects the capacity of each D1 instance. Slow queries occupy its execution thread and delay work queued there, even when the account's read charges remain within the included allowance.
Measure the call from the Worker as well as SQL execution time. D1's queryBatchTimeMs metric excludes routing and waiting before SQL begins, and requests that fail before reaching SQL may be absent from database analytics. Fast queries and an empty activity chart do not rule out a platform-side delay. Check end-to-end errors and service status before treating every overload response as proof that the application needs more partitions.
Use EXPLAIN QUERY PLAN with representative data. A scan can be appropriate when the query needs much of a table; it is not automatically evidence of a missing index. Where a query examines many rows to return a few, test an index and compare latency, rows read, storage and write cost.
D1 charges for rows read, rows written, storage and databases above the included count. Paid plans include 25 billion rows read, 50 million rows written, 5 GB of storage and 100,000 databases. Beyond those allowances, reads cost $0.001 per million rows, writes $1.00 per million, storage $0.75 per GB-month and each additional database $0.10 per month, including idle or empty databases. Chapter 20 connects these meters to fleet costs. 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 combines statements into one request and transaction, reducing round trips. One batch() call counts once against the per-invocation query allowance, but each statement retains its own limits and the whole batch must fit the call's duration limit. A very large batch can still monopolise the database or time out.
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.
Bound result size independently of transaction size. D1 limits a query result to 50 MB; REST import and export operations are exempt. Select the required columns and paginate large application reads, using a stable ordering and an explicit policy for concurrent changes. Returning every row to the Worker is not an export strategy, even when the database itself fits within 10 GB.
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. SQLite's FTS5 extension indexes text for word and phrase searches, giving D1 a built-in option for keyword retrieval.
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.
SQLite has five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. Map PostgreSQL-specific types deliberately: UUIDs can use TEXT, timestamps TEXT or INTEGER, and JSON documents TEXT with JSON functions. Retain supported invariants in the database through STRICT tables and appropriate CHECK, UNIQUE and foreign-key constraints. Application validation complements rules the database enforces; it need not replace them.
Test values at the binding's type-conversion boundary. SQLite's 64-bit INTEGER range exceeds JavaScript Number's exact integer range, and the binding does not accept BigInt. Keep numeric operations within a proven safe range or use an explicit text or encoded representation for larger identifiers and exact quantities. Correct storage does not guarantee an exact round trip through application code.
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.
A database per tenant scales queries that identify the tenant. A support screen searching every tenant by customer email has a different growth pattern. As an illustrative workload, 2,000 databases can turn one search into 2,000 queries; ten searches per second become 20,000 underlying queries per second before retries. Every database participates, so adding tenants adds work to every search.
For infrequent administration, a bounded background search may be enough. For an interactive feature, consider a separate lookup projection containing only the fields needed to find the owning tenant. Restrict that lookup to authorised operators, since revealing which tenant holds a record can itself disclose information, then authorise the detail query at its owner. The projection needs freshness, deletion and rebuild rules. Analytical totals belong on the reporting path in Chapter 14; querying the whole fleet does not create one consistent snapshot.
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 accidentally deleted rows, but decide what should happen to legitimate writes made after the restore point.
Deleting the database resource has a different recovery boundary. The undelete API accepts requests only within seven days of deletion. After undeletion, Time Travel cannot restore to a bookmark or timestamp at or before that undeletion. A 30-day history therefore does not provide a 30-day deleted-database recovery promise; retain an independent export when the recovery requirement extends beyond those boundaries.
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 needs a deliberate mapping of types, constraints and generated identifiers. SQLite can generate row identifiers through INTEGER PRIMARY KEY; foreign keys are enabled by default for D1 queries and migrations. PostgreSQL-style sequences need a different design. Preserve the constraints SQLite can enforce, and move only unsupported rules into application logic. Stored procedures and pub/sub patterns using LISTEN/NOTIFY need separate solutions; neither is replaced by translating the table definitions.
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.