Skip to content

End-to-End Tests With Playwright: Frontend, Backend and Database · Part 4 of 14 in the series

Part 4: A Database for Every Worker

Give every parallel worker its own copy of the database, made from a template that the real migrations build, and every test empty tables. Tests then run in parallel and in any order, and your development data is left alone.

Quest objectives

  • Understand what a worker is and why parallel tests need data of their own
  • Build a template database with the real migrations of the app in a setup project
  • Write your own fixtures with test.extend, one per worker and one per test
  • Copy the template for every worker and empty the tables before every test
  • Show that the tests pass on three workers and on a single one

Since the last part the suite has its four sections, but three of them still work on your development database. Part 2 showed where that leads: a test that was green because of what an earlier run had left behind.

This part solves the database half of that problem. Every worker gets a database of its own, every test starts with empty tables, and the tests of the database section never touch your development data again. The other half, the shared server, follows in part 6.

One template, copied once for every worker: all of them start from the same database, and none of them shares it.
One template, copied once for every worker: all of them start from the same database, and none of them shares it.

Workers, and what they must not share

Playwright does not run your tests one after another in a single process. It starts several workers: separate processes that each take tests from the queue and run them, one at a time. The line Running 5 tests using 4 workers in the last part said so. Workers are the reason a large suite finishes in seconds.

They are also the reason shared data hurts. If two workers write to the same table at the same moment, a test that counts the quests gets a number that depends on what its neighbour is doing. Such a test fails now and then, and rarely while you are watching.

The plan has three steps:

  1. Once per run, a setup step builds a template database called questboard_template, with the real migrations of the app.

  2. Once per worker, the template is copied into a database that belongs to this worker alone: questboard_test_0, questboard_test_1 and so on. PostgreSQL does that with one statement, create database … template …, and it takes milliseconds.

  3. Before every test, the tables of the worker's database are emptied.

The copies keep the workers apart, and the emptying keeps the tests of one worker apart. Together they let the tests run in parallel and in any order.

The template database

Two small functions deal with databases as a whole. Create tests/support/databases.ts:

tests/support/databases.ts
import pg from 'pg';

/** Every worker's database is a copy of this one. */
export const TEMPLATE = 'questboard_template';

/** The address of another database on the server that DATABASE_URL points to. */
export function databaseUrl(name: string) {
  const url = new URL(process.env.DATABASE_URL!);
  url.pathname = `/${name}`;
  return url.href;
}

/**
 * Runs statements that create or drop whole databases. They need a connection that is not
 * inside one of those databases, so this one goes to "postgres", which every server has.
 */
export async function onServer(...statements: string[]) {
  const client = new pg.Client({ connectionString: databaseUrl('postgres') });
  await client.connect();
  try {
    for (const statement of statements) await client.query(statement);
  } finally {
    await client.end();
  }
}

databaseUrl takes the address from your .env and swaps its last piece, the name of the database, for another one. Server, user and password stay as they are. The config has loaded .env since part 3, so DATABASE_URL is there when the tests run.

onServer runs statements that create or drop whole databases. You cannot drop a database you are connected to, and one that is still to be created cannot be connected to at all, so these statements need a connection to somewhere else. That is the database postgres, which every server has. The function connects to it, runs its statements in order and closes the connection again, also when a statement fails: that is what finally is for.

Something has to build the template before the first test needs it. In Playwright such a step is an ordinary test in a project of its own. Create tests/setup/database.setup.ts:

tests/setup/database.setup.ts
import { test as setup } from '@playwright/test';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { connect } from '../../server/db/client.ts';
import { TEMPLATE, databaseUrl, onServer } from '../support/databases.ts';

setup('build the template database', async () => {
  await onServer(
    `drop database if exists ${TEMPLATE} with (force)`,
    `create database ${TEMPLATE}`,
  );

  // The same migrations the app runs, from the first to the last.
  const { db, pool } = connect(databaseUrl(TEMPLATE));
  await migrate(db, { migrationsFolder: './drizzle' });
  await pool.end();
});

The import renames test to setup, so the file reads as what it is. First the template of the last run is dropped and an empty one is created. with (force) ends connections that are still open to the old one. Without it, a single open connection would make the drop fail.

Then come the migrations: the same migrate call on the same drizzle/ folder as in server/db/migrate.ts from part 1. So the tests work on the tables your migrations really build, not on a second description of them. Here it pays off that connect(url) takes the address as a parameter: the same function now opens the template instead of the development database.

