October 11, 20264 min read

Take Your First Ptah Project to PostgreSQL

Generate PostgreSQL migrations for the new order service, test them on a scratch database, and verify the same application contract after fresh replay.

Simon HartSolutions Engineer

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

Our SQLite guide created an order worth 3,990 cents. We now want the same service contract on PostgreSQL: known customer, known product, positive quantity, and a queued order.

We ran this guide with Ptah 0.13.0, PostgreSQL 18.6, and Psycopg 3.3.6. It starts from empty PostgreSQL databases; it does not transfer the SQLite file or migrate an existing service’s data between engines.

Generate for the database you will use

The example keeps the same four-table desired schema. This small definition uses types and constraints accepted by both engines. PostgreSQL-specific features would belong in its own schema and tests.

Provision three empty disposable databases: one for the service, one for tests, and one for replay. Set DB_URL, TEST_DB_URL, and REPLAY_DB_URL to their PostgreSQL connection URLs. The example README contains the setup and pinned environment.

ptah migrations generate --db-url "$DB_URL" --schema-file schema.sql --migrations-dir migrations --name shop

Ptah generated a new PostgreSQL migration pair. The up migration creates the tables and foreign keys for this dialect. We reviewed that pair instead of assuming that the SQLite migration was the PostgreSQL release artifact.

The schema file has a different job from the history directory: it is the expected structure used to generate and compare changes. Keeping both makes that expectation explicit after several releases have accumulated.

Test before deploying the service database

The same test file inserts a customer and then an order without a status. This time the test runs against the disposable PostgreSQL test database:

ptah migrations hash --dir migrations
ptah migrations test --dir tests --migrations-dir migrations --db-url "$TEST_DB_URL"

=== MIGRATION TEST ===
PASS  case "order starts queued"
    PASS    step "(unnamed step)" — migrated to latest
    PASS    step "(unnamed step)" — exec ok
    PASS    step "(unnamed step)" — exec ok
    PASS    step "(unnamed step)" — scalar "queued"

1 cases, 1 passed, 0 failed, 0 skipped

The test exits 0 and reads queued. It exercises PostgreSQL execution, not merely the ability to parse the schema as SQL. Give test runs their own database; the runner changes its contents to exercise the migration history.

Then deploy the reviewed files to the service database:

ptah migrations validate --dir migrations
ptah migrations up --db-url "$DB_URL" --migrations-dir migrations --verify-sum

Validation confirmed that the directory matches ptah.sum, and --verify-sum repeated that check before the migration applied. This example uses a privileged disposable database account to keep setup short. The CI example covers a deployment role and the separation between validation and deployment credentials.

Keep the application’s assertion

Our Python client changes its connection to Psycopg and uses PostgreSQL’s parameter placeholders. Its inserts and result assertion retain the same business requirement:

python3 -m pip install -r requirements.txt
python3 application.py

order=1 status=queued total_cents=3990

The complete client uses DB_URL. It inserts one order with two items at 1,995 cents each and asserts the queued state and the total. This is a small sequential client test, not a measurement of PostgreSQL throughput or isolation under concurrent orders.

ptah schema compare --db-url "$DB_URL" --schema-file schema.sql --exit-code

The schema comparison also exited 0. That catches a different class of mistake from the client assertion, such as deploying to a database with a missing constraint or unexpected column.

Prove that an empty database can catch up

A new developer and a new environment both need to reconstruct the service. Apply the same frozen migration directory to the separate replay database:

ptah migrations up --db-url "$REPLAY_DB_URL" --migrations-dir migrations --verify-sum
DB_URL="$REPLAY_DB_URL" python3 application.py
ptah schema compare --db-url "$REPLAY_DB_URL" --schema-file schema.sql --exit-code

order=1 status=queued total_cents=3990

Fresh replay produced the same client result and a second passing schema comparison. The evidence covers the checked-in migration bytes; it does not require regenerating SQL to match a historical timestamp.

Choose the next boundary deliberately

Starting with Ptah let us retain the desired schema, native migration tests, and reviewable SQL while choosing a different database client and engine. The workflow ran in our own test environment using the open-source CLI. Those are practical reasons to adopt it before a new service accumulates a migration history.

A service intended for PostgreSQL should run its release tests on PostgreSQL. Passing the SQLite guide is useful local feedback, but it cannot establish PostgreSQL-specific locking, extensions, permissions, or query behavior. Our next operational step is the CI deployment pipeline. When a later change has to keep existing orders intact, test the data that migration must preserve before it reaches the service database.

Put Ptah to work

Example files

← All articles