Integration testing with Jest and PostgreSQL
Tags: testing, database, jest
Published: 2023-05-20 06:20:00

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_
```typescript
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_
```typescript
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_
```typescript
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_
```typescript
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_
```typescript
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:
```typescript
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)_
```typescript
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)_
```typescript
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](https://github.com/Thomas-Heniart/Jest-PostgresSQL-Integration-Testing)