Database Testing for QA Engineers: SQL Queries Every Tester Should Know

A form that returns HTTP 200 and shows a success message can still fail to write to the database if the error handler silently swallows a constraint violation. Direct SQL queries catch this: they verify the expected row exists in the expected table with the expected values, bypassing the UI and API entirely. This article covers the five query patterns that handle most QA scenarios, how to set up and tear down test data in seconds, and how to run database assertions from within Playwright tests.

Why SQL belongs in a QA engineer's toolkit

Every web application has the same structure: a frontend that displays data, an API that processes requests, and a database that stores state. Automated tests through the UI verify the first layer. API tests verify the second. But the third layer (whether the right data actually landed in the right table with the right values) stays invisible unless you look directly.

The gap shows up constantly in real testing work. A form submission returns HTTP 200, the UI shows a success message, and the database write silently failed due to a constraint violation that was swallowed by the error handler. A status update visually reflects in the UI because the front end updated its local state, but the API call that was supposed to write to the backend never fired. A test seed script runs, the test executes, and you discover the test data was inserted into the wrong environment's database.

SQL gives you three concrete capabilities: verify that actions had the expected effect on data, set up test data faster than any UI workflow allows, and debug failures that the application's own interface hides from you.

None of this requires advanced database knowledge. Five query patterns cover the vast majority of what a QA engineer does in practice. The rest of this article teaches those patterns, shows how to use them for verification and test data management, and covers how to run queries directly from Playwright tests.

The five queries that cover 80% of QA work

Start here. These five patterns handle most situations you'll encounter as a tester.

-- Get a specific user by email
SELECT id, email, role, is_active
FROM users
WHERE email = 'testuser@example.com';

-- Get all orders for a customer
SELECT id, status, total_amount, created_at
FROM orders
WHERE customer_id = 42;

-- Get orders placed in the last 24 hours
SELECT id, customer_id, status, total_amount
FROM orders
WHERE created_at > NOW() - INTERVAL '24 hours';

-- Orders with customer email
SELECT orders.id, orders.status, orders.total_amount, users.email
FROM orders
JOIN users ON orders.customer_id = users.id
WHERE orders.id = 1001;

-- How many orders per status
SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status;

INSERT INTO users (email, password_hash, role, created_at)
VALUES ('qa_test_user@test.com', 'hashed_value', 'customer', NOW());

UPDATE orders
SET status = 'cancelled'
WHERE id = 1001;

These five patterns form the foundation. Everything else in this article builds on them.

Verifying test results: asserting what the UI cannot show you

After a test action, the most direct verification is a query that checks the expected state in the database. If the query returns the expected row, the operation succeeded end-to-end. If it returns nothing, something failed between the user action and the persistent state, even if the UI appeared to succeed.

After a user registration:

-- Did the user get created with the right defaults?
SELECT id, email, role, is_active, email_verified
FROM users
WHERE email = 'newuser@test.com';

You expect one row, with role = 'customer', is_active = true, and email_verified = false. Any deviation is a bug worth investigating before it reaches production.

After placing an order:

-- Did the order land with the right status and amount?
SELECT 
    o.id,
    o.status,
    o.total_amount,
    o.created_at,
    u.email AS customer_email
FROM orders o
JOIN users u ON o.customer_id = u.id
WHERE u.email = 'testuser@test.com'
ORDER BY o.created_at DESC
LIMIT 1;

You expect status = 'pending' and total_amount matching what the UI displayed. If the total is off by a cent, you found a rounding bug. If status is anything other than 'pending', you found a state transition bug.

For row count assertions, verifying that exactly N records were created, updated, or deleted:

-- Verify exactly three order items were created for this order
SELECT COUNT(*) AS item_count
FROM order_items
WHERE order_id = 1001;

-- Verify no duplicate user records exist for this email
SELECT COUNT(*) AS duplicate_count
FROM users
WHERE email = 'testuser@test.com';

These queries are assertion-ready. In an automated test, you compare the returned count against the expected value. In manual testing, you run them in your SQL client and eyeball the result.

Setting up test data with INSERT, and cleaning it up

Going through the UI to create test data is slow. A test that needs a user with five completed orders, two pending orders, and a cancelled order might take ten minutes to set up manually. With SQL, it takes seconds.

-- Create a test user
INSERT INTO users (email, password_hash, role, is_active, created_at)
VALUES (
    'qa_load_test_user@test.com',
    '$2b$10$placeholder_hash_value',
    'customer',
    true,
    NOW()
);

