PR-preview database

Spin up an isolated, schema-only branch of acme/orders for every pull request. The branch copies main’s schema into empty tables — no rows are inherited — so it adds essentially no storage until your tests write to it, and no request or row charges accrue until your tests query it. Reclaiming the same ref is idempotent — call it on every CI run with the same pr-${num} ref.

CI step — the easy way

Use the persql/preview-db-action GitHub Action. One step, no teardown:

.github/workflows/preview-db.yml

name: Preview DB
on:
  pull_request:

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

      - name: Claim PerSQL branch
        id: persql
        uses: persql/preview-db-action@v1
        with:
          token: ${{ secrets.PERSQL_TOKEN }}
          database: acme/orders
          branch: pr-${{ github.event.pull_request.number }}
          ttl-seconds: 86400  # 1 day, default

      # Anything in this job can now target the branch via its scoped token.
      - name: Apply migrations + smoke test
        env:
          PERSQL_TOKEN: ${{ steps.persql.outputs.token }}
        run: |
          pnpm dlx @persql/cli@latest db migrate
          pnpm test:e2e

      - name: Comment branch URL on PR
        uses: actions/github-script@v8
        with:
          script: |
            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: `🌿 Preview DB: \`${{ steps.persql.outputs.branch-ref }}\` (expires ${{ steps.persql.outputs.expires-at }})`,
            });

The action returns five outputs: branch-ref, database-slug (address the branch as /v1/db/<namespace>/<database-slug>/...), token (scoped to the branch, masked in logs), expires-at (ISO-8601), and outcome (created on first claim, reset when reclaiming an existing ref).

A complete working example — migrations, smoke test, and the PR comment, with a live pull request you can inspect — is at persql/preview-db-demo.

CI step — without the action

If you’d rather call the API directly (different CI system, or you want to avoid the dependency), upsert the branch with PUT:

- name: Provision preview DB
  env:
    PERSQL_TOKEN: ${{ secrets.PERSQL_TOKEN }}
    PR_NUMBER: ${{ github.event.pull_request.number }}
  run: |
    curl -sf -X PUT "https://api.persql.com/v1/db/acme/orders/branches/pr-$PR_NUMBER" \
      -H "Authorization: Bearer $PERSQL_TOKEN" \
      -H "Content-Type: application/json" \
      -d '{
        "fromRef": "main",
        "expiresAt": "'"$(date -u -d '+14 days' +%FT%TZ)"'"
      }'

The expiresAt field auto-deletes the branch when its lease elapses; you don’t need to wire pr_closed events.

App side

Your preview app reads PR_NUMBER from its env and connects to the right branch — same auth, same SDK:

import { PerSQL } from "@persql/sdk";

const persql = new PerSQL({ token: process.env.PERSQL_TOKEN! });
const db = persql.database("acme", `orders.pr-${process.env.PR_NUMBER}`);

await db.query("SELECT COUNT(*) FROM customers");

Merging schema back to main

Once the PR is approved, fold the branch’s schema changes (new tables, indexes, views) back into the parent. Branches carry schema only, so merge applies DDL, not data — move data with an explicit migration:

await db.branches.merge("pr-42", { mode: "schema" });

The parent is auto-snapshotted first (pre-merge:pr-42) so you can roll back.

Next step

Run db.doctor() on the branch as a CI check — fail the PR if the migration introduced an LLM-hostile schema (missing PKs, ambiguous column names, unindexed FKs).

const report = await db.doctor();
if (report.findings.some((f) => f.severity === "error")) process.exit(1);