CODEWITHSUNDEEP
node.jspostgresqltestingjestjs
Dycey
Dycey

Reputation: 4705

Testing Fixtures for Postgres and NodeJS

I am trying to write some basic test fixtures for Postgres in a React app.

I have some setup and teardown SQL files:

-- ### Setup File ###

DROP TABLE IF EXISTS "requests";
-- This script only contains the table creation statements and does not fully represent the table in the database. It's still missing: indices, triggers. Do not use it as a backup.

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS requests_id_seq;

-- Table Definition
CREATE TABLE "requests" (
    "id" int4 NOT NULL DEFAULT nextval('requests_id_seq'::regclass),
    "status" varchar(10),
    "requestor" varchar(255),
    "template_id" varchar(255),
    "version" varchar(10),
    "detail" text,
    PRIMARY KEY ("id")
);

-- ### Teardown File ###

DROP TABLE IF EXISTS "requests" CASCADE;
DROP SEQUENCE IF EXISTS "requests_id_seq" CASCADE;

which are called from within the tests using a dbtools.js helper file:

/*eslint no-console: ["error", { allow: ["warn", "error"] }]*/
const Pool = require('../../db');

require('dotenv').config(); // Load .env file
//eslint-disable-next-line no-undef
const {PGUSER, PGPASSWORD, PGDATABASE, PGHOST, PGPORT} = process.env;
const util = require('util');
const exec = util.promisify(require('child_process').exec);
const create_sql = 'bin/sql/tables_setup.sql';
const droptables_sql = 'bin/sql/tables_down.sql';

function execute_sql_file(fileName){ //eslint-disable-line no-unused-vars
  exec(
    `PGPASSWORD="${PGPASSWORD}" psql -U ${PGUSER} -h ${PGHOST} -p ${PGPORT} -d ${PGDATABASE} -f ${fileName}; exit 0`,
    function(err){
      if (err !== null) {
        console.error('Error populating db: ' + err);
      }
    }
  );
}

function prepare_db(){
  execute_sql_file(create_sql);
}

function clean_db(){
  execute_sql_file(droptables_sql);
}

module.exports = { prepare_db, clean_db };

that is used in the app.test.js file:


  ...

  beforeEach(async () => {
    await prepare_db();
  });

  afterEach(async () => {
    await clean_db();
  });

  ...

When executed, and the route is hit from Postman, everything works the first time around, and fails the second time.

It seems to be dropping the table, no problem, but not re-setting it. When I run the Setup file manually (psql webui -U postgres < /Users/dd/webui/backend/bin/sql/tables_setup.sql), it works fine. Is there something obvious that am I doing wrong?

(Also - please no puns this time, I'm not looking to use Mocks)

Upvotes: 1

Views: 787

Answers (1)

Tudor Constantin
Tudor Constantin

Reputation: 26861

Edit

After re-reading your question, I realized my below observation might not actually be what you're after, because you might want to run the setup/tear down of the DB in the actual beforeEach()/afterEach().

What I notice is that your exec() function starts a promise, but it doesn't actually await for it, so try to:

async function execute_sql_file(fileName){ //eslint-disable-line no-unused-vars
  await exec(
    `PGPASSWORD="${PGPASSWORD}" psql -U ${PGUSER} -h ${PGHOST} -p ${PGPORT} -d ${PGDATABASE} -f ${fileName}; exit 0`,
    function(err){
      if (err !== null) {
        console.error('Error populating db: ' + err);
      }
    }
  );
}

Disregard the below observation.

try setting up/tearing down the DB in the before()/after() methods, not in beforeEach()/afterEach():

...

  before(async () => {
    await prepare_db();
  });

  after(async () => {
    await clean_db();
  });

  ...

Upvotes: 2

Related Questions