SqlProof Class

SqlProof is the entry point for a test suite. Create one instance from a schema file, connection string, or config object, then use @sqlproof, check(), or invariant().

Constructors

from sqlproof import SqlProof

proof = SqlProof.from_schema_file("./schema.sql")
proof = SqlProof.from_connection_string("postgresql://localhost:5432/testdb")

For explicit configuration:

from sqlproof import SqlProof, SqlProofConfig

proof = SqlProof.from_config(
    SqlProofConfig(
        schema_file="./schema.sql",
        image="postgres:16",
        reuse_container=True,
    )
)

Exactly one of schema_file or connection_string must be provided.

Decorator Usage

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)

Imperative Usage

def check_totals(db):
    rows = db.query("SELECT total FROM orders")
    assert all(row["total"] >= 0 for row in rows)


proof.check(
    name="order totals are non-negative",
    sizes={"customers": 10, "orders": 50},
    property=check_totals,
    runs=100,
)

Invariants

Use invariant() when a SQL query should return zero rows (or, with expect_empty=False, at least one row) for every generated dataset.

Each run loads a generated dataset into the database inside a savepoint, executes query, and rolls the savepoint back. If the query returns rows when expect_empty=True (or none when expect_empty=False), invariant() raises SqlProofPropertyFailure with the dataset as the counterexample. Errors from the database, such as an unknown table or a syntax error, are raised as-is and never count as a pass.

Pass seed=1234 to check() or invariant() to repeat a search with the same schema, strategies and dependency versions. An explicit seed disables Hypothesis’s example database for that run. Without a seed, Hypothesis’s normal profile and --hypothesis-seed settings apply. Failing invariants shrink their datasets, and the exception’s counterexample includes the seed used (or None for a derandomized run), the run that first failed and the number of shrinking calls after it. It is also saved under .sqlproof/failures/, as check() does.

A recorded seed repeats the random search, not necessarily the failure. When Hypothesis replays a failure saved in its example database by an earlier run, the seed of the current run never searched for it, and passing it back turns the database off. Keep the .hypothesis/ directory or the counterexample file to reproduce such a failure. Each property and invariant has its own example-database entry, so one passing property never discards another’s saved failure.

invariant() needs a database connection (from_connection_string) to run arbitrary SQL. A proof built only from a schema file evaluates queries in memory, which handles just a plain SELECT <columns> FROM <table>. Any other query, including one with a WHERE clause or a JOIN like the example below, raises SqlProofUsageError instead of being evaluated with its predicates ignored.

proof.invariant(
    name="no orphan orders",
    sizes={"customers": 10, "orders": 50},
    query="""
        SELECT o.id
        FROM orders o
        LEFT JOIN customers c ON o.customer_id = c.id
        WHERE c.id IS NULL
    """,
    expect_empty=True,
)

External Tables

When your schema has FKs into a table SqlProof shouldn’t generate (e.g. Supabase’s auth.users), register an ExternalTableSpec so the generator samples FK values from the live external parent:

from sqlproof import ExternalTableSpec, SqlProof
from hypothesis import strategies as st

proof = SqlProof.from_connection_string(
    "postgresql://...",
    external_tables={
        "auth.users": ExternalTableSpec(
            primary_key="id",
            seed_count=st.integers(min_value=1, max_value=5),
            sample=lambda db: [r["id"] for r in db.query(
                "SELECT id FROM auth.users WHERE email LIKE 'sqlproof_%%'"
            )],
        )
    },
)

See the Supabase guide for a full walkthrough.

Stateful tests

For invariants that only surface across sequences of mutations (pagination, windowed aggregates, RLS membership churn), use a state machine via proof.run_state_machine:

from sqlproof.testing import SqlProofStateMachine
from hypothesis.stateful import rule, invariant

class MyMachine(SqlProofStateMachine):
    @rule()
    def step(self): ...

    @invariant()
    def some_invariant(self): ...

def test_x(proof: SqlProof):
    proof.run_state_machine(MyMachine)

See the stateful testing API for details.

Cleanup

SqlProof is a context manager:

with SqlProof.from_schema_file("./schema.sql") as proof:
    proof.invariant(...)