sqlpush

Prisma db push for SQLAlchemy. Your models are the migration. sqlpush diffs them (SQLAlchemy, SQLModel, anything built on MetaData) against the live PostgreSQL / TimescaleDB database, classifies every operation by risk (safe / risky / destructive), and applies the plan atomically. No migration files to write, no upgrade step to forget. Drift checks exit with codes your CI can gate on.

sqlpush diff "myapp.models:metadata"      # see the SQL, ordered by risk
sqlpush check "myapp.models:metadata"     # CI gate: exit 0/2/3
sqlpush push "myapp.models:metadata"      # apply (destructive gated)

If you've run Base.metadata.create_all() in production and known it was wrong, sqlpush is for you.

Install

pip install sqlpush

Or from source:

git clone https://github.com/juanmicl/sqlpush && cd sqlpush && uv sync

Python 3.10 or newer. PostgreSQL only.

Why

Declarative models are already the source of truth. Migration files re-encode what the models say, drift from them, and pile up forever. sqlpush closes the loop the way Prisma's db push does for its schema language, but for the SQLAlchemy ecosystem (SQLModel included):

• No migration files required. The diff is the migration: computed fresh from models vs. live database on every run, via alembic's autogenerate engine used as a library. Files exist as a second workflow when you want them (see below).
• Risk-aware by default. Every operation is classified safe / risky / destructive. Destructive ops (drops) are blocked until --allow-destructive: nothing executes at all while any is present.
• Drift detection built for CI. check plans once and reports through its exit code, no output parsing; --json emits a stable versioned contract.
• Safe under concurrency. An advisory lock coordinates workers: one pusher at a time, losers wait bounded and re-verify, so deploy pipelines can race without corrupting anything.
• Hypertables without hand-written SQL. Decorate a model with @hypertable and the create_hypertable directive is planned state-aware: idempotent pushes, clean checks, no false drift.

If you know alembic: sqlpush is its autogenerate engine, productized into apply and check verbs, with no revision scripts to maintain.

PostgreSQL only, by design.

The 30-second tour

Point sqlpush at your metadata (module:attribute) and a database (--dsn or $DATABASE_URL):

$ export DATABASE_URL="postgresql+psycopg://user:pass@host:5432/db"

$ sqlpush diff "myapp.models:metadata"
-- safe
CREATE TABLE hero (
    id SERIAL NOT NULL,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

-- risky
CREATE INDEX ix_hero_name ON hero (name);

Push it (the destructive gate is on by default):

$ sqlpush push "myapp.models:metadata"
1 destructive operation(s) blocked; re-run with --allow-destructive
$ echo $?
1

$ sqlpush push "myapp.models:metadata" --allow-destructive
$ echo $?
0

In CI, check drift and fail loudly (see exit codes below). Limit scope with repeated --schema / --exclude options.

FastAPI: retire create_all()

Most FastAPI + SQLModel apps ship the lifespan the tutorials teach:

@asynccontextmanager
async def lifespan(app):
    async with engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.create_all)
    yield

create_all creates tables that are missing. That is all it ever does. Add a column to a model and the database never hears about it; an index on an existing table, a type change, a drop: nothing. Production drifts from the models in silence, so every real change still rides the alembic treadmill: autogenerate, review, upgrade, and two histories to keep in agreement forever.

The sqlpush lifespan is one line:

from contextlib import asynccontextmanager
from sqlpush import aensure_schema


@asynccontextmanager
async def lifespan(app):
    await aensure_schema(SQLModel.metadata, engine, mode="check")
    yield

mode="check" verifies the models against the database at startup and raises when they disagree: the app refuses to boot against a schema it does not match, which beats failing on the first query at 3am. The schema change itself comes from wherever you put it: sqlpush push in the deploy pipeline (destructive ops gated), or aensure_schema(..., mode="push") when you want the API to apply it.

asyncpg URLs work too: a DSN or AsyncEngine spelling postgresql+asyncpg is translated to the psycopg driver automatically, and asyncpg is never required in the sqlpush process.

Push in the deploy pipeline, check at boot.

An inherited database

The first check against a database with history often reports drift: hand-built indexes, audit tables, a column someone added by hand. If any of the drift looks destructive, check exits 3 and push blocks. That is the tool refusing to silently drop your legacy objects. Two escape hatches: --exclude accepts objects you choose to keep (fnmatch patterns, repeatable), and --allow-destructive accepts the drops when you really do want them.

When you want files: the chain

Most changes never need a file. When one does, sqlpush has a second workflow built on the same diff engine: the chain. revision writes the next numbered SQL file from your models against a reference DB, migrate replays pending files with gates and checksums, and stamp adopts an existing database without executing anything.

The files are plain SQL you can review, edit before first apply, and run under psql. Schema change and data backfill ship as one file. The chain guide covers the format, the gates and the workflows.

Project hook

Drop a sqlpush.py where sqlpush can find it (the alembic env.py / pytest conftest.py pattern) and the CLI stops needing flags, specs and env vars. Two discovered locations, first match wins — or name any file explicitly with --hook/$SQLPUSH_HOOK:

1. migrations/sqlpush.py — preferred: it lives next to the chain, no repo-root clutter.
2. sqlpush.py — repo root, kept as the backwards-compat fallback.

# migrations/sqlpush.py — the preferred location
def get_metadata():  # REQUIRED for diff/check/push/revision
    from myapp.models import metadata

    return metadata  # -> a populated MetaData


def get_dsn() -> str:  # REQUIRED for every verb
    from myapp.settings import DATABASE_URL

    return DATABASE_URL  # -> a full psycopg DSN


CHAIN_DIR = "migrations/chain"  # OPTIONAL — default for --dir

