Database Testing with Playwright and PostgreSQL

Database testing in automation is not about turning every UI test into a SQL inspection. It is about using the database when it gives you faster setup, clearer persistence checks, or coverage for side effects that the UI cannot show directly.

By the end of this tutorial, you'll have:

• A Playwright suite connected to a real PostgreSQL database
• A custom DB fixture that seeds data, asserts persistence, and cleans up safely
• Five working test patterns you can copy into your own project
• Docker Compose running an isolated Postgres instance on a non-default port

I built a small demo for this pattern using Playwright Test, PostgreSQL, Docker Compose, and the pg client for Node.js. The full working project is on GitHub — clone it and follow along.

---

Why Use DB Testing

DB testing is useful when the thing you need to prove is not fully visible from the UI or API response.

In automation, it helps answer questions like:

• Did the data actually get saved?
• Was invalid data blocked from being persisted?
• Did the right row, status, or value change after the user action?
• Can I set up the test faster by inserting known data directly?
• Is the test environment connected to the right database and schema?

The key idea: UI/API tests prove behavior from the outside. DB checks prove the persistence side effect behind that behavior.

Good Cases to Use DB Testing

1. Seed data faster — Instead of creating a user, order, task, or product through a long UI flow, insert the exact record the test needs directly in the DB, then verify the UI displays it correctly.

   Example: insert a task in PostgreSQL, open /tasks, assert the task appears.

2. Verify persistence after UI/API action — After submitting a form or calling an API, query the DB to confirm the row was created or updated correctly.

   Example: create a task from the UI, then check the DB has status = 'open'.

3. Negative persistence checks — When an action should fail, verify the bad data was not saved.

   Example: submit a duplicate title, expect 409, then query the DB and confirm there is still only one row.

4. Check database constraints — Useful for unique constraints, foreign keys, required fields, status checks, and other business rules enforced at the database layer.

5. Environment smoke checks — A simple SELECT 1 or table-exists check can quickly tell you if the test environment is broken before the real tests run.

---

When Not to Use It

• Don't add DB queries to every UI test.
• Don't test implementation details that don't matter to the user or business rule.
• Don't rely on global cleanup like TRUNCATE if tests run in parallel.
• Don't wrap full UI tests in DB transactions expecting rollback — the app uses separate DB connections.

DB testing is strongest when it supports intent: faster setup, persistence verification, negative checks, and confidence that real data contracts are working.

---

What You Need

Every piece in this demo has a specific job:

Component	Purpose
Docker Compose	Isolated Postgres on host port 55432
db/init/001_schema.sql	Schema applied on first container boot
Express app (src/app.ts)	App under test — same DATABASE_URL as tests
playwright.config.ts	Starts the app via webServer and passes env vars
tests/fixtures/db.fixture.ts	Worker-scoped pg Pool + per-test runId cleanup
tests/tasks-db.spec.ts	Five canonical test patterns
npm scripts	db:up, db:down, test:e2e

The useful mental model:

Playwright page fixture     -> user behavior
Playwright request fixture  -> API behavior
Custom db fixture           -> setup, persistence assertions, cleanup

Prerequisites: Node.js, Docker, and Playwright installed locally.

---

Project Structure

The project is intentionally small:

database-testing-demo/
├─ db/init/001_schema.sql
├─ src/app.ts
├─ tests/fixtures/db.fixture.ts
├─ tests/tasks-db.spec.ts
├─ docker-compose.yml
├─ playwright.config.ts
└─ package.json

PostgreSQL runs in Docker. The app is a tiny Express task list. Playwright starts the app through webServer, then the tests use the browser, the API request fixture, and a custom database fixture.

---

Step-by-Step Setup

1. Start Postgres with Docker Compose

docker-compose.yml provides an isolated PostgreSQL instance for local and CI use:

services:
  db:
    image: postgres:17-alpine
    container_name: playwright-db-demo-postgres
    ports:
      - "55432:5432"          # host:container — avoid clashing with local Postgres
    environment:
      POSTGRES_DB: demo
      POSTGRES_USER: demo
      POSTGRES_PASSWORD: demo
    volumes:
      - ./db/init:/docker-entrypoint-initdb.d:ro

