Unit Testing SQL Queries: Test Frameworks, Fixtures, and CI Strategies

By Sharon Ben-Moshe · October 13, 2026 · 1 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

How to unit test SQL queries and database logic. Explore pgTAP, ephemeral test databases with Testcontainers, and test assertion strategies.

Quick answer

Unit testing SQL queries involves executing queries against ephemeral, isolated test databases (using Docker Testcontainers or in-process PGlite/SQLite) seeded with known test fixtures, and asserting expected output sets. Frameworks like pgTAP (PostgreSQL) and tSQLt (SQL Server) allow running unit tests directly inside the database engine itself.

FAQs

Why is testing SQL against in-memory SQLite often misleading?

While SQLite is fast for unit tests, it lacks PostgreSQL-specific functions, window function nuances, locking semantics, and strict type checking. Testing queries against real PostgreSQL/MySQL instances using Testcontainers prevents dialect-specific production bugs.

What is pgTAP?

pgTAP is a unit testing suite written in PL/pgSQL for PostgreSQL. It produces Test Anything Protocol (TAP) output and includes assertions like `is()`, `results_eq()`, `has_table()`, and `col_is_pk()`.

How do you achieve test isolation in database testing?

Run each test inside an uncommitted database transaction (`BEGIN; ... test logic ... ROLLBACK;`), or spin up fresh lightweight ephemeral database containers per test suite.

What query edge cases should always be unit tested?

Always test four boundary conditions: 1) completely empty tables, 2) single-row match, 3) duplicate rows / ties, and 4) records containing NULL values.

Engineering Workflow

SQL Query Optimization and Tuning: The End-to-End Practitioner's Guide

By Sharon Ben-Moshe · Oct 13, 2026

The definitive guide to SQL query optimization. Master EXPLAIN execution plans, SARGable predicates, covering indexes, and keyset cursor pagination.

Read post

Engineering Workflow

How SQL Query Engines Actually Execute Queries: The Complete Lifecycle

By Sharon Ben-Moshe · Oct 13, 2026

Journey through the SQL query lifecycle. Explore lexical parsing, Abstract Syntax Trees, logical optimization, Cost-Based Optimizers, and Volcano execution.

Read post

Engineering Workflow

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Key takeaways

Test against real database engines (via Docker or Testcontainers) rather than simplified in-memory mocks.

Wrap tests in rolled-back transactions to keep test runs isolated and fast.

Assert edge cases: empty tables, NULL values, and ranking ties.

Integrate automated database tests into GitHub Actions pull request checks.

Native in-database testing with pgTAP

pgTAP allows writing assertions directly in SQL for testing views and stored functions:

Unit testing a reporting query using pgTAP in PostgreSQL

BEGIN;
-- Plan 2 tests
SELECT plan(2);

-- Test 1: Verify the reporting view exists
SELECT has_view('reporting', 'customer_monthly_summary', 'View should exist');

-- Test 2: Verify the view returns the exact expected calculation
SELECT results_eq(
  'SELECT order_count, total_spend FROM reporting.customer_monthly_summary WHERE customer_id = 1',
  '$$ VALUES (2::bigint, 150.00::numeric) $$',
  'Summary view should aggregate 2 orders totaling $150.00'
);

-- Finish test suite and rollback changes to leave DB clean
SELECT * FROM finish();
ROLLBACK;

Application-level integration testing with Vitest/Jest

In modern TypeScript stacks, test query results against ephemeral databases:

Testing SQL queries in TypeScript with Vitest

import { describe, it, expect, beforeEach } from 'vitest';
import { db } from '@/lib/db';

describe('calculateMonthlyRevenue', () => {
  beforeEach(async () => {
    // Seed test fixtures inside a rollback transaction
    await db.execute('TRUNCATE orders RESTART IDENTITY CASCADE');
    await db.execute(`
      INSERT INTO orders (id, customer_id, amount, status)
      VALUES (1, 10, 100.00, 'completed'),
             (2, 10,  50.00, 'completed'),
             (3, 10,  75.00, 'refunded')
    `);
  });

  it('excludes refunded orders from total revenue', async () => {
    const res = await db.query('SELECT SUM(amount) AS rev FROM orders WHERE status = $1', ['completed']);
    expect(res.rows[0].rev).toBe('150.00');
  });
});

Unit Testing SQL Queries: Test Frameworks, Fixtures, and CI Strategies

Quick answer

FAQs

Why is testing SQL against in-memory SQLite often misleading?

What is pgTAP?

How do you achieve test isolation in database testing?

What query edge cases should always be unit tested?

SQL Query Optimization and Tuning: The End-to-End Practitioner's Guide

How SQL Query Engines Actually Execute Queries: The Complete Lifecycle

Relational Database Schema Design and Normalization: From 1NF to 5NF and Pragmatic Denormalization

Key takeaways

Native in-database testing with pgTAP

Application-level integration testing with Vitest/Jest

Official references

Quick answer

FAQs

Why is testing SQL against in-memory SQLite often misleading?

What is pgTAP?

How do you achieve test isolation in database testing?

What query edge cases should always be unit tested?

Keep reading

SQL Query Optimization and Tuning: The End-to-End Practitioner's Guide

How SQL Query Engines Actually Execute Queries: The Complete Lifecycle

Relational Database Schema Design and Normalization: From 1NF to 5NF and Pragmatic Denormalization

Key takeaways

Native in-database testing with pgTAP

Application-level integration testing with Vitest/Jest

Related SQL guides

Official references