Mocking PostgreSQL queries in Node.js
Tags: postgres, node
Published: 2020-02-07 13:03:39

---
title: Mocking PostgreSQL queries in Node.js
published: true
description: Mocking query execution and results for testing purposes.
tags: postgres, node
---


[Slonik](https://github.com/gajus/slonik) added a new feature – [connection mocking](https://github.com/gajus/slonik#mocking-slonik).

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:

```js
import {
  createMockPool,
  createMockQueryResult,
} from 'slonik';

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

createMockPool(
  overrides: OverridesType
): DatabasePoolType;

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

```

If you are already using [Slonik](https://github.com/gajus/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](https://en.wikipedia.org/wiki/Method_stub) that will return results for those queries without connecting to the database. The most popular framework for spying and stubbing in JavaScript [sinon](http://sinonjs.org/).

Here is an example `updatePassword` implementation and tests:

```js
import sinon from 'sinon';
import {
  createMockPool,
  createMockQueryResult,
  sql,
} from 'slonik';
import bcrypt from 'bcrypt';

const query = sinon
  .stub()
  .onCall(0)
  .returns(
    createMockQueryResult([])
  )
  .onCall(1)
  .returns(
    createMockQueryResult([
      {
        id: 'bar',
      },
    ])
  )
  .onCall(2)
  .throws();

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

  if (lastPasswordHash) {
    if (bcrypt.compare(newPassowrd, 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 https://sinonjs.org/releases/latest/spies/

```

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`](https://sinonjs.org/releases/latest/spies/#spycalledwitharg1-arg2-) and even [`calledBefore`](https://sinonjs.org/releases/latest/spies/#spycalledbeforeanotherspy) to provide fake results and to assert their order of invocation. Refer to [Slonik](https://github.com/gajus/slonik) and [sinon](https://sinonjs.org/releases/latest/spies) 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.