Running Jest for multiple DB with GitHub Actions

Introduction

If you’re working on an application that supports multiple databases, even minor adjustments can lead to issues related to SQL dialects or query builder specifications like Knex.

The headless CMS I’m currently developing also offers users the option to choose their preferred database, so it’s essential to ensure not only the logic but also the ability to perform CRUD operations correctly in each database when developing features.

The same holds true for testing, which requires some clever strategies, such as connecting to multiple databases to verify a single scenario. However, by automating these processes through continuous integration (CI), you can reduce the likelihood of unexpected bugs.

In this article, we’ll demonstrate how to use GitHub Actions to execute Jest tests on multiple databases while creating a disposable environment.

All the code is available on GitHub for your reference!

GitHub - collectionscms/collections: Drag and drop to import WordPress contents, Open source…

Environment

• TestTool — Jest
• DB — MySQL, PostgreSQL, SQLite…. .etc. As many as you want!
• CI — GitHub Actions

Goal

Don’t repeat “MySQL is OK, PostgreSQL is an error 😱”

Test Flow

• Launch Docker
• Migration /seed in Jest setup
• Test execution on multiple DBs!

Jest

Configuration

Begin by initializing the database for testing purposes. You can specify the setup code in the Jest configuration file’s globalSetup section.

For the finer details, please consult the repository, as we’ve excluded all but the most essential code.

/** @type {import('ts-jest').JestConfigWithTsJest} */
export default {
  ...
  globalSetup: './test/setups/setup.ts',
  ...
};

https://github.com/superfastcms/superfast/blob/main/jest.integrations.config.mjs

In this stage, migrations are executed, and seed data is provided for each database to be tested. The key distinction is that these processes are repeated within an array known as testDatabases.

export default async (): Promise<void> => {
  for (const testDatabase of testDatabases) {
    const database = knex(config.knexConfig[testDatabase]!);

    if (testDatabase === 'sqlite3') {
      writeFileSync('test.db', '');
    }

    await database.migrate.latest();
    await database.seed.run();
  }
};

https://github.com/superfastcms/superfast/blob/main/test/setups/setup.ts

Next is the test code.

Test Codes

While iterating with testDatabases as well, you can write the usual test cases 🙆

describe('Users', () => {
  describe('Create', () => {
    it.each(testDatabases)('%s - should create', async (database) => {
      const connection = databases.get(database)!;

      const repository = new UsersRepository(tableName, { knex: connection });
      const result = await repository.create(user);

      expect(result).toBeTruthy();
    });
  });
});

An important point to remember is that afterAll discards all connections. Failing to do so can result in Jest continuing to run for several minutes without terminating the process, even after the test has passed.

const databases = new Map<string, Knex>();

beforeAll(async () => {
  for (const database of testDatabases) {
    databases.set(database, knex(config.knexConfig[database]!));
  }
});

afterAll(async () => {
  for (const [_, connection] of databases) {
    await connection.destroy();
  }
});

https://github.com/superfastcms/superfast/blob/main/test/api/repositories/users.test.ts

Test run

With the setup now complete, let’s get started!

yarn test:int

The test succeeds like this~~Yay!! 🙌

yarn run v1.22.19
$ node --loader ts-node/esm --experimental-vm-modules node_modules/jest/bin/jest.js --config=jest.integrations.config.mjs
(node:2144) ExperimentalWarning: Custom ESM Loaders is an experimental feature and might change at any time
(Use `node --trace-warnings ...` to show where the warning was created)

Setup databases: [ 'sqlite3', 'mysql', 'maria', 'postgres' ]
🟢 Starting tests! 
(node:2144) ExperimentalWarning: VM Modules is an experimental feature and might change at any time
PASS test/api/repositories/projectSettings.test.ts (6.145 s)
PASS test/api/repositories/users.test.ts
-------------------------|---------|----------|---------|---------|------------------------------------------------------
File                     | % Stmts | % Branch | % Funcs | % Lines | Uncovered Line #s                                    
-------------------------|---------|----------|---------|---------|------------------------------------------------------
All files                |   72.97 |    83.33 |      40 |   72.97 |                                                      
 src                     |     100 |      100 |     100 |     100 |                                                      
  env.ts                 |     100 |      100 |     100 |     100 |                                                      
 src/api/database        |   39.74 |      100 |       0 |   39.74 |                                                      
  connection.ts          |   39.74 |      100 |       0 |   39.74 | 32-78                                                
 src/api/repositories    |   54.77 |    82.35 |   41.66 |   54.77 |                                                      
  base.ts                |   87.91 |       70 |   54.54 |   87.91 | 31-35,77-78,85-86,89-90                              
  projectSettings.ts     |   72.22 |      100 |      50 |   72.22 | 13-17                                                
  users.ts               |   29.54 |      100 |   27.27 |   29.54 | 13-17,25-30,40-49,52-63,66-81,84-107,110-116,119-131 
 src/exceptions          |      68 |      100 |   33.33 |      68 |                                                      
  base.ts                |   66.66 |      100 |      50 |   66.66 | 12-17                                                
  invalidCredentials.ts  |   71.42 |      100 |       0 |   71.42 | 5-6                                                  
 src/exceptions/database |     100 |      100 |     100 |     100 |                                                      
  recordNotUnique.ts     |     100 |      100 |     100 |     100 |                                                                                                         
-------------------------|---------|----------|---------|---------|------------------------------------------------------
Test Suites: 2 passed, 2 total
Tests:       5 passed, 5 total
Snapshots:   0 total
Time:        8.422 s
Ran all test suites.
🏁 Tests complete!

In this scenario, we’re conducting tests across all databases (SQLite, MySQL, MariaDB, and PostgreSQL). However, running these tests each time can be time-consuming and resource-intensive in a CI environment. In such cases, it’s advisable to pass specific tests as parameters, as demonstrated below, to streamline the process.

export const allDatabases = ['sqlite3', 'mysql', 'maria', 'postgres'];
export const testDatabases = process.env.TEST_DB?.split(',').map((v) => v.trim()) ?? allDatabases;

In my headless CMS, I use only SQLite for all DBs except when I modify the logic.

TEST_DB=sqlite3 yarn test:int

GitHub Actions

Having successfully executed the tests locally, you can now set them up as a GitHub Actions workflow. Automating the testing process helps minimize the chances of unexpected issues arising from minor adjustments or library updates made through Renovate.

jobs:
  integration:
    name: ${{ matrix.database }}
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        database:
          - sqlite3
          - mysql
          - maria
          - postgres
    steps:
      - name: Checkout repository
        uses: actions/checkout@v3

      - name: Yarn install
        run: yarn install

      - name: Start Services
        if: matrix.database != 'sqlite3'
        run: docker compose -f test/docker-compose.yml up ${{ matrix.database }} -d --quiet-pull --wait

      - name: Run Tests
        run: TEST_DB=${{ matrix.database }} yarn test:int

https://github.com/superfastcms/superfast/blob/main/.github/workflows/integration-full.yml

In continuous integration (CI), the focus is on the database to be tested for each pull request. While there are various approaches, one method involves specifying paths to run any files that have been modified by a PR, especially if they relate to backend code or the test code itself.

pull_request:
  branches:
    - main
  paths:
    - src/api/**
    - test/api/**
    - package.json
    - .github/workflows/integration-full.yml

Alternatively, you can save time and CI resources by exclusively using SQLite, as it doesn’t necessitate Docker startup!

Finally

We are developing a headless CMS Collections that will turn WordPress articles into an API with drag and drop 🚀

Collections

Full TypeScript, works with RDB. And it is open source. Please try the Live Demo.

Happy Coding!!