Mocking PostgreSQL queries in Node.js

Node.js

PostgreSQL

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 {
  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 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 {
  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 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.