Tests

A test projects a list of fields and asserts on the SQL the planner generates for them. Tests live in tests/*.yml, one per file, and run on the service on every zsql deploy, zsql check and zsql test. They catch a changed join route, a lost aggregate, a renamed column, before an application does.

Format

name: store net paid by category
projections:
  - Item Category
  - Store Net Paid
assert_regex: sum\(.*ss_net_paid.*\)

A test may carry both assertions. There is no filters, context or spec key: a test is projections only, planned with no security context. Policies therefore do not fire in tests; test the model, not the policy.

Missing pieces fail the deploy: Test must have a name, Test must have at least one field in 'projections', Test must have at least one assertion (assert_sql or assert_regex).

zsql new test

$ zsql new test "store net paid by category"
create tests/store_net_paid_by_category.yml

The template, verbatim:

# A planner test: project these fields and compare the SQL. Runs on every deploy.
name: store net paid by category
projections:
  - Category
  - Revenue
# assert_sql: |
#   SELECT ...
assert_regex: SELECT

Replace the projections with your fields and tighten the assertion.

A worked example

The model: Store Sales (fact, cost: 100) joins Item (cost: 10) through store_sales_item on ss_item_sk = i_item_sk. Store Net Paid is sum(ss_net_paid), Item Category is i_category.

tests/store_net_paid_by_category.yml:

name: store net paid by category
projections:
  - Item Category
  - Store Net Paid
assert_sql: |
  SELECT
  	T1."i_category" AS "Item Category",
  	sum(T0."ss_net_paid") AS "Store Net Paid"
  FROM
  	store_sales T0
  	JOIN item T1
  		ON T0.ss_item_sk = T1.i_item_sk
  GROUP BY
  	T1."i_category"

$ zsql deploy
deploy tpcds (6214 bytes) as tpcds/main (the production branch) to https://app.0sql.io
deployed tpcds/main: 6 tables, 42 fields, 5 joins, 9 paths, 1 policies
PASSED store net paid by category
tests: 1 passed, 0 failed

Now someone changes the join to join: left. The next deploy:

$ zsql deploy
deploy tpcds (6218 bytes) as tpcds/main (the production branch) to https://app.0sql.io
deployed tpcds/main: 6 tables, 42 fields, 5 joins, 9 paths, 1 policies
FAILED store net paid by category
       --- generated
       SELECT
       	T1."i_category" AS "Item Category",
       	sum(T0."ss_net_paid") AS "Store Net Paid"
       FROM
       	store_sales T0
       	LEFT JOIN item T1
       		ON T0.ss_item_sk = T1.i_item_sk
       GROUP BY
       	T1."i_category"
       --- expected
       SELECT
       	T1."i_category" AS "Item Category",
       	sum(T0."ss_net_paid") AS "Store Net Paid"
       FROM
       	store_sales T0
       	JOIN item T1
       		ON T0.ss_item_sk = T1.i_item_sk
       GROUP BY
       	T1."i_category"
tests: 0 passed, 1 failed
Error: tests failed

The branch is deployed (the HTTP call succeeded), the CLI exits non-zero, and the diff is on screen.

Output

Each test prints as PASSED, FAILED or ERROR followed by its name. A failed assert_sql prints --- generated and --- expected blocks; a failed assert_regex prints the generated SQL (the response also carries the pattern). ERROR means the planner could not plan the projections at all, or the regex did not compile (bad assert_regex: ...); its message is printed under the name. The last line is tests: N passed, N failed.

On the HTTP side the same data is test_results in the deploy response and the body of POST /projects/{uid}/branches/{branch}/test:

{"passed": 0, "failed": 1, "results": [
  {"name": "store net paid by category", "status": "failed", "file": "store_net_paid_by_category.yml",
   "expected_sql": "SELECT T1...", "generated_sql": "SELECT T1..."}
]}

zsql test

$ zsql test
PASSED store net paid by category
PASSED catalog quantity by month
PASSED date range
tests: 3 passed, 0 failed

Runs the tests stored with the deployed branch, on the service, without sending the project again. Exits non-zero with tests failed when any fail.

Tips

• Prefer assert_regex for the thing you care about. sum\(.*ss_net_paid.*\) survives alias changes, a reordered GROUP BY and a switch of adapter; a full assert_sql breaks on every one of them. Use assert_sql when the whole statement is the contract.
• Assert the join, not the whitespace. JOIN item or FROM\s+store_sales T0\s+JOIN item pins the route; whitespace is already normalised for assert_sql and . matches newlines in assert_regex.
• One concern per file. A test per fact-to-dimension route and one per compound measure keeps failures readable.
• Use the repl to write them. zsql sql --expr "item category, store net paid" prints the SQL to paste into assert_sql.

Next steps

• Deploying
• CI/CD
• Querying from the CLI