Portable schema generator

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:

1. Strip schema prefix for SQLite — CREATE SCHEMA IF NOT EXISTS foo; and SET search_path are removed. Keep bare table names.
2. Replace BIGSERIAL with INTEGER PRIMARY KEY AUTOINCREMENT.
3. Replace TIMESTAMPTZ with TEXT; default becomes (strftime('%Y-%m-%dT%H:%M:%fZ','now')).
4. Replace BOOLEAN with INTEGER (values 0/1 at insert time — use an adapter coercion).
5. Drop RESTART IDENTITY on truncates (SQLite uses DELETE FROM sqlite_sequence WHERE name = 'table' instead).
6. Rewrite function-style triggers to inline BEGIN ... END trigger 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 $N placeholders → ? for SQLite
• Strip release_history. schema prefix for SQLite
• Translate now() → strftime(...), ::type casts → 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

1. Author the Postgres version first (richer type system reduces ambiguity).
2. Translate row-by-row using the rules above into the SQLite version.
3. Test both: apply each migration to a disposable DB, run schema-check script against the adapter.
4. 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 .env conventions
• file-operation-patterns — migration file naming discipline