Key takeaway
- Database testing and unit testing in 2027: what to test, tSQLt, pgTAP, utPLSQL, Testcontainers and dbt tests compared, with test-first SQL examples.
Database testing checks that the schema, constraints, stored procedures, triggers, views and data transformations in a database behave as intended. Database unit testing is the narrowest and fastest part of it: each test sets up a small, known set of rows, runs one database object, compares the result with an expected value and then rolls the changes back, so tests stay independent and can run on every commit.
Test-driven database development applies the red, green, refactor cycle described in our test-driven development guide to SQL: write a failing test for a table, procedure or model, make it pass, then refactor safely. If you are choosing between test styles for the application layer, see TDD vs BDD vs ATDD; this post covers the database layer only, with tool choices for SQL Server (tSQLt), PostgreSQL (pgTAP), Oracle (utPLSQL), throwaway test databases (Testcontainers) and analytics pipelines (dbt tests).
What is database unit testing, and how is it different from other database testing?
Database testing is a family of checks, not one activity. Our guide to database testing types covers the full range. For an enterprise leader, the useful split is by layer: what each test checks, how long it takes and where it runs in the pipeline.
| Test layer | What it checks | Typical tools | When it runs |
| Database unit test | One table, constraint, procedure, function, trigger, view or model, against a few known rows | tSQLt, pgTAP, utPLSQL, dbt unit tests | Every commit, in seconds |
| Data access integration test | Application code talking to a real database engine of the same type as production | Testcontainers with your normal test framework | Every commit or pull request |
| Data test (data quality) | Assertions about data already loaded, such as unique keys, non-null columns and valid relationships | dbt data tests | After each build of a model, including production |
| End-to-end and UI test | A user journey that reads or writes data through the interface | Browser automation tools such as Selenium WebDriver | Before release, on a smaller set of journeys |
| Performance and load test | Response time and throughput under expected and peak load | Load testing tools such as Apache JMeter | Before major releases and capacity changes |
The earlier version of this post listed Selenium WebDriver and Apache JMeter as database testing tools. They are useful, but they test the user interface and system load, not database logic. A failing UI test tells you something is wrong; a failing database unit test tells you which object is wrong and why.
What should a database unit test cover?
Most database defects that reach production come from logic and structure that nobody tested directly: a constraint that was dropped in a migration, a trigger that fires twice, a view that silently changes its grain. The checklist below maps each database object to the check that protects it.
| Database object | What to test | Example assertion |
| Schema | Tables and columns exist with the right types | pgTAP has_table(), has_column(), col_type_is(); tSQLt AssertEqualsTableSchema |
| Constraints | Primary keys, foreign keys, NOT NULL, unique and check rules reject bad data | pgTAP col_is_pk(), fk_ok(), col_not_null(); insert a bad row and expect an error |
| Stored procedures and functions | Given known inputs, they return or write the expected rows and raise the expected errors | tSQLt AssertEqualsTable, ExpectException; pgTAP results_eq(), throws_ok() |
| Triggers | The trigger exists, calls the right function and changes the right rows once | pgTAP has_trigger(), trigger_is(); tSQLt ApplyTrigger on a faked table |
| Views and transformations | Calculated columns, joins and filters give the expected output for edge cases | dbt unit tests with mocked inputs; tSQLt or pgTAP result comparison |
| Transactions | A failed step rolls back the whole unit of work and leaves data consistent | Force an error inside the procedure and assert that no partial rows remain |
Two rules keep these tests valuable. First, test behaviour, not implementation: assert on the rows a procedure produces, not on the exact SQL inside it, so refactoring does not break the tests. Second, keep isolation in mind. When you replace a table with a fake to isolate a procedure, the fake usually has no constraints, so you need separate tests that exercise the real constraints. The tSQLt documentation gives exactly this warning for its FakeTable procedure.
How does test-driven database development work?
Test-driven database development follows the same short loop as application TDD, with one extra concern: database state. Each test must start from a known state and leave nothing behind. The main frameworks handle this for you:
- tSQLt runs tests automatically within transactions, so changes are rolled back when each test ends.
- pgTAP scripts start a transaction and roll it back at the end, and with xUnit-style test functions,
runtests()runs each function in its own transaction. - utPLSQL isolates the changes made by every setup, cleanup and test procedure with savepoints by default, unless a suite is marked
--%rollback(manual).
A typical cycle for a new requirement, such as "every order line must belong to an existing order", looks like this:
- Red: write a test that inserts an order line with an unknown order ID and expects an error. Run it and watch it fail, because the foreign key does not exist yet.
- Green: write the smallest migration that makes it pass, here a foreign key constraint.
- Refactor: rename, index or restructure with the test as a safety net, then commit the migration and the test together.
- Automate: run the whole suite in the CI/CD pipeline on every pull request, against a fresh database built from migrations.
The last step matters most. Tests that only run on a developer's machine against a long-lived shared database drift out of date. Building a clean database from migration scripts for every run proves that the migrations themselves work, not only the objects they create.
Which database testing tools fit which platform?
Choose the unit testing framework that matches your database engine, then add Testcontainers for integration tests and dbt tests if your analytics transformations are built in dbt. Each description below comes from the tool's own site or documentation, read in November 2026.
| Tool | What it is | When it fits | Current status |
| tSQLt | Open source database unit testing framework for SQL Server; tests are written in T-SQL and can fake tables and views and spy on stored procedures | SQL Server estates with logic in stored procedures, functions and triggers | Active; compatible with all editions of SQL Server from 2005 Service Pack 2 onwards; plain text or XML output for CI tools |
| pgTAP | A suite of PostgreSQL database functions for writing TAP-emitting unit tests in psql scripts or xUnit-style test functions, with many schema test functions | PostgreSQL schemas, functions, triggers and migrations; tests run with pg_prove |
Active; documentation shows version 1.3.4 |
| utPLSQL | Free and open source (Apache 2.0) unit testing framework for Oracle PL/SQL, inspired by the xUnit family; tests are packages marked with annotations such as --%suite and --%test |
Oracle databases with business logic in PL/SQL packages | Active; documentation shows v3.2.3; command-line client and Maven plugin at v3.2.2 for CI/CD pipelines |
| Testcontainers | An open source library for throwaway, lightweight instances of databases, message brokers, web browsers and other software that runs in a Docker container | Integration tests of application data access code against the same engine as production, starting from a known state each run | Active; implementations for Java, Go, .NET, Node.js, Python, Rust and other languages; Java database modules include PostgreSQL, MySQL, MS SQL Server, Oracle Database Free and MongoDB |
| dbt data tests | Assertions about models, sources, seeds and snapshots; a test returns failing rows and passes when it returns none | Data quality checks on built analytics models: four built-in generic tests (unique, not_null, accepted_values, relationships) plus custom SQL tests |
Active; the YAML key was renamed from tests: to data_tests: when unit tests arrived (tests: still works as an alias) |
| dbt unit tests | Validate SQL modelling logic on a small set of static, mocked inputs before the full model is built | Complex transformation logic such as parsing, window functions and incremental logic, in development and CI | Available from dbt v1.8; SQL models only; not for materialised views, recursive SQL or introspective queries |
One practical point from the Testcontainers documentation: a real containerised database is a better choice than an in-memory substitute such as H2 when your tests depend on database features the substitute does not emulate. That covers most enterprise code that uses vendor-specific SQL, JSON types or stored procedures.
What does a database unit test look like in practice?
The original post used a month_value table and a cm_abs_month view that adds an absolute month number (y * 12 + m). Here is the same check written as a proper unit test in tSQLt for SQL Server. FakeTable replaces the table with an empty copy without constraints, so the test controls every input row, and tSQLt rolls everything back afterwards.
EXEC tSQLt.NewTestClass 'MonthTests';
GO
CREATE PROCEDURE MonthTests.[test view returns absolute month]
AS
BEGIN
EXEC tSQLt.FakeTable 'dbo.month_value';
INSERT INTO dbo.month_value (eid, m, y, v) VALUES (1, 12, 2017, 20);
SELECT eid, am INTO #actual FROM dbo.cm_abs_month;
CREATE TABLE #expected (eid INT, am INT);
INSERT INTO #expected VALUES (1, 24216);
EXEC tSQLt.AssertEqualsTable '#expected', '#actual';
END;
GO
EXEC tSQLt.Run 'MonthTests';
The equivalent structural checks in PostgreSQL with pgTAP run inside a transaction that is rolled back, so the database is left unchanged:
BEGIN;
SELECT plan(4);
SELECT has_table('public'::name, 'month_value'::name, 'month_value table exists');
SELECT col_is_pk('month_value', 'eid', 'eid is the primary key');
SELECT col_not_null('month_value', 'y', 'year is required');
SELECT results_eq(
'SELECT count(*)::int FROM cm_abs_month WHERE am IS NULL',
ARRAY[0],
'every row has an absolute month'
);
SELECT * FROM finish();
ROLLBACK;
Run the pgTAP file with pg_prove in the pipeline. Treat these snippets as patterns to adapt: check names, schemas and column types against your own database before you use them.
What has changed in database testing since this post was first written?
- Analytics code now has its own unit tests. dbt added unit tests in v1.8, which test transformation logic on mocked inputs before a model is built. dbt Labs strongly recommends running them only in development or CI, because static inputs gain nothing from running in production. Data tests remain the check on real data after each build.
- Throwaway databases replaced shared test databases. Testcontainers gives each test run a fresh, real database engine in a container, so tests start from a known state and no longer depend on a shared server that other teams change.
- Database code is treated as application code. Schema migrations, procedures and tests live in the same repository and go through the same pull request and CI checks as the services that use them.
- AI coding agents write more SQL. This raises the value of tests written first: a failing test defines what the agent must achieve, and a test suite that the agent cannot quietly weaken is the control. Our own engineering teams have moved away from chasing coverage numbers towards an agentic TDD standard, where tests are written before code whether a person or an agent writes it.
- Old installation steps were removed. The earlier post included SQL Server 2017 installation commands for Linux and screenshots of that setup. They were out of date and are not needed to write database unit tests, so they have been removed.
How do governance, security and cost fit into database testing?
| Concern | Risk | Control |
| Test data | Copies of production data in test environments expose personal or regulated data | Use small synthetic fixtures in unit tests; fakes and mocked inputs need only the columns under test |
| Environment drift | Tests pass on a shared database that no longer matches production | Build a fresh database from migrations for every run, for example with Testcontainers |
| Change control | Schema changes reach production without evidence that they were tested | Keep migrations and tests in one pull request; store CI test reports (tSQLt XML output, pgTAP TAP output, utPLSQL reports) as evidence |
| AI-generated SQL | An agent changes logic and edits or deletes the tests that would catch it | Protect test files with code-owner review; treat any change that disables a test as a review flag |
| Compute cost | Heavy test suites run against large warehouses on every commit | Run unit tests on static inputs in CI; run data tests only after builds; limit end-to-end tests to key journeys |
When AI agents generate or change database code, the tests need checking as well as the code. AgentQA tests agent outputs and workflows continuously, so quality issues are caught as products change. For teams building and governing those agents, ElixirClaw is an agentic OS platform to build, orchestrate and govern AI agents and automation workflows while keeping humans in control.
How should an enterprise adopt database unit testing?
- Find the risky logic. List the procedures, triggers, views and models where a defect would cost the most: billing, entitlements, regulatory reporting.
- Pick one framework per engine. tSQLt for SQL Server, pgTAP for PostgreSQL, utPLSQL for Oracle, dbt tests for dbt projects. Avoid building your own.
- Put the schema under version control. Every change becomes a migration script that a pipeline can apply to an empty database.
- Write tests for new changes first. Do not try to backfill tests for the whole estate. Add a test whenever an object is created, changed or involved in an incident.
- Run tests in CI on a fresh database. Fail the build on any failing test and keep the reports.
- Add data tests on the production pipeline. Unit tests prove the logic; data tests catch bad inputs that the logic never expected.
- Review the suite each quarter. Remove tests that no longer protect anything and add tests where incidents showed gaps.
Teams that plan this work in sprints can track test-first database changes alongside other delivery work; AgentScrum automates sprint planning and Scrum ceremonies with agentic AI.
When is database unit testing the wrong tool?
- Thin databases. If the database only stores data and all logic lives in application code accessed through an ORM, integration tests with Testcontainers give more value than framework-level unit tests inside the database.
- Data quality problems. Unit tests run on fixtures and cannot see bad data arriving from a source system. Use data tests on real loads for that.
- Performance questions. A unit test with five rows says nothing about query plans on five billion. Use load tests and query plan reviews.
- Heavy faking. If a test fakes every table and procedure around the object under test, it may only prove that the fakes work. Keep some tests against real constraints and real dependencies.
The trade-off is effort: writing tests for database code takes SQL skill and time, and the first suite in an older estate is slow to build. The return is that schema changes stop being the riskiest part of a release.
To map one data decision your agents and pipelines depend on, and the tests and policy that should guard it, book a Context Workshop. See what happens in the workshop.
What should you plan for in 2027?
Two vendor dates fall in 2027 for teams that test database code against specific engine versions.
- SQL Server 2017: end of support is 12 October 2027, according to Microsoft's end of support list for 2027. Build your tSQLt suite against the version you will move to, so the upgrade is proven by tests rather than hope.
- PostgreSQL 15: the final release is due on 11 November 2027, per the PostgreSQL versioning policy. Run your pgTAP suite against the next major version in CI well before that date.
Frequently asked questions
What is the difference between database testing and database unit testing?
Database testing covers every check on a database, including integration, data quality, UI and load tests. Database unit testing is the narrowest part: it tests one object, such as a procedure, trigger, view or model, against a small set of known rows and rolls the changes back.
Which tool should I use for SQL Server, PostgreSQL or Oracle unit tests?
tSQLt is an open source unit testing framework for SQL Server with tests written in T-SQL. pgTAP is a suite of PostgreSQL functions for writing TAP-emitting unit tests. utPLSQL is a free, Apache 2.0 licensed unit testing framework for Oracle PL/SQL.
What is the difference between dbt data tests and dbt unit tests?
dbt data tests are assertions on built models that pass when they return no failing rows, with four built-in generic tests: unique, not_null, accepted_values and relationships. dbt unit tests, available from dbt v1.8, check SQL modelling logic on static mocked inputs before the model is built, and dbt Labs recommends running them only in development or CI.
Do I still need a shared test database?
Usually not. Testcontainers provides throwaway, lightweight database instances in containers, so each test run starts from a known state on the same engine as production, instead of on a shared server that drifts.
How do database unit tests stay independent of each other?
The frameworks roll back changes after each test. tSQLt runs tests within transactions, pgTAP scripts and test functions run inside transactions that are rolled back, and utPLSQL isolates each setup, cleanup and test procedure with savepoints by default.
Does test-driven development work for database code?
Yes. Write a failing test for the new rule, write the smallest migration or procedure change that passes it, refactor, and run the whole suite in CI on a fresh database built from migrations.
Follow XenonStack on LinkedIn: XenonStack on LinkedIn for new guides on agentic AI, data and platform engineering.
Explore the XenonStack platform family: ElixirData · ElixirClaw · ElixirHub · NexaStack · MetaSecure · Neural AI · AgentGRC · AgentSRE · AgentAnalyst · AgentInstruct · AgentRAI · AgentScrum · AgentSearch · AgentForce · AI Aviator · SAIF Aviator · CohortIQ · XenonStack AI · XenonStack UAE