Run database migrations
Apply schema changes to a Pier Postgres from your laptop, from CI, or from inside the app
Goal. api/migrations/001_init.sql is applied to main, repeatably, and the same
command works from a CI job.
From your laptop
pier psql main -- -v ON_ERROR_STOP=1 -f api/migrations/001_init.sql→ Connecting as cli_admin_68f5fe5b (role: pier_admin, expires 15:32 UTC)
CREATE TABLE
-v ON_ERROR_STOP=1 makes psql exit non-zero on the first error, so a failed migration
fails the command. Check the result:
pier psql main -- -c '\dt'→ Connecting as cli_admin_17697ea5 (role: pier_admin, expires 15:32 UTC)
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+------------
public | customers | table | pier_admin
(1 row)
From CI
The same command runs anywhere the pier CLI is logged in (Scripting):
pier psql main -e prod -- -v ON_ERROR_STOP=1 -f api/migrations/001_init.sqlRun it before pier deploy when the new code requires the new schema, and after it when
the old code must keep working during the roll.
From inside the app
When the image carries a migration tool, run it in a running instance with the environment the app already has:
pier ssh api -- ./migrate uppier ssh <ref> -- <command> runs one command and returns its exit status.
Variations
- Migration frameworks (Prisma, Alembic, goose, Flyway, …) take a URL:
DATABASE_URL="$(pier pg credentials main --uri)" npx prisma migrate deploy. - A backup first:
pier backups create main;pier backups ls mainlists what is restorable. - Staging first:
-e stagingruns the same file against the staging instance.
See also
Use Postgres from your laptop ·
pier psql · pier backups ·
pier ssh