Testing with PostgreSQL and Vitest

Testing database functionality can be challenging. Often, projects either use a different datastore for testing, rely on mocking, or, worse, ignore testing database-related functionality altogether. By leveraging PostgreSQL and template databases, we can run tests using a real PostgreSQL database. This approach allows us to test database features, including migrations, procedures, and queries, while also preventing accidental data leakage between tests.

In this article, we'll explore how to achieve this using Typescript, PostgreSQL (within a Docker container), and Vitest. Keep in mind that same techniques can be done with other programming languages and tools.

A PostgreSQL template database serves as a blueprint for creating new databases. When you create a new database, you can use an existing template database as a starting point. In this tutorial, we won't cover setting up the template database with customized data. Instead, we'll use the default template database template0 and run migrations and seeding before each test using Vitest.

Prerequisites

Before you get started, ensure that you have the following installed:

Docker
Node.js (including project with Typescript, Vitest and the PostgreSQL client; examples use pg)

Start Up the Database

To configure the PostgreSQL database, follow these steps:

Open your terminal.
docker run --name test-db -e POSTGRES_PASSWORD=docker -p 5432:5432 -d postgres:16.3

Tests

Creating Helpers

First, we'll create a couple of helper functions for tests. Let's break down each part:

runWithDefaultDatabaseConnection
- This function establishes a connection to the default database, executes a provided function, and then closes the connection.
- In PostgreSQL, connecting to the template database (template0) cannot be done because it cannot be used as a template while active connections exist.
createTestDatabase
- This function creates a new database using template0 as a template. Uses Universally Unique Identifier (UUID) in database name to ensure uniqueness.
- It returns a Client object connected to the newly created database.
teardownTestDatabase
- Responsible for cleaning up after tests by closing the connection and dropping the database.

In summary, these helper functions handle database setup and teardown specifically for testing purposes. You could also consider adding migration and data seeding functionality to createTestDatabase.

The final code of helper.ts looks like this:

import { Client } from "pg";

const config = {
  host: process.env.POSTGRES_HOST || "localhost",
  port: Number(process.env.POSTGRES_PORT || 5432),
  user: process.env.POSTGRES_USER || "postgres",
  password: process.env.POSTGRES_PASSWORD,
  database: process.env.POSTGRES_DATABASE || "postgres",
};

const runWithDefaultDatabaseConnection = async (
  fn: (client: Client) => Promise<void>,
): Promise<void> => {
  const client = new Client(config);
  await client.connect();
  await fn(client);
  await client.end();
};

/**
 * Creates a new database using template0 and
 * returns a `Client` connected to the newly created database.
 *
 * @returns `Client`
 */
export const createTestDatabase = async (): Promise<Client> => {
  if (process.env.VITEST === undefined) {
    throw new Error("Function was not executed with Vitest.");
  }

  const database = `vitest-db-${crypto.randomUUID()}`;
  await runWithDefaultDatabaseConnection(async (client) => {
    await client.query(`CREATE DATABASE "${database}" TEMPLATE template0;`);
  });
  const client = new Client({ ...config, database });
  await client.connect();
  return client;
};

/**
 * Closes the connection and drops the database.
 */
export const teardownTestDatabase = async (client: Client): Promise<void> => {
  if (process.env.VITEST === undefined) {
    throw new Error("Function was not executed with Vitest.");
  }
  const database = await client
    .query<{ current_database: string }>("SELECT current_database();")
    .then(({ rows }) => rows[0].current_database);
  await client.end();

  await runWithDefaultDatabaseConnection(async (client) => {
    await client.query(`DROP DATABASE "${database}";`);
  });
};

Creating the Tests

Now, let's create some tests that utilize the helpers we've developed. In the beforeEach block, we'll set up a new database and establish a connection using the createTestDatabase function, and then run migrations. In the afterEach block, we'll clean up by dropping the database and closing the connection using teardownTestDatabase.

