Unit Test Functions Connecting to a Postgres DB

Question

I am using mocha, nodejs and pg-promise connecting to a Postgres db. I am wondering how can I unit test a function that executes a DML? This is just a simple example:

export function insertUser(db, { firstName, lastName }) {
  db.one(`
    INSERT INTO users
    (
      firstName,
      lastName
    )
    VALUES
    (
      $<firstName>,
      $<lastName>
    )
  `, { firstName, lastName });
}

If there is a typo in the query, how can the unit test capture it? I can create a test db, creating temporary tables on the fly but this will cause my test to slow down.

@vitaly-t - I can see several issues with this approach. First, it will slow down unit test since it will connect to DB. Second, it will be harder to maintain since we need to create a new test database that is the exact replica of the main database. Third, it is like we are testing pg-promise functionality. In unit testing, we assumed that pg-promise is properly tested on its own so there is no need to test its functionality. — Chris
– Chris, Commented May 5, 2017 at 0:47
Modern testing for database code requires a good isolation, which means using such systems as Travis CI, which gives you a fresh database for each of your PR-s, which is the way to go. And if you run tests locally, they are not slow at all, they are very fast, I do it all the time. Also, no idea what you mean by it is like testing pg-promise functionality. — vitaly-t
– vitaly-t, Commented May 5, 2017 at 8:40

Nicolas Oliver Feigenbaum · Accepted Answer · 2017-05-04 06:08:03Z

1

Given that the db variable is a function parameter, unit-testing this type of code will be straight-forward.

Go ahead and install a mocking library. We have had good success with sinonjs.

npm install sinon --save-dev

With the mock library in place you can now create the unit-test, something along the lines as follows:

const should = require('should'),
      sinon = require('sinon'),
      helper = require('your-db-module');

describe('DB functions', () => {
  describe('insertUser', () => {
    const { insertUser } = helper;

    it('correctly inserts a new user', () => {
      const db = {
        one: sinon.stub()
      };

      insertUser(db, { firstName: 'john', lastName: 'doe' });

      db.one.calledWithExactly('INSERT INTO users(firstName, lastName) VALUES($<firstName>, $<lastName>)', { firstName: 'john', lastName: 'doe'}).should.eql(true);
    });
  });
});

That should set you off on the right foot.

answered May 4, 2017 at 6:08

Nicolas Oliver Feigenbaum

1085 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

So we need to replicate the query from the code to the test. I was just thinking that with this approach, a developer will simply copy and paste the query from the test to the code. What if I mispelled "INSERT" to "INSET"? How can a unit test catch that error?

Nicolas Oliver Feigenbaum Over a year ago

IMHO, you should write the test before you write the function itself to help avoid that. Beyond that you would have 1 or more functionals tests around this area of code to check that things are working as expected. That should be more than adequate test coverage to continue.

Collectives™ on Stack Overflow

Unit Test Functions Connecting to a Postgres DB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest