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

❤ It works both in node or in browser.

⭐ this repo if you like this package, it helps to motivate me :)

👉 See it in action with pg-mem playground

📐 Usage

Using NodeJS

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

Using Deno

Pretty straightforward :)

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

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

Only use the SQL syntax parser

❤ Head to the pgsql-ast-parser repo

⚠ Disclaimer

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

Moreover, even if I wrote hundreds of tests, keep in mind that this implementation is a best effort to replicate PG. Keep an eye on your query results if you perform complex queries. Please file issues if some results seem incoherent with what should be returned.

Finally, I invite you to read the below section to have an idea of you can or cannot do.

🔍 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'}

Custom functions

You can declare custom functions like this:

db.public.registerFunction({
            name: 'say_hello',
            args: [DataType.text],
            returns: DataType.text,
            implementation: x => 'hello ' + x,
        })

And then use them like in SQL select say_hello('world').

Custom functions support overloading and variadic arguments.

⚠ However, the value you return is not type checked. It MUST correspond to the datatype you provided as ‘returns’ (wont fail if not, but could lead to weird bugs).

Extensions

No native extension is implemented (pull requests are welcome), but you can define kind-of extensions like this:

db.registerExtension('my-ext', schema => {
    // install your ext in 'schema'
    // ex:  schema.registerFunction(...)
});

Statements like create extension "my-ext" will then be supported.

📃 Libraries adapters

pg-mem provides handy shortcuts to create instances of popuplar libraries that will be bound to pg-mem instead of a real postgres db.

  • pg-native
  • node-postgres (pg)
  • pg-promise (pgp)
  • slonik
  • typeorm

See the wiki for more details

Inspection

💥 Subscribe to events

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', () => {});
// called when a CREATE EXTENSION schema is encountered.
db.on('create-extension', ext => {});

Experimental events

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
// (which leads to a O(n*m) lookup with the current implementation)
db.on('catastrophic-join-optimization', () => {});

🙋‍♂️ FAQ

Detailed answers in the wiki

📃 Supported features

It supports:

  • 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
  • Transactions (only one of multiple concurrent transactions can be commited, though)

It does not (yet) support (this is kind-of a todo list):

  • Gin Indices
  • Cartesian Joins
  • Most of the pg functions are not implemented - ask for them, they’re easy to implement !
  • Some aggregations are to be implemented (avg, count, …) - easy job, but not yet done.
  • Stored procedures
  • Lots of small and not so small things (collate, timezones, tsqueries, custom types …)
  • Introspection schema (it is faked - i.e. some table exist, but are empty - so Typeorm can inspect an introspect an empty db & create tables)
  • Concurrent transaction commit
  • Collation (see #collation tag in code if you want to implement it)

… PRs are open :)

🐜 Development

Pull requests are welcome :)

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

… 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.

Alternatively, you could just run npm run test wihtout installing anything, but this is a bit long.