Database Testing: Types, SQL Examples and Tools

Database testing checks that a database stores, changes, retrieves and protects data correctly. It covers the schema, constraints and business rules, migrations, queries under realistic load, permissions, and the application paths that read or write data. Good database tests start from a known fixture, run repeatably in isolation, assert both successful and rejected operations, and leave the database clean for the next run.

If you would rather have the API tests that exercise these database-backed flows written and run for you, Qodex writes them from your spec, runs them against the preview on every pull request, and classifies every failure. See Qodex API testing.

What database testing is

Data bugs are their own category. An order with no customer. A price that went negative. A column that was added last week and is still empty for every row written before the deploy. None of those show up as a crash. They show up as wrong numbers, weeks later, in something nobody was looking at.

Database testing is the part of testing that talks to the data layer on purpose. You connect to the database, put it in a known state, do one thing, and check what changed. That is different from an API test, which sends a request and reads a response, and from a UI test, which clicks and reads a screen. Both of those touch the database, but they only see what the application chooses to show. A constraint that is missing may not be visible from the API response alone. It shows up the first time two rows have the same email.

People say backend testing and database testing to mean roughly the same work, and the two do overlap. Backend testing covers services, queues and the database together. Database testing is the slice that asserts against stored data and the rules the database itself enforces.

Every database test has the same four beats. Set up a fixture, so you know exactly what rows exist. Run one action, a query, an insert, a migration. Assert the result, including the failures you expect to be rejected. Then clean up, so the next test starts from the same place. Skip the last beat and your suite passes in the order you wrote it and fails in any other.

Five types of database testing

These five cover the risks that matter for most applications. Give them equal weight: a perfect schema with an unindexed hot query is as broken in production as a missing constraint.

Schema testing

Schema testing asserts the shape: tables, columns, types, nullability, defaults, keys, indexes. The useful version reads the database's own catalogue rather than the migration files, because the catalogue is what the running system has. Assert the properties the code depends on, not every column, or the suite becomes a second copy of the schema that has to be edited twice on every change.

Data integrity testing

Integrity testing proves the database rejects bad data on its own. Unique keys, not-null columns, check constraints, foreign keys, and the business invariants that sit above them. PostgreSQL documents check, not-null, unique, primary-key, foreign-key and exclusion constraints, and a violation raises an error rather than writing the row (PostgreSQL constraints, read 15 September 2026). Write the negative case: try the bad insert and assert it fails. Transactions belong in the same section. PostgreSQL describes a transaction as a single, all-or-nothing operation, where BEGIN and COMMIT define it and ROLLBACK cancels its updates (PostgreSQL transactions, read 15 September 2026). So assert the other half of integrity too: when one step in a transaction fails, no partial write is left behind.

Migration testing

A migration changes existing schema or data, which makes it worth testing carefully. Apply it to a copy that looks like production, not an empty schema. Then assert four things: the new structure exists, old rows were backfilled correctly, the old read and write paths still work, and you know the documented way back. Forward verification beats a clever rollback script: use your system's documented and tested recovery path rather than an untested reverse migration.

Performance testing

Performance testing needs three inputs before it means anything: representative data volume, a defined workload, and a budget you wrote down. Without the budget you are collecting numbers, not testing. Check the plan for the queries that matter, then measure latency and throughput under the workload you expect and a little past it. Our guides to performance testing tools and stress testing tools cover the load side.

Security testing

Start where the data is reached: the application. OWASP's primary defence against SQL injection is prepared statements with parameterized queries. It also recommends allow-list validation where a bind variable cannot stand in for an identifier such as a table or column name, and least privilege for database accounts (OWASP SQL Injection Prevention Cheat Sheet, read 15 September 2026). Then check the database side: roles, grants, what is encrypted, what is logged. For the attack shapes, see SQL injection and its types. Test in authorized non-production environments with synthetic or properly de-identified data. The UK Information Commissioner's Office lists seven data protection principles, among them accuracy and integrity and confidentiality (ICO guide to the data protection principles, read 15 September 2026). Tests can support control evidence for principles like those. They do not prove GDPR, PCI DSS, SOC 2 or ISO compliance.

How to test a database step by step

Seven steps, in order. The first one is the one teams skip.

• Rank the risk. List what the data must never be: an order with no customer, a negative balance, a duplicate account. Those sentences are your first tests. Everything else is a schema listing.

• Match production. Test against the same engine and the same major version you run, with the same extensions. SQLite is fine for learning the loop. It is not evidence about PostgreSQL behaviour.

• Isolate the fixture. Each test creates the rows it needs and owns them. A disposable database per test is cleanest. A transaction rolled back at the end is the usual compromise. Shared seed data that every test mutates is how suites start failing in the order they happen to run.

• Write the positive and the negative case. The insert that should work, and the insert that should be refused. A constraint with no negative test is an assumption.

