CODEWITHSUNDEEP
javascriptnode.jsunit-testingjestjssinon
R Johnson
R Johnson

Reputation: 53

Unit Testing Database Queries

Problem: So an issue that I have been running into lately as a beginner to unit testing, is dealing with classes that have heavy database usage.

Technologies Used: Typescript, SinonJS, Node-Postgres and Jest

Example: For the given function

public async insertUserDetails() {

  const confirmationCode = this.generateConfirmationCode();

  try {

    await this.db.query({
      text: 'INSERT INTO users (firstname, lastname, email, password, registration_date, confirmation_code) VALUES ($1, $2, $3, $4, $5, $6) RETURNING *',
      values: [this.firstname, this.lastname, this.email, this.password, this.registrationDate, confirmationCode]
    });


  } catch (error) {

    throw error;

  }

}

Question: Would a suitable unit test be to make sure that the db.query() function is called with the correct sql such as the following? Or would this be considered a waste of time and a brittle test?

test("should format correct sql", async () => {

    //Test Setup
    const user = new User({
        firstname: "John",
        lastname: "doe",
        email: "[email protected]",
        password: "some_password_hash",
        registrationDate: "some_random_date",
    });

    //Mocks
    const queryStub = sinon.stub(user.db, "query");
    const confirmationCode = sinon.stub(user,'generateConfirmationCode').returns("48fsakb8a-cfjdab");

    const sampleQuery = {
        text: 'INSERT INTO users (firstname, lastname, email, password, registration_date, confirmation_code) VALUES ($1, $2, $3, $4, $5, $6) RETURNING *',
        values: [user.firstname, user.lastname, user.email, user.password, user.registrationDate, confirmationCode]
    }

    //Preform
    await user.insertUserDetails();

    //Assert
    sinon.assert.calledWith(queryStub, sampleQuery);


});

Or should I skip unit tests all together on these type of functions and classes and set up an integration test database and test these cases via integration tests?

This is a very simplified example as I did not go into testing Database transactions, but testing transactions become an even larger can of worms, as there are multiple queries being preformed.

Thank you for any help! This is driving me crazy -__-

Upvotes: 5

Views: 4876

Answers (1)

Tyagi Akhilesh
Tyagi Akhilesh

Reputation: 860

Would a suitable unit test be to make sure that the db.query() function is called with the correct sql such as the following?

What you are trying to do is not unit test if you focus on making db.query. Then it becomes an integration test. Here your unit, which you want to test, is insertUserDetails. So you focus on behavior of this function. What you can test is: 1. that db.query is called with correct parameters (hoping this.db can be mocked) 2. If db.query throws exception, then your catch block is executed and your exception is thrown.

Similarly you should try to write unit test for this.db class covering query function.

So once you have made sure, both unit's are properly covered, then it shall be a good start.

Now if you have option of writing integration test in one shot, where you can use some kind of in memory database or may be a real database at your disposition, then go ahead and do that and you can not write the unit tests altogether.

Hope this helps.

Upvotes: 2

Related Questions