Skip to content
onwardpg
Esc
↑↓navigate↵open⌘Jpreview
On this page

onwardpg

Plan and verify PostgreSQL schema migrations for rolling deployments.

onwardpg turns your desired PostgreSQL schema into SQL for a rolling deployment. It generates expand SQL to run before the application deploys and contract SQL to run after the old processes have drained. It verifies both in disposable PostgreSQL databases. Your deployment system runs the SQL in production.

Jump to the quick start, or read on for the deployment problems it addresses.

Why do I need this?

Locally, you can stop the app, migrate the database, and restart it. During a rolling deployment, old and new instances share the same database. A worker may still be processing yesterday’s job while the new release serves requests. A migration can execute successfully and still break those processes.

These changes need more than a single schema cutover:

  • Renaming a column. Rename display_name to full_name before deploying, and old queries fail. Deploy first, and new queries fail. For supported same-type renames, onwardpg adds a second column and a trigger that keeps writes through either name synchronized. You choose how to backfill existing rows. Contract checks that the values agree and removes the bridge after old callers drain.
  • Adding a required field. status text NOT NULL can reject inserts from old code that doesn’t supply a status. Expand adds it as nullable. The new release writes the field and tolerates nulls during rollout; contract runs your cleanup, checks for remaining nulls, and applies NOT NULL.
  • Changing allowed values. Suppose a CHECK accepts email and slack, while the new release writes slack and push. Either endpoint constraint rejects one version’s writes. Expand can remove the old check; contract uses your rule for retired values, checks the data, and restores enforcement. That temporarily weakens the database guarantee, which the plan reports.
  • Changing a type beneath views and indexes. Converting age text to age integer needs a rule for blanks and malformed values, a way to support both application versions, and work on dependent views. A materialized view may also need rebuilding or refreshing. onwardpg identifies those dependencies, asks for the conversion and compatibility SQL, and verifies the completed bundle.

The order is expand → deploy → drain → contract. The new application must handle the overlap schema and the final schema, including values old writers can still produce. If that cannot fit one deployment, split the change across releases. onwardpg asks for the missing decisions and SQL; it cannot infer application behavior or a backfill’s business meaning from the schema.

Quick start

Install

brew install jokull/tap/onwardpg
onwardpg version

This is a preview release. Release archives and installation from Go or source are also available.

You’ll need PostgreSQL 15–18 for scratch databases. Use a dedicated local or CI cluster: onwardpg creates and drops databases and temporary login roles there. The scratch login needs permission to manage both. Never use a production or shared application cluster for scratch.

For a local walkthrough with Docker:

docker run --name onwardpg-scratch \
  -e POSTGRES_PASSWORD=onwardpg \
  -p 127.0.0.1:55432:5432 -d postgres:18

export ONWARDPG_SCRATCH_DATABASE_URL='postgres://postgres:onwardpg@localhost:55432/postgres'

Wait for PostgreSQL to accept connections before continuing.

Describe the starting schema

In an empty project directory, create schema.sql:

CREATE SCHEMA app;

CREATE TABLE app.bookings (
  id bigint PRIMARY KEY
);

Add .onwardpg.toml alongside it:

version = 1
bundle_root = "migrations/onward"

[targets.app]
schema_file = "schema.sql"
scratch_database_env = "ONWARDPG_SCRATCH_DATABASE_URL"

Check the configuration and record the starting schema:

onwardpg config check
onwardpg init

init creates and verifies the baseline migration history. In an existing project, initialize from the schema you have before the feature change. It does not change your application database.

Add a column

Suppose bookings now need a status. Replace schema.sql with:

CREATE SCHEMA app;

CREATE TABLE app.bookings (
  id bigint PRIMARY KEY,
  status text
);

The file always describes the complete desired schema. Run:

onwardpg plan add-booking-status

The bundle is written to migrations/onward/app/add-booking-status/. Its plan.json lists the statements and hazards; phases/expand.sql contains:

ALTER TABLE "app"."bookings" ADD COLUMN "status" text;

There is no contract phase for this change. Old code can keep inserting rows without a status. Check that inserts name their columns and that readers can handle an extra result column if they use SELECT *.

Review and verify

Read the generated SQL and hazards, then run:

onwardpg verify

Verification replays the migration in disposable databases, runs its assertions, and checks that the final schema matches schema.sql with no remaining diff. Unresolved SQL placeholders fail verification.

Commit the schema, configuration, and generated migration files together. In CI, select the bundle explicitly and check its saved verification evidence:

onwardpg verify --bundle add-booking-status --check

Use the same PostgreSQL major for scratch and production. Scratch also needs the roles, languages, and extension packages referenced by your schema. Project SQL runs as a restricted database owner, so extensions requiring superuser execution need separate provisioning.

Required columns

Before merging the feature, suppose you decide every booking must have a status. Change the column in schema.sql to status text NOT NULL and rerun:

onwardpg plan

This revises the same bundle. Expand still adds a nullable column: the old application will omit status until it drains. The new application must write a status and tolerate nulls when reading during that overlap.

The planner asks how those nulls will be resolved. Choose assert_only if another operation will fill them, manual_sql to supply cleanup in this plan, or split_plan to defer enforcement to a later release.