Example person.test.ts:

import { describe, test, expect, afterEach, beforeEach } from "vitest";
import { Client } from "pg";
import { createTestDatabase, teardownTestDatabase } from "./helper";
import { createPerson } from "./person";
import { runMigrations } from "./migrations";

describe("person", () => {
  let client: Client;

  beforeEach(async () => {
    client = await createTestDatabase();
    await runMigrations(client);
  });

  afterEach(async () => {
    await teardownTestDatabase(client);
  });

  test("should create persons", async () => {
    await expect(createPerson(client, { name: "John", age: 29 })).resolves
      .toMatchInlineSnapshot(`
      {
        "age": 29,
        "id": 1,
        "name": "John",
      }
    `);

    await expect(createPerson(client, { name: "Violet", age: 35 })).resolves
      .toMatchInlineSnapshot(`
      {
        "age": 35,
        "id": 2,
        "name": "Violet",
      }
    `);
  });
});

Example pet.test.ts:

import { describe, test, expect, afterEach, beforeEach } from "vitest";
import { Client } from "pg";
import { createTestDatabase, teardownTestDatabase } from "./helper";
import { runMigrations } from "./migrations";
import { createPet } from "./pet";

describe("pet", () => {
  let client: Client;

  beforeEach(async () => {
    client = await createTestDatabase();
    await runMigrations(client);

    // Seed person data
    await client.query(
      `INSERT INTO person(name, age) VALUES ('John', 29), ('Violet', 35), ('James', 41), ('Daisy', 55), ('V', 23);`,
    );
  });

  afterEach(async () => {
    await teardownTestDatabase(client);
  });

  test("should create a pet", async () => {
    await expect(
      createPet(client, { name: "Nibbles", species: "cat", owner_id: 5 }),
    ).resolves.toMatchInlineSnapshot(`
      {
        "id": 1,
        "name": "Nibbles",
        "owner_id": 5,
        "species": "cat",
      }
    `);
  });
});

If your tests don't modify the database, consider using beforeAll and afterAll to speed up the setup process.

Running tests

Running in Continuous Integration (CI)

Most CI/CD tools will provide an easy way to set up a PostgreSQL service for the job. Here are examples for Github Actions and Gitlab CI/CD.

Gitlab CI/CD

stages:
  - test

test:
  image: node:20
  services:
    - name: postgres:16.3
      alias: database
  variables:
    POSTGRES_USER: vitest
    POSTGRES_PASSWORD: vitest
    POSTGRES_DB: vitest
  stage: test
  before_script:
    - export POSTGRES_HOST=database
    - export POSTGRES_USER=vitest
    - export POSTGRES_PASSWORD=vitest
    - export POSTGRES_DATABASE=vitest
  script:
    - npm ci
    - npm run test

Github Actions

name: Test CI

on:
  push:
    branches: ["main"]

jobs:
  test:
    runs-on: ubuntu-latest
    container: node:20
    services:
      database:
        image: postgres:16.3
        env:
          POSTGRES_USER: vitest
          POSTGRES_PASSWORD: vitest
          POSTGRES_DB: vitest
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432
    steps:
      - uses: actions/checkout@v4
      - name: Install dependencies
        run: npm ci
      - name: Run tests
        run: npm run test
        env:
          POSTGRES_HOST: database
          POSTGRES_USER: vitest
          POSTGRES_PASSWORD: vitest
          POSTGRES_DATABASE: vitest

Conclusion

Running tests that use a real PostgreSQL database is fast and easy. Taking advantage of template databases ensures that each test has a fresh state, and no time-consuming cleaning is needed. Seeding must be done each time, and for large seeds, this may be too slow. However, this can be tackled by creating a template database with the data already present.

Overall, this is a great way to test code in the same way it runs in the real environment. It also provides a way to test things that happen only in the database layer, such as procedures, triggers, and column default values.

Source code

Full source code is available at GitHub.