Getting Started

SqlProof is a Python property-based testing library for PostgreSQL. It generates random valid datasets that respect your schema constraints, runs your properties against them, and reports the minimal counterexample when one fails.

Prerequisites

• Python 3.11+
• PostgreSQL 13+ or Docker, if using testcontainers
• PostgreSQL client library (libpq) on the host running Python

Install

On Debian/Ubuntu, install build-essential libpq-dev. On macOS, run brew install libpq and put $(brew --prefix libpq)/bin on PATH. On Windows, install PostgreSQL’s client tools and put the directory containing libpq.dll on PATH. PyGreSQL uses these tools even when the database runs in Docker.

pip install sqlproof

Quick Start

Given a schema file:

-- schema.sql
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total NUMERIC(10,2) NOT NULL CHECK (total >= 0)
);

Write a pytest property:

from sqlproof import SqlProof, sqlproof

proof = SqlProof.from_schema_file("./schema.sql")


@sqlproof(proof, sizes={"customers": 10, "orders": 50}, runs=50)
def test_order_totals_non_negative(db):
    rows = db.query("SELECT total FROM orders")
    assert all(row["total"] >= 0 for row in rows)

Run it:

pytest

Connection Modes

SqlProof supports two primary connection modes:

Mode	Options	Docker needed?	When to use
Testcontainers	schema_file only	Yes	Local development with no external DB
Connection string	connection_string + schema	No	CI Postgres, staging, Supabase, Render

What Happens Under the Hood

1. Schema parsing reads your .sql file or introspects a live DB.
2. Topological sort orders tables by FK dependencies.
3. Data generation maps Postgres types to Hypothesis strategies.
4. Run isolation inserts generated data, runs your property, then rolls back or drops the run schema.
5. Shrinking uses Hypothesis to minimize failing counterexamples.