Metadata-Version: 2.4
Name: sqlpush
Version: 0.5.1
Summary: Schema lifecycle tool for production PostgreSQL/TimescaleDB on SQLAlchemy 2.0 (SQLModel included): diff, push and check schema drift straight from your models
Keywords: sqlalchemy,sqlmodel,fastapi,alembic,postgresql,timescaledb,prisma,schema,migrations,database,drift,cli
Author: Juan Miguel Contreras
Author-email: Juan Miguel Contreras <19253629+juanmicl@users.noreply.github.com>
License-Expression: MIT
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Quality Assurance
Requires-Dist: alembic>=1.18,<2
Requires-Dist: psycopg[binary]>=3.2
Requires-Dist: sqlalchemy>=2.0
Requires-Dist: typer>=0.12
Requires-Python: >=3.10
Project-URL: Homepage, https://github.com/juanmicl/sqlpush
Project-URL: Repository, https://github.com/juanmicl/sqlpush
Project-URL: Issues, https://github.com/juanmicl/sqlpush/issues
Project-URL: Changelog, https://github.com/juanmicl/sqlpush/blob/main/CHANGELOG.md
Description-Content-Type: text/markdown

# sqlpush

[![PyPI](https://img.shields.io/pypi/v/sqlpush?style=for-the-badge)](https://pypi.org/project/sqlpush/)
[![Python](https://img.shields.io/pypi/pyversions/sqlpush?style=for-the-badge)](https://pypi.org/project/sqlpush/)
[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg?style=for-the-badge)](LICENSE)

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

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

```console
pip install sqlpush
```

Or from source:

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

```console
$ 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):

```console
$ 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:

```python
@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:

```python
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](docs/the-chain.md) covers the format, the gates and
the workflows.

## 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) `-m/--message` `--no-concurrently` `--dir` |
| `migrate` | `--allow-destructive` `--advisory-wait` `--lock-timeout` `--statement-timeout` `--dir` |
| `stamp` | `--force` `--dir` |

Every verb except `revision` takes `--dsn` (or `$DATABASE_URL`).
`revision` requires `--ref-dsn`, 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

```mermaid
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"]
```

- **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](https://github.com/sqlalchemy/alembic) (4.4k★) | yes | migration scripts (autogenerate assists) | no | no | no |
| [atlas](https://github.com/ariga/atlas) (8.7k★) | optional (HCL) | HCL / SQL (ORMs via providers) | lint policies | yes | no |
| [prisma `db push`](https://www.prisma.io/docs/orm/reference/prisma-cli-reference) (47k★) | none | Prisma schema (Node/TS) | no | no | no |
| [migra](https://github.com/djrobstep/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](docs/the-chain.md) (file format, gates, backfills),
[migrating from alembic](docs/migrating-from-alembic.md), and
[migrating from migra](docs/migrating-from-migra.md) (deprecated).
Changes land in the [CHANGELOG](CHANGELOG.md).

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