Testing a project with raw SQL using integration tests

Covering our project with tests can help us ensure that our application works as expected and is reliable. While unit tests play a significant role, they are not enough. Therefore, this article explains the significance of integration tests and implements them in a NestJS project that uses raw SQL queries. The significance of integration tests The role of a unit test is to cover an individual piece of code and verify if it works as expected. For example, a particular unit test suite can test a single function or a class. When all our unit tests pass, it means that each part of our system works well on its own. However, that does not yet mean that all parts of the application interact with each other correctly. To verify the above, we need integration tests. Their job is to ensure that two or more pieces of our system integrate correctly. Testing NestJS services Writing integration tests does not mean we don’t mock any part of the application. For example, in our integration tests, we won’t be using an actual database. Tests that verify the application from start to finish are referred to as end-to-end (E2E) tests. They should mimic a real system as close as possible. posts.service.test.ts import PostDto from './post.dto'; import { Test } from '@nestjs/testing'; import DatabaseService from '../database/database.service'; import { PostsService } from './posts.service'; import PostWithCategoryIdsModel, { PostWithCategoryIdsModelData, } from './postWithCategoryIds.model'; import PostsRepository from './posts.repository'; import PostsStatisticsRepository from './postsStatistics.repository'; import PostsSearchRepository from './postsSearch.repository'; describe('The PostsService', () => { describe('when calling the create method with category ids', () => { it('should return an instance of the PostWithCategoryIdsModel', async () => { const postData: PostDto = { title: 'Hello world!', content: 'Lorem ipsum', categoryIds: [1, 2, 3], }; const sqlQueryResult: PostWithCategoryIdsModelData = { id: 1, author_id: 2, title: postData.title, post_content: postData.content, category_ids: postData.categoryIds, }; const runQueryMock = jest.fn(); runQueryMock.mockResolvedValue({ rows: [sqlQueryResult], }); const module = await Test.createTestingModule({ providers: [ PostsService, PostsRepository, PostsStatisticsRepository, PostsSearchRepository, { provide: DatabaseService, useValue: { runQuery: runQueryMock, }, }, ], }).compile(); const postsService = await module.get(PostsService); const result = await postsService.createPost(postData, 1); expect(result instanceof PostWithCategoryIdsModel).toBe(true); }); }); }); The PostsService when calling the create method with category ids ✓ should return an instance of the PostWithCategoryIdsModel Above, we test if the PostsService class works well with the PostsRepository. posts.service.ts import { Injectable } from '@nestjs/common'; import PostsRepository from './posts.repository'; import PostDto from './post.dto'; @Injectable() export class PostsService { constructor( private readonly postsRepository: PostsRepository, ) {} createPost(postData: PostDto, authorId: number) { if (postData.categoryIds?.length) { return this.postsRepository.createWithCategories(postData, authorId); } return this.postsRepository.create(postData, authorId); } // ... }In our test, we only mock the DatabaseService. Thanks to that, we test if the PostService integrates correctly with the PostsRepository. posts.repository.ts import { BadRequestException, Injectable, } from '@nestjs/common'; import DatabaseService from '../database/database.service'; import PostModel from './post.model'; import PostDto from './post.dto'; import PostWithCategoryIdsModel from './postWithCategoryIds.model'; import PostgresErrorCode from '../database/postgresErrorCode.enum'; import { isDatabaseError } from '../types/databaseError'; @Injectable() class PostsRepository { constructor(private readonly databaseService: DatabaseService) {} async create(postData: PostDto, authorId: number) { try { const databaseResponse = await this.databaseService.runQuery( ` // ... `, [postData.title, postData.content, authorId], ); return new PostModel(databaseResponse.rows[0]); } catch (error) { if ( !isDatabaseError(error) || !['title', 'post_content'].includes(error.column) ) { throw error; } if (error.code === PostgresErrorCode.NotNullViolation) { throw new BadRequestException( `A null value can't be set for the ${error.column} column`, ); } throw error; } } async createWithCategories(postData: PostDto, authorId: number) { const databaseResponse = await this.databaseService.runQuery( ` // ... `, [postData.title, postData.content, authorId, postData.categoryIds], ); return new PostWithCategoryIdsModel(databaseResponse.rows[0]); } // ... } export default PostsRepository; Mocking differently in each test So far, in our test, we put all of our logic in a single it function. An alternative for that is moving the setup code into the beforeEach functions. posts.service.test.ts import PostDto from './post.dto'; import { Test } from '@nestjs/testing'; import DatabaseService from '../database/database.service'; import { PostsService } from './posts.service'; import PostWithCategoryIdsModel, { PostWithCategoryIdsModelData } from "./postWithCategoryIds.model"; import PostsRepository from './posts.repository'; import PostsStatisticsRepository from './postsStatistics.repository'; import PostsSearchRepository from './postsSearch.repository'; describe('The PostsService', () => { let postData: PostDto; let runQueryMock: jest.Mock; let postsService: PostsService; beforeEach(async () => { runQueryMock = jest.fn(); const module = await Test.createTestingModule({ providers: [ PostsService, PostsRepository, PostsStatisticsRepository, PostsSearchRepository, { provide: DatabaseService, useValue: { runQuery: runQueryMock, }, }, ], }).compile(); postsService = await module.get(PostsService); }); describe('when calling the create method with category ids', () => { let sqlQueryResult: PostWithCategoryIdsModelData; beforeEach(() => { postData = { title: 'Hello world!', content: 'Lorem ipsum', categoryIds: [1, 2, 3], }; sqlQueryResult = { id: 1, author_id: 2, title: postData.title, post_content: postData.content, category_ids: postData.categoryIds } runQueryMock.mockResolvedValue({ rows: [sqlQueryResult], }); }); it('should return an instance of the PostWithCategoryIdsModel', async () => { const result = await postsService.createPost(postData, 1); expect(result instanceof PostWithCategoryIdsModel).toBe(true); }); it('should return an object with the correct properties', async () => { const result = await postsService.createPost(postData, 1) as PostWithCategoryIdsModel; expect(result.id).toBe(sqlQueryResult.id); expect(result.authorId).toBe(sqlQueryResult.author_id); expect(result.title).toBe(sqlQueryResult.title); expect(result.content).toBe(sqlQueryResult.post_content); expect(result.categoryIds).toBe(sqlQueryResult.category_ids); }) }); }); The PostsService when calling the create method with category ids ✓ should return an instance of the PostWithCategoryIdsModel ✓ should return an object with the correct properties Thanks to using beforEach, we can set up multiple tests using the same piece of code and avoid repeating it. We can take it further and change how we mock the DatabaseService at each test. posts.service.test.ts import PostDto from './post.dto'; import { Test } from '@nestjs/testing'; import DatabaseService from '../database/database.service'; import { PostsService } from './posts.service'; import PostWithCategoryIdsModel, { PostWithCategoryIdsModelData, } from './postWithCategoryIds.model'; import PostsRepository from './posts.repository'; import PostsStatisticsRepository from './postsStatistics.repository'; import PostsSearchRepository from './postsSearch.repository'; import PostModel, { PostModelData } from './post.model'; describe('The PostsService', () => { let postData: PostDto; let runQueryMock: jest.Mock; let postsService: PostsService; beforeEach(async () => { runQueryMock = jest.fn(); const module = await Test.createTestingModule({ providers: [ PostsService, PostsRepository, PostsStatisticsRepository, PostsSearchRepository, { provide: DatabaseService, useValue: { runQuery: runQueryMock, }, }, ], }).compile(); postsService = await module.get(PostsService); }); describe('when calling the create method with category ids', () => { let sqlQueryResult: PostWithCategoryIdsModelData; beforeEach(() => { postData = { title: 'Hello world!', content: 'Lorem ipsum', categoryIds: [1, 2, 3], }; sqlQueryResult = { id: 1, author_id: 2, title: postData.title, post_content: postData.content, category_ids: postData.categoryIds, }; runQueryMock.mockResolvedValue({ rows: [sqlQueryResult], }); }); it('sh