Source: https://architectingoncloudflare.com/chapter-13 # 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. > **D1's Sweet Spot** > > 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 every PostgreSQL feature, including `LISTEN/NOTIFY` and advisory locks, so some operations need a direct connection or an existing backend service. 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 Is a Durable Object** > > 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 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. 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](https://blog.cloudflare.com/d1-control-plane/): 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. **Read-Your-Writes with Sessions** ```typescript 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 // highlight-next-line 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. > **Inspect the Work Behind a Query** > > 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 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. **Batched Queries** ```typescript 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. > **Keep the invariant inside the database** > > 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. 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. 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 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.