Portable Schema Generator

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.

aibot88 Updated Sep 9, 2026 3 repo stars

Data & Analytics

Install

Press C to copy

$ npx skillmds@latest add aibot88/portable-schema-generator

Installs into ~/.claude/skills (or .claude/skills inside a project). Restart Claude Code to pick it up.

＋ Add to plugin

README badge

[![SkillMD](https://skillmd.com/badge/aibot88/portable-schema-generator.svg)](https://skillmd.com/skills/aibot88/portable-schema-generator)

Trust & safety

2 of 3 audits passed

Last audited Sep 24, 2026 · commit 3c0488f

Permissions this skill uses

Documentation only
No scripts, network access, or credentials are involved.

Works with: Claude Code Claude.ai OpenAI Codex +

File contents

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 conventions
file-operation-patterns — migration file naming discipline

---name: portable-schema-generatordescription: 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.--- # 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

aibot88/sec_skill_store/tree/main/skills/claudskills/portable-schema-generator commit 3c0488f4be

Frequently asked questions

npx skillmds@latest add aibot88/portable-schema-generator