Testing Postgres and Node.js APIs with Jest

April 28, 2021 · 13 min read

Alex Pearson

Platter CTO

Testing applications and their databases is tricky but important.

When done correctly, tests that include real database queries help to reassure developers that their features are implemented correctly across the entire stack. When done poorly, database-dependent test suites can be slow, flaky, and even dangerous to run.

In this post, we'll explore strategies for keeping data-dependent tests realistic, consistent, isolated, and fast by running tests against short-lived, isolated databases. The concepts in this post will apply to any application that depends on a database, but we'll be using Node for our runtime and Jest as our test runner to explore different strategies.

tip

This post starts with a discussion of testing principles and strategies. If you'd rather skip that and go right to the example, skip ahead to the Example Application section.

When to Test Your Database#

Generally speaking, most of your tests should be unit tests that don't require any additional "infrastructure" to run. Database connections are a kind of integration with an external system, which means that they should be mocked during "proper" unit testing.

But while this rule might make sense for testing logical errors within a single function or handler, it's often not feasible to mock the behavior of a relational database beyond basic inputs and outputs. This is especially true if you treat your database as a fortress, protecting against invalid data with constraints, types, and explicit relationships between entities. Those protections are an integral part of your application, and failing to test the queries made against those constraints can often result in runtime errors.

For this reason, the "unit" under test in an application should include the database itself whenever a change in the database schema could affect the behavior you are testing.

Consider a test suite for a handler in an HTTP server: testing stateless properties of that handler, like path parsing, response methods, token-based authentication, body parsing, and redirection are good use-cases for a mocked database. But when that handler queries or changes data in a database, it's better to include that database in your unit under test.

Testing Goals and Constraints#

The ideal test suite for an application has the following characteristics:

Realistic: tests should test things that matter in as close to real-world conditions as possible.
Consistent: tests should have the same outcome with the same inputs.
Isolated: tests should not affect one another.
Fast: the most useful tests are those that are run often, and devs skip slow test suites.

Unfortunately, those goals are also in conflict with one another when working with databases.

The ideally realistic database test would be run against the production database itself.
The ideally consistent database test would be run against a perfectly-reliable database connection or in-memory version of the database.
The ideally isolated database test would use separate databases for each test.
The ideally fast database test would compromise on the previous ideals in pursuit of shorter runtimes.

In the real world, we have to compromise on perfection for a number of reasons, including safety, speed, and ease-of-use. In the next section, we'll introduce a serverless API that needs testing, and a few approaches to testing that application that balance our more general testing requirements.

Our Example Application#

For the rest of this post, we'll be using this example application. It's a serverless application build on Netlify Functions (built on AWS Lambdas) that lets users POST messages to a single endpoint that stores those messages to a Postgres database. Without knowing anything else about the API, it's pretty easy to figure out what's going from this single handler:

functions/messages/index.js

// imports and postgres initialization omitted here

exports.handler = async event => {
  try {
    checkRequest(event)
    const payload = parseMessage(event) 
    const message = await insertMessage(payload, postgres)

    return new Response(message)
  } catch (responseError) {
    return responseError
  }
}

This handler checks the request parts (i.e. the method, path, headers, and body), then parses the message payload from the request body, then inserts that message payload into the database, and finally returns some generated message properties to the user as a JSON Response. And if anything goes wrong, the user gets a ResponseError instead.

Because we have divided up our steps into separate functions, it should be easy to create separate test suites for each step. Those test suites are going to have three approaches to data: omitting the database entirely, mocking the database's input and output, and running tests against the database. Let's examine how to set up tests for each case.

Data-less Tests#

checkRequest() and parseMessage() are both functions that take in request data that is easily mocked with a simple JavaScript Object. For example, a simple test suite for checkMessage might look like this:

functions/messages/checkRequest.test.js

const { ResponseError } = require('./response')
const checkRequest = require('./checkRequest')

const VALID_REQUEST = {
  httpMethod: 'POST',
  path: '/.netlify/functions/messages',
  headers: {
    'content-type': 'application/json'
  },
  body: '{foo:bar}'
}

describe('Message checkRequest()', () => {
  it('allows valid requests', () => {
    expect(checkRequest(VALID_REQUEST)).toBe(null)
  })

  it('rejects disallowed methods', () => {
    const getRequest = {...VALID_REQUEST, httpMethod: 'GET'}
    expect(() => checkRequest(getRequest)).toThrow(ResponseError)
    // additional methods omitted
  })
  // additional tests omitted
})

This kind of test is about as fast, consistent, and isolated as possible. It's almost always worth the time to write or reorganize a codebase with most of your testing happening with similarly simple inputs and outputs.

Mocked Database Tests#

Things are less simple with insertMessage(). As the name implies, this function INSERTs a new message into the database. Let's take a look at the function itself to see what needs to be tested:

