📖 The Story: The Lying UI

An e-commerce company was testing their new "Quick Signup" form. The Playwright test filled out the name, email, and password, clicked "Submit", and asserted that the "Welcome to the app" message appeared. The test passed. They deployed to production.

The next day, the marketing team complained that no new welcome emails were being sent. The engineering team investigated and found a silent bug: a recent database migration had added a NOT NULL constraint to the `marketing_opt_in` column. The frontend forgot to send that field.

The frontend showed the success message instantly (optimistic UI), but the backend threw a database error and failed to save the user. The Playwright test passed because it only checked the UI. The UI lied.

The QA team updated the test. After asserting the UI success message, they added a database query: SELECT * FROM users WHERE email = 'test@test.com'. They asserted that the row existed. The next time the migration broke, the test failed instantly. The UI couldn't lie anymore.

If the data isn't in the database, the feature doesn't work.

🎯 Why Database Testing?

🛡️

Source of Truth

The UI can lie with optimistic updates. The database is the ultimate source of truth.

🔍

Hidden Fields

Verify fields the UI can't see, like created_at, is_deleted, or password hashes.

🔗

Relationships

Ensure clicking "Add to Cart" created the correct foreign key relationships in the DB.

🧹

Direct Cleanup

Delete test data directly via SQL after tests, avoiding slow UI deletion flows.

🌍 Real World Example

You are testing a "Delete Account" feature. When clicked, the UI hides the user's profile. But did the backend hard-delete the row, or did it just set is_active = false (soft delete)? The UI looks identical either way. By querying the database, you can assert exactly what happened, ensuring compliance with GDPR or data retention policies.

🧒 Explain Like I'm 10

Imagine you order a toy online. The website shows a green checkmark saying "Order Placed!" That's the UI.

But to be absolutely sure the toy is coming, you go to the warehouse (the database) and look at the shipping manifest to see if your name is written on a box. If it's not on the manifest, you aren't getting the toy, no matter what the website says.

🎓 Professional Explanation

Because Playwright tests run in a Node.js environment, you have full access to npm packages. You can install database drivers like mysql2 or pg and query the database directly from your test file. The enterprise pattern is to create a custom Playwright Fixture that manages the database connection pool. This allows tests to execute queries, validate state, and perform teardown without instantiating a new connection every time.

📊 The Full Stack Test

Validating the Entire Stack

🖱️
1. UI Action

Playwright interacts with the browser, filling forms and clicking buttons.

🌐
2. API Request

The frontend sends data to the backend server.

🗄️
3. DB Query

Playwright queries the DB directly to verify the server actually saved it.

🔌 Step 1: Install the Database Driver

Install the appropriate Node.js driver for your database. For MySQL, use mysql2. For PostgreSQL, use pg.

bash
npm install --save-dev mysql2

⚙️ Step 2: Create a DB Fixture

Instead of creating a connection in every test, create a custom fixture. This ensures the connection is reused and properly closed.

typescript · fixtures.ts
import { test as base } from '@playwright/test';
import mysql from 'mysql2/promise';

// Define the fixture type
type Fixtures = {
  db: mysql.Pool;
};

// Extend the base test
export const test = base.extend<Fixtures>({
  db: [async ({}, use) => {
    // Create a connection pool
    const pool = mysql.createPool({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'test_db'
    });

    // Provide the pool to the tests
    await use(pool);

    // Close the pool after all tests in the worker finish
    await pool.end();
  }, { scope: 'worker' }] // Reuse across all tests in this worker
});

export { expect } from '@playwright/test';

Scope: 'worker': This is crucial. You don't want to open a new DB connection for every single test. By setting the scope to worker, the connection pool is created once per CPU core and shared among all tests running on that worker.

✅ Step 3: Validating UI Actions vs DB

Now, let's test the "Quick Signup" flow. We'll fill the form in the UI, submit it, and then query the database to verify the record was saved.

typescript · signup.spec.ts
import { test, expect } from '../fixtures';

test('user signup saves to database', async ({ page, db }) => {
  const testEmail = `test_${Date.now()}@test.com`;

  // 1. UI Action: Fill out the form
  await page.goto('/signup');
  await page.getByLabel('Email').fill(testEmail);
  await page.getByLabel('Password').fill('password123');
  await page.getByRole('button', { name: 'Submit' }).click();

  // 2. UI Assertion: Verify success message
  await expect(page.getByText('Welcome')).toBeVisible();

  // 3. DB Validation: Query the database
  const [rows] = await db.query(
    'SELECT * FROM users WHERE email = ?', 
    [testEmail]
  );

  // 4. Assert the DB row exists and matches
  expect((rows as any[]).length).toBe(1);
  expect(rows[0].email).toBe(testEmail);
  expect(rows[0].is_active).toBe(1); // Check hidden fields!
});

