Database testing in 2026: what to check, SQL queries for testers and tools

Users see the interface, but everything valuable lives in the database: orders, payments, profiles, history. A button can say "Saved" while the table gets a wrong amount, a duplicate or an orphan row with no parent. A migration can run in a second on the test server and lock a production table for half an hour. And a query that flew on a hundred rows can fall over on a million.

Database testing checks exactly that: is data written and stored correctly, are the relationships intact, do schema changes roll out safely, are queries fast, and is personal data protected from leaks.

Quick pick

What to test

SQL checks worth knowing by heart

These queries catch most data problems. Table names are illustrative; the syntax is standard and works in PostgreSQL and MySQL.

Orphans: child rows without a parent.

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

Duplicates where there shouldn't be any.

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Empty values in fields that are required by the business.

SELECT COUNT(*) AS total,
       SUM(CASE WHEN phone IS NULL OR phone = '' THEN 1 ELSE 0 END) AS no_phone
FROM customers;

Consistent totals: order header vs. line items.

SELECT o.id, o.total, SUM(i.price * i.qty) AS items_total
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.total
HAVING o.total <> SUM(i.price * i.qty);

Impossible values: negative stock, future dates, unknown statuses.

SELECT id, stock FROM products WHERE stock < 0;
SELECT id, created_at FROM orders WHERE created_at > CURRENT_TIMESTAMP;
SELECT DISTINCT status FROM orders;

What an app action changed. Before the test, note MAX(id) or the time, perform the action in the UI and look at new and changed rows:

SELECT * FROM orders WHERE id > 15230 ORDER BY id;
SELECT * FROM audit_log WHERE created_at >= '2026-11-04 10:15:00' ORDER BY created_at;

These queries are easy to turn into automated checks: the test fails if the query returns at least one row.

Manual database testing

Manual work needs a good client. DBeaver Community is free and connects to almost any database, JetBrains DataGrip is free for non-commercial use and understands SQL very well, and pgAdmin is the official PostgreSQL tool. On shared environments connect with a read-only user so you don't accidentally change other people's data.

What to check manually:

• Data after UI actions. Created an order? Check every related table: orders, line items, stock movements, payment, audit log.
• Boundary values. Maximum string length, zero and negative numbers, very large amounts, 29 February and 31 December, NULL vs. empty string.
• Non-Latin characters and emoji. A name like "Ґалаґан-Їжакевич" or "Zoë 😊" must be saved and returned without "??". In MySQL that requires utf8mb4, not the legacy utf8. Also check sorting and search: in accent-insensitive collations, letters such as "ї" and "і" or "й" and "и" may compare as equal, and a unique index may unexpectedly reject a different name.
• Time zones. Save an event at 23:30 local time and see which date it gets in the database and in reports. Check daylight saving transitions separately.
• Concurrent operations. Open two sessions and change the same row at once, or buy the last item in two tabs. That's where isolation and locking bugs hide.
• Deletion. What happens to related rows: cascade delete, restriction or soft delete with a flag?
• Restore from backup. At least once a quarter, together with the admins, restore a copy into a separate environment and check that the app works with it.

Automated tests

Unit tests inside the database

If business logic lives in stored procedures and triggers, test it there.

• pgTAP for PostgreSQL checks schema structure (does a table, column, index or key exist) and the results of functions and queries. Run with pg_prove.
• tSQLt for SQL Server: tests in T-SQL, each in a rolled-back transaction, and FakeTable isolates a procedure from real data.
• utPLSQL for Oracle: annotated tests in PL/SQL packages and code coverage reports.

Integration tests with a real database

Running app tests on an embedded "lightweight" database instead of the real one often misses bugs: different types, different NULL and sorting behaviour, missing functions. Test on the same engine and version as production.