functions/messages/insertMessage.js

const { ResponseError } = require('./response')

// "postgres" is a generic database client
// with a single async query() method
const insertMessage = async (message, postgres) => {
  try {
    const [insertedMessage] = await postgres.query(
      `INSERT INTO messages (text, replying_to)
       VALUES ($1, $2)
       RETURNING id, created_at`,
      [message.text, message.replyingTo]
    )

    return insertedMessage
  } catch (error) {
    const payload = {
      message: 'Bad Request',
      error: error.toString()
    }

    throw new ResponseError(payload, 400)
  }
}

While it's obvious that there is a very important database interaction in this test, there are still important, testable components of this function that do not depend on a particular database schema. In this case, we can verify the following:

Errors that are thrown from query() are re-emitted as Bad Request-flavored ResponseError
The first row of whatever comes back from query() is returned from insertMessage()

These are tests about how inputs and outputs of the database client are handled, and have nothing to do with the actual values or schema in the database. This is a perfect use-case for mocking that client instead of spinning up a new database.

Dependency Injection#

Before we actually mock this database client, let's talk about what makes this pattern possible in the first place: dependency injection. Up to this point, we've intentionally hand-waved the structure of that postgres parameter in insertMessage(). And that's because postgres is not a specific structure, but rather an interface. That interface might be a node-postgres-based connection pool, it might be Platter Postgres Client, or it might be something else entirely.

If we were to describe the contract between insertMessage() and postgres in TypeScript, it might look something like this:

interface Postgres {
  query(statement?: string, parameters?: any[]): Promise<any[]>
}

...where any is too permissive, but can be read as "any valid JSON". While most of the time that JSON will represent rows from a database, we can construct those rows directly for mocking out this client for testing.

Mocked `postgres`#

With the interface established, we can see that the simplest possible mocked database client would look like this:

const mockDatabase = {
  async query() {
    return []
  }
}

Because statement and parameters are optional, we can ignore them for testing purposes. In a real test, we'd also want to return some mocked data. In our test application, the response from the database should include a SERIAL id and a created_at timestamp. But the mocked version of that response could be anything, since we're really testing that insertMessage() returns the first result from the Array returned by query() (rather than thinking at all about the contents of that message). Let's take a look at how to handle this mocked case in a Jest-powered test suite:

functions/messages/insertMessage.test.js

const MOCK_MESSAGE_REQUEST = {
  text: 'this is legit text'
}

// an empty Object will suffice for these tests
const MOCK_MESSAGE_RESPONSE = {}

describe('Message insertMessage()', () => {
  it('returns the first row from a successful message insert', async () => {
    // each test gets its own mocked database client
    const mockDatabase = {
      async query() {
        return [MOCK_MESSAGE_RESPONSE]
      }
    }

    const response = await insertMessage(
      MOCK_MESSAGE_REQUEST,
      mockDatabase,
    )

    expect(response).toBe(MOCK_MESSAGE_RESPONSE)
  })

  it('handles query errors as response errors', async () => {
    // this implementation throws an error,
    // just like most `query()` implementations would
    const mockDatabase = {
      async query() {
        throw new Error('Something went wrong!')
      }
    }

    await expect(insertMessage(MOCK_MESSAGE_REQUEST, mockDatabase))
      .rejects
      .toBeInstanceOf(ResponseError)
  })
})

These tests, like the data-less unit tests before, are also very fast, isolated, consistent, and useful. By mocking the database client, we avoid many of the compromises that we'll encounter when running database-dependent tests.

Live Database Tests#

And now, we enter the realm of compromises. Like good engineers, let's evaluate our options and see which set of tradeoffs might best fit our circumstances.

Option 0: Don't Test Database Interactions#

While we made the case earlier that your database interactions should be under test, we've also managed to test a lot of things without ever touching a live database. Perhaps this level of testing is sufficient! While the messages in this example have defaults, constraints, types, and relationships that cannot be tested, other applications might validate all of those constraints in application code. Or perhaps slower database interactions are only tested in certain cases, like when pull requests are created, while the faster unit tests are run locally in watch mode during the development cycle.

This option maximizes speed, but it's important to understand the tradeoff with usefulness and correctness: by not testing these interactions regularly, you risk some potentially devastating blind spots as your schema evolves.

Option 1: Recreate Production Schema Locally#

Speaking of evolving schema: you're probably already using a migration framework to track changes to your schema. If that's the case, then you could use a local installation of Postgres (or its Dockerized version) to create brand new test databases before each test suite, then run your migrations against that database, then run your tests against that up-to-date schema.

