Integration testing

Integration tests focus on testing how separate parts of the program work together. In the context of applications using a database, integration tests usually require a database to be available and contain data that is convenient to the scenarios intended to be tested.

One way to simulate a real world environment is to use to encapsulate a database and some test data. This can be spun up and torn down with the tests and so operate as an isolated environment away from your production databases.

Note: This offers a comprehensive guide on setting up an integration testing environment and writing integration tests against a real database, providing valuable insights for those looking to explore this topic.

Prerequisites

This guide assumes you have and installed on your machine as well as Jest setup in your project.

The following ecommerce schema will be used throughout the guide. This varies from the traditional User and Post models used in other parts of the docs, mainly because it is unlikely you will be running integration tests against your blog.

schema.prisma
1// Can have 1 customer
2// Can have many order details
3model CustomerOrder {
4 id Int @id @default(autoincrement())
5 createdAt DateTime @default(now())
6 customer Customer @relation(fields: [customerId], references: [id])
7 customerId Int
8 orderDetails OrderDetails[]
9}
10
11// Can have 1 order
12// Can have many products
13model OrderDetails {
14 id Int @id @default(autoincrement())
15 products Product @relation(fields: [productId], references: [id])
16 productId Int
17 order CustomerOrder @relation(fields: [orderId], references: [id])
18 orderId Int
19 total Decimal
20 quantity Int
21}
22
23// Can have many order details
24// Can have 1 category
25model Product {
26 id Int @id @default(autoincrement())
27 name String
28 description String
29 price Decimal
30 sku Int
31 orderDetails OrderDetails[]
32 category Category @relation(fields: [categoryId], references: [id])
33 categoryId Int
34}
35
36// Can have many products
37model Category {
38 id Int @id @default(autoincrement())
39 name String
40 products Product[]
41}
42
43// Can have many orders
44model Customer {
45 id Int @id @default(autoincrement())
46 email String @unique
47 address String?
48 name String?
49 orders CustomerOrder[]
50}

The guide uses a singleton pattern for Prisma Client setup. Refer to the singleton docs for a walk through of how to set that up.

Add Docker to your project

Docker compose code pointing towards image of container holding a Postgres database

With Docker and Docker compose both installed on your machine you can use them in your project.

  1. Begin by creating a docker-compose.yml file at your projects root. Here you will add a Postgres image and specify the environments credentials.
docker-compose.yml
1# Set the version of docker compose to use
2version: '3.9'
3
4# The containers that compose the project
5services:
6 db:
7 image: postgres:13
8 restart: always
9 container_name: integration-tests-prisma
10 ports:
11 - '5433:5432'
12 environment:
13 POSTGRES_USER: prisma
14 POSTGRES_PASSWORD: prisma
15 POSTGRES_DB: tests

Note: The compose version used here (3.9) is the latest at the time of writing, if you are following along be sure to use the same version for consistency.

The docker-compose.yml file defines the following:

  • The Postgres image (postgres) and version tag (:13). This will be downloaded if you do not have it locally available.
  • The port 5433 is mapped to the internal (Postgres default) port 5432. This will be the port number the database is exposed on externally.
  • The database user credentials are set and the database given a name.
  1. To connect to the database in the container, create a new connection string with the credentials defined in the docker-compose.yml file. For example:
.env.test
1DATABASE_URL="postgresql://prisma:prisma@localhost:5433/tests"

The above .env.test file is used as part of a multiple .env file setup. Checkout the using multiple .env files. section to learn more about setting up your project with multiple .env files

  1. To create the container in a detached state so that you can continue to use the terminal tab, run the following command:
$docker-compose up -d
  1. Next you can check that the database has been created by executing a psql command inside the container. Make a note of the container id.

    docker ps
    Hide CLI results
    CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
    1322e42d833f postgres:13 "docker-entrypoint.s…" 2 seconds ago Up 1 second 0.0.0.0:5433->5432/tcp integration-tests-prisma

