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
Top comments (0)