-- Get the new user's ID for subsequent inserts
SELECT id FROM users WHERE email = 'qa_load_test_user@test.com';

-- Create orders for that user (assuming id = 99)
INSERT INTO orders (customer_id, status, total_amount, created_at)
VALUES
    (99, 'completed', 89.99, NOW() - INTERVAL '10 days'),
    (99, 'completed', 124.50, NOW() - INTERVAL '7 days'),
    (99, 'completed', 45.00, NOW() - INTERVAL '4 days'),
    (99, 'pending', 67.25, NOW() - INTERVAL '1 day'),
    (99, 'pending', 210.00, NOW()),
    (99, 'cancelled', 30.00, NOW() - INTERVAL '5 days');

This puts the database in exactly the state your test requires, without clicking through any UI. The test can now verify behaviors that depend on order history: things like loyalty discounts, order limit enforcement, or dashboard statistics.

The cleanup is equally important. Test data left in shared databases causes test pollution: a later test finds unexpected records, counts come back wrong, and you spend an hour debugging something that was never actually broken.

-- Clean up after tests using a consistent naming convention
DELETE FROM orders WHERE customer_id IN (
    SELECT id FROM users WHERE email LIKE '%@test.com'
);

DELETE FROM users WHERE email LIKE '%@test.com';

The convention matters: use a consistent domain like @test.com or a prefix like qa_ for all test-generated data. This makes cleanup queries safe and reliable. You can run them at the end of every test run without worrying about touching real data.

JOINs for QA: finding orphaned records and verifying relationships

Relational databases enforce relationships through foreign keys, but bugs happen at the boundary between tables. An order item might reference a product that was deleted. A session record might point to a user that no longer exists. These orphaned records cause silent failures that are difficult to trace without looking at the data directly.

LEFT JOIN exposes these gaps. Unlike a regular JOIN which only returns rows where both sides have a match, a LEFT JOIN returns all rows from the left table and fills in NULL for any columns from the right table where no match exists:

-- Find order items with no matching product (orphaned records)
SELECT 
    order_items.id AS item_id,
    order_items.order_id,
    order_items.product_id,
    products.name AS product_name
FROM order_items
LEFT JOIN products ON order_items.product_id = products.id
WHERE products.id IS NULL;

Any row this returns is an orphaned order item: it references a product that doesn't exist. In a healthy database, this query returns zero rows.

