Part 22: Database Testing | TechByteByByte

The screen shows a user-facing result; the database stores the records behind it. A test may safely verify both.

browser action → application logic → database record

Series: Playwright Zero to Expert | Demo app used throughout this series: saucedemo.com (conceptual — SauceDemo itself has no exposed database for direct testing, so this part builds general, transferable database-testing skills using SQL fundamentals and a generic example schema)

Part 0.1 established three layers: frontend, backend, database. Parts 6 through 21 have thoroughly covered the first two. This part completes the picture — going all the way down to where data actually, permanently lives, and asking the question Part 0.3 first posed as a hypothetical: when the UI shows something, how do you know it’s actually correct, at the source?

SQL Basics, for QA Purposes

A database stores structured data permanently, organized into tables — think of a table conceptually like a spreadsheet: rows and columns. Each row represents one single record (one user, one order, one product). Each column represents one specific piece of information about that record (a username, an order date, a price). A primary key is a column (often an auto-generated id) guaranteed to be unique for every row in a table — the reliable way to reference one exact, specific record without ambiguity.

SQL (Structured Query Language)

is the language used to ask a database questions and make changes to it. You don’t need to become a database administrator for QA work — but a working knowledge of a handful of core statements is genuinely valuable.

SELECT * FROM users WHERE username = 'standard_user';

Read this exactly as it sounds: “select every column, from the users table, where the username column equals standard_user.” SELECT retrieves data; WHERE filters which rows are returned, using conditions exactly analogous to the if conditions from Part 2.

SELECT order_id, total, status FROM orders WHERE user_id = 42;

This selects only three specific columns (rather than every column with *), for rows where user_id matches 42 — a more targeted, deliberate query than pulling back everything.

A JOIN combines data across multiple related tables — genuinely common, since real data is rarely self-contained in a single table. Imagine an orders table that only stores a user_id, not the user’s actual name — to answer “what orders has Amar placed?” you need to combine orders and users:

SELECT orders.order_id, orders.total, users.username
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.username = 'standard_user';

This reads as: “give me each order’s id and total, along with the matching user’s username, by connecting the orders and users tables wherever an order’s user_id matches a user’s id, but only for the user named standard_user.”

Connecting UI/API Verification to the Database

Here’s where this genuinely pays off, tying directly back to Part 0.3’s original walkthrough:

Analogy: Checking the Bank Ledger vs. The ATM Screen Imagine placing money into a bank:

UI / API verification (The ATM Screen): You insert your card into the machine. The screen flashes: “Deposit successful! Your balance is $1,000.” You trust the screen because it’s what you see.

Database verification (The Vault Ledger): Behind the scenes, the bank’s computers must write a permanent row to the SQL database ledger table. If the database crashes or corrupts during that write, the ATM screen might still say $1,000 (loaded from transient memory cache), but your money doesn’t actually exist on the books. Reaching directly into the SQL database ledger with a query is how you confirm the transaction is permanently and legally recorded at the source.

📊 Visual Flowchart: Three-Layer Integration Validation

Here is the data transmission chain from a front-end user action to its final storage spot in the database:

graph TD
    subgraph LayerFrontend ["Layer 1: Frontend UI"]
        UI["User clicks 'Place Order'<br>Asserts: 'Order Confirmed' visible"]
    end

subgraph LayerBackend ["Layer 2: Backend API"]
        API["API returns '201 Created'<br>Payload: { id: 987, total: 89.97 }"]
    end

subgraph LayerDB ["Layer 3: Database Storage"]
        DB["orders table: Row 987 inserted<br>user_id: 42, total: 89.97, status: 'confirmed'"]
    end

UI -->|1. Triggers HTTP POST| API
    API -->|2. Writes record| DB
    Test["Playwright Test"] -.->|3. Connects & runs SELECT query| DB

Imagine testing an order-placement flow. A test might click through the UI, place an order, and see “Order Confirmed” on screen. A stronger test also verifies, via the API (Part 17), that the order was correctly returned in a subsequent GET /orders call.

But there’s a question neither the UI nor even the API layer can fully answer on their own: was the order actually, correctly, and permanently stored — with the right total, the right status, the right user association — in the database itself?

A bug where an order appears to succeed in the UI, and even briefly appears correct via the API, but is subtly malformed or incomplete once actually persisted, is a genuinely real class of bug — and the only way to catch it directly is to check the database itself.

import { test, expect } from "@playwright/test";
import { Client } from "pg"; // example using a PostgreSQL client library

test("order is correctly persisted in the database after checkout", async ({
  page,
}) => {
  // ... complete checkout through the UI ...
  await page.getByRole("button", { name: "Place Order" }).click();
  await expect(page.getByText("Order Confirmed")).toBeVisible();

  // Now verify directly at the source
  const client = new Client({ connectionString: process.env.TEST_DB_URL });
  await client.connect();

  const result = await client.query(
    "SELECT total, status FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 1",
    [testUserId],
  );

  expect(result.rows[0].status).toBe("confirmed");
  expect(result.rows[0].total).toBe(89.97);

  await client.end();
});

Notice process.env.TEST_DB_URL — exactly Part 16 and Part 21’s established pattern for handling sensitive, environment-specific configuration, applied here to a database connection string, which is very much the kind of credential that genuinely should never be hardcoded.

When Not to Verify via the Database Directly

