Join us Nov 24 to watch Insights Agent turn slow queries into PRs. RSVP
Navigation

Reference

Spin Up and Tear Down a Neki Database per Pull Request with the PlanetScale CLI

This guide gives each pull request its own sharded Neki database for integration tests and deletes it when the pull request closes. One script creates a database named after the pull request, adds shards, applies the schema and the data topology, writes a connection file for the test job, and tears everything down. A second command removes databases whose pull requests are no longer open, so a job that never ran its teardown does not leave a database billing.

What you will build

pr-env.sh up 41      pscale database create pr-41 ... -> role -> 2 shards -> schema + topology -> pr-41.env
(test job)           source pr-41.env, run the integration tests against pr-41
pr-env.sh down 41    pscale database delete pr-41 --force
pr-env.sh sweep 41   delete every pr-* database whose number is not in the list of open pull requests

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0), psql and jq, create a sharded Neki database per pull request for integration tests and delete it afterwards.

Requirements:
- Write pr-env.sh with the commands up <number>, down <number> and sweep <open numbers>. Name each database with a prefix and the pull request number, for example pr-41. Database names use lowercase letters, digits and hyphens.
- up creates the database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 0 for a single-node shard that is cheap to keep for a few minutes, creates an admin role with --ttl 4h, waits until the database answers, adds two shards with pscale branch shard create, waits until every shard answers a direct SELECT 1 with SET __neki.shard, applies the schema and the data topology, and writes the connection settings to <name>.env with mode 600.
- Keep the shard ids out of the topology file by looking them up with SELECT uid FROM __neki.list_shards() WHERE name = 'sh2'.
- down deletes the database with pscale database delete --force. sweep lists databases with pscale database list --format json and deletes the ones that have the prefix and a number that is not in the list.
- Run a smoke test against the pull request database that checks the routing with EXPLAIN (NEKI_PLAN, COSTS OFF).
- Run the verification step below before finishing.

Prerequisites

  • A PlanetScale organization with access to the Neki Platform Preview. Create an account if you do not have one, and see Neki availability and access for how an organization administrator joins the preview.

  • pscale 0.344.0 with a service token that can create databases, psql 18.6, jq, and python3:

export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version

1. Write the script

cat > pr-env.sh <<'EOF'
#!/bin/bash
# pr-env.sh up <number> | down <number> | sweep <open numbers, comma separated>
set -u
PREFIX=${PREFIX:-pr-}
ORG=$PLANETSCALE_ORG

up() {
  db=$PREFIX$1
  pscale database create "$db" --engine neki --org "$ORG" --region us-east \
    --cluster-size PS-10-AWS-ARM-NEKI --replicas 0 --wait > /dev/null || return 1

  pscale role create "$db" main admin --inherited-roles postgres --ttl 4h --org "$ORG" --format json > "$db.json" || return 1
  echo "export PGHOST=$(jq -r .access_host_url "$db.json") PGUSER=$(jq -r .username "$db.json") PGPASSWORD=$(jq -r .password "$db.json") PGPORT=5432 PGDATABASE=postgres PGSSLMODE=require" > "$db.env"
  chmod 600 "$db.env"; rm "$db.json"
  . "./$db.env"
  until psql -At -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done

  pscale branch shard create "$db" main --config-profile default --count 2 --org "$ORG" > /dev/null
  until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "3" ]; do sleep 10; done
  for u in $(psql -At -c "SELECT uid FROM __neki.list_shards()"); do
    until psql -At -c "SET __neki.shard='$u';" -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
  done

  psql -q -v ON_ERROR_STOP=1 -f schema.sql 2>&1 | grep -v NOTICE
  uid() { psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name = '$1'"; }
  sed -e "s/@SHARD_1@/$(uid sh1)/g" -e "s/@SHARD_2@/$(uid sh2)/g" -e "s/@SHARD_3@/$(uid sh3)/g" topology.json \
    | pscale branch data-topology update "$db" main --org "$ORG" --format json | jq -r '.status // "applied"'
  sleep 10
  echo "$db ready, connection settings in $db.env"
}

down() { pscale database delete "$PREFIX$1" --force --org "$ORG" > /dev/null && rm -f "$PREFIX$1.env" && echo "$PREFIX$1 deleted"; }

sweep() {
  open=",$1,"
  for db in $(pscale database list --org "$ORG" --format json | jq -r '.[].name' | grep "^$PREFIX[0-9]*$"); do
    n=${db#$PREFIX}
    case "$open" in *",$n,"*) echo "keep $db" ;; *) down "$n" ;; esac
  done
}

"$@"
EOF
chmod +x pr-env.sh

cat > schema.sql <<'EOF'
CREATE TABLE accounts (account_id bigint PRIMARY KEY, name text NOT NULL, balance_cents bigint NOT NULL DEFAULT 0);
CREATE TABLE ledger (account_id bigint NOT NULL, entry_id bigint NOT NULL, amount_cents bigint NOT NULL, PRIMARY KEY (account_id, entry_id));
EOF