-- Find orders with no customer (shouldn't be possible, but worth verifying)
SELECT orders.id, orders.customer_id, orders.total_amount
FROM orders
LEFT JOIN users ON orders.customer_id = users.id
WHERE users.id IS NULL;

-- Verify every order has at least one order item
SELECT 
    o.id AS order_id,
    o.status,
    COUNT(oi.id) AS item_count
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.status
HAVING COUNT(oi.id) = 0;

Run these queries after test scenarios that involve deletion, archiving, or bulk operations. Cascade delete rules are easy to misconfigure, and an orphaned record that should have been cleaned up can cause application errors days later when some process tries to reference it.

Aggregate queries: verifying calculations the UI displays

When your application shows a total, an average, a count, or a summary, you can verify it directly against the database. If the UI shows "Revenue: $4,827.50" for last month, the database should agree.

-- Verify total revenue for a time period
SELECT 
    SUM(total_amount) AS total_revenue,
    COUNT(*) AS order_count,
    AVG(total_amount) AS average_order_value
FROM orders
WHERE status = 'completed'
    AND created_at >= '2026-05-01'
    AND created_at < '2026-06-01';

-- Verify per-product revenue breakdown
SELECT 
    p.name AS product_name,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY p.id, p.name
ORDER BY revenue DESC;

These queries become powerful when the UI calculation and the database calculation disagree. A discrepancy means either the UI is computing something incorrectly, the query that feeds the UI has a bug, or the test data itself is in an unexpected state. All three are worth finding.

-- Verify inventory counts match what the product page displays
SELECT 
    id,
    name,
    stock_quantity,
    CASE WHEN stock_quantity = 0 THEN 'out_of_stock' 
         WHEN stock_quantity < 10 THEN 'low_stock'
         ELSE 'in_stock' 
    END AS computed_status
FROM products
WHERE id IN (101, 102, 103);

The computed_status column lets you compare what SQL calculates against what the UI displays. If the UI shows "In Stock" but the query returns 'low_stock', you've found either a display bug or a business logic inconsistency.

Connecting to a database from a test

For manual verification during debugging, a SQL client like TablePlus or DBeaver is the fastest path. Connect to the database, run your query, read the result. But for automated test suites, you want database verification to happen as part of the test itself.

# Run a single query and get the result
psql $DATABASE_URL -c "SELECT COUNT(*) FROM orders WHERE status = 'pending';"

# Run a query file
psql $DATABASE_URL -f verify_order_state.sql

import { test, expect } from '@playwright/test';
import { Client } from 'pg'; // npm install pg

test('order is saved to database after checkout', async ({ page }) => {
    const testEmail = 'qa_checkout_test@test.com';

    // Perform the UI action
    await page.goto('/checkout');
    // ... fill cart, enter shipping, submit order ...

    // Verify the result in the database
    const db = new Client({ connectionString: process.env.DATABASE_URL });
    await db.connect();

    try {
        const result = await db.query(
            `SELECT o.id, o.status, o.total_amount
             FROM orders o
             JOIN users u ON o.customer_id = u.id
             WHERE u.email = $1
             ORDER BY o.created_at DESC
             LIMIT 1`,
            [testEmail]
        );

        expect(result.rows).toHaveLength(1);
        expect(result.rows[0].status).toBe('pending');
        expect(parseFloat(result.rows[0].total_amount)).toBeCloseTo(89.99, 2);
    } finally {
        await db.end();
    }
});

The $1 placeholder is important. Never concatenate user-controlled values into a SQL string; use parameterized queries for any value that comes from test input or external data. Beyond preventing SQL injection in test utilities, it also handles special characters in email addresses or names without breaking the query.

A practical pattern for larger test suites is to create database helpers alongside your page objects:

// helpers/db.ts
import { Client } from 'pg';

export async function getLatestOrderForUser(email: string) {
    const db = new Client({ connectionString: process.env.DATABASE_URL });
    await db.connect();
    try {
        const result = await db.query(
            `SELECT o.* FROM orders o
             JOIN users u ON o.customer_id = u.id
             WHERE u.email = $1
             ORDER BY o.created_at DESC LIMIT 1`,
            [email]
        );
        return result.rows[0] ?? null;
    } finally {
        await db.end();
    }
}

export async function cleanupTestUser(email: string) {
    const db = new Client({ connectionString: process.env.DATABASE_URL });
    await db.connect();
    try {
        await db.query(
            `DELETE FROM orders WHERE customer_id IN
             (SELECT id FROM users WHERE email = $1)`,
            [email]
        );
        await db.query('DELETE FROM users WHERE email = $1', [email]);
    } finally {
        await db.end();
    }
}

Then in your test:

import { getLatestOrderForUser, cleanupTestUser } from '../helpers/db';

test.afterEach(async () => {
    await cleanupTestUser('qa_checkout_test@test.com');
});

test('order status is pending after checkout', async ({ page }) => {
    // ... UI actions ...
    const order = await getLatestOrderForUser('qa_checkout_test@test.com');
    expect(order).not.toBeNull();
    expect(order.status).toBe('pending');
});

This keeps the SQL out of the test body and makes the assertions read clearly.

Safety rules for database access in testing

A few rules that prevent the kind of incidents that ruin a workday.

BEGIN;

INSERT INTO users (email, role, is_active)
VALUES ('qa_transaction_test@test.com', 'customer', true);

INSERT INTO orders (customer_id, status, total_amount)
VALUES (LASTVAL(), 'pending', 99.00);

-- If anything above failed, this rolls back both inserts
COMMIT;

FAQ

No. You need to understand the tables your application uses: what they're called, what columns they have, how they relate to each other. This comes from reading the schema (most SQL clients show it in a sidebar), asking a developer, or looking at ORM models in the codebase. You don't need to know why the schema was designed that way.

INNER JOIN (or just JOIN) returns rows only where both tables have a matching record. LEFT JOIN returns all rows from the left table, with NULL in the right table columns where no match exists. For QA work: use JOIN when you want to see the combined data, use LEFT JOIN when you want to find missing relationships.

A dedicated test database is strongly preferred. When multiple developers and test pipelines share one database, test data from different runs mixes together and assertions about counts or state become unreliable. Set up a separate database with the same schema, seeded with minimal baseline data, that your tests own entirely.

Four common causes: the value you're filtering on has different casing than what's in the database (WHERE email = 'Test@example.com' won't match test@example.com in a case-sensitive collation); there's leading or trailing whitespace in a column value; you're connected to the wrong database or schema; or the data was inserted inside an uncommitted transaction that another connection can't see yet.

In PostgreSQL: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';. In MySQL: SHOW TABLES;. Most SQL GUI clients also display the schema in a tree view on the left side.