# schemashift Declarative schema management for SQLite and PostgreSQL. Keep the desired schema as plain SQL DDL files in git; `schemashift` diffs it against a live database (or another DDL set) and generates an ordered, safe migration, flagging every step that can lose data, take locks, rewrite a table or not be undone. It ships its own DDL parser, a canonical schema model, rename detection, a dependency-aware planner, the PostgreSQL online-DDL patterns (`CONCURRENTLY`, `NOT VALID` + `VALIDATE`) and the SQLite 12-step table rebuild. ## Features - Own recursive-descent DDL parser (PostgreSQL + SQLite subset) with `file:line:col` errors: tables, columns, defaults, generated/identity columns, PK/UNIQUE/CHECK/FK constraints (named or not), indexes (expression, partial, `DESC`, `INCLUDE`), views, enums, sequences, schemas, triggers (SQLite), `COMMENT ON`. - Canonical model with normalization (`int`/`int4`/`integer`, `now()`/`CURRENT_TIMESTAMP`, redundant parentheses and literal casts, `IN (...)` vs `= ANY (ARRAY[...])`, implicit constraint names), so equivalent schemas diff to nothing. - Introspection of SQLite (`sqlite_master` + PRAGMAs) and PostgreSQL (`pg_catalog`). - Diff of tables, columns, constraints, indexes, views, enums, sequences, triggers and comments, with rename detection (columns: same type, position and constraints; tables: column-set Jaccard >= 0.8). Renames are only applied when you accept them. - Planner: FK/view/enum dependency ordering, reverse-order drops, circular FK handling, PostgreSQL `CREATE INDEX CONCURRENTLY`, FK/CHECK as `NOT VALID` then `VALIDATE`, `SET NOT NULL` via a validated CHECK, enum value additions, type-change hazard classification; SQLite table rebuild with data copy, index/trigger/view recreation and `foreign_key_check`. - Hazards `DATA_LOSS`, `LOCKING`, `REWRITE`, `IRREVERSIBLE` per step; plans containing them need `--allow-hazards`. A down migration is generated by planning the reverse diff. - Commands: `diff`, `plan`, `apply` (with `--dry-run`), `dump`, `lint`, `drift` (CI), `history`. Applied plan hashes are recorded in `_schemashift_history`. ## Install ```bash uv venv --python 3.12 .venv uv pip install -p .venv/bin/python -e '.[dev]' # '.[pg]' for PostgreSQL support only export PATH=$PWD/.venv/bin:$PATH ``` ## Quickstart (the v1 to v2 SQLite example) ```bash ./examples/build_v1.sh # builds examples/v1.db from examples/v1/*.sql with sample rows schemashift lint examples/v1 schemashift diff --from sqlite:///examples/v1.db --to examples/v2 RENAMES="--accept-rename audit_log=audit_events --accept-rename users.full_name=display_name" schemashift plan --from sqlite:///examples/v1.db --to examples/v2 $RENAMES # refused: hazards schemashift plan --from sqlite:///examples/v1.db --to examples/v2 $RENAMES --allow-hazards -o migrations/0007.sql schemashift apply --from sqlite:///examples/v1.db --to examples/v2 $RENAMES --allow-hazards --dry-run schemashift apply --from sqlite:///examples/v1.db --to examples/v2 $RENAMES --allow-hazards schemashift drift --from sqlite:///examples/v1.db --to examples/v2 # exit 0 ``` ### Real output `lint` (exit 1 when there are findings): ``` audit_log.actor_id: [nullable-fk] foreign key column is nullable (references users) audit_log.actor_id: [fk-without-index] foreign key to users has no index starting with these columns 2 finding(s) ``` `diff` (abridged). Rename candidates are reported, never applied silently: ``` + table audit_events + table payments - table audit_log + column invoices.currency text - column invoices.legacy_ref text - check constraint on invoices: status in ('draft', 'sent', 'paid') + check constraint on invoices: status in ('draft', 'sent', 'paid', 'void') + column users.display_name text - column users.full_name text - column users.password_hash text ~ view invoice_totals ~ trigger trg_invoices_status ? possible rename of table audit_log -> audit_events (column overlap 100%); pass --accept-rename audit_log=audit_events to treat it as a rename instead of drop + add ? possible rename of column users.full_name -> display_name (same type (text), position (3) and constraints); pass --accept-rename users.full_name=display_name to treat it as a rename instead of drop + add ``` `plan` without `--allow-hazards` (exit 3): ``` Refusing: the plan contains hazardous steps (re-run with --allow-hazards to accept): DATA_LOSS, IRREVERSIBLE, REWRITE: rebuild table invoices (add column, add constraint, drop column, drop constraint) REWRITE: rebuild table organizations (add column, add constraint, drop constraint) DATA_LOSS, IRREVERSIBLE: drop column users.password_hash ``` `plan ... -o migrations/0007.sql` (the up section, abridged; a `-- migrate:down` section follows): ```sql -- migrate:up PRAGMA foreign_keys = OFF; BEGIN; -- drop trigger trg_invoices_status drop trigger if exists trg_invoices_status; -- drop view invoice_totals drop view invoice_totals; -- rename table audit_log to audit_events alter table audit_log rename to audit_events; -- rename column users.full_name to display_name alter table users rename column full_name to display_name; -- rebuild table invoices (add column, add constraint, drop column, drop constraint) [DATA_LOSS, IRREVERSIBLE, REWRITE] create table _ss_new_invoices (...); insert into _ss_new_invoices (id, org_id, number, status, total_cents, issued_at) select id, org_id, number, status, total_cents, issued_at from invoices; drop table invoices; alter table _ss_new_invoices rename to invoices; create index idx_invoices_open on invoices (org_id, issued_at desc) where status not in ('paid', 'void'); ... -- drop column users.password_hash [DATA_LOSS, IRREVERSIBLE] alter table users drop column password_hash; ... PRAGMA foreign_key_check; COMMIT; PRAGMA foreign_keys = ON; ``` `apply`: ``` foreign_key_check: ok Dry run succeeded and was rolled back: 18 steps, plan hash ceaebf81c03d foreign_key_check: ok Applied: 18 steps, plan hash ceaebf81c03d No drift: database matches the desired schema. ``` After the migration all rows are still there (users keep their names under `display_name`, audit rows moved to `audit_events`, every invoice gained `currency = 'USD'`), `PRAGMA foreign_key_check` is empty, and checking the migrated database against v1 reports drift (exit 1). ### PostgreSQL ```bash schemashift plan --from "postgresql://user@host/app" --to schema/ --allow-hazards -o migrations/0042.sql schemashift apply --from "postgresql://user@host/app" --to schema/ --lock-timeout 3s schemashift diff --from examples/pg/v1 --to examples/pg/v2 --format sql # no database needed ``` Plan for `examples/pg/v1` to `examples/pg/v2` (abridged, real output; each `BEGIN/COMMIT` group is separate, the rest runs outside a transaction): ```sql alter type invoice_status add value if not exists 'void' after 'paid'; BEGIN; drop view open_invoices; COMMIT; drop index concurrently if exists invoices_org_idx; BEGIN; create table payments (...); alter table invoices add column currency char(3) not null default 'USD'; alter table invoices drop column note; -- [DATA_LOSS, IRREVERSIBLE] alter table organizations alter column name type varchar(200) using name::varchar(200); COMMIT; alter table users add constraint users_full_name_not_null_tmp check (full_name is not null) not valid; alter table users validate constraint users_full_name_not_null_tmp; alter table users alter column full_name set not null; alter table users drop constraint users_full_name_not_null_tmp; alter table users alter column age type bigint using age::bigint; -- [LOCKING, REWRITE] alter table invoices add constraint invoices_chk_e3d7c4 check (...) not valid; alter table invoices validate constraint invoices_chk_e3d7c4; create unique index concurrently users_org_id_email_key on users (org_id, email); alter table users add constraint users_org_id_email_key unique using index users_org_id_email_key; create index concurrently invoices_org_status_idx on invoices (org_id, status); ... ``` ## Usage Sources are database URLs (`sqlite:///relative.db`, `sqlite:////absolute.db`, `postgresql://...`) or SQL files (a file, a directory of `*.sql`, a quoted glob, or extra shell-expanded arguments). | Command | Purpose | | --- | --- | | `diff --from A --to B [--format text\|sql\|json] [--exit-code]` | Show what differs. | | `plan --from A --to B [-o file] [--allow-hazards] [--no-down] [--format sql\|json]` | Emit the migration (dbmate-style `-- migrate:up` / `-- migrate:down`). | | `apply --from DB --to B [--dry-run] [--allow-hazards] [--lock-timeout 5s]` | Execute the plan. SQLite: one transaction, `foreign_key_check`, rolled back on any error (`--dry-run` always rolls back). PostgreSQL: each step on its own with `lock_timeout`; `--dry-run` prints the plan. | | `drift --from DB --to B` | Exit 1 if the database differs from the desired schema. | | `dump URL_OR_FILES` | Canonical DDL. | | `lint FILES` | Missing FK indexes, tables without PK, nullable FKs, duplicate indexes, `varchar` without length (PostgreSQL), money stored as float/`money`. | | `history --from DB` | List applied plan hashes. | Common options: `--dialect sqlite|postgres` (inferred from a database URL; for plain DDL it is guessed from PostgreSQL-only constructs such as enums, `uuid`, `jsonb`, comments), `--accept-rename old=new` (table) or `table.old=new` (column; table name as in the desired schema), `--interactive` (ask per suggestion). Exit codes: `0` success / no drift, `1` differences with `--exit-code`, drift, lint findings, `2` error, `3` plan refused because of hazards. ## Configuration There is no config file. Everything is a flag. `SCHEMASHIFT_TEST_PG_DSN` (a `postgresql://` URL with permission to create databases) enables the live PostgreSQL tests. ## Architecture ``` src/schemashift/ lexer.py tokenizer (line/col, quoted identifiers, dollar quoting) parser.py recursive-descent DDL parser -> model, SchemaBuilder merges files expr.py expression parser/printer used for normalization, view body normalizer model.py dataclasses: Schema, Table, Column, constraints, Index, View, Enum, ... normalize.py dialect normalization + validation introspect.py SQLite and PostgreSQL readers (both emit DDL fed through the parser) diff.py diff engine, rename detection, rename application deps.py view dependency extraction, topological orders planner.py dependency ordering, PostgreSQL patterns, SQLite rebuild, hazards plan.py Plan/Step/Hazard and SQL rendering sqlgen.py model -> SQL (also used by `dump`) lint.py lint rules db.py connections, source loading, apply, history cli.py click commands ``` Design notes are in `docs/`: [model](docs/model.md), [planner](docs/planner.md), [sqlite-rebuild](docs/sqlite-rebuild.md), [hazards](docs/hazards.md). ## Tests ```bash .venv/bin/pytest -q # SQLite fully, PG tests skipped SCHEMASHIFT_TEST_PG_DSN="postgresql://me@localhost/postgres" .venv/bin/pytest -q # include live PostgreSQL ``` The central property test applies `plan(a -> b)` to a database built from `a` for 26 schema pairs (including the v1/v2 examples in both directions), then checks that the database equals `b`, and that the generated down migration restores `a`. ## Limitations - Removing or reordering PostgreSQL enum values is rejected (the up plan fails; in a down plan the values are left in place with a warning). - Moving a table between PostgreSQL schemas, `EXCLUDE` constraints, partitioning, inheritance, functions/procedures, policies, grants, `NULLS FIRST/LAST` and per-column collations in indexes are not supported. - PostgreSQL views are compared by normalized text; the server re-deparses them, so complex views can show as changed even when equivalent (WHERE/HAVING/ON predicates and literal casts are normalized, select lists are not). Triggers are compared as text too. - SQLite types are normalized with PostgreSQL aliases (`int` becomes `integer`). Table/column comments do not exist in SQLite and are ignored there. - SQLite triggers in other tables that mention a rebuilt table are dropped and recreated by name matching on identifiers. - A PostgreSQL apply is not atomic across steps (concurrent index builds cannot run in a transaction). It stops at the first failure and re-planning resumes from what is already applied. - Down migrations restore structure, not data.