Thomas Heniart

Posted on May 20, 2023 • Edited on Jan 13

Integration testing with Jest and PostgreSQL

#testing #database #jest

One of the crucial tasks of a developer is to write integration tests for the database to ensure that the implementation is performing as expected. However, running integration tests independently and concurrently against a shared database can lead to tests interfering with one another and failing intermittently. All code samples are in TypeScript, but the same logic applies to any other language.

Problem

The problem arises when integration tests suites run concurrently against a shared database instance. For instance, given a simple implementation of an SQL repository using ts-postgres library and Jest test suites provided in the code blocks, you can run jest a couple of times and experience the concurrency problem. The issue stems from the database being emptied between the await repository.insert(id); and expect(await repository.findAll()).toEqual([[id]]); statements, due to the fact that the selects nothing test finishes and deletes table records just before the expect statement.

src/exampleRepository.ts

import { Client, Value } from "ts-postgres";

export class ExampleRepository {
  private readonly _client: Client;

  constructor(client: Client) {
    this._client = client;
  }

  async findAll(): Promise<Array<Value>> {
    return (await this._client.query("SELECT * FROM example")).rows;
  }

  async insert(id: string): Promise<void> {
    await this._client.query("INSERT INTO example (id) VALUES ($1)", [id]);
  }
}

src/test/problematic/insert.test.ts

import { Client } from "ts-postgres";
import { ExampleRepository } from "../../exampleRepository";
import { config } from "dotenv";
import { v4 } from "uuid";
import { rootConnect } from "../postgresUtils";

describe("Insert Test Suite", () => {
  let client: Client;
  let repository: ExampleRepository;

  beforeAll(async () => {
    config();
    client = await rootConnect();
  });

  afterAll(async () => {
    await client.end();
  });

  beforeEach(() => {
    repository = new ExampleRepository(client);
  });

  afterEach(async () => {
    await client.query("DELETE FROM example");
  });

  it("inserts and select", async () => {
    const id = v4();
    await repository.insert(id);
    expect(await repository.findAll()).toEqual([[id]]);
  });
});

src/test/problematic/select.test.ts

import { Client } from "ts-postgres";
import { ExampleRepository } from "../../exampleRepository";
import { config } from "dotenv";
import { rootConnect } from "../postgresUtils";

describe("Select Test Suite", () => {
  let client: Client;
  let repository: ExampleRepository;

  beforeAll(async () => {
    config();
    client = await rootConnect();
  });

  afterAll(async () => {
    await client.end();
  });

  beforeEach(() => {
    repository = new ExampleRepository(client);
  });

  afterEach(async () => {
    await client.query("DELETE FROM example");
  });

  it("selects nothing", async () => {
    expect(await repository.findAll()).toEqual([]);
  });
});

src/test/postgreUtils.ts

export const rootConnect = async () => connectToDatabase(process.env.DB_NAME);

const connectToDatabase = async (database: string) => {
  const client = new Client({
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT),
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database,
    ssl: SSLMode.Disable,
  });

  await client.connect();
  return client;
};

Solution

While we could specify --maxWorkers=1 to jest, this would drastically increase our tests' duration. A better approach would be to enable reliable, isolated integration testing, by ensuring that each test suite runs against a separate, isolated database instance. To achieve this, we can create a freshly new database during the beforeAll function.

Assuming that your test suite is in a folder src/__test__ and SQL scripts needed to set up your database tables are in src/sql, the solution is as easy as using the root connection to create a new database with a UUID name and then using that connection to run SQL migration scripts. To achieve this, we can create a simple helper that will allow us to call only one function in our test suites. The modified code is shown below.

src/test/postgreUtils.ts

import { Client, SSLMode } from "ts-postgres";
import { v4 as uuid } from "uuid";
import { readdirSync, readFileSync } from "fs";

export const connectToTestDatabase = async () => {
  let client = await rootConnect();
  const database = uuid();
  await client.query(`CREATE DATABASE "${database}"`);
  await client.end();
  client = await connectToDatabase(database);
  await runMigrations(client);
  return client;
};

export const rootConnect = async () => connectToDatabase(process.env.DB_NAME);

const connectToDatabase = async (database: string) => {
  const client = new Client({
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT),
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database,
    ssl: SSLMode.Disable,
  });

  await client.connect();
  return client;
};

