| 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 | stable |
| updated | "2026-04-17T00:00:00.000Z" |
| type | skill |
| category | gsd-meta |
| origin | tibsfox |
| modified | false |
| first_seen | "2026-04-17T00:00:00.000Z" |
| first_path | examples/skills/gsd-meta/portable-schema-generator/SKILL.md |
| superseded_by | null |
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.
- with ; default becomes
.
