Back to all posts

A Better Way To Run Integration Tests With Prisma and PostgresQL

By Manthan Mallikarjun, Published about 3 years ago

Prisma, the TypeScript ORM, has taken the development community by storm. It provides a fully type-safe interface with PostgresQL (and other SQL and no-SQL DBMS) which makes it a no-brainer for us to use at ludicrous.

The Prisma team recently released a guide on how to write integration tests but it is different than how we do it at ludicrous. Before this guide even existed, we searched through the Prisma GitHub issues and community posts, and other third-party blog posts to find the best method. After combining the tips and tricks scattered throughout, we found a solution that works really well for us.

Our method has the following benefits compared to the Prisma official guide:

• Does not require the use of Docker

  • You can reuse the already existing connection to your local development database.

• Does not require you to write any teardown code in your tests

  • Between each run, it drops the schema and pushes a new one. Don't worry though, it does not use the default schema so your actually local development data is never touched.

There are a few limitations to keep in mind though. Since the schema is being dropped and created between each test, it generally adds a few seconds to every test. Don't worry though, these tests can run in parallel so the overall effect is not too noticeable. Additionally, when running the tests in a CI environment, you will need to set up a temporary database to read and write to. We have included a snippet from our GitHub Actions configuration at the end of this post to provide some pointers.

NOTE: This method only works with PostgresQL, however, you can try to port it to other SQL/NoSQL DBs. It should be possible with any DBMS that supports schema's and with everything else you could drop the whole DB and create a new one if that works for you. We will point out the lines that require changing if you are not using PostgresQL.

Prerequisites

First, make sure you have a working Prisma application set up before you follow this guide. Additionally, we are going to use Jest as the test runner, but you should be able to modify it to work with other runners as well.

If you don't have an existing application, check out the prisma-integration-test-example for a good starting point.

Setting up

In your application, Prisma requires you to initialize your Prisma client with code that looks something like this: const prismaClient = new PrismaClient();. For this testing system to work, we need to separate out this command into its own file so that we can mock it more easily. We recommend creating a file prisma/index.ts and placing the following code in it.

import { PrismaClient } from '@prisma/client';
 
export const prisma = new PrismaClient();

Example: prisma-integration-test-example/prisma/index.ts

Additionally, we need to make sure that the uuid library is available. If you don't have it installed already, you can run the following command.

npm install uuid @types/uuid -D

Mocking PrismaClient to use the test schema

Now that we have all our pre-requisites and have our code set up, we can go ahead and mock the Prisma Client to use the test schema.

The first thing we need to do is create a mock replacement for the prismaClient. Create a folder in the same location as your prismaClient initialization called __mocks__ and then create a file inside that folder with the same name as the file with your prismaClient. In our case, we will create a file in prisma/__mocks__/index.ts. Then fill it in with the following code.

import { PrismaClient } from '@prisma/client';
import { execSync } from 'child_process';
import { join } from 'path';
import { URL } from 'url';
import { v4 } from 'uuid';
 
const generateDatabaseURL = (schema: string) => {
  if (!process.env.DATABASE_URL) {
    throw new Error('please provide a database url');
  }
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.append('schema', schema);
  return url.toString();
};
 
const schemaId = `test-${v4()}`;
const prismaBinary = join(
  __dirname,
  '..',
  '..',
  'node_modules',
  '.bin',
  'prisma',
);
 
const url = generateDatabaseURL(schemaId);
process.env.DATABASE_URL = url;
export const prisma = new PrismaClient({
  datasources: { db: { url } },
});
 
beforeEach(() => {
  execSync(`${prismaBinary} db push`, {
    env: {
      ...process.env,
      DATABASE_URL: generateDatabaseURL(schemaId),
    },
  });
});
afterEach(async () => {
  await prisma.$executeRawUnsafe(
    `DROP SCHEMA IF EXISTS "${schemaId}" CASCADE;`,
  );
  await prisma.$disconnect();
});

Example: prisma-integration-test-example/prisma/__mocks__/index.ts

There is a lot going on but the logic boils down to the following:

• Before each test

  • Generate a random name with a UUID (e.g. test-b57f20c3-f849-4966-b84a-8d8d633e88dc).

  • Use the prisma db push command to push your Prisma schema into the DB with the random name as the PostgresQL schema name

• Run the test

• After each test

  • Drop the PostgresQL schema

  • Disconnect prisma client

If you are using something other than PostgresQL, you will need to replace the step that pushes the Prisma Schema and the step that drops the PostgresQL schema.

We are almost done! The last thing we need to do is to tell Jest to load this mock when it is starting up. To do that we need to first create a mocks.ts file somewhere in our codebase.

jest.mock('./prisma/index');

Example: prisma-integration-test-example/mocks.ts

Followed by updating the jest.config.js to tell it to load the file using the setupFilesAfterEnv configuration.

module.exports = {
  // ...
  setupFilesAfterEnv: ['./mocks.ts'],
};

Example: prisma-integration-test-example/jest.config.js

That's it! You can now start writing a test.

NOTE: Using a .env in prisma/.env seems to work fine, however, this may not work in the future. If you see an error message saying please provide a database url, this is likely the problem.

Writing a Test

When writing a test, you can use prisma inside of your tests exactly how you would in the rest of your application. A sample test should look like this.

import { Post } from '@prisma/client';
import faker from 'faker';
import supertest from 'supertest';
import { prisma } from '../../prisma/index';
import { app } from '../app';
 
describe('with existing post', () => {
  let post: Post;
 
  beforeEach(async () => {
    post = await prisma.post.create({
      data: {
        title: faker.lorem.words(3),
        content: faker.lorem.words(10),
      },
    });
  });
 
  it('should return the post', async () => {
    const response = await supertest(app).get('/posts').expect(200);
    expect(JSON.parse(response.text)).toMatchObject([post]);
  });
});

A test to see if creating something works could look something like this.

it('should create a post', async () => {
  expect(await prisma.post.count()).toBe(0);
  const title = faker.lorem.word();
  const content = faker.lorem.word();
  const response = await supertest(app)
    .get(`/posts/new?title=${title}&content=${content}`)
    .expect(200);
  expect(JSON.parse(response.text)).toEqual(
    expect.objectContaining({ title, content }),
  );
  expect(await prisma.post.count()).toBe(1);
});

Sample app

If something isn't working, feel free to reference the following repository to see if what you have matches what was said in this blog post. github.com/ludicroushq/prisma-integration-test-example

Running in Github Actions

Finally, this system also works smoothly in many different CI environments. At ludicrous, we use Github Actions which allows us to spin up a PostgresQL server and connect it to the job. If you would like to run your tests in Github Actions, you can copy the yaml below!

name: main
on: [push]
jobs:
  test:
    runs-on: ubuntu-latest
 
    services:
      postgres:
        image: postgres
        env:
          POSTGRES_USER: root
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: example
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432
 
    steps:
      - uses: actions/checkout@v2
      - name: Use Node.js 14.x
        uses: actions/setup-node@v1
        with:
          node-version: '14.x'
 
      - run: npm ci
 
      - run: npm run ci:test
        env:
          DATABASE_URL: 'postgresql://root:postgres@localhost:5432/example'

Conclusion

While this system requires you to set up a running PostgresQL server before using it, it allows you to not think about mocking individual lines of code and instead focus on the data itself leading to tests that are easier to read and maintain.

There are some improvements we can make in the tests, including building a "factory" system to help initialize data in your tests very quickly, but that will be covered in another post. Thanks for reading!