Test PostgreSQL with Testcontainers

Kacper Walczak · 13-07-2024

How to PostgreSQL integration tests with Testcontainers.

Introduction

In this article we will learn how to build integration tests with Testcontainers (opens in a new tab).

Testcontainers is a library that helps with databases in your tests.

Simply spins up a container that is ready to test.

We are going to build Repository that creates any table in PostgreSQL, inserts any data and fetches all rows for a given Table name.

Results

Integration testing without debug process will look like this:

> npm test
 
> testcontainers@1.0.0 test
> jest
 
 PASS  ./integration.test.js (8.499 s)
  Dynamic Table Repository
    ✓ should create and return multiple customers (10 ms)
 
Test Suites: 1 passed, 1 total
Tests:       1 passed, 1 total
Snapshots:   0 total
Time:        8.565 s
Ran all test suites.

Code

We are going to create dynamic SQL table creation with PostgreSQL and Testcontainers.

Let's build this system based on Customers table, each Customer contains id and name fields, where id is a primary key.

It's very simple CRUD but it shows how easy it is to integration tests with Testcontainers.

Prerequisites

You need to have:

installed Docker
installed Node.js

Setup

npm init -y
npm install pg --save
npm install jest @testcontainers/postgresql --save-dev

Run

Firstly turn on docker.

Regular test:

npm test

With debug:

DEBUG=testcontainers* npm test

Files

Our goal is to test if any Table can be created, any data will be inserted and returned rows properly.

const { Client } = require("pg");
const { PostgreSqlContainer } = require("@testcontainers/postgresql");
const { createTable, create, findAll} = require("./dynamic-table.repository");
 
describe("Dynamic Table Repository", () => {
    jest.setTimeout(60000);
 
    let postgresContainer;
    let postgresClient;
    const customersTable = {
        name: "customers",
        fields: {
            id: {
                primary: true,
                type: "INT",
                notNull: true
            },
            name: {
                type: "VARCHAR",
                notNull: true
            }
        }
    };
 
    beforeAll(async () => {
        postgresContainer = await new PostgreSqlContainer().start();
        postgresClient = new Client({ connectionString: postgresContainer.getConnectionUri() });
        await postgresClient.connect();
        await createTable(postgresClient, customersTable);
    });
 
    afterAll(async () => {
        await postgresClient.end();
        await postgresContainer.stop();
    });
 
    it("should create and return multiple customers", async () => {
        const customer1 = { id: 1, name: "John Doe" };
        const customer2 = { id: 2, name: "Jane Doe" };
 
        await create(postgresClient, {table: customersTable.name, fields: customer1});
        await create(postgresClient, {table: customersTable.name, fields: customer2});
 
        const customers = await findAll(postgresClient, customersTable.name);
        expect(customers).toEqual([customer1, customer2]);
    });
});

Output with debug on

With debug mode turned on:

> DEBUG=testcontainers* npm test
 
> testcontainers@1.0.0 test
> jest
 
  testcontainers [DEBUG] Checking container runtime strategy "TestcontainersHostStrategy"... +0ms
  testcontainers [DEBUG] Container runtime strategy "TestcontainersHostStrategy" is not applicable +1ms
  testcontainers [DEBUG] Checking container runtime strategy "ConfigurationStrategy"... +1ms
  testcontainers [DEBUG] Container runtime strategy "ConfigurationStrategy" is not applicable +0ms
  testcontainers [DEBUG] Checking container runtime strategy "UnixSocketStrategy"... +0ms
  testcontainers [DEBUG] Container runtime strategy "UnixSocketStrategy" is not applicable +0ms
  testcontainers [DEBUG] Checking container runtime strategy "RootlessUnixSocketStrategy"... +0ms
  testcontainers [TRACE] Fetching Docker info... +3ms
  testcontainers [TRACE] Fetching remote container runtime socket path... +36ms
  testcontainers [TRACE] Resolving host... +0ms
  testcontainers [TRACE] Fetching Compose info... +0ms
  testcontainers [TRACE] Looking up host IPs... +636ms
  testcontainers [TRACE] Initialising clients... +3ms
  ...
  testcontainers [DEBUG] [c1d1a17f4139] Removing container... +0ms
  testcontainers [DEBUG] [c1d1a17f4139] Removed container +40ms
  testcontainers [INFO] [c1d1a17f4139] Stopped container +0ms
  PASS  ./integration.test.js
   Dynamic Table Repository
     ✓ should create and return multiple customers (10 ms)
 
  Test Suites: 1 passed, 1 total
  Tests:       1 passed, 1 total
  Snapshots:   0 total
  Time:        3.931 s, estimated 4 s
  Ran all test suites.