For this example, assume old bookings should become pending and the table is small enough for one update. Supply that decision:

onwardpg plan \
  --hint '{"kind":"reconcile","object":"column","name":["app","bookings","status"],"strategy":"manual_sql"}' \
  --hint '{"kind":"manual_sql","action":"reconcile_contract_sql","object":"column","name":["app","bookings","status"]}'

The command reports needs_sql_edits and names the file to edit. In phases/contract.sql, replace the placeholder inside the edit markers with:

UPDATE "app"."bookings"
SET "status" = 'pending'
WHERE "status" IS NULL;

Keep the surrounding generated SQL. After this cleanup it checks that no nulls remain, then enforces the constraint:

ALTER TABLE "app"."bookings" ALTER COLUMN "status" SET NOT NULL;

Run onwardpg verify again. The cleanup rule is yours to review; the schema cannot tell onwardpg what an old booking means. For a large table, use an operator_batched operation with a completion check, or split the work across releases. See the contract reference for those options.

Day-to-day use

Run onwardpg plan as you edit the schema. It updates the active migration instead of adding a migration for every draft. After rebasing onto new migration history, run it again. Decisions and SQL edits carry forward when they still apply; changes to their underlying schema can require another answer or review.

onwardpg plan       # Revise the active plan
onwardpg status     # Inspect it
onwardpg verify     # Verify the current files

For renames, type conversions, and destructive changes, follow the questions in next_actions. Answer with the reported --hint and edit only the named SQL sections. Exit code 2 means the plan needs input or SQL edits. A rename needs an explicit identity decision; a type conversion needs a rule for the data. Adding another compatible column can introduce a new rename candidate, so a previous rename decision may need confirming again. That keeps a changed set of possible identities from silently reusing an earlier answer. Use the complete suggested command (argv) for each answer: it can carry confirmed decisions that have not yet been saved while your edited SQL waits for the remaining choices.

Use your framework’s schema

Instead of maintaining SQL by hand, export your framework’s complete desired PostgreSQL DDL. Replace schema_file with a command that writes it to stdout:

schema_command = ["pnpm", "--silent", "schema:export"]

schema:export is a script you provide. It must produce the same DDL for the same models, including everything needed to build the schema from empty. See the schema input requirements and existing-project recipes for Drizzle, Prisma, and Django. Choose one executor for production DDL; running both framework migrations and the onwardpg bundle would apply the same change twice.

Adopt from an already-deployed schema: export the old models and run init before the feature edit. Keep existing framework migration history, but do not replay the onwardpg baseline against an existing database. Django’s example exports migration state, so run makemigrations after model changes and use reviewed state-only migrations when onwardpg takes over their DDL.

Connect a development database

Optionally add these fields to [targets.app]:

dev_database_env = "ONWARDPG_DEV_DATABASE_URL"
dev_mode = "workspace"

Set that environment variable to your development database URL. onwardpg reads its catalog and reports separate SQL to bring it up to date. In workspace mode, objects left by another branch are preserved. Migration history still comes from accepted bundles. The development and scratch databases must use the same PostgreSQL major.

This comparison has its own --dev-hint decisions. A ready durable bundle can still return exit code 2 because local reconciliation needs an answer; inspect the durable, development, and next_actions sections separately.

Work with a coding agent

Give the agent the onwardpg skill:

Read and follow https://onwardpg.solberg.is/skill.md.
Plan this schema change, answer decisions from the application code,
verify the bundle, and report the remaining deployment checks.

Deployment

Each bundle surrounds one application deployment:

expand → deploy application → drain old processes → contract
  1. Apply expand before deploying the new code. Review lock, scan, index-build, and transaction requirements in the plan. Keep nontransactional batches out of transactions.
  2. Deploy the application. It must work both before and after contract.
  3. Drain old processes. Include workers, scheduled jobs, queues, connection pools, and any other client that could still write using the old schema.
  4. Check readiness, then apply contract. Finish the specified data cleanup and require a ready result before enforcing constraints or removing old interfaces.

For bundles with contract work, your release system supplies expiring writer records in deploy-readiness.json. The file identifies the exact plan, bundle, environment, and release. Check it against production through a restricted read-only observer:

onwardpg contract check \
  --target app \
  --bundle add-booking-status \
  --environment production \
  --database-env PROD_READONLY_DATABASE_URL \
  --evidence deploy-readiness.json

ready permits proceeding. reconciliation_required identifies cleanup your release runner must complete before checking again. needs_evidence, blocked, and stale need resolution. The contract reference defines the evidence file, observer permissions, and cleanup order. Contract SQL repeats the data assertions at enforcement time.

Verification proves the migration runs and reaches the requested schema in scratch. It does not prove your production data is valid, locks will be short, replicas can keep up, or old processes have drained. Review those against the actual workload. Plans are forward-only; arrange your application rollback procedure before deploying.

Reference

Use onwardpg <command> --help for flags. The repository has the detailed CLI reference, supported features, bundle format, and safety model.

onwardpg is MIT licensed. Source and issues are on GitHub.

Was this page helpful?