2

I have 2 SQL queries I want to test, an INSERT and a SELECT. I want to run a test that calls the insert, and then calls the select, which should return the data from the insert.

I found a stackoverflow post about mocking postgres, but I want to actually run a test instance of postgres instead of mocking the actual queries.

Zach G
  • 597
  • 2
  • 10
  • 23
  • If you're looking to test a query actually works, why not just test it against an actual database? Either run one locally, using docker-compose or an instance in the cloud. If you're looking to really know if it works, then test it properly. If you're unit testing, mock it and ensure it's working as expected. You'll need to test it against a real instance at some stage though. Alternatively, in-memory instances are usually a thing, I know one exists for MongoDB (not sure about PSQL) – Isolated May 11 '20 at 02:27

1 Answers1

5

An integration test with postgres could look like this:

const { Pool } = require('pg');

describe('testing postgres', () => {

    let pgPool;

    beforeAll(() => {
        pgPool = new Pool({
            connectionString: process.env.TEST_DATABASE_URL
        });
    });

    afterAll(async () => {
        await pgPool.end();
    });

    it('should test', async () => {
        const client = await pgPool.connect();
        try {
            await client.query('BEGIN');

            const { rows } = await client.query('SELECT 1 AS "result"');
            expect(rows[0]["result"]).toBe(1);

            await client.query('ROLLBACK');
        } catch(err) {
          throw err;
        } finally {
            client.release();
        }

    })

});

You would call this test with:

TEST_DATABASE_URL=postgresql://sth:sth@someserver/testdb jest

Testing an insert and select can be done by using RETURNING *.

     const { rows } = await client.query('INSERT INTO ... RETURNING *');

Using BEGIN and ROLLBACK is necessary - so you do not alter the database and have side effects for the next test to come.

madflow
  • 7,718
  • 3
  • 39
  • 54
  • I'm using a single Client in my API instead of a Pool. I also never explicitly call client.end() but instead have a listener client.on('end', ( )=>{ }) Is that bad practice? – Zach G May 12 '20 at 14:50
  • Hard to tell from your comment. Maybe create a new Question? I would argue that using connection pooling in context of a (web?) api is always beneficial. https://node-postgres.com/features/pooling. – madflow May 12 '20 at 15:50