Postgres in memory for CI/CD purposes in node runtime

Question

I'm looking for a solution to be able to spin up postgres-like solution that runs just in memory to be able to run full e2e tests in a pipeline. I know there are ways to spin up a temp database and connect to that, but I like the idea of what https://github.com/nodkz/mongodb-memory-server is to mongodb but then for postgres.

I can't imagine that a popular and often used db doesn't have an lightweight in memory db alternative

I've tried using pg-mem in the below repo, but it doesn't work as expected https://github.com/rkristelijn/pg-mem-server. See the README for more info.

Answer 1

I don't think there is an in-memory option for PostgreSQL. (It looks like pg-mem doesn't fully support all the PostgreSQL syntax. A full re-implementation of the PostgreSQL language and engine in memory is indeed an ambitious task.)

There are a few options, for example:

Ramdisk

You could initialise your cluster on a ramdisk partition, using either tmpfs or ramfs on Linux (the former can use "swap" memory).

This is probably the cleanest option, but may come with some overhead in terms of setup, even in memory.

Test-user schema

Another option is to keep the same database, and drop and re-create a schema named after the user you're connecting with (assuming the default search path). This assumes that you're not specifying the schema in your queries.

If your test user is called testuser, you can drop and create a schema also called testuser just before running each test:

DROP SCHEMA IF EXISTS testuser CASCADE;
CREATE SCHEMA testuser;

Then, if you always connect as testuser for those tests (be careful not to connect as anything else so that you don't put anything into your public schema), those queries (without schema name) would use the testuser schema (and be wiped next time you drop that schema as above):

CREATE TABLE mytable (id SERIAL PRIMARY KEY, column_a TEXT);
-- Effectively testuser.mytable, instead of public.mytable by default
INSERT INTO mytable(column_a) VALUES ('A');
SELECT id, column_a FROM mytable;
-- ...

This should work reasonably well as long as you're not specifying schemas in your application.

Depending on what your application does, you could actually make use of schemas (maybe using the public schema too or another search path) and have some data readily-available that wouldn't be wiped when you drop your testuser schema. That's very application-dependent.