pool.end() is more than tidiness. PostgreSQL copies a database only while nobody else is connected to it, so the setup has to let go of the template before the workers come.

Now tell the config about it. Add a project setup in front of the others, and let database depend on it:

playwright.config.ts
{
  name: 'setup',
  testDir: './tests/setup',
  testMatch: /.*\.setup\.ts/,
},
{
  name: 'database',
  testDir: './tests/database',
  dependencies: ['setup'],
},

By default Playwright only takes files with names like board.spec.ts or board.test.ts for tests. testMatch tells this project to take the files that end in .setup.ts.

dependencies does the ordering. Whenever database runs, Playwright runs setup first and waits for it, also when you ask for one project only with --project=database. If the setup fails, the tests that depend on it are not started.

One more line goes to the top of the config: fullyParallel. This is the whole file now:

playwright.config.ts
import { existsSync } from 'node:fs';
import { defineConfig, devices } from '@playwright/test';

// The tests read the same settings as the app. Where there is no .env, such as in a
// pipeline, the variables are set from outside.
if (existsSync('.env')) process.loadEnvFile();

const api = 'http://localhost:3000';
const web = 'http://localhost:5173';

export default defineConfig({
  fullyParallel: true,
  reporter: [['list'], ['html']],
  use: {
    trace: 'on-first-retry',
  },
  projects: [
    {
      name: 'setup',
      testDir: './tests/setup',
      testMatch: /.*\.setup\.ts/,
    },
    {
      name: 'database',
      testDir: './tests/database',
      dependencies: ['setup'],
    },
    {
      name: 'backend',
      testDir: './tests/backend',
      use: { baseURL: api },
    },
    {
      name: 'frontend',
      testDir: './tests/frontend',
      use: { ...devices['Desktop Chrome'], baseURL: web },
    },
    {
      name: 'e2e',
      testDir: './tests/e2e',
      use: { ...devices['Desktop Chrome'], baseURL: web },
    },
  ],
  // Playwright starts both servers before the tests and stops them afterwards.
  webServer: [
    {
      command: 'npm run dev:api',
      url: `${api}/api/health`,
      reuseExistingServer: !process.env.CI,
    },
    {
      command: 'npm run dev:web',
      url: web,
      reuseExistingServer: !process.env.CI,
    },
  ],
});

Without fullyParallel, Playwright runs test files in parallel, but the tests inside one file one after another in the same worker. With it, the tests of a single file may go to different workers too. That is faster, and it only works when no test relies on the one above it, which is what the rest of this part sets up.

A fixture per worker, a reset per test

You have used two fixtures already: page in part 2 and request in part 3. A fixture is a named thing a test asks for in its arguments. Playwright builds it before the test and cleans it up afterwards, and a test that does not ask for it does not get it built.

You can add fixtures of your own, and each has a scope. A fixture of scope test, the default, is built fresh for every test. A fixture of scope worker is built once per worker process and shared by all tests that run in it. That is the pair the plan needs: the database per worker, the emptying per test. Create tests/fixtures.ts:

tests/fixtures.ts
import { test as base } from '@playwright/test';
import { sql } from 'drizzle-orm';
import { connect, type Db } from '../server/db/client.ts';
import { TEMPLATE, databaseUrl, onServer } from './support/databases.ts';

type TestFixtures = {
  /** This worker's database, with empty tables. */
  db: Db;
};

type WorkerFixtures = {
  workerDb: Db;
};

export const test = base.extend<TestFixtures, WorkerFixtures>({
  // Once per worker: a database of its own, copied from the template.
  workerDb: [
    async ({}, use, workerInfo) => {
      const name = `questboard_test_${workerInfo.parallelIndex}`;
      await onServer(
        `drop database if exists ${name} with (force)`,
        `create database ${name} template ${TEMPLATE}`,
      );
      const { db, pool } = connect(databaseUrl(name));

      await use(db);

      await pool.end();
      await onServer(`drop database ${name}`);
    },
    { scope: 'worker' },
  ],

  // Before every test: empty the tables, so no test sees what another one left behind.
  db: async ({ workerDb }, use) => {
    await workerDb.execute(sql`truncate quests restart identity`);
    await use(workerDb);
  },
});

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

base.extend takes Playwright's own test and returns one that knows more fixtures. The two type parameters list them: first the fixtures per test, then the fixtures per worker.

workerDb is the worker's database. A fixture with options is written as a pair: the function first, then the options, here { scope: 'worker' }. The function gets three arguments. The first names the fixtures this one needs. It needs none, and the empty {} is how Playwright wants that written, because it reads the names from this pattern. The second is use, the third describes the worker.