Parameterized Queries: Always use ? placeholders for values. Never concatenate strings to build SQL queries in your tests, as this leads to SQL injection vulnerabilities and syntax errors with quotes.

🧹 Step 4: Database Cleanup (Teardown)

Test data will bloat your database. Use afterEach to clean up the specific records created by the test.

typescript
test.afterEach(async ({ db }) => {
  // Delete all test users created during the run
  await db.query("DELETE FROM users WHERE email LIKE '%@test.com'");
});

⚠️ Common Mistakes

Mistake 1: Testing Production Database

Never, ever point your test suite at your production database. Tests mutate data. Always use a dedicated QA or staging database. Store the DB credentials in a .env file specific to the test environment.

Mistake 2: Race Conditions (Querying too fast)

typescript · ❌ Race condition
// ❌ BAD: Querying the DB immediately after the UI click
await page.click('Submit');
const [rows] = await db.query('SELECT ...'); // Might be empty!

// ✅ GOOD: Wait for the API response first, then query DB
const responsePromise = page.waitForResponse('**/api/signup');
await page.click('Submit');
await responsePromise; // API has acknowledged the save
const [rows] = await db.query('SELECT ...'); // Safe to query

Even if the UI shows success, the backend might still be processing the database write. Wait for the network response to complete before querying the DB to avoid flaky "row not found" errors.

✅ Best Practices

  1. Use a connection pool. Creating a new connection per test is slow. Pools reuse connections.
  2. Use parameterized queries (?). Protect your test database from accidental SQL injection.
  3. Clean up with afterEach or afterAll. Never leave test data behind.
  4. Wait for network responses. Don't query the DB until the API confirms the action is done.
  5. Verify hidden fields. The real value of DB testing is checking fields the UI can't see (e.g., updated_at, boolean flags).

🏗️ Senior Engineer Deep Dive

Parallelism and Database Deadlocks

When running tests in parallel, multiple workers might try to write to the same table simultaneously. If your tests rely on specific counts (e.g., "there should be 5 users"), parallel workers will cause race conditions. The solution is to ensure every test creates unique data (using timestamps or UUIDs) and only queries for its own data, rather than checking global counts.

Transactions for Cleanup

An advanced cleanup pattern is to wrap your test in a database transaction (BEGIN). Instead of actually committing data to the DB, you do a ROLLBACK at the end of the test. This leaves the database 100% pristine without needing complex DELETE queries. However, this requires modifying your application code to use the test transaction connection, which can be complex.

💼 Interview Questions

Beginner
Can Playwright interact with a database?
Show Answer
Yes. While Playwright doesn't have native DB commands, because it runs in Node.js, you can import packages like mysql2 or pg to connect to and query the database directly from your test scripts.
Intermediate
How do you manage database connections efficiently in a test suite?
Show Answer
I use a custom Playwright Fixture with scope: 'worker'. This creates a database connection pool once per worker process, sharing it across all tests running on that worker, and closes the pool when the worker terminates.
Advanced
How do you avoid race conditions when validating database state?
Show Answer
The UI might show a success message before the DB write finishes. I use page.waitForResponse() to wait for the backend API to acknowledge the save before executing my SELECT query. Additionally, I ensure parallel tests create unique data to avoid deadlocks.
Scenario
A test verifies that deleting a user removes them from the UI. How would you enhance this test using database validation?
Show Answer
After the UI confirms deletion, I would query the database: SELECT is_deleted FROM users WHERE id = ?. I would assert that the row still exists but is_deleted is true (soft delete), or I would assert the row no longer exists (hard delete), depending on the business requirement.

🏋️ Practical Exercise

🎯 Hands-On Practice Medium

Verify a Todo Saved

  1. Find a simple Todo app online (or build one).
  2. Install mysql2 or pg.
  3. Write a test that adds a Todo item via the UI.
  4. After the UI updates, query the database to find the Todo by its text.
  5. Assert that the completed column in the DB is false.
  6. Delete the Todo in the afterEach hook.

🚀 Mini Project

