Prisma•4w ago

Find the way to write unit-test by postgres in memory db

Hi, I'm starting to write unit-test but I don't want to use docker to mock DB. Any way to use in memory postgres db?

19 Replies

You decided to hold for human wisdom. We'll chime in soon! Meanwhile, #ask-ai is there if you need a quick second opinion.

nhan.nguyenvanOP•4w ago

#ask-ai

Dan😏•4w ago

yeah, postgresql doesnt have a true in-memory mode like sqlite, but there are a few ways you can achieve fast, disposable databases for unit testing without using docker

nhan.nguyenvanOP•4w ago

Can you give me a guide?

Dan😏•4w ago

first of all, i'd like say to you use pg_tmp - fast, temporary postgres instance you know, pg_tmp is a tool that spins up a temporary postgresql instance in the background and cleans it up after tests it's super lightweight and doesnt require docker you can install via it using this cargo install pg_tmp # if you have Rust installed and then, start your test with this pg_tmp -c 'your_test_command_here

nhan.nguyenvanOP•4w ago

I saw a libray is pg-mem, but i am not sure it work in memory, let me share with you I am using nodejs And want to write test with api level

Dan😏•4w ago

yeah, since you are using node.js and testing at the api level, pg-mem is a solid option for ocking a postgresql database entirely in memory it's super fast and doesnt require a real database connection npm install pg-mem - install then, set up an in-memory database

import { newDb } from 'pg-mem';
import { Pool } from 'pg';

// Create an in-memory database
const db = newDb();
db.public.none(`CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);`);

// Mock the pg Pool to use this in-memory DB
db.adapters.createPg();
const pool = new Pool({ connectionString: db.adapters.createConnection() });

// Example query
async function testQuery() {
    await pool.query(`INSERT INTO users (name) VALUES ('Alice')`);
    const res = await pool.query(`SELECT * FROM users`);
    console.log(res.rows); // Should log [{ id: 1, name: 'Alice' }]
}

testQuery();

import { newDb } from 'pg-mem';
import { Pool } from 'pg';

// Create an in-memory database
const db = newDb();
db.public.none(`CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);`);

// Mock the pg Pool to use this in-memory DB
db.adapters.createPg();
const pool = new Pool({ connectionString: db.adapters.createConnection() });

// Example query
async function testQuery() {
    await pool.query(`INSERT INTO users (name) VALUES ('Alice')`);
    const res = await pool.query(`SELECT * FROM users`);
    console.log(res.rows); // Should log [{ id: 1, name: 'Alice' }]
}

testQuery();

and next, you have to use pg-mem in jest tests if you are testing api routes using jest + supertest, you can do something like this

import request from 'supertest';
import { newDb } from 'pg-mem';
import { app } from '../app'; // Your Express app

let db: any;
beforeAll(async () => {
    db = newDb();
    db.public.none(`CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)`);
});

test('GET /users should return an empty array', async () => {
    const res = await request(app).get('/users');
    expect(res.status).toBe(200);
    expect(res.body).toEqual([]);
});

import request from 'supertest';
import { newDb } from 'pg-mem';
import { app } from '../app'; // Your Express app

let db: any;
beforeAll(async () => {
    db = newDb();
    db.public.none(`CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)`);
});

test('GET /users should return an empty array', async () => {
    const res = await request(app).get('/users');
    expect(res.status).toBe(200);
    expect(res.body).toEqual([]);
});

in my exp, pg-mem is great for unit tests but doesnt fully replicate postgresql behavior, so it might not work well for complex queries so if you need real postgres behavior, pg-mem or using a RAM-disk-backed postgresql instance is better

RaphaelEtim•4w ago

Either pg_tmp and pg-mem should work well for our usecase just like Dan has suggested.

nhan.nguyenvanOP•4w ago

Hello Are you here Ok What is the best way to deal with my case? Pg-mem But you said that pg cant use in memory Why pg-mem work As it base on memory or not?

Dan😏•4w ago

yeah, i get the confusion you know, postgresql itself doesn’t have a true "in-memory" mode like sqlite, but pg-mem works differently, it simulates a postgresql database entirely in memory, without actually running a real postgres instance so, pg-mem isnt postgresql itself, but a library that mimics its behavior using javascript it’s super fast and great for unit tests where you dont need full postgresql features like advanced indexing, triggers, or extensions

nhan.nguyenvanOP•4w ago

Will pg-mem outdate in the future as it use js to mimic behavior?

Dan😏•4w ago

so if your queries are simple like basic crud, joins etc, pg-mem is a solid choice but if you need full postgresql behavior like complex transactions, foreign key constraints, or postgis, then you’d need a real postgres instance like running one on a ram disk or using pg_tmp for a temporary database so the best approach depends on what you’re testing - simple unit tests → pg-mem is great - more realistic database behavior → pg_tmp or a real sostgres instance good question~~ pg-mem is great for now, but yeah, there's always a risk that it could become outdated or inaccurate over time since it mimics postgresql using javascript, it's not guaranteed to stay 100% in sync with newer postgresql features, optimizaions, or quirks that said, pg-mem is still useful for lightweight unit tests where you dont need full postgresql behavior anyway, if you wanna long-term reliability, you might wanna look at altervatives like testcontainers for node.js, pg_tmp, using a ram-backed postgresql instance

nhan.nguyenvanOP•4w ago

Dan😏•4w ago

so, while pg-mem work today, it's smart to keep an eye on whether it meets your needs as your app grows

nhan.nguyenvanOP•4w ago

When I use pg-tmp i dont’t need to use docker to create temp db right?

Dan😏•4w ago

yeah, that's right pg_tmp lets you create a temporary postgresql database without needing docker it spins up a real postgresql instance in the background, runs your tests, and automatically cleans

nhan.nguyenvanOP•3w ago

Oh, to implement them in CICD process, pg-tmp or docker is better and quicker Like implement them in github flow, jenkin. I mean faster to build or quick check current test to allow merge code How can i caculate test coverage as well mate Hey mate, I can't find pg_tmp library .. Hello, anyone is here

RaphaelEtim•3w ago

Hi @nhan.nguyenvan https://github.com/eradman/ephemeralpg

GitHub

GitHub - eradman/ephemeralpg: Quickly spin up a temporary PostgreSQ...

Quickly spin up a temporary PostgreSQL test databases - eradman/ephemeralpg

nhan.nguyenvanOP•3w ago

thanks mate, The number of Star of this library seems not much Do we have another library with higher star?

Gaming

Programming

Find the way to write unit-test by postgres in memory db

Did you find this page helpful?