Portable Schema Generator
Two-driver schemas mean two migration files. Hand-keeping them in sync drifts fast. This skill captures the translation rules and the minimal tooling that keeps them aligned.
When to Use
- Your tool needs to work on both Postgres and SQLite
- You want fresh-project onboarding to be zero-config (SQLite default) while shared-infra deployments keep Postgres
- A single schema change should land in both driver files with no manual diff-copying
Core Differences
| Concern | Postgres | SQLite |
|---|---|---|
| Schemas | CREATE SCHEMA foo; SET search_path TO foo | Namespace-flat — prefix names instead |
| Autoincrement | BIGSERIAL PRIMARY KEY | INTEGER PRIMARY KEY AUTOINCREMENT |
| Timestamps | TIMESTAMPTZ DEFAULT now() | TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')) |
| Booleans | BOOLEAN | INTEGER (0/1) |
| Enums via CHECK | CHECK (col IN ('a','b')) | Same — both support inline CHECK |
| Foreign keys | On by default | PRAGMA foreign_keys = ON required at connect |
| Updated-at trigger | CREATE TRIGGER ... EXECUTE FUNCTION touch_updated_at() | CREATE TRIGGER ... BEGIN UPDATE ... END |
| Cascading drops | ON DELETE CASCADE | Same, but only enforced with foreign_keys = ON |
| Reserved-word casing | Case-insensitive | Case-insensitive, but be consistent |
Translation Rules
When authoring the Postgres version first:
- Strip schema prefix for SQLite —
CREATE SCHEMA IF NOT EXISTS foo;andSET search_pathare removed. Keep bare table names. - Replace
BIGSERIALwithINTEGER PRIMARY KEY AUTOINCREMENT. - Replace
TIMESTAMPTZwithTEXT; default becomes(strftime('%Y-%m-%dT%H:%M:%fZ','now')). - Replace
BOOLEANwithINTEGER(values 0/1 at insert time — use an adapter coercion). - Drop
RESTART IDENTITYon truncates (SQLite usesDELETE FROM sqlite_sequence WHERE name = 'table'instead). - Rewrite function-style triggers to inline
BEGIN ... ENDtrigger bodies.
File Layout
migrations/<feature>/
001-init.postgres.sql
001-init.sqlite.sql
002-next.postgres.sql
002-next.sqlite.sql
Numbered pairs. The adapter picks the right one based on cfg.db.driver.
Adapter Runtime Responsibilities
The query adapter at runtime should:
- Rewrite
$Nplaceholders →?for SQLite - Strip
release_history.schema prefix for SQLite - Translate
now()→strftime(...),::typecasts → drop - Coerce JS booleans → 0/1 on param bindings
- Provide a portable
truncate(table)helper
Reference implementation: tools/release-history/db.mjs in this repo.
Workflow
- Author the Postgres version first (richer type system reduces ambiguity).
- Translate row-by-row using the rules above into the SQLite version.
- Test both: apply each migration to a disposable DB, run schema-check script against the adapter.
- Commit both files together; never commit one without the other.
Anti-patterns to avoid
- Single mixed-syntax file — both drivers will try to parse it and one or both will fail.
- Runtime translation of whole migration files — too many edge cases.
- Letting the Postgres file diverge "just for now" — alignment rots quickly.
Example (from this repo)
See migrations/release-history/001-init.postgres.sql and
migrations/release-history/001-init.sqlite.sql. Six tables (release,
feature, metric, retrospective, lesson, publish_target). Same semantics,
driver-idiomatic DDL.
Related
env-setup— database credentials and.envconventionsfile-operation-patterns— migration file naming discipline