# Run database migrations

> Apply schema changes to a Pier Postgres from your laptop, from CI, or from inside the app

URL: https://pier.run/docs/guides/run-database-migrations

**Goal.** `api/migrations/001_init.sql` is applied to `main`, repeatably, and the same
command works from a CI job.

### From your laptop

```bash
pier psql main -- -v ON_ERROR_STOP=1 -f api/migrations/001_init.sql
```

```text title="output"
→  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:

```bash
pier psql main -- -c '\dt'
```

```text title="output"
→  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](/docs/cli/scripting)):

```bash
pier psql main -e prod -- -v ON_ERROR_STOP=1 -f api/migrations/001_init.sql
```

Run 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:

```bash
pier ssh api -- ./migrate up
```

`pier 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 main` lists what is
  restorable.
* **Staging first**: `-e staging` runs the same file against the staging instance.

## See also

[Use Postgres from your laptop](/docs/guides/use-postgres-locally) ·
[`pier psql`](/docs/cli/reference/psql) · [`pier backups`](/docs/cli/reference/backups) ·
[`pier ssh`](/docs/cli/reference/ssh)