The name of the database ends in workerInfo.parallelIndex: 0, 1, 2 and so on, one number for each worker that runs at the same time. When a test fails, Playwright replaces its worker with a fresh one, and the fresh one takes over the same number. The first statement drops a database of that name if there is one, a leftover of a run that crashed. The second creates it as a copy of the template.

await use(db) is the middle of every fixture. What stands before it is the preparation. Then the tests run, in the case of a worker fixture all tests of this worker. What stands after it is the cleanup: the connections are closed and the database is dropped, so a run that ends normally leaves only the template behind.

db is what the tests ask for. It names workerDb in its first argument, so Playwright builds that one first and hands it in. As a test fixture, db runs before every test that asks for it: truncate empties the table, and restart identity sets the counter of the ids back, so the first quest of every test gets the id 1. Then it passes the worker's database on.

The last line hands expect through. From now on, tests that need the database import test and expect from ../fixtures.ts instead of @playwright/test.

See the isolation work

The database test from part 3 read the development database. Remove it:

questboard
$ rm tests/database/connection.spec.ts

In its place come three tests that only pass if the isolation works. Create tests/database/isolation.spec.ts:

tests/database/isolation.spec.ts
import { quests } from '../../server/db/schema.ts';
import { expect, test } from '../fixtures.ts';

test('starts with an empty table', async ({ db }) => {
  expect(await db.select().from(quests)).toEqual([]);
});

test('can add a quest', async ({ db }) => {
  await db.insert(quests).values({ title: 'Find the lost sword' });

  expect(await db.select().from(quests)).toHaveLength(1);
});

test('does not see the quests of other tests', async ({ db }) => {
  await db.insert(quests).values({ title: 'Feed the dragon' });

  const rows = await db.select().from(quests);

  expect(rows.map((quest) => quest.title)).toEqual(['Feed the dragon']);
  // Even the counter of the ids starts over.
  expect(rows[0].id).toBe(1);
});

Each of them would be wrong in a shared database. The first expects an empty table, the second exactly one row, and the third only its own quest, with the id 1 at that. In whatever order and in however many workers they run, each must find the table as if it were alone.

questboard
$ npm run test:database> questboard@1.0.0 test:database> playwright test --project=databaseRunning 4 tests using 3 workers  ✓  1 [setup] › tests/setup/database.setup.ts:6:1 › build the template database (357ms)  ✓  3 [database] › tests/database/isolation.spec.ts:4:1 › starts with an empty table (23ms)  ✓  2 [database] › tests/database/isolation.spec.ts:8:1 › can add a quest (27ms)  ✓  4 [database] › tests/database/isolation.spec.ts:14:1 › does not see the quests of other tests (27ms)  4 passed (3.4s)

Running 4 tests: the setup counts as one, and it ran first, as the dependency demands. Building the template took a fraction of a second. using 3 workers: because of fullyParallel, the three tests of this one file went to three workers, and each worker copied the template into a database of its own.

Three tests in three databases cannot get in each other's way. The harder case is a single worker, where all three use the same database, one after another:

questboard
$ npx playwright test --project=database --workers=1Running 4 tests using 1 worker  ✓  1 [setup] › tests/setup/database.setup.ts:6:1 › build the template database (126ms)  ✓  2 [database] › tests/database/isolation.spec.ts:4:1 › starts with an empty table (21ms)  ✓  3 [database] › tests/database/isolation.spec.ts:8:1 › can add a quest (12ms)  ✓  4 [database] › tests/database/isolation.spec.ts:14:1 › does not see the quests of other tests (13ms)  4 passed (3.1s)

The same result. Here only the emptying before each test keeps them apart: "can add a quest" left a row behind, and "does not see the quests of other tests" still found nothing but its own quest, with the id 1. Run either command as often as you like. The outcome no longer depends on the run before, and that is the difference to part 2.

What you have now

A template database that every run builds from your real migrations, a copy of it for every worker, and empty tables before every test. The tests of the database section run in parallel and in any order, and they leave your development database alone.

The backend and e2e sections use none of this yet: they still talk to the one development server. Their turn comes in parts 6 and 8. In the next part you first use the clean database for what it is best at: testing the database itself, with its constraints, its queries and its migrations.

Comments

The comments are GitHub Discussions: sign in with GitHub to write one.

The comments come from GitHub, through giscus. Loading them sends your IP address to giscus.app and GitHub.