\
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 can we mock all your Postgres calls? Is it even necessary? Which plugin do we 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 lifting yourself.

Before running the 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 reasonable effort.

\
Even easier option — look up 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 a lib that does not provide you with limited functionality is a bad idea.

Now you may ask, what do I do then? We have a [simple answer…](https://github.com/shelfio/jest-postgres#readme)

\
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 the Jest Plugin?

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 the Jest Plugin

\

1. **Install a plugin:**

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

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

   ```javascript
   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.

\

3. **Create** `jest-postgres-config.js` a file that would contain configs.

   \

```javascript
const cwd = require('cwd');

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

\

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

```javascript
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.*

\

6. **Profit, write some tests!**

## Under the hood

To run Postgres locally using node.js we followed the path of our first child [elasticsearch-local](https://github.com/shelfio/elasticsearch-local) (read more about it [here](https://medium.com/shelf-io-engineering/test-elasticsearch-with-jest-like-a-pro-42386713b899)).

\
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](https://github.com/shelfio/jest-postgres/tree/master/test).

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

[Read me on Twitter!](https://twitter.com/harazdovskiy)

\

---

[Originally published here](https://medium.com/shelf-io-engineering/test-postgres-using-jest-755389b28d22)

Too Long; Didn't Read

Cassandra Forward a free Cassandra-focused community event

How to Test Your Postgres Business Logic with Jest Plugin

Too Long; Didn't Read

@harazdovskiy

RELATED STORIES