Database Testing for QA Engineers

What database testing covers

Most defects that reach a database are invisible at the interface. An order confirmation page can render perfectly while the order row carries the wrong total, a payment is linked to the wrong order, or a cancelled order’s items stay reserved. Users meet these defects later — in a monthly statement, a refund that fails, a report that does not add up — when they are expensive to trace.

Database testing is not a separate test level. It is a set of checks applied at the integration, system and acceptance levels wherever data is created or changed. Its oracle is the data model itself: the schema, its constraints, the business rules that relate one table to another, and the numbers that must reconcile.

• Structural (schema) testing — tables, columns, types, lengths, defaults, constraints and indexes match the design and the migration scripts.
• Functional data testing — every create, read, update and delete the UI or API performs leaves exactly the rows it should.
• Integrity testing — keys, uniqueness, NOT NULL, CHECK and foreign-key rules reject bad data, and related records stay consistent.
• Logic testing — stored procedures, triggers, views and scheduled jobs give the right results for normal, boundary and error inputs.
• Transaction and concurrency testing — all-or-nothing behaviour, and correct results when two users change the same data at once (covered in depth on the transactions and migrations page).
• Migration testing — schema and data changes run correctly on realistic volumes and keep the running application working.
• Non-functional testing — query performance on large tables, access control and protection of personal data.

CRUD validation: act through the product, confirm in the database

The core technique is simple: perform the action the way a user or client would — through the UI or the API — then query the database to confirm the effect. Inserting rows directly and calling that a test of the feature skips the application code you are supposed to be testing. Direct inserts are for setting up preconditions, not for exercising the behaviour.

For each operation, check the target row and its neighbours. A create should write one row with every field mapped correctly, server-generated values (ids, timestamps, status) set, and child rows created. An update should change only the intended columns and move updated_at. A delete should remove or soft-delete exactly what the requirement says and treat child rows as designed. A read should return what is stored — Arabic text, emoji, maximum lengths and decimals included — without truncation or re-rounding.

For example, after creating an order with two units of a 75.00 SAR item and one 40.00 SAR item through the API, the query at the end of this section verifies the order row, its item rows and the server-set values. Typical CRUD defects this kind of check catches:

• Field mapping — the API accepts delivery_notes, but the column stays NULL.
• Precision — an amount of 19.995 is stored as 19.99 in one table and 20.00 in another.
• Encoding — Arabic names are saved as “????” because a column or the connection uses a non-Unicode character set.
• Time zones — created_at is stored in local time by one service and in UTC by another.
• Partial writes — the order row exists but its items do not after a validation error on the third item.

-- After POST /api/orders returned order_id 1042 for customer 317
SELECT order_id, customer_id, status, total_amount, created_at, updated_at
FROM orders
WHERE order_id = 1042;

SELECT product_sku, quantity, unit_price
FROM order_items
WHERE order_id = 1042
ORDER BY order_item_id;

-- Expected: customer_id = 317, status = 'PENDING',
-- total_amount = 2 x 75.00 + 1 x 40.00 = 190.00,
-- exactly two item rows, created_at set by the server (UTC)

Try it: SQL Formatter Try it: SQL Query Validator

Constraints: the rules the database enforces

Constraints are the database’s last line of defence: even when application validation has a bug, a well-designed schema refuses data that breaks the model. Testing them has two sides. First, confirm the constraint exists — read the schema or the migration, not only the design document. Second, confirm the application handles the rejection gracefully: a clear validation message or a 4xx API response, not a 500 carrying a raw database error.

Engines differ, so check the one you ship on. MySQL enforces CHECK constraints only from version 8.0.16 (earlier versions parsed and ignored them), and SQLite enforces foreign keys only when the connection enables them with PRAGMA foreign_keys = ON. A constraint that exists in the migration file but is not enforced at runtime is a finding.

• Primary key — every row is uniquely identified. A retried request must not create a second row with a new id for the same business event; that is an idempotency problem the key alone does not solve.
• UNIQUE — register the same email twice, including with different letter case and leading or trailing spaces. Agree with the team whether Layla.H@example.com and layla.h@example.com are the same customer, then check that the database agrees.
• NOT NULL — send the field missing, empty and whitespace-only. An empty string is not NULL in PostgreSQL or MySQL, so NOT NULL alone does not stop blank names.
• CHECK — quantity above zero, amounts not negative, status within an allowed set. Test the boundaries (0, 1, the maximum) and one step outside them.
• FOREIGN KEY — create an order for a customer id that does not exist. The insert must fail and the API must return a meaningful error.
• Defaults, types and lengths — omitted fields get the documented default, and each column holds the widest valid input. A 50-character column behind a field the UI allows 100 characters for is a truncation bug waiting to happen.

