Skip to content
Query Doctor

Writing tests for CI

Query Doctor analyzes the queries your CI runs against a real Postgres. It has nothing to say about a query no test exercised, and nothing to read from a mocked repository — a mock hands back canned rows, so the real SQL never runs.

Those are the tests worth writing regardless. An integration test that drives your real data-access code against a real database catches what mocks hide: a query that references a column a migration dropped, a join that returns duplicate rows, an N+1 nobody noticed. A mock only confirms your code called it the way you told the mock to expect — it says nothing about whether the SQL is correct.

What makes these tests worth running

Section titled “What makes these tests worth running”
  • Run against a real Postgres, matched to production. A service or system Postgres in CI, or Testcontainers locally, on the same major version you deploy. Planner behavior, index types, and available SQL features all change between versions. Don’t mock the database — a mock emits no query to analyze, and can’t tell you the query works.
  • Go through your real data-access code. Call the same query-building functions your app calls, rather than hand-writing SQL in the test. The test then exercises the code that ships. Run it through the same instrumented instance your app uses, and the queries carry their source tags too — see Source Code Mapping.
  • Seed representative data. A query over three rows always sequential-scans, and its plan tells you nothing about how it behaves at scale — the missing index hurts in production, not on an empty table. Seed enough rows, with a realistic spread of values, that the planner makes the choices it would make on real data.
  • Cover the paths that matter. Hot reads, queries with real filters and joins, the ones behind your slowest endpoints.
  • Assert on results, not on calls. Insert, query, check what comes back. A test that only checks a mock was called survives any change to the SQL — including one that breaks it.

Running them in CI

Section titled “Running them in CI”

Point your test suite at a real Postgres:

  1. Start Postgres — a service container, or on the runner with auto_explain for per-call-site source (CI Quickstart).
  2. Run migrations and seed so the schema and data are in place.
  3. Run your integration suite against that database.
  4. The analyzer reads the queries that ran and reports on them.

Further reading

Section titled “Further reading”