• Test the application boundary too. Direct SQL proves the database enforces a rule. It does not prove the endpoint uses it. Cover the path the application actually takes, which is what API integration testing is for.

• Reset. Drop the fixture, roll back, or throw the database away. Assert nothing is left, at least once, or cleanup rots quietly.

• Automate and run it on every change. A database suite that runs by hand before a release finds the problem after the migration already shipped.

Two of these steps need care in the same place. Migrations and recovery are the moments when the fixture is real data, so define the invariants first. Our guide to recovery testing covers what has to survive.

A runnable SQL example

This is SQLite 3.53.4, small and disposable on purpose, so you can run it in one command with nothing installed beyond the sqlite3 binary. It creates two tables, seeds a row, applies a migration, then asserts two things and prints a query plan. Save it as database-test.sql.

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0)
);

INSERT INTO customers (id, email) VALUES (1, 'sam@example.com');
INSERT INTO orders (id, customer_id, total_cents) VALUES (10, 1, 2500);

ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending'
  CHECK (status IN ('pending', 'paid', 'cancelled'));
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

SELECT 'migration_backfill' AS test,
       CASE WHEN status = 'pending' THEN 'pass' ELSE 'fail' END AS result
FROM orders WHERE id = 10;

SELECT 'referential_integrity' AS test,
       CASE WHEN COUNT(*) = 0 THEN 'pass' ELSE 'fail' END AS result
FROM orders o LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

Run it with sqlite3 :memory: < database-test.sql. The output:

migration_backfill|pass
referential_integrity|pass
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?)

Three things in this script are worth copying, and three limits are worth knowing.

The first line matters more than it looks. SQLite enforces foreign keys only when the pragma is switched on, and it has to be switched on for each connection separately. The documentation is explicit that applications should not assume it is enabled (SQLite foreign key support, read 15 September 2026). A suite that forgets it tests nothing about referential integrity and still passes. The in-memory database keeps every run disposable: opening :memory: creates a private database with no file on disk (SQLite in-memory databases, read 15 September 2026).

The migration assertion is the pattern to steal. The row was written before the status column existed. After the change it must read as pending, and the query proves it did. The referential check is the same idea from the other side: a left join that finds orphans should return no rows, so the count is the assertion.

The plan output is diagnostic, not a snapshot to compare against. SQLite warns that the format of EXPLAIN QUERY PLAN can change between releases (SQLite EXPLAIN QUERY PLAN, read 15 September 2026). Assert the property you care about, that the query searched an index instead of scanning the table, and never assert the whole rendered string. And nothing here tells you how PostgreSQL or SQL Server will behave. Run engine-specific checks on the engine and major version you ship.

A small automated database test

The SQL script proves the ideas. This turns them into a suite that fails a build. It uses Python's standard library only, so there is nothing to install: unittest for structure and sqlite3 for the database. Save it as test_database.py.

import sqlite3
import unittest

SCHEMA = """
PRAGMA foreign_keys = ON;
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0)
);
"""

class DatabaseTests(unittest.TestCase):
    def setUp(self):
        self.db = sqlite3.connect(":memory:")
        self.db.executescript(SCHEMA)

    def tearDown(self):
        self.db.close()

    def seed(self):
        self.db.execute("INSERT INTO customers VALUES (?, ?)",
                        (1, "sam@example.com"))

    def test_constraints(self):
        self.seed()
        self.db.execute("INSERT INTO orders VALUES (?, ?, ?)", (10, 1, 2500))
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO customers VALUES (?, ?)",
                            (2, "sam@example.com"))
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO orders VALUES (?, ?, ?)", (11, 99, 100))
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO orders VALUES (?, ?, ?)", (12, 1, -1))

    def test_migration_backfills_old_rows(self):
        self.seed()
        self.db.execute("INSERT INTO orders VALUES (?, ?, ?)", (10, 1, 2500))
        self.db.execute("ALTER TABLE orders ADD COLUMN status TEXT NOT NULL "
                        "DEFAULT 'pending'")
        status = self.db.execute(
            "SELECT status FROM orders WHERE id = ?", (10,)
        ).fetchone()[0]
        self.assertEqual(status, "pending")

    def test_bound_lookup_uses_unique_index(self):
        self.seed()
        payload = "' OR 1=1 --"
        rows = self.db.execute(
            "SELECT * FROM customers WHERE email = ?", (payload,)
        ).fetchall()
        plan = self.db.execute(
            "EXPLAIN QUERY PLAN SELECT * FROM customers WHERE email = ?",
            ("sam@example.com",),
        ).fetchall()
        self.assertEqual(rows, [])
        self.assertTrue(any("INDEX" in row[3] for row in plan))

if __name__ == "__main__":
    unittest.main()

Run it with python -m unittest -v test_database.py. Three tests, three beats each, and every test gets its own throwaway database.

setUp and tearDown do the fixture work. A fresh in-memory database per test means no ordering problems and no cleanup script to maintain: the database disappears when the connection closes.

