Mastering Database Testing with Jest and SuperTest: A Hands-On Approach for PostgreSQL

If you’re building a NodeJS/ExpressJS Serverless Application app that connects to PostgreSQL, testing your code is essential to ensure it works as expected. While many frameworks offer the ability to mock functions and database calls, this approach may not be sufficient for complex functions that require realistic database responses. That’s where the “SuperTest” npm package comes in. In this blog post, i’ll show you how to use SuperTest to write comprehensive test cases for your NodeJS/ExpressJS app that accurately simulate real-world scenarios. With this knowledge, you can confidently validate your code and catch bugs before they become bigger problems.

SETUP CRUD APP

In this blog post, we’ll explore how to write comprehensive test cases for a NodeJS/ExpressJS app that connects to a PostgreSQL database. To get started, we’ll create a simple app that performs CRUD operations using the express and pg-promise packages. The code snippet below shows the database connection file (db.js), where we set up our PostgreSQL database connection using pg-promise:

db.js

const pgp = require('pg-promise')()

const coreDB = {
    user: 'postgres',
    password: 'postgres',
    host: 'localhost',
    port: 5432,
    database: 'testUser',
    ssl: false
  };
  
const db = pgp(coreDB)

module.exports = db

and here is the index.js file where i am doing CRUD operation on user and at last export the express app which will be used in test file to write test case based on the routes.

index.js

const express = require('express')

const db = require('./db')
const app = express()

app.use(express.json())

app.get('/user', async (req,res) =>{
    const allUsers = await db.query('Select * From blog.users')
    res.send(allUsers)
})

app.post('/createUser', async (req,res) =>{
    const { id, name, email, age, phone} = req.body
    const createdUser = await db.one('INSERT INTO blog.users(userId, userName, email, age, phone) VALUES($1, $2, $3, $4, $5) Returning*', [id, name, email, age, phone])
    res.send(createdUser)
})

app.put('/updateUser', async (req, res) =>{
    const { id, name } = req.body
    const updatedResponse = await db.one('UPDATE blog.users SET userName = $2 WHERE userId=$1 Returning*', [id, name])
    res.send(updatedResponse)
})

app.delete('/deleteUser', async (req, res) =>{
    const { id } = req.body
    const deletedUser = await db.none('DELETE FROM blog.users WHERE userId=$1', [id])
    res.status(204).send();
})

app.listen(3020, () =>{
    console.log('server started')
})

module.exports = app;

WRITE TEST CASE USING SUPERTEST

To get started, we need to install the required packages, such as Jest and Supertest, using npm. We also need to add a script in the package.json file to run the test cases.

npm i jest supertest

We can then create a new file, index.test.js, to write the test cases. In this file, we define the variables for the user and its details that we want to create, update, and delete. Then, we define the test cases to test the functionality of the CRUD operations for the created user. We can make use of the supertest package to simulate the requests to the app and get the response back.

index.test.js

const request = require("supertest");
const app = require("./index");

const user = {
  id: 5,
  name: "userNew5",
  email: "user5@gmail.com",
  age: 25,
  phone: "7685895869",
};

const id = 5;
const updateUserInfo = { id: 5, name: "user5" };

describe("test case for postgresql crud app", () => {
  it("test case for getting all users", async () => {
    const response = await request(app).get("/user");
    expect(response.status).toBe(200);
  });

  it("test case for creating a user", async () => {
    const createUser = await request(app).post("/createUser").send(user);
    console.log("response after creating user", createUser);
    expect(createUser.status).toBe(200);
    expect(createUser.body.isDeleted).toBeFalsy();
  });

  it("test case to update the user", async () => {
    const updateUser = await request(app).put("/updateUser").send(updateUserInfo);
    expect(updateUser.status).toBe(200);
    expect(updateUser.body.name).toBe(updateUser.name);
  });

  it("test case for deleting the user", async () => {
    const deleteUser = await request(app).delete("/deleteUser").send({ id });
    expect(deleteUser.status).toBe(204);
  });
});

HOW IT WORKS?

Remember we exported the express app?, import here in the second line, then i defined the userInfo to do CRUD operation.

  const response = await request(app).get("/user");
  const createUser = await request(app).post("/createUser").send(user);

Here, we pass the Express app as a parameter to the request function, which allows us to access the get, post, put, and delete methods. We simply pass the router endpoint to these methods, and if required, we can send query parameters using the .query method. For the post method, we send the data in the request body, just like we do in Axios, here as .send. If you want to know what are all the methods available in supertest you can refer the original document. so now the test case is ready.

now run the command in your terminal

 npm test

see your test cases are been succeeded.

It’s fascinating to note that we are not mocking the behavior of the app return in the index.js file, but instead, we are calling the database to perform the CRUD operation, which means it will actually create, modify, and delete the record in the database. We have added a console statement to see the response back after creating the user, remember this line.

    console.log("response after creating user", createUser);
    

I here provide a screenshot of the response after creating the user, which shows the response status, body, headers, and other methods get back from supertest while testing.

CONCLUSION

By learning how to write scenario-based test cases, you can greatly improve the maintainability of your code. With this approach, you can better understand the behavior and requirements of your application, leading to more efficient and effective testing. By focusing on real-world scenarios, you can identify and address issues before they become problems for end-users. Ultimately, writing easier and more effective test cases will help you to create more reliable software that meets the needs of your users.