With that file in place, uv run sqlpush revision -m "change" just works: no --dsn, no module:attribute, no credentials on the command line, no PYTHONPATH. Inputs resolve with a fixed precedence:

input	explicit flag	hook	fallback
--dsn / --ref-dsn	wins	get_dsn()	$DATABASE_URL, only without a hook and never for --ref-dsn
module:attribute (diff/check/push/revision)	wins	get_metadata()	usage error
--dir (revision/migrate/stamp)	wins	CHAIN_DIR	migrations/versions
which file is the hook	--hook PATH	—	$SQLPUSH_HOOK, else first match: migrations/sqlpush.py, then root sqlpush.py

The hook's location is the alembic -c equivalent: --hook > $SQLPUSH_HOOK > discovery, any location you want (relative paths resolve against the CWD) — and the explicit forms fail loud, with an error naming that path (custom/hook.py: file not found) instead of falling back to the discovered candidates.

A hook that is missing a member a verb needs — or whose get_dsn()/get_metadata() raises — fails with a typed error naming the file that actually loaded and the member (migrations/sqlpush.py: missing get_dsn()), never a traceback. Without a hook, every verb behaves exactly as before.

One deliberate detail: on discovering the hook, sqlpush appends your CWD to sys.path instead of prepending it — whatever location the hook loaded from, never the hook's own directory. A root sqlpush.py would otherwise shadow the installed package the moment you ran the CLI from your repo root; appending guarantees the real package always wins, and the hook itself is loaded by path only (the migrations/ candidate has no shadowing concern but loads the same way).

Exit codes

verb	0	1	2	3
diff	always
check	clean		drift	destructive drift
push	applied	destructive blocked	error (incl. partial failure)
revision	file written	error (empty drift refuses)
migrate	clean	blocked or partial failure
stamp	registered	blocked or refused

Failures print a typed error on stderr, never a traceback.

push --safe-only runs only safe operations and skips the rest informationally (exit 0). Indexes on existing tables build CONCURRENTLY by default (opt out with --no-concurrently); a failed CREATE INDEX CONCURRENTLY marks the run as partial failure (exit 2) instead of silently half-applying, and leaves an INVALID index: drop it (DROP INDEX CONCURRENTLY) and re-push. stamp refuses a file whose checksum no longer matches the registry; --force accepts the new content.

The knobs, per verb:

verb	flags
push	--allow-destructive --safe-only --no-lock --lock-timeout --advisory-wait --no-concurrently --statement-timeout
revision	--ref-dsn (required without a hook) -m/--message --no-concurrently --dir
migrate	--allow-destructive --advisory-wait --lock-timeout --statement-timeout --dir
stamp	--force --dir

Every verb takes --hook PATH (or $SQLPUSH_HOOK) to name the hook file explicitly. Every verb except revision takes --dsn (or $DATABASE_URL, or the project hook's get_dsn()). revision takes --ref-dsn — or the hook — with no env fallback: the reference DB is a different database from the push target. diff, check, push and revision also take repeatable --schema / --exclude. Timeouts are seconds; a lock_timeout bounds how long a statement waits on a lock before failing, statement_timeout bounds each statement's runtime, and an exhausted advisory-wait raises instead of hanging on a stuck lock holder.

How it works

flowchart LR
    models["SQLAlchemy MetaData"] --> diff["diff<br>alembic autogenerate, scoped"]
    db[("live PostgreSQL")] --> diff
    diff --> risk["risk classification<br>safe / risky / destructive"]
    risk --> plan["plan"]
    plan --> render["render"]
    render --> apply["apply<br>atomic txn · CONCURRENTLY split · advisory lock"]
    apply --> report["report"]

Loading

• Diff engine scopes reflection to your target schemas and prunes system catalogs (TimescaleDB internals included) before reflection even starts.
• Classifier maps each operation to a risk class; unknown operations are risky, never silently safe.
• Executor splits the plan: concurrent index builds run one per transaction on autocommit, everything else applies in a single atomic transaction with a bounded lock_timeout.
• Typed errors: only SqlpushError / ConnectFailed / MetadataImportError escape the API, never raw driver exceptions.

Scoping: --schema restricts the diff to named schemas (default: the session's real search_path). Extension-owned schemas never enter scope automatically, and schemas you pass explicitly are never filtered. The chain's registry table (public.sqlpush_versions) always lives in public and is pruned from every diff, so check after migrate is clean. alembic_version gets the same treatment.

Comparison

An honest view of the neighborhood (stars as of 2026-08):

	migration files	source of truth	risk gate	CI drift exit codes	TimescaleDB
sqlpush	optional: push needs none; the chain has reviewable, checksummed files	SQLAlchemy MetaData	classified safe/risky/destructive, destructive blocked by default	check 0/2/3	@hypertable directives
alembic (4.4k★)	yes	migration scripts (autogenerate assists)	no	no	no
atlas (8.7k★)	optional (HCL)	HCL / SQL (ORMs via providers)	lint policies	yes	no
prisma db push (47k★)	none	Prisma schema (Node/TS)	no	no	no
migra (3.1k★)	diff only	SQL	n/a	partial	no (deprecated)

sqlpush is narrower than atlas and younger than alembic, deliberately. It is one tool for one job: keep a PostgreSQL schema in lockstep with SQLAlchemy models, safely enough to run from CI.

Guides: the chain (file format, gates, backfills), migrating from alembic, and migrating from migra (deprecated). Changes land in the CHANGELOG.

Design notes

• import sqlpush stays light: the public API loads lazily, so the annotations module carries none of alembic/typer/psycopg.
• The advisory-lock key derives from the database OID: two DSN spellings of the same database contend for the same lock.
• --json output is a versioned contract ("version": 1) meant for tooling; additive changes only within a version (operations now carry a concurrent boolean).