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.

1. Replace BIGSERIAL with INTEGER PRIMARY KEY AUTOINCREMENT.
2. Replace TIMESTAMPTZ with TEXT; default becomes

(strftime('%Y-%m-%dT%H:%M:%fZ','now')).

1. Replace BOOLEAN with INTEGER (values 0/1 at insert time — use an

adapter coercion).

1. Drop RESTART IDENTITY on truncates (SQLite uses

DELETE FROM sqlite_sequence WHERE name = 'table' instead).

1. 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.

1. 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