D1 Implementation for Multi-Tenant SaaS: A Production Guide

Written by Huifer, solo developer and maintainer of TanStack Ship. I shipped D1 as the primary store for three multi-tenant SaaS products — a B2B analytics tool serving 480 tenants at 140k requests/day, a usage-based billing platform, and an internal developer dashboard. The patterns below are what survived paying customers, not what looked clean on day one.

Verified sources: Cloudflare D1 documentation · D1 best practices · D1 query API reference · SQLite query planner · Cloudflare Queues · TanStack Ship D1 reference repo · D1 pricing and limits · D1 migrations

Last updated: 2026-08-23 · Changelog

TL;DR: For most multi-tenant SaaS products on Cloudflare D1, the right pattern is shared database, shared schema, tenant_id column on every table — enforced at the query layer by a typed helper that injects the predicate automatically. This gives you operational simplicity (one schema, one migration run, one read-replica topology) without sacrificing tenant isolation when you combine it with composite indexes starting with tenant_id. The two things the docs undersell are the write-throughput ceiling (~250 req/s for sustained writes on the hot path before SQLite's global write lock queues up) and the migration cost (every column added on a 50-million-row events table takes minutes of D1 write suspension). Plan for both before you sign your first enterprise customer.

The Three Multi-Tenancy Patterns on D1

There are three viable shapes for a multi-tenant SaaS on D1. Each has a real cost. I will name them and tell you which one I ship by default.

Shared Database, Shared Schema with tenant_id

Every table has a tenant_id column. Every query filters by tenant_id. The application layer is responsible for never leaking data across tenants — but a typed query helper makes this a compile-time discipline rather than a runtime check.

When to use it: Default choice for B2B SaaS with under ~5,000 tenants and per-tenant data that fits in a single schema. This is the pattern I ship in TanStack Ship's starter. (Cloudflare D1 best practices treats this as the recommended starting point.)

Shared Database, Schema-Per-Tenant

One D1 database, but each tenant gets its own SQLite schema (tenant_acme.users, tenant_globex.users, …). Migration tooling fans out to every schema on deploy.

When to use it: Rare on D1. The operational overhead — N migrations per release, schema-discovery at runtime, larger-than-necessary SQL surface — outweighs the isolation benefit for almost everyone. I tried this once on a tenant onboarding prototype and rolled back within a week.

Database-Per-Tenant

Each tenant gets their own D1 database bound to the Worker via dynamic dispatch (Cloudflare Workers bindings).

When to use it: Large enterprise tenants with contractual data-isolation requirements, or tenants whose workloads would each individually exceed the per-database write ceiling. This is real, but it costs you: per-tenant migration orchestration, per-tenant analytics rollups, and a routing layer in front of every request.

For 95% of multi-tenant SaaS products, the first pattern is right. The rest of this article assumes that pattern.

The Schema: tenant_id First, Always

The single most important rule: every tenant-scoped table has tenant_id as the first column of every composite index. Not the second column. Not "also indexed." The first column. SQLite's query planner can only use one index per table reference, and if tenant_id is not the leading column of that index, your "tenant-scoped" query silently degrades to a full table scan.

-- Tenant table — never tenant-scoped itself
CREATE TABLE tenants (
  id          TEXT PRIMARY KEY,           -- ULID, e.g. 01HZX5K4...
  slug        TEXT NOT NULL UNIQUE,        -- 'acme', 'globex'
  plan        TEXT NOT NULL DEFAULT 'free', -- 'free' | 'pro' | 'enterprise'
  created_at  INTEGER NOT NULL DEFAULT (unixepoch())
);

-- A typical tenant-scoped table
CREATE TABLE projects (
  id          TEXT PRIMARY KEY,
  tenant_id   TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  created_at  INTEGER NOT NULL DEFAULT (unixepoch())
);

-- The composite index that makes tenant-scoped queries fast
CREATE INDEX idx_projects_tenant_created
  ON projects(tenant_id, created_at DESC);

-- The query the index is designed to serve
SELECT id, name FROM projects
WHERE tenant_id = ?
ORDER BY created_at DESC
LIMIT 50;

The tenant_id, created_at DESC composite is also a covering index for that exact query — SQLite returns the rows already sorted, with no separate sort step. This is the same index shape I documented in the D1 advanced optimization guide, and it is the single biggest performance lever you have on a multi-tenant schema.

A Query Helper That Won't Let You Forget

The most common cross-tenant leak in a multi-tenant app is a developer writing db.prepare("SELECT * FROM projects WHERE id = ?") without the tenant_id = ? predicate. The fix is a typed wrapper that requires the tenant context at the type level. Here is the shape I use in TypeScript on Cloudflare Workers:

// src/server/db/tenant-query.ts
import type { D1Database } from "@cloudflare/workers-types";

export type TenantContext = { tenantId: string };

export function tenantDb(db: D1Database, ctx: TenantContext) {
  return {
    async getProject(id: string) {
      return db
        .prepare("SELECT * FROM projects WHERE id = ? AND tenant_id = ?")
        .bind(id, ctx.tenantId)
        .first();
    },
    async listProjects(limit = 50) {
      return db
        .prepare(
          "SELECT * FROM projects WHERE tenant_id = ? ORDER BY created_at DESC LIMIT ?"
        )
        .bind(ctx.tenantId, limit)
        .all();
    },
    async createProject(name: string) {
      return db
        .prepare(
          "INSERT INTO projects (id, tenant_id, name) VALUES (?, ?, ?)"
        )
        .bind(crypto.randomUUID(), ctx.tenantId, name)
        .run();
    },
  };
}

This is not a perfect guarantee — a developer can still escape the wrapper and call db.prepare directly. But it puts a wall around the common path. The fix to the residual risk is integration tests, not more abstraction.

The Write-Throughput Ceiling

This is the part of D1 multi-tenancy that surprises everyone.

D1 is built on SQLite. SQLite has a global write lock per database — only one writer at a time, period. (SQLite locking docs.) D1 hides most of the latency cost of this with batching and a fast WAL-mode implementation, but the lock is still global.

In my production deployment of the B2B analytics tool, organic write load averages 45 req/s across 480 tenants. At that rate the lock queue stays at depth 0–2 and p99 write latency is sub-10ms. The first time I saw p99 climb to 8,000ms was at ~250 req/s sustained, when a billing webhook retry storm doubled effective load. I wrote up the full incident in D1 Write Lock Contention at 200 req/s; the short version is that one hot path did four sequential writes per request and SQLite serialized them all.

The ceiling is not a hard number — D1's storage layer is fast, and the Workers runtime has a 30-second CPU budget per request — but plan for ~200–300 req/s sustained writes as your headroom, not 10,000. If your roadmap has you above that, you need one of three mitigations:

1. Batched writes via db.batch() — group related writes inside a single transaction. The lock is held for one batch, not N writes. (D1 query API reference)
2. Offload the hot path to Cloudflare Queues — webhook handlers enqueue work, a queue consumer drains it at a rate D1 can sustain. (Cloudflare Queues docs.)
3. Detect hot tenants and shard — one tenant's analytics ingestion drowning the lock is not "D1 scaling problems," it is "this tenant needs their own database."

I ship options 1 and 2 by default in TanStack Ship's billing and event-collection modules. Option 3 is a manual call when a single tenant's load exceeds ~30% of your database's write ceiling.

Read Replicas, Hot Reads, and the resolve Hint

D1 provides read replication across regions via the D1 read replication API. For multi-tenant SaaS, this matters for two reasons:

• A tenant in Singapore reading their dashboard should hit a replica near them, not the primary in enam.
• Hot tenants — the ones whose dashboards account for 40% of read traffic — should not starve smaller tenants for replica capacity.

The default db.prepare(...).first() call routes to the nearest replica. The first() method accepts a resolve option to force routing to the primary when you need read-your-writes consistency:

// After a write, force the next read to hit the primary
await db
  .prepare("INSERT INTO projects (id, tenant_id, name) VALUES (?, ?, ?)")
  .bind(crypto.randomUUID(), ctx.tenantId, name)
  .run();

const fresh = await db
  .prepare("SELECT * FROM projects WHERE id = ?")
  .bind(newId)
  .first({ resolve: "primary" }); // bypass replica, see our own write

Use this sparingly. Every read forced to the primary is a read that hits the same write-locked region; if you scatter resolve: "primary" through your codebase, you lose the latency win the replicas give you. The right places are: (a) immediately after a write on the same request, and (b) dashboard widgets that show "just created" indicators.

Migrations Across a Million-Row Tenant Table

D1 migrations run via Wrangler's d1 migrations command. Each migration is one or more SQL files applied in order. For multi-tenant SaaS, two migration patterns bite people:

Adding a NOT NULL Column With a Default

SQLite supports adding a column with a constant default in O(1) — it does not rewrite the table. The migration is fast even on a 50-million-row events table:

-- Safe even on huge tables — SQLite stores the default in metadata
ALTER TABLE events ADD COLUMN source TEXT NOT NULL DEFAULT 'web';

Adding a NOT NULL Column Without a Default

This requires a full table rewrite. On D1, this means minutes of write suspension and a non-trivial billable row scan. Do not do this on a live tenant table. The pattern I use:

1. Add the column as nullable in migration N.
2. Backfill in a Cloudflare Workflow that processes rows in batches of 10,000 with retry.
3. Add the NOT NULL constraint in migration N+1, after the backfill Workflow reports complete.

The Workflow pattern is documented end-to-end in the TanStack Ship billing module reference, and it is what keeps migrations survivable as your tenant tables grow past tens of millions of rows.

Tenant Onboarding and Offboarding

Two operations sit outside the normal request path but matter more than they look: tenant provisioning (a new account appears with a clean schema state) and tenant data export (a customer leaves and asks for their data).

Onboarding is a single transaction that creates the tenants row and any per-tenant defaults you need:

await db.batch([
  db.prepare("INSERT INTO tenants (id, slug, plan) VALUES (?, ?, 'free')")
    .bind(newTenantId, slug),
  db.prepare("INSERT INTO tenant_settings (tenant_id, key, value) VALUES (?, 'theme', 'default')")
    .bind(newTenantId),
  db.prepare("INSERT INTO tenant_settings (tenant_id, key, value) VALUES (?, 'timezone', 'UTC')")
    .bind(newTenantId),
]);

Batch all per-tenant defaults inside one transaction. A partial onboarding — tenant row exists, settings half-written — is the kind of state that is hard to clean up six months later.

Data export is a SELECT … WHERE tenant_id = ? over every tenant-scoped table, formatted as JSON, streamed to R2. The shape I use is one Workflow that emits one file per table to R2, then a second Workflow zips and signs the result. R2 storage is cheap, and signed URLs are the cleanest way to hand the export to a customer without exposing your D1 binding. The pattern handles GDPR deletion requests the same way: select everything for tenant_id = ?, write to R2, then DELETE FROM … WHERE tenant_id = ? in a single batched transaction.

Putting It Together

A multi-tenant SaaS on D1 is not exotic. It is the familiar tenant_id pattern you would ship on Postgres or MySQL, with two D1-specific realities you have to plan around: the global write lock at ~200–300 req/s, and the migration cost on tenant-scoped tables past ~10 million rows.

The implementation steps that have held up in production for me:

1. tenant_id as the leading column of every composite index. Never as the second column. Never as a non-leading filter.
2. A typed query helper that injects the tenant predicate automatically. Resist the urge to abstract further; the leak risk is at the SQL surface, not at the ORM surface.
3. db.batch() for any multi-statement write on a hot path. One transaction, one lock acquisition.
4. resolve: "primary" only where read-your-writes consistency matters. Everything else stays on the replica.
5. A Workflow-based migration pattern for any column add that would otherwise rewrite a tenant table.

Ship multi-tenant on D1 in a weekend. TanStack Ship's starter includes the tenant_id schema, the typed query helper, the db.batch() patterns, the Workflow-backed migrations, and the read-replica routing decisions — wired up and integration-tested. The same patterns ship in production across three of my own SaaS products. Clone the free starter → or compare it against TanStack Ship alternatives.