RegreSQL

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 Fixturize and dryrun. See the project page for the full overview.

Installing

Homebrew (macOS)

brew tap boringsql/boringsql
brew install regresql

Go

go install github.com/boringsql/regresql/v2@latest

Binary goes to $GOPATH/bin (defaults to ~/go/bin).

Requirements

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 postgresql

Quick Start

# 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/

# Edit plan files to set parameter values (if your queries have parameters)
vim regresql/plans/src/sql/users.yaml

# Capture expected output
regresql update

# Run tests
regresql test

Why RegreSQL

• Language-agnostic — works with any .sql file, no specific language, ORM, or database extension required
• Snapshot testing for SQL — expected results committed as fixtures, diff when something changes
• 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
• Migration testing — run queries before and after a migration, see exactly what changed

Core Commands

regresql discover

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.

regresql add <path...>

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 pattern

regresql remove <path...>

Removes 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 deleted

regresql update

Captures current query output as the expected baseline:

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 change

regresql test

Runs 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 protocol

Output formats: console (default), pgtap, junit, json, github-actions

regresql baseline

Tracks EXPLAIN cost estimates/I/O buffers over time. When a schema change or migration causes a query plan regression. Cost spikes, sequential scans on large tables — you'll catch it in CI before it reaches production.

regresql baseline
regresql baseline --analyze          # include actual timing

SQL Query Files

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.

Query Parameters

Named (:param) and positional ($1) parameters are supported. Set values in plan files:

# regresql/plans/src/sql/users.yaml
"1":
  id: 42
"2":
  id: 100

Each numbered entry runs as a separate test case.

Query Metadata

Control test behavior per-query:

-- name: expensive-report
-- regresql: nobaseline, noseqscanwarn
SELECT ...

Options: notest, nobaseline, noseqscanwarn, difffloattolerance:0.01

Snapshots

Snapshots capture database state for reproducible tests. Build once, restore before each test run.

# regresql/regress.yaml
pguri: postgres://localhost/mydb
snapshot:
  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 testing

Snapshots track hashes of schema and migrations. If sources change, regresql test fails with instructions to rebuild.

Snapshot Versioning

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 current

Fixturize

RegreSQL 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.

Migration Testing

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.

Ignoring Files

Create .regresignore (gitignore syntax):

*_test.sql
db/migrations/

Or in config:

# regresql/regress.yaml
ignore:
  - "*_test.sql"
  - "db/migrations/"

Configuration

# regresql/regress.yaml
pguri: postgres://localhost/mydb
root: "."

plan_quality:
  ignore_seqscan_tables:
    - genre
    - media_type

snapshot:
  schema: db/schema.sql
  migrations: db/migrations/
  fixtures: [users, products]

File Structure

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)

History

Fork of the original regresql by Dimitri Fontaine, from Mastering PostgreSQL. Extended as part of the boringSQL project.

Learn more

• 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

License

BSD-2-Clause