Database Testing: What to Test and How to Interview for It

J. Kessler 12 min read

What to actually test in your database, when API tests cover you and when they don't, the architectural smells to catch, and the interview questions that filter for real database experience.

Most QA and SDET jobs never touch the database directly. That’s not because the database isn’t worth testing. It’s because there is no dedicated “database-testing engineer” role at most companies. Senior QA people set the strategy for what gets tested where; junior implementers write the black-box API and UI tests that fill the daily work. The parts of the system that live inside the database (stored procedures, triggers, complex views, migration paths) mostly get validated by developers who are reluctant to learn testing technologies for it, or by nobody, until something breaks in production.

This piece is the technical answer to “how do we actually test the database” plus the interview lens for the roles that require it. It applies whether you’re an SDET interviewing at a fintech that runs its accounting logic in PL/SQL, a senior QA writing a database test strategy for the first time, or an engineering manager writing a JD for a database-testing role that doesn’t quite exist yet.

If you’re preparing for a broader loop, this is one spoke of the 2026 QA & SDET interview questions guide.

When database testing is even required

The first question to answer, before writing any database tests, is whether your app needs them at all.

The naive answer is “yes always.” Every app has a database, so every app needs database tests. The real answer is more nuanced. Well-designed API tests exercise your database through the application layer. If your service does a POST /orders that inserts a row and a GET /orders/:id that reads it back, an API test covering both endpoints has verified that your SQL works, your schema accepts the write, and your data model round-trips correctly. That’s real coverage of the database, achieved without writing a single SQL assertion.

Where API tests don’t cover you:

  • Business logic that lives inside the database. Stored procedures, triggers, generated columns, computed views. If a discount calculation runs inside a Postgres function, an API test can verify the output. It can’t verify the function under conditions the API never exercises: edge-case inputs, concurrent execution, transaction rollback behavior.
  • Constraints the app doesn’t fully exercise. Foreign keys, unique indexes, check constraints. API tests typically trigger only the constraints the happy-path hits. Corrupt data that arrives via a migration, a data-load job, or a bug in a sibling service will hit constraints your API tests never touched.
  • Query performance and plan stability. An API test measures end-to-end latency. It doesn’t tell you which query is slow, which index it’s using, or whether tomorrow’s query planner is going to pick a different plan when the data grows.
  • Schema evolution. Whether your migration successfully applied, and whether the app after the migration behaves the same as the app before it, is a class of test that has to run against the database itself.

The honest checklist for “do we need database testing beyond API tests”:

  • How much of the business logic lives in SQL? If the answer is “CRUD only, all business logic in the service layer,” API tests probably cover you. If the answer is “significant logic in stored procedures, triggers, or views,” you need direct database tests.
  • How complex is the data model? Simple normalized schemas rarely surprise. Denormalized reporting tables, EAV models, or heavily-related data with cascades are worth direct testing.
  • How aggressively do you change schema? A monolith with five schema changes per year can rely on migration reviews. A microservice fleet doing daily schema changes needs automated migration-safety tests.
  • What’s the blast radius of a bad query? If a bad SQL statement ships and it locks a table for two hours, that’s an outage. Direct testing pays for itself.

The five things to actually test

Once you’ve decided database tests are worth writing, these are the five categories that cover most of the value.

1. Schema integrity and migration safety

Every migration should pass three checks: it applies cleanly to a production-shaped database, the app on the new schema behaves correctly, and the app on the OLD schema still works because deploys are not atomic across services.

The last one is the check most teams miss. If a migration drops a column, and the old service version is still running while the new one deploys, the old service crashes on every request that touches that column. This is why backwards-compatible migrations are a hard requirement in any org running more than one service: never drop or rename columns until every client has confirmably migrated off them. Add-only until the readers have moved.

Test this by running your application-layer test suite against the pre-migration schema, applying the migration, running it again, and confirming both passes are green.

2. Data integrity through business logic

If your business logic lives in the database (stored procs, triggers, computed columns), test it directly. The pattern:

-- Setup: insert known input
INSERT INTO orders (customer_id, subtotal, coupon_code)
VALUES (42, 100.00, 'SUMMER10');

-- Trigger the logic: call the proc or update the row
CALL apply_discount(1);

-- Assert: verify the computed state
SELECT discount_amount, total FROM orders WHERE id = 1;
-- Expected: 10.00, 90.00

This is unit-level testing of database code, and it’s the highest-ROI test you can write when the business logic actually lives there. Skipping it because “our test framework doesn’t do SQL” is how you end up with a discount bug that ships to prod.

3. Query correctness under realistic data

Your app’s queries need to return the right rows under conditions the API tests don’t create. Common failure modes:

  • Sort order not deterministic. A query returns “the first order” via LIMIT 1 ORDER BY created_at. Under real data with millisecond collisions, it may return either of two rows. Fix: order by a tiebreaker.
  • NULL handling drift. A WHERE status != 'closed' filter also excludes rows where status is NULL, unlike the mental model most developers hold.
  • Data-volume-sensitive queries. A query fast on 10K rows becomes a full-table scan on 10M. The plan changes; the query doesn’t.

Test these against a database seeded to realistic volume (millions of rows in the largest tables), not the dozens used by API tests.

4. Performance and query-plan stability

Run EXPLAIN on every SQL query you own, automated, checked into CI. Alert on plan changes when data grows. The most valuable performance test isn’t a load test; it’s a query-plan-regression test that catches the moment the planner switches from an index scan to a sequential scan.

Under real load, add:

  • Concurrent-connection tests that verify locking behavior.
  • Isolation-level tests (does a read-uncommitted read return dirty data your app can’t handle?).
  • Long-running-transaction tests (does a slow read block writes?).

5. Concurrency and race conditions

