ADR-011: Database Portability

Status

Accepted

Context

Three questions came up during a codebase review:

1. Where does Toast use Drizzle ORM, and how deeply?
2. At what points do we assume PostgreSQL?
3. How hard would it be to support MySQL — either as a replacement or alongside PostgreSQL?

These are worth answering formally because the answers affect our self-hosting story (some operators prefer MySQL), our competitive positioning against Ghost (which is MySQL-only), and the long-term maintenance cost of our database layer.

How Deeply Is Drizzle Used?

Drizzle is used in three layers, all flowing through the @toast/db package:

Layer 1 — The @toast/db package (ground zero):

• schema.ts — all 11 tables, 3 enums, 11 relation definitions using drizzle-orm/pg-core
• client.ts — connection pool using postgres (postgres-js) driver + drizzle-orm/postgres-js adapter
• migrate.ts — migration runner using drizzle-orm/postgres-js/migrator
• seed.ts — seeder using the postgres driver directly + Drizzle queries
• index.ts — re-exports Drizzle operators (eq, and, sql, etc.) + schema + client
• drizzle.config.ts — dialect: 'postgresql'
• 9 migration SQL files + Drizzle metadata (all pure PostgreSQL DDL)

Layer 2 — API repositories and services (consumers via @toast/db):

• 5 repository files (content, site, staff, audit-log, collaboration)
• 1 service file (version.service.ts) that uses raw SQL via Drizzle's sql tag
• All use Drizzle's query builder: select, insert, update, delete, where, orderBy, limit, offset, leftJoin, returning

Layer 3 — Auth integration:

• auth.ts uses drizzleAdapter(getDb(), { provider: 'pg' }) from Better Auth

The admin panel (apps/admin) does not import from @toast/db — it accesses data through the API. This is good: the PostgreSQL coupling does not extend to the frontend.

Where Do We Assume PostgreSQL?

The coupling is deep and deliberate:

The 9 migration SQL files are entirely PostgreSQL DDL — CREATE TYPE ... AS ENUM, gen_random_uuid(), "public"."table_name" schema references, citext column types, ::text casts, USING btree index syntax.

What Would Switching to MySQL Require?

A full swap (dropping PostgreSQL, going MySQL-only) would be a significant rewrite of the database layer but is tractable because the coupling is well-contained in @toast/db:

Estimated effort: 2-4 days for someone experienced with both Drizzle and MySQL.

What Would Supporting Both Require?

This is fundamentally harder because of how Drizzle works.

Drizzle does not support multi-dialect schemas. There is no shared table() function. pgTable and mysqlTable come from different modules, produce different types, and generate different SQL. You cannot write a single schema that works with both databases.

This means any "support both" approach requires:

1. Two parallel schema files — one using pgTable, one using mysqlTable. Every table defined twice. Every schema change made twice. Every new column added twice.

2. Two separate migration histories — Drizzle Kit generates dialect-specific SQL. The 9 existing PostgreSQL migrations cannot be reused for MySQL. Each dialect needs its own drizzle.config.ts and its own migrations directory.

3. Dialect-specific workarounds for .returning() — PostgreSQL supports INSERT ... RETURNING; MySQL does not. Drizzle's MySQL adapter offers $returningId() but it only returns auto-increment IDs, which is useless with UUID primary keys. Every .returning() call (~15) would need a helper that generates the UUID application-side, inserts, then does a follow-up SELECT on MySQL.

4. Behavioral differences that cause subtle bugs:

   • citext (PG extension) vs MySQL's default case-insensitive collation — similar but not identical semantics
   • jsonb (PG binary JSON with indexing) vs json (MySQL text-based JSON) — different query operators if we ever add JSON-level queries
   • timestamp with time zone vs MySQL TIMESTAMP — different timezone handling semantics
   • pgEnum (reusable named types) vs MySQL ENUM() (per-column, not reusable) — different migration behavior
   • Transaction isolation defaults differ between PG and MySQL

There are three approaches to structuring this, each with significant trade-offs.

Approach A: Database Adapter Pattern (what Payload CMS does)

Define an abstract interface at the repository boundary. Each database gets its own adapter package implementing that interface:

packages/
  db/              → shared types + DatabaseAdapter interface
  db-pg/           → pgTable schema, postgres.js driver, PG adapter
  db-mysql/        → mysqlTable schema, mysql2 driver, MySQL adapter