Why port 55432? Many developer machines already have Postgres on 5432. A dedicated host port prevents tests from hitting the wrong database by accident.

The init volume: Scripts in db/init/ run once when the container is first created. Mount them read-only at /docker-entrypoint-initdb.d.

Resetting the database: npm run db:down runs docker compose down -v, which removes the volume so init scripts re-apply on the next db:up.

2. Define the Schema

Create db/init/001_schema.sql:

CREATE TABLE IF NOT EXISTS tasks (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL UNIQUE,
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'done')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The UNIQUE constraint on title is what makes the negative duplicate test possible later.

3. Wire Playwright

In playwright.config.ts, start the app and pass the same DATABASE_URL the fixture uses:

const appUrl = process.env.BASE_URL ?? 'http://localhost:3000';
const databaseUrl = process.env.DATABASE_URL ?? 'postgres://demo:demo@localhost:55432/demo';

export default defineConfig({
  testDir: './tests',
  fullyParallel: true,
  use: { baseURL: appUrl },
  webServer: {
    command: 'npm run app',
    url: `${appUrl}/health`,
    reuseExistingServer: !process.env.CI,
    env: {
      DATABASE_URL: databaseUrl,
      PORT: '3000',
    },
    timeout: 60_000,
  },
});

The app and tests must agree on DATABASE_URL. The /health endpoint gives Playwright a ready signal before tests start.

4. Create the DB Fixture

The database connection lives in one fixture file. This is the core integration piece:

Fixture	Scope	Purpose
db	worker	Shared pg.Pool; smoke-tested with SELECT 1
runId	test	UUID prefix for all titles in one test; triggers cleanup after test
makeTitle(label)	test	Returns e2e-{runId}-{label} — unique, grep-friendly title
seedTask(title, status?)	test	INSERT ... RETURNING helper
findTaskByTitle(title)	test	SELECT helper for assertions

Connection setup:

const db = new Pool({
  connectionString: databaseUrl,
  max: 5,
  connectionTimeoutMillis: 5_000,
  allowExitOnIdle: true,
});

await db.query('SELECT 1');

Per-test cleanup — the reliability piece:

runId: async ({ db }, use) => {
  const runId = randomUUID();
  await use(runId);
  await db.query('DELETE FROM tasks WHERE title LIKE $1', [`e2e-${runId}-%`]);
},

Avoid global TRUNCATE unless the suite is intentionally serial. If tests run in parallel, each test must own its data and clean only what it created.

Tests import test and expect from ./fixtures/db.fixture, not from @playwright/test directly.

5. Run It

Clone the demo repo and run the suite:

git clone https://github.com/StevenG0211/database-testing-demo.git
cd database-testing-demo

npm install
npm run db:up
npm run test:e2e
npm run db:down

Playwright starts the app automatically — you do not need to run npm run app separately. For a deeper porting guide, see the integration guide in the repo.

The verified run used a real Postgres container and passed twice in a row:

5 passed

---

What The Tests Prove

The suite covers five database-testing patterns that map directly to the good cases above:

• Connector smoke test — Run SELECT 1 so the suite fails fast if the database is unavailable.
• DB seed → UI verification — Insert a row directly, open the UI, and verify the task appears.
• UI action → DB verification — Create a task through the form, then query PostgreSQL to confirm persistence.
• API action → DB verification — Post to the API, then poll the database until the row exists.
• Negative persistence test — Attempt to create a duplicate task and verify the database still has only one row.

That last one is the most important teaching example. The test does not just check the response code. It verifies the database state after the failed operation:

const countResult = await db.query<{ count: number }>(
  'SELECT COUNT(*)::int AS count FROM tasks WHERE title = $1',
  [title],
);

expect(countResult.rows[0].count).toBe(1);

The response says "duplicate." The database proves the duplicate was not persisted.

For async writes, use expect.poll when UI/API write and DB read may not be instantaneous:

await expect.poll(async () => {
  return (await findTaskByTitle(title))?.status;
}).toBe('open');

---

Takeaway

The database connector is just a Playwright fixture. The value comes from using it with discipline: parameterized SQL, isolated data per test, targeted cleanup, and assertions that prove persistence matters.

A UI test should still mostly describe user behavior. The database check should support the intent, not replace it.