NodeJS + PostgreSQL integration testing

Question

I would like to include Postgres interaction into my integration tests, i.e. not mock the database part, and I need help on figuring out the best way to do the test cleanup.

My setup is NodeJS, Postgres, Sequelize, Karma+Mocha. Currently, before running the tests a new database is created and migrated, after each test I run a raw query that truncates all the tables, and after all tests cases are finished the test database is dropped. As you probably guessed it, the execution time for running tests like this is pretty slow.

I was wondering if there is a way to speed the process up. Is there an in-memory psql database that I could use for my test cases (I've search for one for a while but couldn't find it), or something like that.

To be more precise, I'm looking for a way to clear the database after a test wrote something to it, in a way that does not require truncating all the tables after every test case.

It is taking too long for them to run (at least on my computer), and I only have a few of them for now. I'm worried that it will take forever for them to run once I've covered a decent amount of use cases and was wondering if there might be a better way. — Vedran, Jul 01 '19 at 19:50
Truncating even many tables shouldn't take more then a few milliseconds. At most "seconds" - maybe the truncate statement was waiting for locks — , Jul 01 '19 at 20:20

score 3 · Accepted Answer · answered Jul 02 '19 at 11:16

Incorporated https://stackoverflow.com/a/12082038/2018521 into my cleanup:

afterEach(async () => {
  await db.sequelize.query(`
    DO
    $func$
    BEGIN
      EXECUTE
      (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' RESTART IDENTITY CASCADE'
        FROM   pg_class
        WHERE  relkind = 'r'  -- only tables
        AND    relnamespace = 'public'::regnamespace
      );
    END
    $func$;
  `);
});

Truncate now runs almost instantly.

NodeJS + PostgreSQL integration testing

1 Answers1