Try it: Boundary Number Dataset Generator Try it: Special Character Dataset Generator Try it: Unicode Dataset Generator

Relationships, referential integrity and cascades

Referential integrity means every reference points to something that exists: every order item belongs to an existing order, every payment to an existing order, every order to an existing customer. When foreign keys are declared, the database enforces this. When they are not — common across microservices, in sharded or legacy schemas, and in tables loaded by batch jobs — the application must, and orphaned rows become one of the most frequent data defects a tester finds.

Cascade rules decide what happens to child rows when a parent is deleted or its key changes. ON DELETE CASCADE removes the children with the parent; RESTRICT or NO ACTION refuses the delete while children exist; SET NULL keeps the child and clears the reference. Each is right for some relationship and dangerous for another: cascading a customer delete into invoices may destroy records the business must keep. Test each relationship against the requirement, not against whatever the schema happens to do.

• Delete a parent through the product, then query every child table for rows that still reference it.
• Check soft deletes: a customer marked as deleted should disappear from lists, while their historical orders may need to stay readable.
• Run orphan-detection queries after batch imports, data migrations and failed jobs — the SQL page shows the LEFT JOIN … IS NULL pattern.

Stored procedures, triggers and back-end rules

When business logic lives in the database — a stored procedure that closes a month, a trigger that writes an audit row, a view that feeds a report — treat it like any other code: define inputs and expected outputs, and choose cases with equivalence partitioning and boundary values. Call procedures directly in a test database with prepared data, and also exercise them through the application path that uses them.

Triggers need special attention because they act silently. Check that they fire on every path that should fire them (insert, update and delete; single-row and bulk statements), that they do not fire twice, and that a failure inside a trigger rolls back the statement that caused it instead of leaving half the work done.

• Audit trigger — changing an order’s status writes exactly one audit row with the old and new value, the acting user and a server timestamp.
• Procedure boundaries — run a month-end procedure for February in a leap year, and for a month with no orders at all.
• Back-end validation — a rule enforced in the UI is also enforced by the API and, where designed, by the database. Bypass the UI with a direct API call to confirm.

Data consistency across services, caches and search indexes

Modern systems keep copies of data: a read replica, a cache, a search index, a reporting warehouse, another microservice’s own database. Each copy can drift from the source of truth. A price updated in the database but still cached, an order visible in the order service but missing from invoicing, a deleted customer still findable by search — these are consistency defects, and you find them by comparing the copies.

Reconciliation is the technique: count and sum the same population in two places and compare. Counts by status and by day, sums of amounts, and lists of ids present on one side but not the other catch most drift. Where replication is asynchronous, agree the acceptable delay with the team — eventual consistency is a design choice, not a defect — and test that the copies converge within it, including after failures, retries and replays.

• Orders created today: count and total in the order database, in the invoicing service and in the finance report.
• Search index: every active product id in the database is in the index, and no deleted id remains.
• Cache invalidation: update a record, then read it through the API immediately and after the cache lifetime; if the design says writes invalidate the cache, both reads return the new value.
• Event-driven flows: stop the consumer, create records, restart it, and confirm that nothing was lost or applied twice.

Try it: SQL IN Clause Generator Try it: SQL VALUES Generator

Test data: synthetic, seeded and resettable

Database tests need known data — known customers, known orders, known totals — so that the expected result can be written before the test runs. Build it deliberately: a seed script creates the baseline, each test adds what it needs and cleans up or runs inside a transaction that is rolled back, and the whole database can be reset to the baseline in minutes.

Do not copy production personal data into test environments. Saudi Arabia’s Personal Data Protection Law (PDPL), overseen by SDAIA, governs the processing of personal data, and a test environment holding real customers’ names, national ids, phone numbers and addresses is personal-data processing under weaker controls than production. Use synthetic data by default. If production-shaped data is genuinely needed, for example for a migration rehearsal, agree the approach with your data protection officer: masked data can still be personal data if it can be linked back to a person.

• Seed from generated SQL: CREATE TABLE and INSERT scripts for a realistic baseline, kept in version control beside the migrations.
• Include difficult values on purpose: maximum lengths, zero amounts, Arabic and Devanagari text, emoji, apostrophes in names, dates at month and year ends.
• Choose a reset strategy: roll back per test, truncate and re-seed per suite, restore a snapshot or template database, or start a fresh containerised database for each pipeline run.
• For performance and migration tests, generate data at production-like row counts, not ten rows.

Try it: SQL Dataset Generator Try it: SQL INSERT Generator Try it: CSV to SQL Try it: JSON to SQL

Performance at a tester’s level: indexes, query plans and N+1

You do not need to tune a database to find its performance defects. The usual cause is plain: a query that was fast on 200 test rows scans millions of rows in production because the column it filters on has no index. Test with realistic volume, watch response times as data grows, and ask for the query plan when something is slow.

