PR preview databases

For every pull request, give your tests an isolated database that carries prod’s schema — tables, indexes, triggers, views — and starts empty, then disappears on its own when the PR merges or closes. No “shared staging that everyone steps on,” no manual cleanup. Run your migrations and seed whatever fixtures the tests need.

The cleanest way is the Branches API, which gives you idempotent create-or-reset by ref. The older fork-and-delete pattern still works.

With branches (recommended)

name: Preview DB

on:
  pull_request:
    types: [opened, reopened, synchronize, closed]

jobs:
  preview:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v5

      - name: Create or reset PR branch
        if: github.event.action != 'closed'
        run: |
          curl -fsS -X PUT \
            -H "Authorization: Bearer ${{ secrets.PERSQL_TOKEN }}" \
            -H 'Content-Type: application/json' \
            -d '{"ttlDays": 7}' \
            https://api.persql.com/api/namespaces/acme/databases/main/branches/pr-${{ github.event.number }}

      - name: Run tests against the preview
        if: github.event.action != 'closed'
        env:
          DATABASE_URL: https://api.persql.com/v1/db/acme/main-pr-${{ github.event.number }}
          DATABASE_TOKEN: ${{ secrets.PERSQL_TOKEN }}
        run: |
          npm ci
          npm run migrate
          npm test

      - name: Tear down on close
        if: github.event.action == 'closed'
        run: |
          curl -fsS -X DELETE \
            -H "Authorization: Bearer ${{ secrets.PERSQL_TOKEN }}" \
            https://api.persql.com/api/namespaces/acme/databases/main/branches/pr-${{ github.event.number }}

The PUT is idempotent: the first push creates the branch from the parent’s current schema; every later push resets it back to that schema. The branch starts empty — the npm run migrate (and any seed) step populates it. The TTL is a belt-and-braces fallback in case the close webhook is missed — the daily cron sweeps any branch whose expiresAt has passed.

With CLI fork (older pattern)

- name: Fork prod for this PR
  if: github.event.action != 'closed'
  run: |
    persql login --token ${{ secrets.PERSQL_TOKEN }}
    persql db fork acme/app pr-${{ github.event.number }} --ttl 7d

- name: Tear down on close
  if: github.event.action == 'closed'
  run: |
    persql login --token ${{ secrets.PERSQL_TOKEN }}
    persql db delete acme/pr-${{ github.event.number }} --force

Forks are not idempotent — re-running the create step on a later commit returns 409. The branches API is preferred for that reason.

Why this is useful

Migration safety. Your ALTER TABLE and new migrations run against the real production schema in isolation, so you catch a broken or out-of-order migration before it ever touches prod.
No shared staging conflicts. Each PR gets its own database; nobody’s WIP migration breaks anybody else’s tests.
Disposable + isolated. Seed whatever fixtures the test needs; the branch dies on PR close (or after the TTL) and never touches prod data.
Idempotent in CI. Re-running the workflow on a new commit resets the branch — no “does it already exist?” bookkeeping.

TTL semantics

TTL is in whole days, capped at 30. Wider ranges aren’t supported.
The daily 04:00 UTC cron deletes any database whose expiresAt is in the past — at most 50 per tick so a backlog can’t starve the worker.
Deletion is a hard drop: the database is destroyed and the registry row is removed. Tokens that referenced the database return 404 from the next call.
For branches, every PUT resets the TTL (or clears it if you omit ttlDays). For raw forks, TTL is set at creation and can’t be renewed.

Variations

Seeding data. Branches start empty. If your tests need rows, run a seed script or load a fixture after the reset step — e.g. persql db import <branch> fixtures.sql. Keep fixtures small and deterministic so each PR run is reproducible.

Per-feature branches. Same pattern but use a feature branch name (feat/checkout-redesign) as the ref instead of a PR number. Add your own teardown step when the branch is deleted.

Local mirror. persql db export <branch> dumps SQL you can apply to a local SQLite. Handy when CI fails and you want to debug interactively.

Caveats

A branch is schema-only, so it adds essentially no storage at creation regardless of how large the parent is; it bills only for the fixtures you load and the requests your tests make — see Pricing & billing.
API tokens are workspace-scoped, so the same token authenticates the PR branch as authenticates prod. Most teams use a CI-only token with Manage permission and rotate it periodically.