| name | portable-schema-generator |
| description | Emit Postgres.sql and SQLite.sql from a single schema spec so tools work across both drivers without duplicating DDL by hand. Use when designing a schema that needs to support both shared Postgres deployments and zero-config SQLite. Reduces two-file sync burden to a single source edit. |
| format | "2025-10-02T00:00:00.000Z" |
| version | 1.0.0 |
| status | ACTIVE |
| updated | "2026-04-17T00:00:00.000Z" |
| triggers | ["designing a schema that needs to support both shared Postgres deployments and zero-config SQLite"] |
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;
and SET search_path are removed. Keep bare table names.
- Replace
BIGSERIAL with INTEGER PRIMARY KEY AUTOINCREMENT.
- Replace
TIMESTAMPTZ with TEXT; default becomes
(strftime('%Y-%m-%dT%H:%M:%fZ','now')).
- Replace
BOOLEAN with INTEGER (values 0/1 at insert time — use an
adapter coercion).
- Drop
RESTART IDENTITY on truncates (SQLite uses
DELETE FROM sqlite_sequence WHERE name = 'table' instead).
- 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
- 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 .env conventionsfile-operation-patterns — migration file naming discipline