• Testcontainers spins up a real database in a Docker container for the duration of the tests and removes it afterwards, for Java, .NET, Python, Go, Node.js and more.
• DbUnit in Java projects loads a known dataset before a test and compares tables afterwards.
• Neon creates a branch of a PostgreSQL database with all its data in seconds, handy for a separate environment per pull request.

To keep tests from interfering with each other, use one of three strategies: a transaction rolled back after each test, a separate clean database per run, or unique data for each test.

Test data

Faker.js (in code, with many locales) and Mockaroo (in the browser, exporting SQL and CSV) generate realistic data. Generate not only "nice" data but edge cases too: long strings, apostrophes in surnames (O'Connor), empty optional fields, very old and future dates.

Schema migrations: the riskiest part

Most production database incidents come not from query bugs but from schema changes. Migrations should live in Git, go through review and be tested like code.

• Flyway from Redgate applies versioned SQL scripts in order. The free Community edition covers the basics; since May 2025 the Teams edition is no longer sold to new customers, leaving Enterprise for commercial features.
• Liquibase describes changes in changelog files and supports rollback. Since version 5.0 (September 2025) Liquibase Community is under the FSL license (code converts to Apache 2.0 after two years), and the commercial edition is called Liquibase Secure.
• Atlas generates migrations from the desired schema and lints them for risky changes.
• Squawk is a PostgreSQL migration linter: it warns that CREATE INDEX without CONCURRENTLY will block writes to the table.
• Bytebase organises review and approval of SQL changes for a team, and SQLFluff checks style and common mistakes in SQL code.

Migration testing checklist:

1. Apply the migration to a copy of the database with a realistic (masked) data volume, not an empty one.

2. Measure the duration and check for table locks. What takes a second on a test database can take an hour in production.

3. Check that the old app code works with the new schema and the new code with the old one. During a rollout both versions run at once. The safe path is "expand, migrate, contract": add the new column first, then move data and code, and only drop the old column in a separate release.

4. Check the rollback or recovery plan.

5. After the migration, run the integrity SQL checks above and pgTAP structure tests.

A database version upgrade is a migration too. MySQL 8.0 reached end of life on 30 April 2026, and many teams are moving to MySQL 8.4 LTS. PostgreSQL 18 came out in September 2025, and PostgreSQL 13 support ended in November 2025. Before upgrading, run a full regression on the new version and compare plans of key queries: the new optimiser may choose a different plan.

Query performance

• Execution plan. EXPLAIN ANALYZE (in PostgreSQL ideally with BUFFERS) shows how the database actually ran a query: which indexes it used, how many rows it read and where it spent time. Raw plans are hard to read, so paste them into explain.dalibo.com (free) or pgMustard (with advice).
• Slow query logs. Enable slow_query_log in MySQL or log_min_duration_statement in PostgreSQL. pgBadger turns a PostgreSQL log into a report of the slowest and most frequent queries. The pg_stat_statements extension collects query statistics inside the database.
• Typical problems: a missing index on a WHERE or JOIN column; a function over an indexed column (WHERE LOWER(email) = ...) that prevents index use; SELECT * on large tables; the N+1 problem when an ORM runs a separate query for each row in a list; pagination with a large OFFSET.
• Data volume. Always test at a realistic volume: a query that works on 100 rows can be a disaster on 10 million.

To load-test the database itself there's HammerDB (standard TPROC-C and TPROC-H workloads) and sysbench (quick OLTP tests for MySQL and PostgreSQL). For load through the application use Apache JMeter with JDBC requests or Grafana k6. More in Load testing.

Database security

• SQL injection. Check every input field, URL parameter and API. sqlmap automates the search; use it only on your own systems or with written permission. The main defence in code is parameterised queries.
• Least privilege. The app account shouldn't have DROP, ALTER or access to other schemas. Check this by querying the system privilege tables.
• Sensitive data. Passwords are stored only as hashes, card numbers are not stored at all or are tokenised, backups are encrypted.
• Errors. Database error text must not reach the user: it reveals the table structure.

More in Security testing.

Test data without personal data

The easiest way to get a realistic test database is to copy production. It's also the most dangerous: test environments are less protected, contractors have access, and data leaks into screenshots, bug reports and chats. Personal data processing is regulated by laws such as the GDPR in the EU.

So mask a production copy before handing it to testing:

• Greenmask dumps PostgreSQL and replaces names, emails, phones and addresses with fake ones on the way, preserving relationships between tables.
• PostgreSQL Anonymizer lets you declare masking rules right in the schema and do static or dynamic masking.

After masking, verify the result: search the test database for a few known real emails or phone numbers. They shouldn't be there.

Data quality in warehouses and ETL

If you test analytics, a data warehouse or ETL pipelines, the focus shifts from single transactions to large datasets: did all the data arrive, are there duplicates, is it fresh, are the aggregates right.

• dbt has built-in unique, not_null, accepted_values and relationships tests plus model unit tests. In June 2026 dbt Labs completed its merger with Fivetran; dbt Core remains open source.
• Great Expectations and Soda describe expectations about data and run them in pipelines.
• Datafold compares data before and after a code change or a cross-database migration and shows the difference right in the pull request.

A baseline set of checks for any load: row counts match between source and target, keys are unique, required fields are filled, control totals (for example, daily sales) match the source, and data is no older than expected.

If you test the database engine itself

Teams building databases or their forks use specialised approaches. SQLancer automatically generates databases and queries and looks for logic bugs where the engine returns a wrong result; it has found hundreds of defects in SQLite, MySQL, PostgreSQL and others. For distributed databases there's the well-known Jepsen approach: checking consistency guarantees under network partitions and node crashes.

How to report data defects

A good database bug report includes:

• the app action that caused the defect and the environment;
• the SQL query that shows the problem and its result (without personal data);
• the expected result: "orders.total should equal the sum of line items";
• record IDs so the developer can find them;
• the database version and, if relevant, the last applied migration;
• for slow queries, the execution plan and the table sizes.

If you can reproduce the problem on a small example, build it in DB Fiddle: the schema, a few rows and the query returning a wrong result, all behind a single link.

A one-month learning plan

More practice tools are in the Practice Sites for Testers subcategory.

AI in database testing

AI assistants are good at writing SQL from a description ("find unpaid orders created more than a day ago"), explaining other people's queries and execution plans, suggesting indexes and generating test data and edge cases. Many clients, including paid editions of DBeaver and DataGrip, have built-in AI assistants that see your schema.

Two rules, though. First, check generated SQL before running it, especially UPDATE and DELETE: AI can easily drop a WHERE condition or mix up columns. Second, don't paste real personal or commercial data into public chatbots; share the schema and masked examples. Look for AI data tools in the Databases subcategory.

For other kinds of testing, see API testing, Load testing, Security testing and Test automation tools. All tools from this article are in the Testing and Databases subcategories.

FAQ

Does a tester need SQL?

Yes. Even manual testers need SQL to check data after UI actions, prepare test data and describe defects precisely. SELECT, JOIN and GROUP BY are enough to start.

How is database testing different from API testing?

API testing checks what the service returns. Database testing checks what was actually stored and whether the data is intact. An API can answer "200 OK" while a wrong row lands in the database.

Can I test on a copy of the production database?

Only after masking personal and sensitive data. Real data in test environments is a common cause of leaks.

How do I test NoSQL databases such as MongoDB?

The principles are the same: data correctness, document schema validation, indexes and query plans (explain()), performance at realistic volume. Testcontainers works for integration tests here too.

What if the logic is in stored procedures and there are no tests?

Start with the most critical procedures (money, stock, permissions) and write unit tests for them in tSQLt, pgTAP or utPLSQL, depending on the database.

Not sure where to start?

Ask Elya: she'll pick database testing tools for your project and level.

Facts checked on 4 November 2026. Service terms, licenses and database support dates may change, so check official websites.