🏗️ The Soft Delete Validator

  1. Write a test that logs in and navigates to "Account Settings".
  2. Click "Delete My Account".
  3. Assert the UI logs you out and shows the homepage.
  4. Query the database using the user's ID.
  5. Assert that the user row still exists, but the deleted_at timestamp is no longer NULL (proving it was a soft delete, not a hard delete).

📝 Quiz

Test your understanding. Click an option to check your answer.

1. Does Playwright have built-in methods to query databases?
A) Yes, page.queryDB()
B) No, you must use Node.js npm packages like mysql2 or pg.
C) Yes, but only for SQL Server.
D) No, Playwright cannot interact with databases.
Answer: B — Playwright relies on the Node.js ecosystem. You install database drivers via npm to connect.
2. What is the best scope for a DB connection pool fixture?
A) scope: 'test'
B> scope: 'worker'
C) scope: 'global'
D) scope: 'page'
Answer: B — worker scope creates one pool per CPU core, shared among all tests on that worker, which is highly efficient.
3. Why should you use parameterized queries (?) in DB testing?
A> They run faster.
B) To prevent SQL injection and syntax errors with quotes.
C) They are required by Playwright.
D) To automatically clean up data.
Answer: B — Parameterized queries safely escape user input, preventing SQL injection and formatting errors.
4. How can you avoid race conditions when querying the DB after a UI click?
A) Use page.waitForTimeout(5000)
B) Query the DB repeatedly until the row appears.
C) Use page.waitForResponse() to wait for the API to finish before querying.
D) Restart the database.
Answer: C — Waiting for the network response guarantees the backend has processed the action before you query.
5. What is a major benefit of validating data in the DB instead of just the UI?
A) It is much faster than UI testing.
B) It verifies hidden fields (like is_deleted or created_at) the UI cannot see.
C) It doesn't require writing code.
D) It works offline.
Answer: B — The UI can lie with optimistic updates. The DB contains the true state, including hidden metadata.
6. Where should you perform database cleanup?
A) Inside the test body, at the beginning.
B) In the playwright.config.ts file.
C) In an afterEach or afterAll hook.
D) Never; test data is harmless.
Answer: C — Hooks like afterEach ensure cleanup happens even if the test fails.
7. Why is testing against a production database a bad idea?
A) It is too slow.
B) Tests mutate and delete data, which can corrupt real user data.
C) Production databases don't use SQL.
D) Playwright cannot connect to production servers.
Answer: B — Tests inherently create, modify, and delete data. Doing this in production is dangerous and can violate user trust.
8. If two parallel tests try to assert "Total Users = 5", what will happen?
A) The tests will pass perfectly.
B) The database will crash.
C) They might fail due to a race condition if one test creates a user while the other is counting.
D) Playwright will automatically queue them.
Answer: C — Parallel tests sharing global counts cause race conditions. Tests should assert on unique data they created.
9. What is the "transaction rollback" pattern in DB testing?
A) Deleting data after the test.
B) Wrapping a test in a transaction and rolling it back so no data is actually committed to the DB.
C) Reverting the database schema.
D) Restarting the database server.
Answer: B — It leaves the DB pristine without needing DELETE queries, but requires app code to use the test connection.
10. Which npm package is commonly used for PostgreSQL?
A) mysql2
B) pg
C) mongodb
D) postgres-playwright
Answer: B — The pg package is the standard PostgreSQL driver for Node.js.

❓ FAQ

Q: Can I use an ORM like Prisma or TypeORM in my tests?

A: Yes! If your project already uses an ORM, you can import your ORM client directly into your Playwright tests instead of writing raw SQL. This keeps your types consistent.

Q: Does DB testing work with NoSQL databases like MongoDB?

A: Yes. You just install the mongodb npm package and connect using the same fixture pattern, querying collections instead of tables.

📦 Summary

🎯 Key Takeaways

  • The UI can lie. The database is the source of truth.
  • Use Node.js drivers (mysql2, pg) to connect to the DB.
  • Create a DB fixture with scope: 'worker' to reuse connection pools.
  • Wait for API responses before querying the DB to avoid race conditions.
  • Verify hidden fields like is_deleted or role_id.
  • Always clean up test data in afterEach hooks.

🔗 Related Articles

Previous Lesson
Uploads & Downloads

Verify uploaded files actually persist in the database.

 Related
API Testing

Combine API calls and DB validation to skip slow UI interactions.