How to really unit test code that uses a DB.
Tags: postgres, typescript, javascript, testing
Published: 2020-07-31 16:04:01

# TLDR

I recently wrote [a small js lib](https://github.com/oguimbal/pg-mem) that allows you to emulate an  in-memory version of Postgres.

It is pure js (it works both in browser or with nodejs), and compatible with most query frameworks/ORMs.

You can play with it on [pg-mem playground](https://oguimbal.github.io/pg-mem-playground/).

# The problem with unit testing and databases:

`pg-mem` is compatible with several major ways to connect to a db, but lets assume you are using [pg-promise](https://github.com/vitaly-t/pg-promise).

When using a DB, you could have methods like

```typescript
const getUsers = () => db.many('select id, name from users');
const addUser = name => db.none('insert into users(name) values ($1)', [name]);
```

If you want to unit test things that uses those methods, you would have three options:

- reimplement a mock "user-service" which replicates what the db essentially does
- hook your request executor (pg-promise, in this case) and tell it "ok, i expect this request to return that".
- create a new clean database on each test run (!!)

Either way, this is **not** reliable nor sustainable as it can lock down implementation details in your unit tests, your mocks can be bugged, or the complexity of testing could explode as your project grows.


# The solution I came up with

In short, I reimplemented a pure js in-memory version of Postgres.

You can [play with it here](https://oguimbal.github.io/pg-mem-playground/) - notice that there are no outgoing queries as you type SQL, but you'll still get the results... yea... sql db, in memory, even in browsers.

It involved developping two quite heavy parts:
- A [postgresql syntax parser](https://github.com/oguimbal/pgsql-ast-parser/)
- A replication of the PG database engine.

But in the end, it works quite well:

```typescript
import {newDb} from 'pg-mem';
const db = newDb();
const results = db.public.many(`create table test(id text primary key, value jsonb);
         insert into test values ('key', '{"data": "hello"}');
         select value->>'data' || ' world' from test;`);
console.log(results) // guess what this prints ? :)
```

The only thing you will have to do to unit test your code that issues actual SQL queries: Replace your usual request framework with an instance of it which is bound to a `pg-mem` instance instead of an actual database !

No more thinking about how-the-hell-am-i-going-to-unit-test-that.

See github repo for examples.

# Benefits

### Performance
If you used to run your unit tests against a DB, know that pg-mem is really performant, and implements b-tree indexing.

Coupled with [vscode+mocha+hmr](https://github.com/oguimbal/vscode-mocha-hmr-sample), pg-mem is the perfect match to reach this nice ultra fast development loop, without compromising on your testing quality.

_(nb: this is a shameless plug - i'm the forker who implemented hmr support for vscode mocha explorer)_ 

This is how running thousands of sql requests feels like with pg-mem and vscode+mocha+hmr :

![run tests](https://dev-to-uploads.s3.amazonaws.com/i/m0mdp8bkon6f78879jap.gif)

### Actual SQL requests unit-testing

`pg-mem` parser is not perfect and can fail if you use fancy features, but it is good enough to handle most common features, and it can help you to unit test your requests syntax (if you write sql directly), and test your codes' actual behaviour against a database without having to rely on an actual database (which brings hell in your codebase: stateful unit tests).

### Index usage unit testing.

(experimental) `pg-mem` has its own implementation of indexes. It is far form being perfect, and does not match the way-more-clever behaviour of an actual posgres instance, but it can tell you when there are things that are obiously wrong... you can create a unit test to check that a specific request uses an existing index, for instance. If pg-mem uses an index, I think PG will be clever enough to use it also :)

### Database immutablility
`pg-mem` uses immutable data structures under the hood.
This means that you can restore a your database at any previous point in time, with no performance overhead. Quite useful (see ORM section below for an example).


# If you use an ORM

Most ORMs support creating the schema based on what you described of it => If you use such an ORM, I recommend this pattern (described here using Typeorm):

```typescript
// test-db.(js|ts)
import {newDb} from 'pg-mem';

let db = newDb();
let backup;
let orm;
export async function createOrm() {
    // close old instance
    // (typeorm has static stuff which prevents intiating multiple connection)
    await orm?.close()

    // create new instance
    orm = await db.adapters.createTypeormConnection({
        type: 'postgres',
        entities: [...allYouEntitiesHere]
    });

    if (!backup) {
        // this is the first test to run using this schema
        // ... lets create your tables
        //   (if you have thousands, this could be heavy)
        await orm.synchronize();

        // custom requests ? fill some shared data, etc...
        await orm.doSomething();
        await db.query('some sql');

        // Then, create a backup of this empty database with created schema
        // nb: this is instantaneous (o(1))
        backup = db.backup();
    } else {
        // Okay, a previous test already create the DB schema
        // => lets restore data as it was after schema creation
        // nb: this is instantaneous (o(1))
        backup.restore();
    }
    return orm;
}
```

Then use this `createOrm()` function  in all your unit tests  instead of creating an instance of your ORM the traditional way.

This way, you will have a clean database on each test run, for free ! (it really is performant in practice...)

# Work in progress: The future

I intend to develop serveral features in the future, like:

- Automatic table creation (i.e. if a table does not exist, the db will guess the table to create when you perform insert requests).
- Advanced features of postgres (gin indices, actual introspection, stored procedures, ...)


It currently supports mocking `pg`, `pg-native` `pg-promise` and `typeorm` (the latests are  using the two firsts, so their "mock" only are hooks to replace real `pg` connection with fake `pg` instance in their internal mechanisms)

See repo for sample usages. Do not hesitate to post issues or comments, and happy unit testing :)

{% github oguimbal/pg-mem %}