Testing a project with raw SQL using integration tests

December 26, 2022

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 { Test } from "@nestjs/testing"
 
import DatabaseService from "../database/database.service"
import PostDto from "./post.dto"
import PostsRepository from "./posts.repository"
import { PostsService } from "./posts.service"
import PostsSearchRepository from "./postsSearch.repository"
import PostsStatisticsRepository from "./postsStatistics.repository"
import PostWithCategoryIdsModel, { PostWithCategoryIdsModelData } from "./postWithCategoryIds.model"
 
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 { Test } from "@nestjs/testing"
 
import DatabaseService from "../database/database.service"
import PostDto from "./post.dto"
import PostsRepository from "./posts.repository"
import { PostsService } from "./posts.service"
import PostsSearchRepository from "./postsSearch.repository"
import PostsStatisticsRepository from "./postsStatistics.repository"
import PostWithCategoryIdsModel, { PostWithCategoryIdsModelData } from "./postWithCategoryIds.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("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 { Test } from "@nestjs/testing"
 
import DatabaseService from "../database/database.service"
import PostDto from "./post.dto"
import PostModel, { PostModelData } from "./post.model"
import PostsRepository from "./posts.repository"
import { PostsService } from "./posts.service"
import PostsSearchRepository from "./postsSearch.repository"
import PostsStatisticsRepository from "./postsStatistics.repository"
import PostWithCategoryIdsModel, { PostWithCategoryIdsModelData } from "./postWithCategoryIds.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("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)
    })
  })
  describe("when calling the create method without category ids", () => {
    let sqlQueryResult: PostModelData
    beforeEach(() => {
      postData = {
        title: "Hello world!",
        content: "Lorem ipsum",
      }
      sqlQueryResult = {
        id: 1,
        author_id: 2,
        title: postData.title,
        post_content: postData.content,
      }
      runQueryMock.mockResolvedValue({
        rows: [sqlQueryResult],
      })
    })
    it("should return an instance of the PostModel", async () => {
      const result = await postsService.createPost(postData, 1)
      expect(result instanceof PostModel).toBe(true)
    })
    it("should return an object with the correct properties", async () => {
      const result = await postsService.createPost(postData, 1)
      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)
    })
  })
})

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
when calling the create method without category ids
✓ should return an instance of the PostMode
✓ should return an object with the correct properties

Testing controllers#

Another approach to integration tests is to include our controllers by performing HTTP requests. By doing that, we can more closely mimic how our application works in a real environment.

npm install supertest @types/supertest

Since our application uses the ValidationPipe, we need to add it explicitly.

import { INestApplication, ValidationPipe } from "@nestjs/common"
import { ConfigModule } from "@nestjs/config"
import { JwtModule } from "@nestjs/jwt"
import { Test } from "@nestjs/testing"
import * as request from "supertest"
 
import DatabaseService from "../database/database.service"
import { UserModelData } from "../users/user.model"
import UsersRepository from "../users/users.repository"
import UsersService from "../users/users.service"
import { AuthenticationController } from "./authentication.controller"
import { AuthenticationService } from "./authentication.service"
import RegisterDto from "./dto/register.dto"
 
describe("The AuthenticationController", () => {
  let runQueryMock: jest.Mock
  let app: INestApplication
  beforeEach(async () => {
    runQueryMock = jest.fn()
    const module = await Test.createTestingModule({
      imports: [
        ConfigModule.forRoot(),
        JwtModule.register({
          secretOrPrivateKey: "Secret key",
        }),
      ],
      controllers: [AuthenticationController],
      providers: [
        AuthenticationService,
        UsersRepository,
        UsersService,
        {
          provide: DatabaseService,
          useValue: {
            runQuery: runQueryMock,
          },
        },
      ],
    }).compile()
    app = module.createNestApplication()
    app.useGlobalPipes(new ValidationPipe())
    await app.init()
  })
  describe("when making the /register POST request", () => {
    describe("and using an incorrect email", () => {
      it("should throw an error", () => {
        return request(app.getHttpServer())
          .post("/authentication/register")
          .send({
            email: "not-an-email",
            name: "John",
            password: "strongPassword",
          })
          .expect(400)
      })
    })
    describe("and using the correct data", () => {
      let registrationData: RegisterDto
      let userModelData: UserModelData
      beforeEach(() => {
        registrationData = {
          email: "john@smith.com",
          name: "John",
          password: "strongPassword",
        }
        userModelData = {
          id: 1,
          email: registrationData.email,
          name: registrationData.name,
          password: registrationData.password,
          address_id: null,
          address_country: null,
          address_city: null,
          address_street: null,
        }
        runQueryMock.mockResolvedValue({
          rows: [userModelData],
        })
      })
      it("should result with the 201 status", () => {
        return request(app.getHttpServer())
          .post("/authentication/register")
          .send(registrationData)
          .expect(201)
      })
      it("should respond with the data without the password", () => {
        return request(app.getHttpServer())
          .post("/authentication/register")
          .send(registrationData)
          .expect({
            id: userModelData.id,
            name: userModelData.name,
            email: userModelData.email,
          })
      })
    })
  })
})

The AuthenticationController
when making the /register POST request
and using an incorrect email
✓ should throw an error
and using the correct data
✓ should result with the 201 status
✓ should respond with the data without the password

The SuperTest library is quite powerful and allows us to verify the response differently. For example, we can ensure the response headers are correct. For a complete list of the features, check out the documentation.

Summary#

In this article, we’ve gone through the idea of integration tests. We’ve described why they’re important and how they can benefit our application. We’ve also implemented integration tests with two different approaches. One of them required us to install the supertest library to simulate actual HTTP requests. All of the above allowed us to test our API more thoroughly and increase the confidence that we’re creating a reliable product.