While setting up that database would take more time than most unit tests, you would at least be able to test against the same types and constraints that you have in your "real" database. This is a significant improvement over skipping database testing entirely, but still comes with an important caveat: the schema doesn't tell the whole story. The data you test against is just as important, and a schema-only set of tests are not sufficient to be certain that your application code and continued schema iterations will work with the actual data in your database.

Option 2: Recreating Production Data Locally#

So if it's best to test against data, one could take the additional step of dumping or faking actual data. In many cases running pg_dump from a user-facing database periodically is perfectly fine (provided you're aware of how that might affect your database's response time). In this way, you can be confident that you have mostly up-to-date data by the time you run your tests.

Eventually, though, pg_dump will take too long for large databases, or production data will contain personal identifiers that ought not be replicated to every developer's machine. The alternative then might be to fake data or to anonymize particular columns, but that means accepting the additional maintenance burden of that data generation while giving up the watertight assurance that you're testing against useful data.

In addition, all of these local options have some shared pitfalls. First, you have to ensure that your local database configuration approximates your real production database configuration. This is not a trivial exercise, and can result in its own painful variants of "works on my ~~machine~~ local database". Second, you accept that there will be environmental differences between the way that your application communicates with your database. While your serverless functions might be communicating with a hosted database service with its own networking rules and pitfalls, your local environment might be using a direct socket connection without the proxies, protocols, pools, and ingress rules of your production database.

Whether those difference in environment matter will depend a bit on your application, but it's important to recognize that perfectly replicating that cloud-based environment locally is impossible.

Option 3: Clone Production#

The most useful test database is an exact copy of your production database running on the same hardware in the same datacenter as your production data. This can be tricky to pull off without using a database provider that includes this superpower as a first-class feature. With branching Postgres databases, using an exact copy of production should only add a couple of seconds per test suite (and it's getting faster every day).

Implementing test databases with `beforeAll()`#

Jest's beforeAll() and afterAll() methods make scaffolding and tearing down databases for every test suite easy! Using Platter Postgres's Client and the branch configuration option, creating databases for each test suite is as simple as:

functions/messages/insertMessage.test.js

const child_process = require('child_process')
const faker = require('faker')
const { ResponseError } = require('./response')
const insertMessage = require('./insertMessage')
const Postgres = require(
  '../../platter/postgres/node/TestingPostgresNodeJest'
)

describe('Message database INSERT', () => {
  // fake a branch name 
  // (although you'd want something more random eventually)
  const branch = faker.lorem.word(10) 

  // initialize a database client pointing at the new branch
  // (without Platter, this might be a connection pool to a local database)
  const postgres = new Postgres({
    branch,
    key: process.env.PLATTER_API_KEY,
  })

  // spawn child processes that create new branches from production data
  // (without Platter, this might be a psql + pg_dump + pg_restore script)
  beforeAll(() => {
    child_process.execSync(
      `npx platter postgres \
       branch create ${branch} \
       --instance testing-postgres-node-jest`
    )
  })

  // spawn child processes to clean up after onesself
  // (without Platter, this might be a psql + DROP DATABASE script)
  afterAll(() => {
    child_process.execSync(
      `npx platter postgres \
       branch delete ${branch} \
       --instance testing-postgres-node-jest`
    )
  })

  test('it generates correct responses for valid messages', async () => {
    const response = await insertMessage(MOCK_MESSAGE_REQUEST, postgres)

    expect(response.id).toBeGreaterThan(0)
  })

  test('it generates correct responses for valid replies', async () => {
    const threadStart = await insertMessage(MOCK_MESSAGE_REQUEST, postgres)
    const reply = {...MOCK_MESSAGE_REQUEST, replyingTo: threadStart.id}
    const response = await insertMessage(reply, postgres)

    expect(response.id).toBe(threadStart.id + 1)
  })

  test('it rejects invalid replies', async () => {
    const invalidPayload = {...MOCK_MESSAGE_REQUEST, replyingTo: 12345}
    const request = insertMessage(invalidPayload, postgres)

    await expect(request).rejects.toBeInstanceOf(ResponseError)
  })
})

Conclusion#

When we run all of the tests included in the example repo, they finish in around ~5 seconds. While this is fast enough to include directly in the loop of a TDD cycle, we're currently working on truly instant branching of production data to speed things up even more.

How do you handle testing against databases in your projects, and what practices and tools could make it better? Let us know at [email protected] or join us on Slack to discuss!

Recent posts

tip

When to Test Your Database#

Testing Goals and Constraints#

Our Example Application#

Data-less Tests#

Mocked Database Tests#

Dependency Injection#

Mocked postgres#

Live Database Tests#

Option 0: Don't Test Database Interactions#

Option 1: Recreate Production Schema Locally#

Option 2: Recreating Production Data Locally#

Option 3: Clone Production#

Implementing test databases with beforeAll()#

Conclusion#

Mocked `postgres`#

Implementing test databases with `beforeAll()`#