Schema Design Patterns for Cloudflare D1

D1 is SQLite

D1 databases are SQLite files served from Cloudflare's infrastructure. This gives you the full power of SQL — JOINs, transactions, CTEs, window functions — without the operational overhead of a traditional database server.

Pagination: Cursor vs Offset

Offset pagination (LIMIT 10 OFFSET 30) is simple but degrades with large tables. For D1, prefer cursor-based pagination:

-- Cursor pagination (fast even at large offsets)
SELECT * FROM articles
WHERE published_at < ?   -- cursor: last seen published_at
ORDER BY published_at DESC
LIMIT 10;

Full-Text Search

D1 supports SQLite FTS5:

CREATE VIRTUAL TABLE articles_fts USING fts5(
  title, excerpt, content,
  content=articles,
  content_rowid=id
);

-- Populate
INSERT INTO articles_fts SELECT title, excerpt, content FROM articles;

-- Query
SELECT a.* FROM articles a
JOIN articles_fts fts ON a.id = fts.rowid
WHERE articles_fts MATCH ?
ORDER BY rank;

JSON Columns

SQLite has native JSON functions. Store flexible metadata in a TEXT column:

ALTER TABLE articles ADD COLUMN meta TEXT DEFAULT '{}' CHECK(json_valid(meta));

-- Query a JSON field
SELECT * FROM articles WHERE json_extract(meta, '$.featured') = 1;

Transactions in D1

D1 supports batched statements that execute in a single round-trip:

const [articles, tags] = await db.batch([
  db.prepare("SELECT * FROM articles LIMIT 10"),
  db.prepare("SELECT * FROM tags"),
]);

Use .batch() for reads that need to be consistent, and .run() wrapped in explicit BEGIN/COMMIT for writes.