const runMigrations = async (client: Client) => {
  const migrationsPath = `${__dirname}/../../sql`;
  for (const filePath of readdirSync(migrationsPath))
    await runMigration(client, `${migrationsPath}/${filePath}`);
};

const runMigration = async (client: Client, filePath) => {
  for (const query of queriesInMigrationFile(filePath))
    await client.query(query);
};

/**
 * :warning: - Fails if a query inserts data containing ";" character
 * @param filePath
 */
const queriesInMigrationFile = (filePath: string) =>
  readFileSync(filePath).toString().split(";");

By making use of the above helper functions, you can ensure that each test suite runs against its own database instance, avoiding concurrency issues and enabling reliable, isolated integration testing.

Optimizing Migrations with PostgreSQL Client

In order to enhance the performance of running migrations, we can leverage the use of a PostgreSQL client. While the previous solution involving custom parsing of SQL files might works just fine for you, a client like PostgreSQL can offer better efficiency and will support migrations involving TRIGGERS for example.

To get started, simply install a PostgreSQL client by following the instructions below depending on your operating system:

For OS X, run brew install libpq && brew link --force libpq
For Ubuntu/Debian, run sudo apt install postgresql-client

Verify the installation by running psql --version

Next, we can slightly modify our runMigrations helper function to execute each migration file using the PostgreSQL client configuration. The code snippet for this is shown below:

export const runMigrations: async (client: Client) => {
  for (const filePath of readdirSync(migrationsPath))
    child_process.execSync(
      `PGPASSWORD="${client.config.password}" psql -h ${client.config.host} -p ${client.config.port} -U ${client.config.user} -d ${client.config.database} -a -f ${migrationsPath}/${filePath}`
    );
};

Bonus: Continuous Integration (CI)

To conclude this article, I would like to provide you with two examples of CI jobs utilizing a PostgreSQL database service, thus automating your tests on a CI/CD pipeline.

With gitlab-ci (.gitlab-ci.yml)

stages:
  - test

integration-tests:
  stage: test
  image: node:18.14.0
  services:
    - name: postgres:15.2
      alias: postgres
  variables:
    POSTGRES_DB: jest-psql-example
    POSTGRES_USER: postgres
    POSTGRES_PASSWORD: example
    POSTGRES_HOST_AUTH_METHOD: trust
    DB_HOST: "postgres"
    DB_PORT: "5432"
    DB_USER: "postgres"
    DB_PASSWORD: "example"
    DB_NAME: "my-database"
  before_script:
    - apt-get update && apt-get install -y postgresql-client
    - yarn
    - for f in ./sql/*.sql; do psql -h ${DB_HOST} -U ${DB_USER} -d ${DB_NAME} -a -f $f > /dev/null; done
  script:
    - jest

With github workflow (.github/worflows/test.yaml)

name: test
on: [ push ]
jobs:
  integration-tests:
    runs-on: ubuntu-latest
    container: node:18.14
    services:
      postgres:
        image: postgres:15.2
        env:
          POSTGRES_DB: jest-psql-example
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: example
          POSTGRES_HOST_AUTH_METHOD: trust
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432
    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-node@v3
        with:
          node-version: '18'
      - run: npm install -g yarn
      - run: yarn
      - name: Run migrations
        env:
          DB_HOST: "postgres"
          DB_USER: "postgres"
          PGPASSWORD: "example"
          DB_NAME: "my-database"
        run: |
          apt-get update
          apt-get install --yes postgresql-client
          for f in ./sql/*.sql; do psql -h ${DB_HOST} -U ${DB_USER} -d ${DB_NAME} -a -f $f > /dev/null; done
      - name: Run integration tests
        env:
          DB_HOST: "postgres"
          DB_PORT: "5432"
          DB_USER: "postgres"
          DB_PASSWORD: "example"
          DB_NAME: "jest-psql-example"
        run: jest

All code samples are available on Jest-PostgresSQL-Integration-Testing repository

DEV Community

Integration testing with Jest and PostgreSQL

Problem

Solution

Optimizing Migrations with PostgreSQL Client

Bonus: Continuous Integration (CI)

Top comments (0)

Read next

Raku Fall Issue Cleanup

Function-Based Index in SQL

Create a simple REST API that can perform CRUD operations

Introduction to Serenity/JS