documentZero Table Dependency

Zero Table Dependency

Zero Table Dependency (ZTD) is a model for unit testing SQL. It evaluates SQL on a real database engine without reading from or writing to any physical table, so that SQL can be tested like a pure function: the input is the data the test provides, and the output is the result of the query.

References

• Zero Table Dependency (ZTD) - Theoretical Overview
• Ultra-fast SQL Unit Testing with rawsql-ts/pg-testkit (Japanese)
• rawsql-ts, the reference implementation for PostgreSQL

The problem

A conventional SQL test depends on physical tables as external state. The tables have to be created by migrations, filled by seeding, and cleaned up afterwards, and tests that share them conflict when they run in parallel. These costs come from the tables, not from the SQL: what SQL operates on is data, and a physical table is only one place to keep it. If SQL is evaluated without physical tables, the costs disappear.

What ZTD is

ZTD evaluates SQL with two properties:

• It uses a real database engine. Parsing, type resolution, functions, and parameter handling behave exactly as in production, unlike a mock.
• It never reads from or writes to a physical table. Evaluation has no side effects and does not depend on external state.

As a result, tests need no migrations, seeding, or cleanup, do not conflict with each other, run in parallel, and give deterministic results.

How ZTD works

ZTD combines four mechanisms.

CTE Shadowing

Every table a query references is overridden by a common table expression (CTE) of the same name that holds the test data. A CTE takes precedence over a physical table of the same name, so the query sees only the CTE, and the physical table is hidden even if it exists.

-- Original
SELECT * FROM users WHERE id = 1;

-- ZTD
WITH users AS (
    SELECT 1 AS id, 'alice' AS name
    UNION ALL
    SELECT 2 AS id, 'bob' AS name
)
SELECT * FROM users WHERE id = 1;

Reads from physical tables are thereby replaced by reads from virtual tables.

Result-SELECT Query

A statement with side effects, such as INSERT, UPDATE, DELETE, or MERGE, is not executed. Instead, the result it would produce is reproduced by a SELECT over the shadowed tables. For unit testing the result is what matters, so this is sufficient.

A statement with a RETURNING clause becomes a SELECT that returns the same rows:

-- Original
UPDATE users SET name = 'Alice' WHERE id = $1 RETURNING id, name;

-- ZTD
WITH users AS (
    SELECT 1 AS id, 'alice' AS name
    UNION ALL
    SELECT 2 AS id, 'bob' AS name
)
SELECT id, 'Alice' AS name FROM users WHERE id = $1;

A statement without RETURNING becomes a SELECT that counts the rows it would affect:

-- ZTD
WITH users AS (...)
SELECT count(*) FROM users WHERE id = $1;

The same applies to each kind of statement:

Query Conversion

CTE Shadowing and Result-SELECT Query require transforming SQL. The SQL is parsed into a syntax tree, and the conversion finds the kind of statement and the tables and columns it uses, adds the shadowing CTEs, and rewrites write statements into SELECT statements. The conversion preserves the meaning of the query, its placeholders, and its syntactic structure.

SQL Interceptor

The conversion is applied at run time, by intercepting the SQL a database driver sends. Existing SQL, handwritten or generated by an ORM, runs unchanged, and the application needs no special API or DSL.

Scope

ZTD is specialized for unit testing the logic of SQL. The following are outside its scope: