SQL queries can break silently. Schema migrations, data changes, and index modifications can alter query results and tank performance — without any test catching it.
RegreSQL is a language-agnostic SQL regression testing tool for PostgreSQL. It finds your *.sql files, runs them against your database, compares output to known-good baselines, and tracks EXPLAIN plan changes. Detect broken queries and performance regressions before production.
RegreSQL is part of the boringSQL stack alongside qshape, Fixturize and dryrun. See the project page for the full overview.
Homebrew 6.0 requires third-party taps to be trusted before their formulae can load:
brew tap boringsql/boringsql
brew trust --tap boringsql/boringsql
brew install regresqlOn Homebrew 5.x and older, skip the brew trust step. If you'd rather not trust the whole tap, trust just the formula with brew trust --formula boringsql/boringsql/regresql.
Every release publishes static binaries for macOS and Linux (arm64 and x86_64) on the releases page. Download the tarball for your platform, unpack it, and put the binary on your PATH:
# Linux x86_64; swap the target for your platform
curl -L https://github.com/boringSQL/regresql/releases/latest/download/regresql-x86_64-unknown-linux-gnu.tar.xz | tar -xJ
sudo mv regresql /usr/local/bin/Targets: aarch64-apple-darwin, x86_64-apple-darwin, aarch64-unknown-linux-gnu, x86_64-unknown-linux-gnu. Checksums are in checksums.txt on each release.
go install github.com/boringsql/regresql/v2/cmd/regresql@latestBinary goes to $GOPATH/bin (defaults to ~/go/bin).
Snapshot commands need PostgreSQL client tools (pg_dump, pg_restore, psql):
# macOS
brew install libpq
# Debian/Ubuntu
apt install postgresql-client
# RHEL/Fedora
dnf install postgresqlNew to RegreSQL? The getting started guide walks through this from scratch, including plan-regression and CI checks.
# Initialize in your project
regresql init postgres://localhost/mydb
# See what SQL files exist
regresql discover
# Add queries to the test suite
regresql add src/sql/
# Replace REQUIRED in the plan files with parameter values (if your queries have parameters)
vim regresql/plans/src/sql/users.yaml
# Capture expected output
regresql update
# Run tests
regresql testinit connects once to check the URI, then writes regresql/regress.yaml. If it can't connect it exits 2 and writes nothing. Running it again rewrites regress.yaml with only root and pguri, so every hand-edited setting (snapshot:, stats:, ignore:, ...) is lost. Until every REQUIRED is replaced, update, test and baseline stop with an error that names the unfilled bindings.
update is the step to be deliberate about. It captures whatever your queries return right now and stores that as the expected result, so run it against a database whose contents you trust. If you don't have one lying around, Snapshots below builds a reproducible one you can restore before every run.
For a complete worked project to read and run, see the cdstore example.
- Language-agnostic — works with any
.sqlfile, no specific language, ORM, or database extension required - Snapshot testing for SQL — expected results committed as fixtures, diff when something changes
- Migration testing — run queries before and after a migration, see exactly what changed
- EXPLAIN plan baselines — track query costs over time, detect performance regressions automatically
- Sequential scan detection — catch missing indexes before they hit production
- CI/CD ready — JUnit, GitHub Actions, pgTAP, and JSON output formats
- Severity policies — re-map warning and error severities per table, and turn seq scans on critical tables into failures
- Production-stats plan testing — inject real statistics with
--statsand thepg_regresqlextension to reproduce production plans on a small local database
Shows all SQL files and their test status:
$ regresql discover
[+] src/sql/users.sql # all queries have plans
[ ] src/sql/orders.sql # no plans yet
[~] src/sql/products.sql # partial coverage
Use --queries to see individual query status within files.
Adds SQL files to your test suite by creating plan files:
regresql add src/sql/users.sql # single file
regresql add src/sql/ # entire directory
regresql add "src/**/*.sql" # glob patternRemoves files from the test suite:
regresql remove src/sql/old_query.sql
regresql remove src/sql/ --clean # also delete expected/baseline files
regresql remove src/sql/ --dry-run # preview what would be deletedCaptures current query output as the expected output:
regresql update # all queries
regresql update src/sql/users.sql # specific file
regresql update --pending # only queries without expected files
regresql update --interactive # review each changeRuns queries and compares output against expected results:
regresql test
regresql test --run "user" # filter by regexp
regresql test --format github-actions # inline PR annotations
regresql test --format junit -o results.xml # Jenkins/CI
regresql test --format pgtap # TAP protocolOutput formats: console (default), pgtap, junit, json, github-actions
For non-console formats without -o, progress lines (connecting, restore, applied statistics) and the mode status line go to stderr, so stdout holds only the report. The json report carries it under environment.
Exit codes for test:
| code | meaning |
|---|---|
0 |
passed |
1 |
a check failed, or a snapshot or strict settings problem stopped the run |
2 |
cannot connect to the database (with a snapshot restore a failed restore exits 1 first), or skipped tests with --fail-on-skipped |
3 |
config error, or --generic without plan choice mode or on PostgreSQL older than 14 |
10 |
severity violation |
13 |
a query could not be run, a plan still holds REQUIRED, or the stats injection check failed |
14 |
invalid formatter |
15 |
pg_regresql not loadable, or the stats file could not be applied |
update uses 1 (snapshot not found or changed, strict settings mismatch), 2 (cannot connect, same restore caveat as test), 3 (config error), 12 (expected output could not be created, including unfilled REQUIRED or a failed stats injection check) and 15 (stats file could not be applied).
| mode | when | what gates |
|---|---|---|
| buffers | no stats file in use | buffers (or cost when the baseline has no buffer data), spills, q-error, critical plan regressions |
| plan choice (injected stats) | a stats file is in use (--stats or stats.default) |
plan shape only; cost is advisory |
Every run prints mode: ... | pg_regresql: ... | GUCs: ... | PG major: ... first. In plan choice mode analyze.comparison: buffers is ignored. test and update apply the stats file when they restore a snapshot. With --no-restore, or without a configured snapshot, they do not: if the database already carries injected stats the run stays in plan choice mode, otherwise it warns and runs in buffers mode. Plan quality warnings (sequential scans, nested loops) are not suppressed by the low-buffer filter there, so --strict can fail on them.
Injected statistics need PostgreSQL 18 or newer and the pg_regresql extension (pgxn install pg_regresql; regresql doctor checks it is loadable). The stats file is pg_dump --statistics-only output from the database you want to reproduce. test and update apply the file while they restore a snapshot, so the project needs a snapshot of the test database and these paths in regress.yaml. baseline applies it directly to the configured database:
regresql snapshot capture # writes snapshots/default.dump# regresql/regress.yaml
snapshot:
path: snapshots/default.dump # relative to the project root
stats:
default: ../stats.sql # relative to regresql/Then regresql baseline, regresql update and regresql test all run in plan choice mode. baseline and update read only stats.default; they have no --stats flag. regresql config set only handles pguri, so edit the file by hand. test --stats path/to/stats.sql overrides the default for one run, and that path is resolved against the current directory. Without a snapshot restore, test and update do not apply the stats file; --generic then exits 3 unless the database already carries injected stats.
regresql test --generic (plan choice mode, PostgreSQL 14+) prepares each parameterised query and runs your bindings through it, in order, with plain EXPLAIN in a rolled-back transaction. It asks the planner whether the statement would switch to its generic plan after five executions. It fails when it would, and that plan seq scans a relation of 1000 pages or more that the custom plans read through an index.
A pass means these bindings, in this order, do not settle on a generic plan that drops an index. Bindings that are all selective pass, because production would not switch either. List the broad bindings production sends most often first (plan keys sort as strings: 1_broad ... 5_broad, 6_selective), and keep bindings inside the statistics' range. Planner settings must match production. Type parameters the server cannot infer with -- paramtypes: int, timestamptz. Opt a query out with -- regresql: nogeneric.
Reports the mode and whether the environment can support it, without restoring or writing anything:
regresql doctor
regresql doctor --stats stats.sql --format jsonExits 15 when plan choice mode is configured but pg_regresql is missing, when the injection check fails, or when the stats file was dumped from a different PostgreSQL major than the server; 2 when the database is unreachable; 3 on config errors (with --format json a config error is reported as {"ok": false, "error": ...}).
Records EXPLAIN cost estimates and I/O buffers for each query, so a later regresql test can tell when a schema change or migration caused a plan regression. Cost spikes and sequential scans on large tables show up in CI before they reach production.
In analyze mode it also checks cardinality error (q-error), disk spills, and rows processed.
regresql baseline
regresql baseline --analyze # include actual timingbaseline applies the configured stats file before recording plans. Exit codes: 2 cannot connect, 3 config error, 11 baseline directory or plan problems, 13 any query's baseline could not be created, 15 environment not fit to run (pg_regresql not loadable, stats file could not be applied, or the injection check failed).
The point of all this is catching a broken query in a pull request instead of in production. regresql test exits non-zero when a result or plan check fails, so any CI runner will fail the build on it. --format github-actions turns each failure into an inline PR annotation.
Here's a full GitHub Actions job. It spins up a Postgres, points regresql at it, restores the snapshot, and runs the tests:
# .github/workflows/regresql.yml
name: regresql
on: [push, pull_request]
jobs:
test:
runs-on: ubuntu-latest
env:
# overrides pguri from the committed regress.yaml
DATABASE_URL: postgres://postgres:postgres@localhost/postgres
services:
postgres:
image: postgres:17
env:
POSTGRES_PASSWORD: postgres
ports: ["5432:5432"]
options: >-
--health-cmd pg_isready --health-interval 10s
--health-timeout 5s --health-retries 5
steps:
- uses: actions/checkout@v4
- uses: actions/setup-go@v5
with:
go-version: "1.25"
- run: go install github.com/boringsql/regresql/v2/cmd/regresql@latest
- run: regresql snapshot restore
- run: regresql test --format github-actionsDATABASE_URL, when set, overrides the pguri in regress.yaml for every command. That's how you point a CI run (or a one-off local run) at a different database without editing the committed config.
The snapshot restore step assumes you've committed a snapshot (see below). Without one, drop that line and load your schema and data however the rest of your test suite does before regresql test.
RegreSQL tests .sql files, so if your queries come out of an ORM (ActiveRecord, SQLAlchemy, Prisma, Sequelize) you have nothing to point it at. qshape fills that gap. It reads pg_stat_statements from your running app, collapses the many per-ORM variants of each query into one canonical shape, and generates the RegreSQL sql/ and plans/ skeletons:
qshape capture "$DATABASE_URL" > clusters.json
qshape regresql-stub --in clusters.json --out .You get one .sql file per query shape and a plan YAML with placeholder test cases to fill in. From there it's the normal loop above. Run qshape attribute first to have the plans filled with real sampled values instead of placeholders.
RegreSQL works with standard SQL files. Multiple queries per file are supported with -- name: annotations:
-- name: get-user-by-id
SELECT * FROM users WHERE id = :id;
-- name: list-active-users
SELECT * FROM users WHERE active = true;Single-query files don't need annotations—the filename becomes the query name.
Named (:param) and positional ($1) parameters are supported. Set values in plan files:
# regresql/plans/src/sql/users.yaml
"1": # test case 1
id: 42
"2": # test case 2
id: 100Each numbered entry runs as a separate test case.
Control test behavior per-query:
-- name: expensive-report
-- regresql: nobaseline, noseqscanwarn
SELECT ...Options: notest, nobaseline, noseqscanwarn, nogeneric, difffloattolerance:0.01, timeout:5s
Result comparison can ignore named columns, ignore row order, tolerate float differences, and compare JSONB by value.
Snapshots capture database state for reproducible tests. Build once, restore before each test run.
# regresql/regress.yaml
pguri: postgres://localhost/mydb
snapshot:
path: snapshots/default.dump
schema: db/schema.sql
migrations: db/migrations/regresql snapshot build # create snapshot
regresql snapshot restore # restore to database
regresql snapshot info # view metadata
regresql test # auto-restores before testingSnapshots track hashes of schema and migrations. If sources change, regresql test fails with instructions to rebuild.
Tag snapshots for comparison across versions:
regresql snapshot tag v1.0
regresql snapshot tag post-migration --note "After user table refactor"
regresql snapshot list
regresql diff --from v1.0 --to currentRegreSQL is fully integrated with fixturize, providing ability to capture consistent data sub-graphs from a PostgreSQL database and apply them for snapshot building.
For more help check fixturize repository or try regresql fixturize.
Test how migrations affect query output:
regresql migrate --script db/migrations/001_add_column.sql
regresql migrate --command "goose up"Runs all queries before and after the migration, reports differences.
Create .regresignore (gitignore syntax):
*_test.sql
db/migrations/
Or in config:
# regresql/regress.yaml
ignore:
- "*_test.sql"
- "db/migrations/"# regresql/regress.yaml
pguri: postgres://localhost/mydb
root: "."
plan_quality:
ignore_seqscan_tables:
- genre
- media_type
snapshot:
path: snapshots/default.dump
schema: db/schema.sql
migrations: db/migrations/
fixtures: [users, products]Set the DATABASE_URL environment variable to override pguri at run time — useful for CI or pointing a run at a different database without touching the committed file.
regresql/
├── regress.yaml # configuration
├── plans/ # parameter bindings
│ └── src/sql/
│ └── users.yaml
├── expected/ # expected query output
│ └── src/sql/
│ └── users.1.json
├── baselines/ # EXPLAIN cost baselines
│ └── src/sql/
│ └── users.1.json
└── out/ # test run output (for comparison)
Fork of the original regresql by Dimitri Fontaine, from Mastering PostgreSQL. Extended as part of the boringSQL project.
- boringSQL, the blog and project home
- RegreSQL project page, overview and docs
- Regression testing for PostgreSQL queries, the why and how
- RegreSQL as a PostgreSQL extension, running checks from inside the database
- Fixturize and dryrun, companion tools in the suite