Do you test your SQL?

Yes, I test every SQL query that filters, joins or aggregates with an integration test on the production database engine. A lookup by primary key gets no test of its own when endpoint tests already run it.

Should I unit test a method that is mostly a query?

No, test a method that is mostly a query with an integration test against a real database instead of a unit test with a mocked connection. Such a method keeps its logic in the SQL, which only the database runs.

Do I need to test the sort order of a query?

Yes, test the sort order of a paginated list with two rows that share a sort value. PostgreSQL returns an unpredictable subset under LIMIT unless ORDER BY gives a unique order, so end the sort with a unique column such as the ID.

Should I test SQL queries?

Verdict

Yes

Yes, test every SQL query that filters, joins or aggregates with an integration test against the database engine production runs, using rows the query must return, rows it must leave out and rows with NULL in the compared columns.

Why

Blast radiususers
Change frequencyregularly
Detectabilityeventually
Reversibilitywith-effort
Test costmoderate

Test every SQL query that filters, joins or aggregates, against the database engine production runs. The typical case is a hand-written query in a backend whose rows customers see. Blast radius is users and Change frequency is regularly, because features add joins and filters to existing queries. Detectability is eventually, because a wrong condition returns plausible rows without an error. Reversibility is with-effort, because code acts on the wrong rows until someone repairs them, and Test cost is moderate, because each test needs a database in a container, so rule R11 gives Test.

When the decision changes
When	Decision	Why
Endpoint tests already run the query against a real database, with rows that each filter must leave out	Do not add a separate query test	Detectability moves to immediately and Reversibility to trivial, because a wrong filter fails those endpoint tests in CI
The query must add the tenant ID, because several companies share one database	Test mandatory: rows of two tenants in each test, asserting that only one tenant's rows come back	Blast radius rises to safety-or-legal and Detectability to never, because a missing tenant filter leaks records without an error
The query sums the line items that become an invoice total	Test mandatory: the main path, known failures and rounding boundaries	Blast radius rises to money and Reversibility to costly, because a wrong invoice needs a refund
A read-only query feeds an internal staff dashboard, and no code acts on its rows	Test minimally: one real-database test of the main filter	Blast radius falls to internal and Reversibility to trivial
The query is a one-time UPDATE that fixes existing rows	Test it differently: rehearse the UPDATE on a restored copy of production data and compare row counts	Change frequency falls to once while Blast radius stays users
The query runs on a cloud data warehouse with no local edition	Test it differently: a scheduled job compares the query's results with a reference query and alerts on a difference	Test cost rises to heavy while Detectability stays eventually and Reversibility with-effort

What breaks if you don't test

A NULL in a compared column makes the condition unknown, so the row drops out. A join to a child table repeats the parent once per child, so a count doubles. A condition on the right table of a LEFT JOIN, written in WHERE, loses the parents with no children. The result still looks complete, and a customer finds the gap weeks later.

What you lose if you over-test

The common over-test mocks the connection and asserts the SQL string. A test with a mocked connection never runs the query, so it passes when the SQL is wrong and fails when you reformat the SQL. A separate test for a primary-key lookup that endpoint tests already run only adds CI time.

How to test

Write integration tests on the production engine and major version, started with Testcontainers, with the schema built by the real migrations. For each query, insert:

Rows it must return and rows it must leave out, including one on each boundary.
A row with NULL in each nullable column the query compares.
A parent with two child rows, for each join.
Two rows with the same sort value, for each paginated query.

Assert the exact returned IDs, since a count passes when a wrong row replaces a right one. For SQL in views or functions, pgTAP runs the tests in SQL.

Procedure and references

When the answer changes

The query is a lookup by primary key that endpoint tests already run.
The query filters by tenant, or its result decides what a customer pays.
The query runs once, as a data fix.

Real incident + Code example

The digest that went to nobody for five weeks

On a community events product I worked on, a weekly digest job selected members with WHERE m.id NOT IN (SELECT member_id FROM unsubscribes). A later release let guests unsubscribe without logging in, which stored a NULL member_id. When the list holds a null and no value matches, NOT IN yields null, not true, so from the first guest unsubscribe the query returned no rows. The job's test mocked the query result, and the job logged "sent 0" every Monday. Five weeks later an organiser asked why sign-ups from the digest had stopped. The fix replaced NOT IN with NOT EXISTS and added this test:

import { Client } from "pg";
import { beforeAll, beforeEach, expect, test } from "vitest";
import { digestRecipients } from "../src/digest";

// PostgreSQL 16 from the CI service, schema built by the real migrations
const db = new Client({ connectionString: process.env.TEST_DATABASE_URL });
beforeAll(() => db.connect());
beforeEach(() => db.query("TRUNCATE members, unsubscribes"));

test("a guest unsubscribe with no member_id leaves other members on the list", async () => {
  await db.query(`INSERT INTO members (id, email)
    VALUES (1, 'ana@example.com'), (2, 'ben@example.com')`);
  await db.query(`INSERT INTO unsubscribes (member_id, email)
    VALUES (2, 'ben@example.com'), (NULL, 'guest@example.com')`);

  const recipients = await digestRecipients(db);

  expect(recipients.map((m) => m.id)).toEqual([1]);
});

Sources

FAQ

Do you test your SQL?: Yes, I test every SQL query that filters, joins or aggregates with an integration test on the production database engine. A lookup by primary key gets no test of its own when endpoint tests already run it.
Is it worth testing data access queries?: Yes, a data access query with a filter, a join or an aggregate is worth one integration test, because a wrong condition returns plausible rows without an error. A test with a mocked connection is not worth writing, because the SQL never runs.
Should I unit test a method that is mostly a query?: No, test a method that is mostly a query with an integration test against a real database instead of a unit test with a mocked connection. Such a method keeps its logic in the SQL, which only the database runs.
Do I need to test the sort order of a query?: Yes, test the sort order of a paginated list with two rows that share a sort value. PostgreSQL returns an unpredictable subset under LIMIT unless ORDER BY gives a unique order, so end the sort with a unique column such as the ID.