DEV Community

Gajus Kuizinas
Gajus Kuizinas

Posted on

Mocking PostgreSQL queries in Node.js

Slonik added a new feature – connection mocking.

Mocking allows you to test parts of the code that interact with the database without the database. Slonik is a PostgreSQL database client for Node.js and it provides two methods to mock queries against the database.

  • Use createMockPool to create a mock connection.
  • Use createMockQueryResult to create a mock query result.

Their API is as follows:

import {
} from 'slonik';

type OverridesType = {|
  +query: (
    sql: string,
    values: $ReadOnlyArray<PrimitiveValueExpressionType>,
  ) => Promise<QueryResultType<QueryResultRowType>>,

  overrides: OverridesType
): DatabasePoolType;

  rows: $ReadOnlyArray<QueryResultRowType>
): QueryResultType<QueryResultRowType>;

Enter fullscreen mode Exit fullscreen mode

If you are already using Slonik in your codebase, then in order to test the individual methods, switch createPool with createMockPool in the test setup and provide a stub/ spy to the createMockPool constructor. All the executed queries will now call query method and you can choose an arbitrary result for 1st, 2nd, etc. invocations.

In practice, this means that if you have a function that executes multiple queries, then you can inject a stub that will return results for those queries without connecting to the database. The most popular framework for spying and stubbing in JavaScript sinon.

Here is an example updatePassword implementation and tests:

import sinon from 'sinon';
import {
} from 'slonik';
import bcrypt from 'bcrypt';

const query = sinon
        id: 'bar',

const updatePassword = async (connection, userId, newPassowrd) => {
  const lastPasswordHash = await connection.maybeOneFirst(sql`
    SELECT password_hash
    FROM user_account_password
      user_id = ${userId}
    ORDER BY created_at DESC
    LIMIT 1

  if (lastPasswordHash) {
    if (, lastPasswordHash)) {
      throw new Error('New password must be different from the last password.');

  await connection.query(sql`
    INSERT INTO user_account_password (user_id, password_hash)
    VALUES (${userId}, ${bcrypt.hashSync(newPassowrd, 10)})

  await connection.query(sql`
    UPDATE user_account
    SET password = ${bcrypt.hashSync(newPassowrd, 10)}
    WHERE id = ${userId}

newPassowrd(createMockPool(), 1, 'foo');

// Write assertions about `query` method.
// @see

Enter fullscreen mode Exit fullscreen mode

This test provides mock results to the first and second queries ([] set and [{ id: 'bar' }]) and guards that if there are more query invocations, then an error must be raised.

If you need to assert the query SQL & the bound values in addition to pre-defining their results, then you can use calledWith and even calledBefore to provide fake results and to assert their order of invocation. Refer to Slonik and sinon documentation for more information.

These methods for mocking a database connection allow you to write unit tests for code that depend on the database state without running a database.

Top comments (1)

oguimbal profile image
Olivier Guimbal

Hi, you can also have a look at :)

It's a lib I recently released.
If you test it, any feedback would be much appreciated !