# A database branch for every pull request

> Give each pull request its own full copy of the database with CREATE DATABASE, run migrations and tests against it, preview the merge with MERGE DATABASE … DRY RUN, then merge or drop it. A shell script for CI.

Source: https://stolondb.com/guides/branch-per-pull-request/ · Page title: A database branch for every pull request — Stolon

Fork the database when a pull request opens, test against the fork, preview the merge, then merge or drop it. On GitHub, [the Stolon App](https://stolondb.com/guides/github/) does all of this for you; this page is the same flow from any CI.

## The lifecycle

| Pull request event | Database |
| --- | --- |
| opened | `CREATE DATABASE pr_42` while connected to `main` |
| pushed to | run migrations and tests with the connection string's database set to `pr_42` |
| in review | `MERGE DATABASE pr_42 INTO main DRY RUN` shows what would land and applies nothing |
| merged | `MERGE DATABASE pr_42 INTO main`, then `DROP DATABASE pr_42` |
| closed | `DROP DATABASE pr_42`; `main` never saw the changes |

## The script

Call `pr-branch.sh` from your CI's pull-request events. `STOLON` is the connection string up to the branch name, `postgresql://app:<password>@db.stolondb.com/acme.`; keep it in a CI secret.

```sh
#!/bin/sh
# usage: pr-branch.sh open|review|merge|close PR_NUMBER
set -eu
STOLON="${STOLON:?set STOLON in a CI secret}"
BR="pr_$2"

case "$1" in
open)    # fork main once; later pushes reuse the branch
  if ! psql "${STOLON}${BR}" -c "SELECT 1" >/dev/null 2>&1; then
    psql "${STOLON}main" -c "CREATE DATABASE $BR"
  fi
  echo "DATABASE_URL=${STOLON}${BR}" ;;
review)  # what merging would change; nothing is applied
  psql "${STOLON}main" -c "MERGE DATABASE $BR INTO main DRY RUN" ;;
merge)   # atomic: a conflict applies nothing and exits non-zero
  psql "${STOLON}main" -v ON_ERROR_STOP=1 -c "MERGE DATABASE $BR INTO main"
  psql "${STOLON}main" -c "DROP DATABASE $BR" ;;
close)   # abandoned: main never saw it
  psql "${STOLON}main" -c "DROP DATABASE $BR" ;;
esac
```

## What it looks like

```sh
./pr-branch.sh open 101
psql "postgresql://app:<password>@db.stolondb.com/acme.pr_101" -c "ALTER TABLE accounts ADD COLUMN email text"
psql "postgresql://app:<password>@db.stolondb.com/acme.pr_101" -c "INSERT INTO accounts (id, owner, balance, email) VALUES (9, 'zed', 1.00, 'z@example.com')"
./pr-branch.sh review 101
```

```text
CREATE DATABASE
DATABASE_URL=postgresql://app:<password>@db.stolondb.com/acme.pr_101
ALTER TABLE
INSERT 0 1
       kind       |                              detail
------------------+------------------------------------------------------------------
 review           | d05632c80632f50dcd9be2164766b7f189d67281f5378343922465d7e70c6f18
 schema           | ALTER TABLE accounts — ADD COLUMN email
 rows to apply    | 1
 deletes to apply | 0
(4 rows)
```

`./pr-branch.sh merge 101` lands the column and the row in one atomic statement. To merge only what was reviewed, see [the review guide](https://stolondb.com/guides/review-agent-changes/).

## Rehearsal or deploy

- **Rehearsal:** prove the migration on a full copy, then `close` whatever happens.
- **Deploy:** the branch holds only the change (a backfill, a config table), and `merge` is the deploy.