TLDR

I recently wrote a small js lib 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.

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.

When using a DB, you could have methods like

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 - 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
• A replication of the PG database engine.

But in the end, it works quite well:

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, 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 :

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):

// 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 :)

npm i pg-mem --save

import { newDb } from "pg-mem";

const db = newDb();
db.public.many(/* put some sql here */);

import { newDb } from "https://deno.land/x/pg_mem/mod.ts";

const db = newDb();
db.public.many(/* put some sql here */);