End-to-End Tests With Playwright: Frontend, Backend and Database · Part 5 of 14 in the series
Part 5: Testing the Database
Fill the database section: tests for the defaults and constraints of the table, for the queries the app sends, and one test that notices when schema.ts has changed and the migration for it is missing.
End-to-End Tests With Playwright: Frontend, Backend and DatabasePart 5 of 14
On this page
Quest objectives
- Understand why the rules in the database deserve tests of their own
- Test defaults and constraints, and check which constraint turned a row down
- Make one test per case from a table of cases
- Test the query functions of the app directly, without HTTP
- Catch a schema change that has no migration
Since the last part a test can ask for a database with empty tables, however many workers run beside it. This part uses that to fill the database section: tests for the defaults and constraints of the table, for the queries of the app, and one that notices when a migration is missing.
Why test the database at all
The API checks its input too: readQuest in server/app.ts turns down an empty title before the database ever sees it. So why test the same rules again, one layer further down?
Because the API is not the only way in. A script that imports quests, a migration that rewrites rows, a second service on the same database: all of them write past the API. The constraints you put into the schema in part 1 hold no matter who writes. The database is the last line of defence, and a defence you rely on deserves tests.
What belongs in this section follows from that:
the defaults and constraints of the table,
the queries the app sends, called directly as functions,
whether the migrations and
schema.tsstill describe the same database.
There is no browser and no HTTP in here. Status codes and error messages are the business of the API, and the next part tests them.
Defaults and constraints
Create tests/database/constraints.spec.ts:
import { quests } from '../../server/db/schema.ts';
import { expect, test } from '../fixtures.ts';
test('fills in what a new quest leaves out', async ({ db }) => {
const [quest] = await db.insert(quests).values({ title: 'Find the lost sword' }).returning();
expect(quest).toMatchObject({ id: 1, title: 'Find the lost sword', reward: 10, done: false });
expect(Date.now() - quest.createdAt.getTime()).toBeLessThan(5000);
});
const rejected = [
{ name: 'an empty title', row: { title: '' }, constraint: 'quests_title_length' },
{ name: 'a title of 81 characters', row: { title: 'x'.repeat(81) }, constraint: 'quests_title_length' },
{ name: 'a negative reward', row: { title: 'Rob the bank', reward: -1 }, constraint: 'quests_reward_range' },
{ name: 'a reward above 1000', row: { title: 'Rob the bank', reward: 1001 }, constraint: 'quests_reward_range' },
];
for (const { name, row, constraint } of rejected) {
test(`rejects ${name}`, async ({ db }) => {
await expect(db.insert(quests).values(row)).rejects.toMatchObject({ cause: { constraint } });
expect(await db.select().from(quests)).toEqual([]);
});
}
test('rejects a title that is taken', async ({ db }) => {
await db.insert(quests).values({ title: 'Find the lost sword' });
await expect(db.insert(quests).values({ title: 'Find the lost sword' })).rejects.toMatchObject({
cause: { code: '23505', constraint: 'quests_title_unique' },
});
});
test('accepts a title of exactly 80 characters', async ({ db }) => {
await db.insert(quests).values({ title: 'x'.repeat(80) });
expect(await db.select().from(quests)).toHaveLength(1);
});The defaults. The first test inserts nothing but a title and looks at the row that returning() hands back. The id is 1, because the db fixture has set the counter back. The reward is 10 and the quest is open: both come from the defaults in the schema. The time of creation is filled in by the database, so the test cannot know its exact value. It checks that the time is less than five seconds old.
The rejected rows. rejected is a table of cases: a name, the row to insert, and the constraint that should turn it down. The for loop calls test once per case, so these are four tests with four titles, such as "rejects an empty title". The alternative, one test with the loop inside, would stop at the first case that fails and would not tell you about the others.
The assertion is await expect(…).rejects.toMatchObject({ cause: { constraint } }). rejects waits for the insert to fail and hands the error to the matcher behind it. If the insert works, the test fails. Drizzle wraps the error that PostgreSQL sends in one of its own. The original is in cause, and its field constraint names the constraint that said no.
That name matters. Part 2 had a test that passed for the wrong reason, and "the insert failed" would be such a test too: a row can fail for many reasons. This one only passes if the title of 81 characters was stopped by quests_title_length and the negative reward by quests_reward_range. The last line of each test then checks that the table is still empty.
The title that is taken. This test also looks at the code of the error, 23505. That is the code isUniqueViolation in server/app.ts reads to answer with 409. The API relies on this detail, so it is pinned down here.
The boundary. The last test is the other side of the rule: a title of exactly 80 characters must be accepted. Without it, a constraint that is too strict by one character would pass every test above.
The queries of the app
The next tests need rows in the table before they can ask anything. A small helper lets a test say what it needs in one line. Create tests/support/seed.ts:
import type { Db } from '../../server/db/client.ts';
import { quests } from '../../server/db/schema.ts';
type NewQuest = typeof quests.$inferInsert;
/** Puts quests into the database and returns them as they were stored. Only a title is needed. */
export function seedQuests(db: Db, ...rows: NewQuest[]) {
return db.insert(quests).values(rows).returning();
}typeof quests.$inferInsert is Drizzle's type for a row you insert: every column that has a default is optional, so a title is enough. The function returns the rows as they were stored, with their ids and times, which the tests use for comparing.
Now the queries. Create tests/database/queries.spec.ts:
import { quests } from '../../server/db/schema.ts';
import { createQuest, deleteQuest, listQuests, setQuestDone } from '../../server/quests.ts';
import { expect, test } from '../fixtures.ts';
import { seedQuests } from '../support/seed.ts';
test('lists open quests first, and the oldest first among them', async ({ db }) => {
await seedQuests(
db,
{ title: 'Feed the dragon', done: true },
{ title: 'Find the lost sword' },
{ title: 'Brew a potion', done: true },
{ title: 'Map the cave' },
);
const titles = (await listQuests(db)).map((quest) => quest.title);
expect(titles).toEqual(['Find the lost sword', 'Map the cave', 'Feed the dragon', 'Brew a potion']);
});
test('creates a quest and returns the stored row', async ({ db }) => {
const quest = await createQuest(db, { title: 'Find the lost sword', reward: 50 });
expect(quest).toMatchObject({ id: 1, title: 'Find the lost sword', reward: 50, done: false });
expect(await db.select().from(quests)).toEqual([quest]);
});
test('marks one quest as done and leaves the others alone', async ({ db }) => {
const [sword, cave] = await seedQuests(db, { title: 'Find the lost sword' }, { title: 'Map the cave' });
const changed = await setQuestDone(db, sword.id, true);
expect(changed).toEqual({ ...sword, done: true });
expect(await listQuests(db)).toEqual([cave, { ...sword, done: true }]);
});
test('says so when there is no quest to change or delete', async ({ db }) => {
expect(await setQuestDone(db, 99, true)).toBeUndefined();
expect(await deleteQuest(db, 99)).toBe(false);
});
test('deletes a quest', async ({ db }) => {
const [sword, cave] = await seedQuests(db, { title: 'Find the lost sword' }, { title: 'Map the cave' });
expect(await deleteQuest(db, sword.id)).toBe(true);
expect(await listQuests(db)).toEqual([cave]);
});These tests import listQuests, createQuest, setQuestDone and deleteQuest from server/quests.ts and call them with the db of the test. These are the functions the API calls, without the API around them. If one of these tests fails, the query is wrong, not a route and not the page.
The order. The board shows open quests first, and the oldest first within each group. The test seeds four quests in alternating order: done, open, done, open. A list in the order of storing would fail here. The expected one starts with the two open quests in the order they were stored and ends with the two finished ones.
Creating. createQuest has to return the row as it was stored. The test compares what the function returns with what a plain select finds afterwards: toEqual([quest]) means one row, and it is the same one.
Changing. The changed quest must equal { ...sword, done: true }, the old row with one field different, so nothing else was touched. The list afterwards shows two more things: the other quest is unchanged, and the finished one has moved to the end.
Nothing there. For an id that does not exist, setQuestDone returns undefined and deleteQuest returns false. The API turns both into a 404, so these answers are part of what the functions promise.
Deleting. Two quests go in, one is deleted, and the list holds exactly the other one.
Do the migrations match the schema?
The app describes its tables twice. server/db/schema.ts is what your code and its types use. The SQL files in drizzle/ are what builds the real database. npm run db:generate writes the second from the first, as long as someone remembers to run it.
The classic mistake is to change schema.ts and forget the migration. TypeScript is content, because it only knows the schema. The database does not have the new column, and the first query that uses it fails. A test can catch that. Create tests/database/migrations.spec.ts:
import { pushSchema } from 'drizzle-kit/api';
import * as schema from '../../server/db/schema.ts';
import { expect, test } from '../fixtures.ts';
test('the migrations build exactly what schema.ts describes', async ({ db }) => {
// What would Drizzle have to change in this database to make it match schema.ts?
const { statementsToExecute } = await pushSchema(schema, db);
expect(statementsToExecute).toEqual([]);
});drizzle-kit/api offers Drizzle Kit's functions to programs. You installed Drizzle Kit in part 1 to generate migrations, so there is nothing to add. pushSchema compares what schema.ts describes with the database it is given, and returns the statements it would need to make the two equal. The test runs none of them. It only looks at the list.
The database it is given is the worker's, a copy of the template, and the template was built by the migrations. So an empty list means: the migrations build exactly what schema.ts describes.
Try the mistake once. In server/db/schema.ts, add a column under done, and do not generate a migration for it:
done: boolean().notNull().default(false),
giver: text(),Then run this one test. The word migrations behind the project is a filter: only test files with that word in their path are run.
$ npx playwright test --project=database migrationsRunning 2 tests using 1 worker ✓ 1 [setup] › tests/setup/database.setup.ts:6:1 › build the template database (126ms)[✓] Pulling schema from database... ✘ 2 [database] › tests/database/migrations.spec.ts:5:1 › the migrations build exactly what schema.ts describes (68ms) 1) [database] › tests/database/migrations.spec.ts:5:1 › the migrations build exactly what schema.ts describes Error: expect(received).toEqual(expected) // deep equality - Expected - 1 + Received + 3 - Array [] + Array [ + "ALTER TABLE \"quests\" ADD COLUMN \"giver\" text;", + ] 7 | const { statementsToExecute } = await pushSchema(schema, db); 8 | > 9 | expect(statementsToExecute).toEqual([]); | ^ 10 | }); 11 | at /home/robin/questboard/tests/database/migrations.spec.ts:9:31 1 failed [database] › tests/database/migrations.spec.ts:5:1 › the migrations build exactly what schema.ts describes 1 passed (4.1s)
The test fails, and the difference it prints is the migration that is missing: ALTER TABLE "quests" ADD COLUMN "giver" text;. In a real project this is the moment to run npm run db:generate. Here the column was only an experiment, so remove the line from schema.ts again.
With the schema back as it was, run the whole section:
$ npx playwright test --project=databaseRunning 17 tests using 8 workers ✓ 1 [setup] › tests/setup/database.setup.ts:6:1 › build the template database (115ms) ✓ 2 [database] › tests/database/constraints.spec.ts:19:3 › rejects an empty title (41ms) ✓ 10 [database] › tests/database/isolation.spec.ts:8:1 › can add a quest (18ms) ✓ 4 [database] › tests/database/constraints.spec.ts:26:1 › rejects a title that is taken (50ms) ✓ 3 [database] › tests/database/constraints.spec.ts:19:3 › rejects a reward above 1000 (57ms) ✓ 11 [database] › tests/database/isolation.spec.ts:14:1 › does not see the quests of other tests (21ms) ✓ 5 [database] › tests/database/constraints.spec.ts:19:3 › rejects a title of 81 characters (38ms) ✓ 7 [database] › tests/database/constraints.spec.ts:4:1 › fills in what a new quest leaves out (41ms) ✓ 12 [database] › tests/database/queries.spec.ts:6:1 › lists open quests first, and the oldest first among them (16ms) ✓ 6 [database] › tests/database/constraints.spec.ts:19:3 › rejects a negative reward (40ms) ✓ 13 [database] › tests/database/queries.spec.ts:20:1 › creates a quest and returns the stored row (13ms) ✓ 9 [database] › tests/database/isolation.spec.ts:4:1 › starts with an empty table (29ms) ✓ 8 [database] › tests/database/constraints.spec.ts:34:1 › accepts a title of exactly 80 characters (35ms) ✓ 16 [database] › tests/database/queries.spec.ts:36:1 › says so when there is no quest to change or delete (11ms) ✓ 15 [database] › tests/database/queries.spec.ts:41:1 › deletes a quest (23ms) ✓ 14 [database] › tests/database/queries.spec.ts:27:1 › marks one quest as done and leaves the others alone (29ms)[✓] Pulling schema from database... ✓ 17 [database] › tests/database/migrations.spec.ts:5:1 › the migrations build exactly what schema.ts describes (62ms) 17 passed (4.2s)
17 tests: the setup and the 16 tests of the section, in about four seconds. The numbers in front of the lines say in which order the tests started. Spread over several workers, they do not finish in that order, which is why the lines above are mixed up. In your terminal they may be in order. How many workers Playwright uses depends on your machine, so your first line may name another number than 8.
What you have now
The database section checks the defaults and the three named constraints of the table, the four query functions of the app, and that the migrations and schema.ts agree. All of it runs without a browser and without a single HTTP request.
The rules that hold for everyone who writes to the database are tested now. In the next part you go one layer up: every worker starts its own copy of the API on its own database, and the backend section tests it with real requests.
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.