test_constraints is four assertions in one place. The good insert works. Then three writes that must be refused: a duplicate email, an order pointing at a customer who does not exist, and a negative total. Each is expected to raise an integrity error, and the test fails if any of them quietly succeeds. That is what catches a constraint someone dropped during a refactor.

test_migration_backfills_old_rows writes a row, adds a column with a default, and reads the old row back. The assertion is that the pre-existing row now reads as pending. On a real migration the equivalent test runs against a copy of production-shaped data, and checks a sample of rows from before the change rather than one.

test_bound_lookup_uses_unique_index does two jobs. It sends a classic injection payload as a bound parameter and asserts the result is zero rows, which is what happens when the value is treated as data rather than SQL. Python's sqlite3 documentation covers the placeholder syntax that makes that true (Python sqlite3, read 15 September 2026). Then it checks the plan mentions an index, which is the cheap version of a performance assertion. Be honest about what the first half proves: this one code path binds its parameters. It says nothing about the other queries in the application, or about identifiers built by string concatenation, which bind variables cannot cover.

Add one test per rule you actually care about and this file becomes the suite. Keep it in CI and the build goes red the day a constraint disappears.

Database testing tools by job

For database testing tools for QA teams, choose by the job in front of you and by the engine you run. A load generator will not check a constraint, and a unit framework will not tell you how the database behaves under concurrent load. Versions and prices below are publisher or vendor claims, each with the date it was read.

Whatever you pick, the checks live in the same suite as everything else. For the wider set of services and queues around the database, see our roundup of backend testing tools.

Common mistakes

• Production data in tests. Use authorized non-production environments and synthetic or properly de-identified fixtures. A test suite is not a lawful basis for holding real customer records.

• Shared mutable fixtures. One seed dataset every test edits. It passes today and fails the first time the runner parallelizes.

• SQLite confidence about another engine. Types, locking, defaults and constraint behaviour differ. Learn on SQLite, verify on what you ship.

• Snapshotting the whole query plan. The output format is not a stable contract. Assert the property, such as index use, not the rendered text.

• Load tests on toy data. Small fixtures can hide performance problems. Run the workload against representative volume, or the result means nothing.

• Destructive security testing. Automated injection tools can send destructive payloads. Run them only against systems you are authorized to test.

• Tests with no cleanup. Rows left behind make the next run pass for the wrong reason, then fail for a reason you cannot reproduce.

Frequently Asked Questions

What is database testing?

Database testing checks that a database stores, changes, retrieves and protects data correctly. It covers the schema, the constraints and business rules, migrations, query performance, and permissions. It also covers the application paths that read and write data, because a rule the database enforces is only useful if the code goes through it.

What are the five main types of database testing?

Schema testing, data integrity testing, migration testing, performance testing and security testing. Other groupings exist, splitting out metadata, reference data or stored procedures, and they are fine. What matters is that all five risks are covered, since a perfect schema with an unindexed hot query still breaks in production.

Is database testing manual or automated?

Both, with automation doing the repeated work. Exploratory checks and a first look at a new migration are usually manual. Constraints, invariants, backfills and query budgets belong in a suite that runs on every pull request, because those are the checks nobody remembers to repeat under deadline.

Is SQL a database testing tool?

Yes, and it is the one you always have. Catalogue queries assert structure, inserts that should fail assert constraints, and a left join finding no orphans asserts referential integrity. A framework such as pgTAP or tSQLt adds structure, reporting and isolation on top. The assertions underneath are still SQL.

How is database testing different from API testing?

An API test sends a request and reads a response, so it sees only what the application exposes. A database test connects to the data layer and checks what was actually stored. A missing unique constraint may not be visible from the API response alone, until two rows collide. Run both: one proves the rule, the other proves the endpoint uses it.

How do you test a database migration safely?

Apply it to a copy that looks like production, never to an empty schema, because a backfill with no old rows has nothing to get wrong. Then assert the new structure exists, old rows were backfilled correctly, and the old read and write paths still work. Know your system's documented and tested recovery path before you start.

How do you test database performance?

Start with three inputs: representative data volume, a defined workload, and a written budget. Check the plans for the queries that matter, then measure latency and throughput under that workload and a little past it. Tools such as JMeter and HammerDB drive the load. Without a budget you are collecting numbers, not testing.

Which database testing tool should I use?

Choose by job and engine. Plain SQL for assertions anywhere. pgTAP for PostgreSQL unit tests, tSQLt for SQL Server. JMeter or HammerDB for load and benchmarks. Your project's migration runner, with assertions around it, for schema changes. A commercial suite only when it replaces several of those at once.

The short version

A useful database suite is smaller than people expect. Assert the constraints the database enforces, the business invariants above them, one migration backfill against realistic old data, one representative query against a written budget, and one authorization path. Run it on every pull request. Add the sixth test the day something reaches production that those five would have caught.