Note: The container id is unique to each container, you will see a different id displayed.

  1. Using the container id from the previous step, run psql in the container, login with the created user and check the database is created:

    docker exec -it 1322e42d833f psql -U prisma tests
    Hide CLI results
    tests=# \l
    List of databases
    Name | Owner | Encoding | Collate | Ctype | Access privileges
    postgres | prisma | UTF8 | en_US.utf8 | en_US.utf8 |
    template0 | prisma | UTF8 | en_US.utf8 | en_US.utf8 | =c/prisma +
    | | | | | prisma=CTc/prisma
    template1 | prisma | UTF8 | en_US.utf8 | en_US.utf8 | =c/prisma +
    | | | | | prisma=CTc/prisma
    tests | prisma | UTF8 | en_US.utf8 | en_US.utf8 |
    (4 rows)

Integration testing

Integration tests will be run against a database in a dedicated test environment instead of the production or development environments.

The flow of operations

The flow for running said tests goes as follows:

  1. Start the container and create the database
  2. Migrate the schema
  3. Run the tests
  4. Destroy the container

Each test suite will seed the database before all the test are run. After all the tests in the suite have finished, the data from all the tables will be dropped and the connection terminated.

The function to test

The ecommerce application you are testing has a function which creates an order. This function does the following:

  • Accepts input about the customer making the order
  • Accepts input about the product being ordered
  • Checks if the customer has an existing account
  • Checks if the product is in stock
  • Returns an "Out of stock" message if the product doesn't exist
  • Creates an account if the customer doesn't exist in the database
  • Create the order

An example of how such a function might look can be seen below:

create-order.ts
1import prisma from '../client'
2
3export interface Customer {
4 id?: number
5 name?: string
6 email: string
7 address?: string
8}
9
10export interface OrderInput {
11 customer: Customer
12 productId: number
13 quantity: number
14}
15
16/**
17 * Creates an order with customer.
18 * @param input The order parameters
19 */
20export async function createOrder(input: OrderInput) {
21 const { productId, quantity, customer } = input
22 const { name, email, address } = customer
23
24 // Get the product
25 const product = await prisma.product.findUnique({
26 where: {
27 id: productId,
28 },
29 })
30
31 // If the product is null its out of stock, return error.
32 if (!product) return new Error('Out of stock')
33
34 // If the customer is new then create the record, otherwise connect via their unique email
35 await prisma.customerOrder.create({
36 data: {
37 customer: {
38 connectOrCreate: {
39 create: {
40 name,
41 email,
42 address,
43 },
44 where: {
45 email,
46 },
47 },
48 },
49 orderDetails: {
50 create: {
51 total: product.price,
52 quantity,
53 products: {
54 connect: {
55 id: product.id,
56 },
57 },
58 },
59 },
60 },
61 })
62}

The test suite

The following tests will check if the createOrder function works as it should do. They will test:

  • Creating a new order with a new customer
  • Creating an order with an existing customer
  • Show an "Out of stock" error message if a product doesn't exist

Before the test suite is run the database is seeded with data. After the test suite has finished a deleteMany is used to clear the database of its data.

Using deleteMany may suffice in situations where you know ahead of time how your schema is structured. This is because the operations need to be executed in the correct order according to how the model relations are setup.

However, this doesn't scale as well as having a more generic solution that maps over your models and performs a truncate on them. For those scenarios and examples of using raw SQL queries see Deleting all data with raw SQL / TRUNCATE

