Originally published here

All of us love working with Postgres. It’s a powerful database with lots of functionality. You can write very complex queries that would join, and group many tables.

I guess with great power comes great responsibility and the bigger the project gets the easier it is to break something. That’s why you should test your Postgres business logic.

Jest is the weapon of choice for this article. We can mock everything with jest? But how to mock all the variety of your Postgres calls? Is it even necessary? Which plugin to choose and what is better for your needs, let’s find out!

Available approaches

To test Postgres with the Jest framework you can do all of the heavy liftings yourself.

Before running tests pre-install docker, spin up the Postgres container with the required params, create a schema, and after tests are finished, stop Postgres, delete the container — done. Then put all of this in a bash script and that's it.

As for me this approach sounds awful and would take at least half a minute to start and shut down properly on your local machine. You would not be able to iterate quickly and run it every time you made a change to query since it would take CPU resources -> your time.

Why should you consider it — you would have a fully capable database up and running for your tests. However with a reasonable effort.

Even easier option — look up some available npm plugins for Postgres there. But basically, all of them have limited capabilities. Sooner or later the queries, you test may become more complex. Therefore relying on lib that does not provide you with limited functionality is a bad idea.

Now you may ask, ok what to do? We have a simple answer…

@shelfio/jest-postgres

The current company I’m working for is using SQL a lot and we love Jest too! That’s why we spend some time creating open-source jest plugins.

Why is this one better?

This plugin is fully self-written, uses up-to-date dependencies, with pretty straightforward functionality.

Apart from the approaches below this plugin utilizes a full-featured, up-and-running Postgres instance on your machine. You can even choose the version and preconfigure scheme.

Since we are using this plugin a lot, we care about the work of this plugin in the first place and can recommend it with no doubts.

Downsides

Currently, only Linux(Ubuntu) and macOS are supported and tested out. Also, you should run your system as a root used in the case of macOS to prevent using sudo. For Linux, all the commands are executed under the postgres user that is installed after apt-get.

How to set up and use it?

1. Install a plugin:

npm i @shelfio/jest-postgres --save-dev

1. Add configuration to jest that would run the plugin only for specific files that have Postgres logic:

module.exports = {
  preset: '@shelf/jest-postgres',
  testRegex: `.*(?<!integration)\\.pg\\.test\\.js$`,
  maxWorkers: 1
};

This means that when you are testing create.js you can create create.pg.test.js the file that would test the logic and run Postgres for it.

1. Create jest-postgres-config.js a file that would contain configs.

const cwd = require('cwd');

module.exports = {
  seedPath: `${cwd()}/schema.sql`,
  version: 12,
  port: 5555
};

1. **Create schema.sql — it **is a required file for seeding Postgres with the schema of tables before running the tests.

create schema test;

create table test.model1
(
  id         varchar(36) not null,
  type       text,
  text       text,
  vector     double precision[],
  json       jsonb,
  updated_at timestamp,
  someBool   bool,
  constraint message
    primary key (id)
);

create index some_cool_index on test.model1 using btree(id);

Suggestion: just create a sctructure of the table in this file to keep it crear. Make insertion in code directly.

1. Profit, write some tests!

@shelfio/postgres-local under the hood

To run Postgres locally using node.js we followed the path of our first child elasticsearch-local (read more about it here).

The npm plugin has two functions — to start and stop Postgres.

When we run start() :

1. Raw bash commands executed via execSync download and install Postgres on your machine.

2. Creating /tmp/postgres-local directory

3. Initializing that temporary directory

4. Start Postgres

When we run stop() :

1. Stop Postgres

2. Delete /tmp/postgres-local

In the case of Linux users, we need to run all the commands as a postgres user to avoid permissions issues, also we need to create a user and database since ubuntu apt-get does not make it by default.

Conclusion

Now you have an alternative, buttle-proved approach for testing Postgres business logic. View examples of ready tests here.

Hope this plugin will help you test your queries better and iterate development faster!

Read me on Twitter!