Skip to content

boringSQL/regresql

 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

134 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

RegreSQL

Regression testing for SQL queries. Write queries, capture expected results, compare when their cost or I/O characteristics change, detect when something changes.

RegreSQL finds your *.sql files, runs them against PostgreSQL, and compares output to known-good baselines. When a query's result changes unexpectedly, you'll know immediately.

Installing

Homebrew (macOS)

brew tap boringsql/regresql https://github.com/boringsql/homebrew-regresql
brew install regresql

Go

go install github.com/boringsql/regresql@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

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 junit -o results.xml

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

regresql baseline

Creates EXPLAIN cost baselines to detect query plan regressions:

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/
  fixtures:
    - users
    - products
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

Test Fixtures

Fixtures define test data declaratively:

# regresql/fixtures/users.yaml
fixture: users
data:
  - table: users
    rows:
      - id: 1
        email: test@example.com
        name: Test User

Generated Data

fixture: large_dataset
generate:
  - table: customers
    count: 1000
    columns:
      id: { generator: sequence, start: 1 }
      email: { generator: email, domain: example.com }
      name: { generator: name, type: full }

Generators: sequence, int, decimal, string, email, name, uuid, date_between, and more.

regresql fixtures list
regresql fixtures validate
regresql fixtures apply users    # for debugging

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.

License

BSD-2-Clause

Packages

No packages published

Languages

  • Go 100.0%