__tests__/create-order.ts
1import prisma from '../src/client'
2import { createOrder, Customer, OrderInput } from '../src/functions/index'
3
4beforeAll(async () => {
5 // create product categories
6 await prisma.category.createMany({
7 data: [{ name: 'Wand' }, { name: 'Broomstick' }],
8 })
9
10 console.log('✨ 2 categories successfully created!')
11
12 // create products
13 await prisma.product.createMany({
14 data: [
15 {
16 name: 'Holly, 11", phoenix feather',
17 description: 'Harry Potters wand',
18 price: 100,
19 sku: 1,
20 categoryId: 1,
21 },
22 {
23 name: 'Nimbus 2000',
24 description: 'Harry Potters broom',
25 price: 500,
26 sku: 2,
27 categoryId: 2,
28 },
29 ],
30 })
31
32 console.log('✨ 2 products successfully created!')
33
34 // create the customer
35 await prisma.customer.create({
36 data: {
37 name: 'Harry Potter',
38 email: 'harry@hogwarts.io',
39 address: '4 Privet Drive',
40 },
41 })
42
43 console.log('✨ 1 customer successfully created!')
44})
45
46afterAll(async () => {
47 const deleteOrderDetails = prisma.orderDetails.deleteMany()
48 const deleteProduct = prisma.product.deleteMany()
49 const deleteCategory = prisma.category.deleteMany()
50 const deleteCustomerOrder = prisma.customerOrder.deleteMany()
51 const deleteCustomer = prisma.customer.deleteMany()
52
53 await prisma.$transaction([
54 deleteOrderDetails,
55 deleteProduct,
56 deleteCategory,
57 deleteCustomerOrder,
58 deleteCustomer,
59 ])
60
61 await prisma.$disconnect()
62})
63
64it('should create 1 new customer with 1 order', async () => {
65 // The new customers details
66 const customer: Customer = {
67 id: 2,
68 name: 'Hermione Granger',
69 email: 'hermione@hogwarts.io',
70 address: '2 Hampstead Heath',
71 }
72 // The new orders details
73 const order: OrderInput = {
74 customer,
75 productId: 1,
76 quantity: 1,
77 }
78
79 // Create the order and customer
80 await createOrder(order)
81
82 // Check if the new customer was created by filtering on unique email field
83 const newCustomer = await prisma.customer.findUnique({
84 where: {
85 email: customer.email,
86 },
87 })
88
89 // Check if the new order was created by filtering on unique email field of the customer
90 const newOrder = await prisma.customerOrder.findFirst({
91 where: {
92 customer: {
93 email: customer.email,
94 },
95 },
96 })
97
98 // Expect the new customer to have been created and match the input
99 expect(newCustomer).toEqual(customer)
100 // Expect the new order to have been created and contain the new customer
101 expect(newOrder).toHaveProperty('customerId', 2)
102})
103
104it('should create 1 order with an existing customer', async () => {
105 // The existing customers email
106 const customer: Customer = {
107 email: 'harry@hogwarts.io',
108 }
109 // The new orders details
110 const order: OrderInput = {
111 customer,
112 productId: 1,
113 quantity: 1,
114 }
115
116 // Create the order and connect the existing customer
117 await createOrder(order)
118
119 // Check if the new order was created by filtering on unique email field of the customer
120 const newOrder = await prisma.customerOrder.findFirst({
121 where: {
122 customer: {
123 email: customer.email,
124 },
125 },
126 })
127
128 // Expect the new order to have been created and contain the existing customer with an id of 1 (Harry Potter from the seed script)
129 expect(newOrder).toHaveProperty('customerId', 1)
130})
131
132it("should show 'Out of stock' message if productId doesn't exit", async () => {
133 // The existing customers email
134 const customer: Customer = {
135 email: 'harry@hogwarts.io',
136 }
137 // The new orders details
138 const order: OrderInput = {
139 customer,
140 productId: 3,
141 quantity: 1,
142 }
143
144 // The productId supplied doesn't exit so the function should return an "Out of stock" message
145 await expect(createOrder(order)).resolves.toEqual(new Error('Out of stock'))
146})

Running the tests

This setup isolates a real world scenario so that you can test your applications functionality against real data in a controlled environment.

You can add some scripts to your projects package.json file which will setup the database and run the tests, then afterwards manually destroy the container.

package.json
1 "scripts": {
2 "docker:up": "docker-compose up -d",
3 "docker:down": "docker-compose down",
4 "test": "yarn docker:up && yarn prisma migrate deploy && jest -i"
5 },

The test script does the following:

  1. Runs docker-compose up -d to create the container with the Postgres image and database.
  2. Applies the migrations found in ./prisma/migrations/ directory to the database, this creates the tables in the container's database.
  3. Executes the tests.

Once you are satisfied you can run yarn docker:down to destroy the container, its database and any test data.