This is worth an honest, direct discussion, because it’s a genuine architectural debate, not a settled rule — and being able to reason through it thoughtfully is exactly the kind of answer that distinguishes a senior candidate in an interview.

The case for direct database verification is exactly what the example above demonstrates: it catches a real class of bug — data corruption or incorrect persistence — that UI and API checks alone genuinely cannot see.

The case against relying on it too heavily is architectural: a test reaching directly into the database creates a tight coupling between your test suite and the database’s internal schema — table names, column names, exact structure — details that are, deliberately, meant to be internal implementation, hidden behind the API layer specifically so they can be refactored freely without breaking anything that depends on the public interface.

If the database schema changes (a column gets renamed, a table gets split into two), and your tests query that schema directly, your tests break — even if the actual, publicly-facing behavior of the application (what the API returns, what the UI shows) hasn’t changed at all from a real user’s perspective.

A reasonable, balanced position: prefer verifying through the public interface (the API, then the UI) as your default, since that’s what real users and real client applications actually depend on, and it naturally stays valid even as internal implementation details change.

Reserve direct database verification specifically for the cases where it catches something genuinely otherwise invisible — data integrity issues, verifying that a background job correctly updated a record, or confirming something was actually deleted rather than just hidden from the UI — and treat it as a deliberate, occasional tool, not your primary or default verification method for every single test.

How It Works in a Real Test Run

Database verification crosses an architectural boundary. A UI action reaches the backend, business logic writes data, and a separate database client observes the stored result. Use this when persistence itself is the requirement, not as a replacement for the UI’s observable outcome.

Queries should use a dedicated low-privilege test account, parameters instead of string concatenation, deterministic cleanup, and environment guards that make production access impossible.

Interview Questions

Q: What is a primary key, and why does it matter for reliably verifying a specific record in a test?

Ans: A primary key is a column guaranteed to be unique for every row in a table, typically an auto-generated id. It matters for testing because it lets you reliably reference one exact, specific record — like a specific order — without ambiguity, rather than trying to identify it by some other value that might not be unique or might change.

Q: What does a SQL JOIN do, and why is it often necessary when verifying data related to a specific user’s action?

Ans: A JOIN combines related data stored across multiple separate tables — real data is rarely fully self-contained in one table. It’s often necessary because verifying something like “did this specific user’s order get recorded correctly” typically requires combining an orders table (which might only store a user_id) with a users table (which has the actual username), to connect the two pieces of related information together in one query.

Q: What kind of bug can direct database verification catch that UI and API-level testing genuinely cannot?

Ans: It can catch data integrity issues — a case where an order appears to succeed and even looks correct through the UI and momentarily through the API, but is actually stored incompletely, incorrectly, or corrupted at the database level. Since the UI and API only reflect what the backend chooses to report back, a bug specifically in how data is actually persisted can be invisible at those layers while still being a genuine, real problem at the source.

Q: What’s the architectural risk of a test suite that verifies data directly against the database as its default, primary method, rather than through the API?

Ans: It tightly couples the test suite to the database’s internal schema — table and column names and structure — which are meant to be internal implementation details, deliberately hidden behind the API so they can be changed freely without affecting anything that depends on the public interface. If the schema changes without any real change to the application’s actual, user-facing behavior, database-coupled tests can break unnecessarily, even though nothing a real user or client application depends on has actually changed.

Q: Given the trade-off, what’s a reasonable general strategy for when to use direct database verification versus verifying through the API or UI?

Ans: Default to verifying through the public interface — the API and UI — since that reflects what real users and client applications actually depend on, and it naturally stays valid as internal implementation details change. Reserve direct database verification specifically for cases where it catches something genuinely otherwise invisible at those layers, like confirming correct data persistence, or verifying a background process updated a record correctly — using it deliberately and sparingly rather than as the default verification method for every test.

Q: Why might it matter that a test’s database connection string, like an API key or password, comes from an environment variable rather than being hardcoded?

Ans: A database connection string typically grants direct, often broad, access to real or test data — exactly the kind of sensitive credential Part 5 and Part 16 already established shouldn’t be committed to version control. Loading it from an environment variable keeps the actual value out of the codebase entirely, letting different environments (local, CI, staging) each safely supply their own appropriate value without ever exposing it in source code.

Exercises — Part 22

Understand: Without looking back, explain in your own words why a test that only checks the UI and the API might still miss a genuine bug in how an order is actually stored in the database.

Simple Practice: Using any simple local database you have access to (or a free online SQL practice environment), write a SELECT query with a WHERE clause filtering on a specific column, and a second query using a JOIN across two related, hypothetical tables (like users and orders).

Real-World Scenario: Design a test scenario, in writing, where a UI shows “Order Confirmed” and an API call also returns the order successfully, but a direct database check reveals the order’s status column is actually still "pending" rather than "confirmed" — a genuine, real discrepancy between what’s displayed and what’s actually stored. Explain what this discrepancy would suggest about where the underlying bug likely lives.

Challenge: Argue, in writing, for a specific real or hypothetical feature, whether verifying it directly against the database is genuinely warranted or would be over-coupling your tests to internal implementation details — using the “when to use it vs. when not to” reasoning from this part to justify your position either way.

Next: Part 23 — Screenshots, Video and Trace

— capturing, storing, and actually reading these artifacts to diagnose a failure quickly.

Author: TechByteByByte Editorial Team
Reviewed by: TechByteByByte Admin
Published: August 31, 2026
Last reviewed: August 31, 2026