Repositories call the adapter interface instead of Drizzle directly. Services and routes don't change.

• Pros: clean separation, each adapter uses dialect-specific features fully, no lowest-common-denominator penalty
• Cons: double schema maintenance forever, adapter interface becomes a bottleneck for new query patterns, CI test matrix doubles

Approach B: Conditional Schema Loading (lighter weight)

Keep two schema files but swap them at module load time based on a DB_DIALECT environment variable. Repositories keep using Drizzle operators (which are dialect-agnostic) but the schema objects resolve to the correct dialect.

• Pros: less abstraction overhead, repositories barely change beyond .returning() rewrites
• Cons: TypeScript can't verify both paths simultaneously, conditional loading is fragile, dialect-specific helpers accumulate

Approach C: Replace Drizzle with a Dialect-Agnostic Query Builder (Knex/Kysely)

This is what Ghost, Strapi, and Directus do — Knex generates dialect-appropriate SQL from a single API.

• Pros: single schema, single migration format, proven pattern
• Cons: throws away all existing Drizzle work, loses type safety, Knex is showing its age

Decision

Stay PostgreSQL-only. Do not pursue multi-database support.

The reasons:

1. The Ongoing Maintenance Cost Is Not Worth It

Supporting two databases means double the schema files, double the migration histories, double the CI matrix, and a permanent tax on every database change. For a project optimizing for velocity ("ship features in days, not weeks" — ADR-000), this friction directly contradicts our core principle.

Ghost ran MySQL-only for over a decade and it was never a meaningful barrier to adoption. The database choice is not what determines whether self-hosters choose Toast.

2. PostgreSQL Is the Right Choice and We Should Use It Fully

ADR-000 explicitly states: "PostgreSQL as the foundation. Use its full capabilities to avoid bolt-on infrastructure."

We're already using PostgreSQL-specific features that have no good MySQL equivalents:

• citext for case-insensitive emails without application-level normalization
• jsonb for structured content bodies with potential for indexing
• RETURNING for atomic insert-and-read operations
• Native uuid type with gen_random_uuid()
• ADR-000 plans for pgvector (embeddings), partitioning, and materialized views

Abstracting over these to support MySQL would mean giving up the features that make PostgreSQL the right choice, or maintaining dialect-specific code paths for every one of them.

3. The Architecture Already Contains the Coupling Well

The current architecture does one thing right: all PostgreSQL coupling is in @toast/db. No Drizzle imports leak into the API layer beyond what @toast/db re-exports. Repositories return business types. Services don't know about the database driver.

If we ever need to revisit this decision, the extraction point is clear: replace @toast/db's internals while keeping its public API. The Adapter Pattern (Approach A) could be introduced later without restructuring the rest of the application.

4. What We Will Do Instead

To keep the door open without paying the multi-database tax today:

• Strengthen the @toast/db abstraction boundary. Ensure repositories return plain business types, not Drizzle row types. The $inferSelect types are fine as an internal convenience but should not appear in service or route signatures.
• Keep all database-specific code in @toast/db. No drizzle-orm/pg-core imports outside of packages/db/. This is already the case and should stay enforced.
• Document PostgreSQL as a requirement clearly. Self-hosting docs should be upfront that PostgreSQL is required, with specific version requirements and setup guides for common platforms.

Consequences

Positive

• Zero maintenance overhead from multi-database support
• Free to use PostgreSQL-specific features (pgvector, partitioning, materialized views, jsonb operators) as planned in ADR-000
• Simpler CI — one database to test against
• Simpler deployment docs — one database to document
• Developer experience remains straightforward — one schema, one migration history, one set of patterns

Negative

• Self-hosters who only have MySQL available cannot use Toast without also running PostgreSQL
• If a future business need requires MySQL support (e.g., enterprise customers with MySQL-only policies), the work described in this ADR would need to be done then
• We are betting that PostgreSQL's market position continues to strengthen (current trends support this)

Neutral

• The @toast/db package boundary means this decision is reversible at moderate cost (estimated 2-4 weeks for an adapter pattern implementation) if circumstances change
• Ghost's eventual migration path (if any) is not a factor — we're building for 2026, not migrating from 2013