September 29, 20264 min read

SQLAlchemy Models, Ptah Migration Review

Turn an Alembic development schema into a reviewed Ptah migration, then catch a missing database default that successful SQLAlchemy inserts can hide.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

An ORM insert can succeed while another writer fails against the same table. One cause is a default that exists in Python but is missing from PostgreSQL. The application supplies a value, so its usual write path never notices.

We’ll add reserved inventory to a SQLAlchemy application, use Ptah to deploy the database change, and deliberately remove its server default afterward. That lets us test whether a live-schema check catches something the ORM’s successful insert does not.

This run used SQLAlchemy 2.0.54, Alembic 1.20.0, psycopg 3.3.6, Python 3.14.5, Ptah 0.10.0, and PostgreSQL 18.6. The supporting files contain the models, native Alembic setup, client programs, and recorded checks.

Put the default in the database model

The product table starts with an ID, SKU, and stock count. The application has two products. We add reserved_units to the model:

reserved_units: Mapped[int] = mapped_column(
    Integer, default=0, server_default=text("0")
)

default=0 supplies a value through SQLAlchemy’s insert path. server_default=text("0") describes a PostgreSQL default. The latter matters to existing rows and writers that do not use this model.

In this workflow, Alembic materializes the model in a disposable development database. Ptah handles migration execution on the application database. Alembic’s env.py reads Base.metadata; its version table lives in alembic_history, while application objects live in public.

alembic revision --autogenerate -m "reserved units"
alembic upgrade head

Review Alembic’s generated revision before applying it to development. Our revision added an integer column with nullable=False and a server default of zero.

Inspect the development schema with Ptah

The handoff uses PostgreSQL’s stored schema, read directly by Ptah:

ptah schema inspect --db-url "$MODEL_DB_URL" \
  --schemas public --format hcl > schema.hcl

The result contains the product table and its server defaults. Restricting inspection to public leaves Alembic’s history outside the desired application schema. The output file is the state we’ll review and deploy.

This route uses the model’s existing migration tool and Ptah’s native inspection command. It does not need a Python program that prints DDL.

Generate and verify the deployment migration

DB_URL points to the application database. SHADOW_URL names another disposable database that Ptah can reset while checking the migration history.

ptah migrations generate --db-url "$DB_URL" \
  --schema-file schema.hcl --schemas public \
  --migrations-dir migrations --name reserved_units \
  --shadow-db "$SHADOW_URL"

Ptah generated this addition:

ALTER TABLE "public"."products" ADD COLUMN "reserved_units" integer NOT NULL DEFAULT 0;

The new pair passed shadow apply/rollback/reapply verification. Review the files, update the checksum, and lint the new version before deployment:

ptah migrations hash --dir migrations
ptah migrations lint --dir migrations --dialect postgres --latest 1 --fail-on error
ptah migrations up --db-url "$DB_URL" \
  --migrations-dir migrations --verify-sum
ptah schema compare --db-url "$DB_URL" \
  --schema-file schema.hcl --schemas public --exit-code

Lint had no findings. The existing database applied one pending migration, and the comparison passed. An empty database replayed both the initial table and the new column migration, then matched the same desired schema.

Check writes through the ORM and plain SQL

The SQLAlchemy application reserved two units of KIT-42 and inserted a new product without explicitly setting reserved_units. Its results were:

Product	Stock	Reserved	Available
KIT-42	10	2	8
CABLE-7	5	0	5
ADAPTER-2	3	0	3

A plain SQL insert that also omitted reserved_units succeeded and returned zero. That second write proves the database can supply the value independently of SQLAlchemy.

Remove the server default and see which check notices

We deliberately dropped the default in the disposable application database. The column remained NOT NULL, and its existing values remained intact.

A new SQLAlchemy insert still succeeded: Python supplied zero. The equivalent plain SQL insert failed:

ERROR:  null value in column "reserved_units" of relation "products" violates not-null constraint

Ptah also detected the missing default. Repeating the same schema comparison returned exit code 1 and proposed restoring DEFAULT 0 on reserved_units. Successful ORM writes had masked a real difference in the database’s contract.

We previewed the repair through ptah schema apply --dry-run. The plan was a single statement that restores DEFAULT 0 and leaves the column’s type and NOT NULL alone. We applied it to the lab database. Another comparison passed, and a plain SQL insert again received zero from PostgreSQL. The full repair plan and commands are in the supporting files.

Keep deployment checks independent of the ORM write path

Alembic can already generate and execute migrations. Here it remains the development tool, while Ptah provides the deployment migration, linting, shadow verification, and live-schema comparison used across services. Only Ptah runs schema changes on the application target in this workflow.

Commit the reviewed desired schema and Ptah migrations with the model change. Test the application through SQLAlchemy, and compare the deployed schema too. An ORM default can make a write succeed; a schema check tells us whether the database still provides the behavior promised to every writer.

Put Ptah to work

Example files

← All articles