One Schema, Two Databases

Media Club is an open-source, self-hosted catalog for collectors: movies, TV shows, vinyl, and books, with owned lists, wishlists, collections, and a show tracker. Because it is built for other people to fork and run, it made a promise most side projects do not have to keep: two first-class deployment paths. One is Cloudflare Workers with D1 on the free tier, so a collector can share a public catalog with zero monthly cost. The other is Docker with a plain SQLite file, for people who want their data on their own disk.

Two deployment targets usually means two data stories, and two data stories is how a small open-source project dies: every feature written twice, every bug fixed in one path and forgotten in the other. The design goal was one schema, one query layer, and one set of business logic, with the difference between "serverless database at the edge" and "file on disk" collapsed into the smallest possible seam.

This dive is about where that seam sits, what it cost, and the discipline that keeps it from leaking.

• Both paths are first-class. Cloudflare and Docker users get the same features, the same migrations, and the same behavior. Neither path is a demo of the other.
• Zero-cost hosting has to stay zero-cost. The Workers path exists so a collector can deploy on the free tier. Any design that requires a hosted database server, managed Postgres included, breaks the project's core pitch.
• The runtimes are genuinely different. D1 is an async binding provided by the Workers platform per request. A local SQLite file is a synchronous driver holding an open file handle. One cannot pretend they are the same process model; the code has to absorb the asymmetry somewhere.
• One maintainer, 151 tests. The test suite has to run fast and hermetically on any contributor's machine, without a Cloudflare account in the loop.

Option A: two data layers behind an interface. Write a repository interface and implement it twice, once per backend. Maximum flexibility, and maximum surface: every new query is written twice and tested twice, and the two implementations drift the way all duplicated logic drifts. For a project whose whole risk is maintenance burden, this doubles it.

Option B: one hosted database for everyone. Point both deployment paths at a managed Postgres and the portability problem disappears. So does the project's reason to exist: the free-tier Workers deployment now needs a paid database, and the Docker path, whose entire appeal is "your data is a file on your disk," grows a network dependency. Lost on mission, not on engineering.

Option C: ORM-level multi-dialect portability. Some ORMs promise one schema across Postgres, MySQL, and SQLite. The promise is leaky in exactly the places that matter: column types, index behavior, and constraint semantics differ per dialect, and code that "supports every database" tends to be tested against one. Portability you have not pinned down is a bug report waiting for a stranger to file it.

The chosen shape: one dialect, two drivers. D1 is SQLite. The local file is SQLite. Committing to the SQLite dialect makes the portability real instead of aspirational: one Drizzle schema, one generated set of SQL migrations, identical semantics, and the only genuine difference left, how you get a connection, is small enough to live in a single function.

The seam is two decisions, each in one place. At build time, an environment variable picks the deployment adapter: the Cloudflare adapter by default, the Node adapter for Docker. At request time, one function returns the database: if the app is running on Workers and the platform hands it a D1 binding, it wraps that binding in Drizzle; otherwise it opens the SQLite file, enables write-ahead logging, and caches one connection per database file so a busy dev server does not leak file handles by reopening the database on every request. That connection cache is the local path's only cleverness, and it exists because the Workers path gets the equivalent for free from the platform.

Everything above the seam is single-sourced. The schema is one module of SQLite table definitions: a discriminated items table with unique indexes doing duplicate prevention, collections, sessions, and the show tracker. Migrations are one set of generated SQL files with two runners: the local runner applies them to the file database, and the platform CLI applies the identical files to D1, locally simulated in preview and remote on deploy. The 151-test suite runs entirely against the file driver, which keeps it fast, hermetic, and runnable by any contributor with nothing installed but the repo.

The part that does not show up in any file is the discipline: staying inside the intersection of what both runtimes support. D1 does not offer interactive transactions, so the codebase does not use them anywhere; writes are structured as single statements and the unique indexes carry the integrity guarantees. Runtime-specific SQL and pragmas stay out of application code entirely; the one pragma in the project lives inside the local connection factory, where the other runtime never sees it.

Both deployment paths shipped and are documented as equals, and features have kept landing on top of the seam without touching it: the MCP endpoint for LLM clients, the show tracker, genre sync, and the admin streaming lists all query the same schema with no awareness of which runtime they are on. The promise that mattered, fork it and deploy free, or fork it and own your file, has held without a compatibility matrix.

The honest tradeoffs: the intersection discipline is a real constraint, not a free lunch. Giving up interactive transactions means multi-step writes lean on single-statement design and unique constraints, which works at this scale and would need rethinking under concurrent writers. The seam also contains one type-level fib: the two Drizzle instances are not identical types, and the local one is cast to the D1 flavor, which the type checker accepts and cannot verify. And because tests run on the file driver, D1-specific behavior only surfaces in the platform's local simulation at preview time; a second CI lane running the suite against simulated D1 is the obvious next hardening step.

The transferable lesson is about where portability comes from. It did not come from an abstraction layer; it came from narrowing the problem until abstraction was barely needed. One dialect, one schema, two drivers, and a seam small enough to read in a minute: that is what "runs anywhere" looks like when it actually does.