Everything the pillar says about parallelization applies to the database layer specifically. Two API tests running concurrently might each try to insert a row with email = '[email protected]' if unique-index handling wasn’t in the setup. A stored procedure that reads a value, computes, and writes it back is a lost-update bug waiting for a second caller.

The interview question that filters on this: describe a race condition you’ve hit in a database and how you tested for it afterward. Candidates who have hit one remember the specifics. Candidates who haven’t will give you a textbook definition.

The concrete pattern that eliminates most of these collisions is per-worker database isolation: each parallel test worker gets its own schema (Postgres) or its own file (SQLite), never a shared one. The docs/scaling-to-10k.md walkthrough in qarepo-playwright-starter maps the SQLite version to a Postgres CREATE SCHEMA worker_${i} pattern for real production suites.

Architectural smells worth catching in review

Some of the highest-value database work isn’t testing. It’s catching architectural decisions early that will be expensive to reverse. If you’re the senior QA reviewing designs, these are the smells:

  • Using the transactional database for reporting. Analytics queries scan wide, run long, and starve OLTP for buffer cache. Any org that grows out of “we’ll just run this SELECT on prod” needs a read replica, an OLAP store, or both. If reports are running against the primary, the question isn’t “how do we test this?” It’s “when do we split the workload?”
  • Heavy materialized views. Materialized views trade query cost for refresh cost and staleness. Fine when it’s clear which one you’re paying for. Not fine when a nightly refresh silently drifts to a two-hour job that overlaps morning traffic.
  • Database jobs adding pressure to transactional performance. Cron jobs that scan large tables, sync data to a downstream system, or aggregate for reporting compete with real user traffic. Test by running them during simulated peak load, not off-hours.
  • Schema changes that aren’t backwards-compatible. Dropping a column, renaming a column, tightening a constraint. Every one of these is a potential outage during the deploy window. Enforce via CI check that flags non-backwards-compatible migrations for architect review.
  • Silent full-table scans. A query that works on 10K rows is a hidden time bomb at 10M. Flag any SQL without a WHERE clause on an indexed column in code review.

Database provider quirks that show up in interviews

Interviewers ask what database you’ve worked with because the specifics matter. Generic SQL knowledge is table-stakes; provider-specific instincts are where senior candidates differentiate.

PostgreSQL

  • MVCC and VACUUM. Postgres never overwrites rows; updates create new tuples and mark old ones dead. Dead tuples are reclaimed by VACUUM. If VACUUM is falling behind, tables bloat and queries slow. A candidate who has diagnosed a bloated table has real Postgres experience.
  • Query planning with EXPLAIN ANALYZE. Not just EXPLAIN. The ANALYZE variant runs the query and shows actual row counts and timing. Every candidate should have opened an EXPLAIN plan and identified the slow node.
  • JSONB. If your app uses JSONB columns, index strategy matters (GIN indexes, expression indexes). Ask how they’d verify that a JSONB query uses an index rather than scanning.

MySQL

  • Storage engine quirks. InnoDB is default now, but legacy systems have MyISAM tables that don’t support transactions. A candidate should know to check.
  • Isolation-level defaults. MySQL’s default is REPEATABLE READ; Postgres’s is READ COMMITTED. A test that relies on Postgres semantics can silently misbehave on MySQL.
  • Character sets. utf8 in MySQL is a 3-byte character set that doesn’t cover the full Unicode range. utf8mb4 is the actual UTF-8. This is a common bug source when emojis break the app.
  • Replication testing. Async replication means reads from a replica can be stale. Tests that write to primary and read from replica need explicit wait or retry.

Oracle

  • PL/SQL testing. Business logic in Oracle almost always lives in PL/SQL packages. Testing them needs a framework (utPLSQL is the reference tool). Candidates who have used it are rare and valuable.
  • Materialized view refresh modes. ON COMMIT vs. ON DEMAND vs. fast-refresh. The choice has performance implications interviewers dig into.
  • Connection pooling. Oracle connections are expensive. Any load test on Oracle needs realistic pool sizing.

The interview lens

If you’re preparing to interview for a role that involves database testing, expect the loop to fork by seniority.

Junior implementer: expect direct SQL questions (write a JOIN, explain the difference between INNER and LEFT), simple stored-procedure test-writing, and one or two “how would you verify this data is correct” prompts.

Senior strategist: expect the architecture questions above: workload-separation, materialized views, migration safety, and provider quirks. You’re being interviewed for judgment about what to test where, not for your ability to write the tests yourself.

What separates a passing senior answer:

  • Naming when API tests are enough and when they aren’t
  • Distinguishing OLTP from OLAP concerns explicitly
  • Volunteering the backwards-compatible-migration rule without prompting
  • Having an opinion on one specific database provider that goes beyond “I’ve used it”

What separates a passing junior answer: actual SQL fluency. Not perfect, but honest. Candidates who say “I’d look up the exact syntax” and can then explain the shape of the query they’d write are stronger than candidates who bluff through unfamiliar territory.

How to prepare

If your loop includes database testing:

  • Know your provider. Pick one (Postgres is the safest bet in 2026) and know its query planner, isolation levels, and index strategies well enough to answer intermediate questions without hedging.
  • Practice writing tests against real databases. Not mocks. Spin up a real Postgres in Docker, load a realistic dataset, and write tests that assert on rows, not on API responses.
  • Read one migration-safety guide end-to-end. The Gitlab and Github engineering blogs both have solid writeups on backwards-compatible schema changes; either is enough.
  • Have an opinion on OLTP-vs-OLAP separation. If your candidate answer is “we just used the primary for reporting,” expect a follow-up.

Practical structured prep on SQL fluency and Postgres specifically: Udemy’s database courses cover the query-planning and indexing surface faster than assembling it from official docs alone.