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

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)

./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):

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

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):

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

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, planner, sqlite-rebuild, hazards.

Tests

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