What is it ?

pg-node is an experimental in-memory emulation of a postgres database.

It works both in node or in browser.

DISCLAIMER

The syntax parser is home-made. Which means that some features are not implemented, and will be considered as invalid syntaxes.

This lib is quite new, so forgive it if some obivious pg syntax is not supported !

... And open an issue if you feel like a feature should be implemented :)

See it in action with pg-mem playground

It supports:

• Transactions
• Indices, somewhat (on "simple" requests)
• Basic data types (json, dates, ...)
• Joins, group bys, ...
• Easy wrapper creator for Typeorm, pg-promise (pgp), node-postgres (pg), pg-native

It does not (yet) support:

• Gin Indices
• Cartesian Joins
• Some aggregations (avg, count)
• Stored procedures
• Lots of small and not so small things (collate, timezones, tsqueries, ...)

... PR are open :)

Usage

As always, it stats with an:

npm i pg-mem --save

Then, assuming you're using something like Webpack if you're targetting a browser:

import { newDb } from 'pg-mem';

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

Features

Rollback to a previous state

pg-mem uses immutable data structures (here and here), which means that you can have restore points for free !

This is super useful if you indend to use pg-mem to mock your database for unit tests. You could:

1. Create your schema only once (which could be an heavy operation for a single unit test)
2. Insert test data which will be shared by all test
3. Create a restore point
4. Run your tests with the same db instance, executing a backup.restore() before each test (which instantly resets db to the state it has after creating the restore point)

Usage:

const db = newDb();
db.public.none(`create table test(id text);
                insert into test values ('value');`);
// create a restore point & mess with data
const backup = db.backup();
db.public.none(`update test set id='new value';`)
// restore it !
backup.restore();
db.public.many(`select * from test`) // => {test: 'value'}

pg-native

You can ask pg-mem to get you an object wich implements the same behaviour as pg-native.

// instead of
import Client from 'pg-native';

// use:
import {newDb} from 'pg-mem';
const Client = newDb.adapters.createPgNative();

node-postgres (pg)

You can use pg-mem to get a memory version of the node-postgres (pg) module.

// instead of
import {Client} from 'pg';

// use:
import {newDb} from 'pg-mem';
const {Client} = newDb.adapters.createPg();

pg-promise (pgp)

You can ask pg-mem to get you a pg-promise instance bound to this db.

// instead of
import pgp from 'pg-promise';
const pg = pgp(opts)

// use:
import {newDb} from 'pg-mem';
const pg = newDb.adapters.createPgPromise();

note: You must install pg-promise module first.

Typeorm

You can use pg-mem as a backend database for Typeorm, node-postgres (pg).

Usage:

const db = newDb();
const connection = await db.adapters.createTypeormConnection({
    type: 'postgres',
    entities: [/* your entities here ! */]
})

// create schema
await connection.synchronize();

// => you now can user your typeorm connection !

See detailed examples here and here.

See restore points (section above) to avoid running schema creation (.synchronize()) on each test.

NB: Restore points only work if the schema has not been changed after the restore point has been created

note: You must install typeorm module first.

Inspection

Subscriptions

You can subscribe to some events, like:

const db = newDb();

// called on each successful sql request
db.on('query', sql => {  });
// called on each failed sql request
db.on('query-failed', sql => { });
// called on schema changes
db.on('schema-change', () => {});

Experimental subscriptions

pg-mem implements a basic support for indices.

These handlers are called when a request cannot be optimized using one of the created indices.

However, a real postgres instance will be much smarter to optimize its requests... so when pg-mem says "this request does not use an index", dont take my word for it.

// called when a table is iterated entierly (ex: 'select * from data where notIndex=3' triggers it)
db.on('seq-scan', () => {});

// same, but on a specific table
db.getTable('myTable').on('seq-scan', () = {});

// will be called if pg-mem did not find any way to optimize a join
db.on('catastrophic-join-optimization', () => {});

Development

Pull requests are welcome :)

To start hacking this lib, you'll have to:

• Use vscode
• Install mocha test explorer with HMR support extension
• npm start
• Reload unit tests in vscode

... once done, tests should appear. HMR is on, which means that changes in your code are instantly propagated to unit tests. This allows for ultra fast development cycles (running tests takes less than 1 sec).

To debug tests: Just hit "run" (F5, or whatever)... vscode should attach the mocha worker. Then run the test you want to debug.