EXPLAIN shows how the database intends to run a query — whether it uses an index or scans the whole table, and in what order it joins. PostgreSQL and MySQL provide EXPLAIN and EXPLAIN ANALYZE; SQLite provides EXPLAIN QUERY PLAN. EXPLAIN ANALYZE actually executes the statement, so never run it on an UPDATE or DELETE outside a transaction you roll back. You do not have to read every line of a plan: a full scan of a large table in a query that should return a handful of rows is worth a bug report.

• N+1 queries — a list page loads 50 orders with one query, then runs one more query per order to fetch its customer: 51 round trips instead of one or two. It shows up in the query log or an APM trace as a burst of near-identical statements.
• Deep pagination — OFFSET-based paging slows down on late pages of large tables; test page 1 and page 5,000.
• Unbounded queries — exports and reports without limits that load a whole table into memory.
• Missing indexes on foreign keys and on columns used in the WHERE, JOIN and ORDER BY of frequent queries.

Security: injection, privileges and personal data

SQL injection happens when user input is concatenated into SQL text. The OWASP defence is parameterised queries (prepared statements) everywhere, plus allow-list validation for parts that cannot be parameters, such as a sort column name. As a tester, confirm the expectation rather than writing exploits: put values containing quotes, semicolons, comment markers and other SQL metacharacters into every input and expect them to be stored and shown literally, with no database error, stack trace or change in behaviour. A database error message in any response is a finding in itself. Deeper injection testing belongs in an authorised security assessment.

Least privilege limits the damage when something does go wrong. The application account should have only what it needs — typically read and write on its own tables, no schema changes, no administrator rights — and migrations should run under a separate account. Testers and reporting tools should use read-only accounts.

• Find out which account the application connects with and what it may do; a production application running as the database owner is a finding.
• Look for personal data where it should not be: application logs, error messages, analytics events, CSV exports, backups on shared drives and test environments.
• Check that passwords are stored with a dedicated password-hashing algorithm (such as Argon2id, bcrypt, scrypt or PBKDF2, which salt each password) rather than a plain or fast general-purpose hash, and that tokens and other secrets are hashed or encrypted as designed, never in plain text.
• Audit columns — created_at, updated_at, created_by, updated_by — are set by the server, not accepted from the client; updated_at and updated_by change whenever the record changes, while created_at and created_by never change after the insert.

Try it: Special Character Dataset Generator

Typical defects, automation and CI

Database checks automate well because their expected results are exact. Put integrity and reconciliation queries in the same suite as the API tests: call the API, query the database, assert. Run the suite in the pipeline against a freshly migrated and freshly seeded database so every build proves that the migrations apply cleanly and the data rules still hold. Keep a small set of read-only integrity queries — orphans, duplicates, totals that do not match — to run on staging after each deployment and on a schedule.

When a check fails, the rows it returns are the evidence: attach the query and its output to the bug report. Defects found this way typically read like this:

• “Order total differs from the sum of item lines when a discount is applied to the last item”
• “Deleting a customer leaves orphaned payment rows (no foreign key on payments.order_id)”
• “Duplicate customer created when Register is double-clicked”
• “Arabic product names truncated in order_items.product_name”
• “Search still returns products deleted more than 24 hours ago”
• “Order history page runs one query per order (N+1): 4 s with 200 orders”
• “API returns 500 with the raw database error when the email already exists”

Frequently asked questions

References

• Constraints — PostgreSQL DocumentationPostgreSQL Global Development GroupCheck, not-null, unique, primary-key, foreign-key and exclusion constraints — the rules a database enforces and a tester verifies.
• Transactions — PostgreSQL DocumentationPostgreSQL Global Development GroupWhat a transaction is: several steps applied all-or-nothing with BEGIN, COMMIT and ROLLBACK, invisible to others until committed.
• Transaction Isolation Levels — MySQL 8.4 Reference ManualOracle (MySQL)The four SQL isolation levels as InnoDB implements them, with REPEATABLE READ as its default.
• OWASP Cheat Sheet Series: SQL Injection PreventionOWASP FoundationParameterised queries, stored procedures used safely, allow-list validation and least privilege — the defences a database test should confirm.
• SQL InjectionOWASP FoundationHow SQL injection works and why parameterised queries, not string building, are the defence.
• Personal Data Protection Law (English translation)Saudi Data & AI Authority (SDAIA) · 2023The Saudi law governing personal data — why test environments should use synthetic data rather than copies of real customer records.
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• ISTQB Certified Tester Foundation Level (CTFL) v4.0ISTQB · 2023The foundation syllabus of software testing: test levels and types, test design techniques, test management and tools.