cat > topology.json <<'EOF'
{
  "shard_indexes": { "account": { "type": "xxhash", "columns": ["account_id"] } },
  "shard_groups": [
    { "uid": "@SHARD_1@", "key_ranges": [ { "shard_uid": "@SHARD_1@" } ] },
    { "uid": "by_account", "default_shard_index": "account",
      "key_ranges": [ { "shard_uid": "@SHARD_2@", "end": "80" }, { "shard_uid": "@SHARD_3@", "start": "80" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "accounts": { "shard_group": "by_account" },
    "ledger":   { "shard_group": "by_account" }
  } } } } },
  "default_shard_group": "@SHARD_1@",
  "authoritative_shard_group": "@SHARD_1@"
}
EOF
ls pr-env.sh schema.sql topology.json

Real output:

pr-env.sh
schema.sql
topology.json

The schema and the topology are files in your repository. The topology uses placeholders for the shard ids, which differ in every database.

2. Spin up the database for pull request 41

time ./pr-env.sh up 41

Real output:

applied
pr-41 ready, connection settings in pr-41.env

real	2m0.729s
user	0m0.406s
sys	0m0.335s

The database, role, two shards, schema and topology were ready in the time shown. The connection settings are in pr-41.env, readable only by you, and the role expires after 4 hours.

3. Run an integration test against it

( . ./pr-41.env
  psql -q -c "INSERT INTO accounts SELECT a, 'account ' || a, 1000 FROM generate_series(1, 20) a" \
       -c "INSERT INTO ledger SELECT a, e, 10 FROM generate_series(1, 20) a, generate_series(1, 3) e"
  psql -At -c "SELECT count(*) || ' accounts, ' || (SELECT count(*) FROM ledger) || ' ledger rows' FROM accounts"
  psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT * FROM accounts WHERE account_id = 7" | grep -oE 'Route \[[A-Za-z]+\]'
  psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT * FROM accounts WHERE name = 'account 7'" | grep -oE 'Route \[[A-Za-z]+\]' )

Real output:

20 accounts, 60 ledger rows
Route [EqualUnique]
Route [Scatter]

The data is spread over the two data shards, a lookup by account_id goes to one shard, and a lookup by name goes to all of them. A test that checks the plan catches a query that lost its shard key.

4. A second pull request, and a sweep

./pr-env.sh up 42 > /dev/null
pscale database list --org "$PLANETSCALE_ORG" --format json | jq -r '.[].name' | grep '^pr-[0-9]*$' | sort

echo "-- only pull request 41 is still open"
./pr-env.sh sweep 41
pscale database list --org "$PLANETSCALE_ORG" --format json | jq -r '.[].name' | grep '^pr-[0-9]*$' | sort

Real output:

pr-41
pr-42
-- only pull request 41 is still open
keep pr-41
pr-42 deleted
pr-41

The sweep kept pr-41 and deleted pr-42, because 42 was not in the list of open pull requests. In a pipeline, build the list from your Git host, and run the sweep on a schedule.

5. Tear down when the pull request closes

./pr-env.sh down 41
ls pr-*.env 2>/dev/null | wc -l | tr -d ' '

Real output:

pr-41 deleted
0

Verify the result

pscale database list --org "$PLANETSCALE_ORG" --format json | jq -r '.[].name' | grep -c '^pr-[0-9]*$'

Expected: 0. No pull request database is left.

How it works

Each pull request gets a database of its own, so tests never share rows and a failed test run can be thrown away by deleting the database. up creates the database with --replicas 0, which makes each shard a single node. A shard with no replicas lists at $10 per month, prorated to the millisecond, against $30 with two replicas. A single-node shard has no failover, so use it only for tests. The role has a time to live, so credentials for a forgotten database stop working by themselves. The database name is the only link between a pull request and its database, so the sweep works from names. The Neki pricing page describes how shards are billed.

Common issues

Creating databases at the same time

Start up for different pull requests a minute or more apart, or from a queue that runs one at a time. Several pscale database create calls started together have returned HTTP 500 in earlier testing, and a database takes a few minutes to provision.

A forgotten database keeps billing

A pipeline that is cancelled before down leaves its database running. Run sweep on a schedule with the list of open pull requests.

Names must be valid

Database names take lowercase letters, digits and hyphens. A branch name such as feature/login must be turned into one of those, or you can use the pull request number as this script does.

Connection files hold a password

pr-41.env contains the role's password. Keep it out of the repository and out of build logs, and let down delete it.

Next steps

  • Read the guide on storing the Neki data topology in git and applying it from CI to keep topology.json under review.
  • Read the guide on creating short-lived least-privilege roles for a batch job to give the test job a narrower role than admin.
  • Read the Neki data topology guide for placing tables in a shard group.

verification:
  status: verified
  tested_at: "2026-10-29"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "number of pr-<number> databases left after down and sweep"
  expected_result: "0"