Cleaning database after tests in node.js

Question

How I can clean database after each it?

In rails I use https://github.com/bmabey/database_cleaner, but I did't find something similar for node.js

node.js (v0.10.26), PostgreSQL (9.3.3), mocha, restify and knex.

Araliaceous

Answer 1

The easy way I have found to clean the database between test is to

DROP SCHEMA public CASCADE;
CREATE SCHEMA public AUTHORIZATION my_test_user;

Once the public schema belongs to the test user, he is able to drop and re-create the schema when needed. Be aware that drop everything that lies in your database's public schema.

Time

Answer 2

You can wrap your test into transaction:

beforeEach(() => {
    return connection.query('START TRANSACTION');
});
afterEach(() => {
    return connection.query('ROLLBACK');
});

It will be much faster to run tests this way, compared to cleaning, or even worse dropping/recreating schema after each test.

If using it with pooled connections (new Pool()), it will be necessary to limit number of connections to 1: new Pool({min:1, max:1}) - transactions must span single connection.

This won't work however if code being tested uses transactions too. It's possible to do nested transactions with savepoints but can get bit more complicated.

Jacquelyn

Answer 3

There's a good package, check it out: https://github.com/emerleite/node-database-cleaner/

Isleana

Answer 4

I use the database-cleaner package to clean the database before each it directive and the db-migrate package to reset the database before running the tests.

Below the setup.

Add the following devDependencies to your package.json:

"devDependencies": {
  "chai": "^3.5.0",
  "database-cleaner": "^1.1.0",
  "mocha": "^3.0.2",
  "db-migrate": "^0.10.0-beta.15",
  "db-migrate-pg": "^0.1.10",
  ...
}

Here is the project structure:

.
├── config
│   ├── cleaner-config.js
│   ├── db.js
│   └── ...
├── db
│   ├── database.json
│   └── migrations
│       ├── ...
│       └── sqls
│           └── ...
├── node_modules
├── scripts
│   └── test
├── ...
├── src
│   ├── db.js
│   ├── models
│   └── ...
└── test
    ├── init.js
    └── src
        └── ...

The cleaner-config.js:

module.exports = {
  postgresql: {
    skipTables: ['migrations']
  }
};

The config/db.js used to get the database configuration:

// Prepare default DB config
const defaultOptions = function(environment = 'development') {
  const host = 'db';
  const port = 5432;
  const user = process.env.POSTGRES_USER;
  const password = process.env.POSTGRES_PASSWORD;

  var conf = {
    host: host,
    port: port,
    user: user,
    password: password,
    database: process.env.POSTGRES_DB,
    max: 10, // max number of clients in the pool
    idleTimeoutMillis: 30000 // Keeps idle connections open for a 30 seconds
  };

  // Change the used database in test environment
  if (environment === 'test') {
    conf.database = require('../db/database.json').test.database;
  }

  return conf;
};

// Return database configuration for all environments
module.exports = {
  development: defaultOptions(),
  test: defaultOptions('test')
};

The src/db.js file is responsible for establishing the database connection:

const PgPool = require('pg-pool');

// create a config to configure both pooling behavior and client options
const CONFIG = require('../config/db')[process.env.NODE_ENV || 'development'];

// Initializes connection pool
const pool = new PgPool(CONFIG);

module.exports = function(callback) {
  pool.on('error', function(error, client) {
    // if an error is encountered by a client while it sits idle in the pool
    // the pool itself will emit an error event with both the error and
    // the client which emitted the original error
    // this is a rare occurrence but can happen if there is a network partition
    // between your application and the database, the database restarts, etc.
    // and so you might want to handle it and at least log it out
    console.error('idle client error', error.message, error.stack);
  });

  // to run a query we can acquire a client from the pool,
  // run a query on the client, and then return the client to the pool
  pool.connect(function(error, client, done) {
    if (error)
      return console.error('error fetching client from pool', error);

    callback(client, done);
  });
};

The test database is hardcoded in the database.json (used also to ):

{
  "test": {
    "driver": "pg",
    "user": {
      "ENV": "POSTGRES_USER"
    },
    "password": {
      "ENV": "POSTGRES_PASSWORD"
    },
    "host": "db",
    "database": "<prefix>_test"
  },
  "development": {
    "driver": "pg",
    "user": {
      "ENV": "POSTGRES_USER"
    },
    "password": {
      "ENV": "POSTGRES_PASSWORD"
    },
    "host": "db",
    "database": {
      "ENV": "POSTGRES_DB"
    }
  },
  "sql-file": true
}

I load all my tests at the end of the test/init.js file:

DB = {
  client: null,
  closeConnection: null
}

beforeEach(function(done) {
  require('../src/db')(function(client, dbDone) {
    DB.client = client;
    DB.closeConnection = dbDone;

    var DatabaseCleaner = require('database-cleaner');
    var databaseCleaner = new DatabaseCleaner('postgresql');

    databaseCleaner.clean(client, done);
  });
});

// TODO: close connection only once - at the end of testing
afterEach(function(done) {
  DB.client = null;
  DB.closeConnection();
  done();
});

require('./src/<some library>.test');
...

Last but not least the scripts/test script used to run the tests:

#!/bin/bash

script_directory="$( cd "$( dirname "$0" )" && pwd )"
project_directory=$script_directory/..

# Stop execution and exit on any error
set -e

cd $project_directory

db_name='<prefix>_test'

# Drop the DB
# Use the development environment because of this issue: https://github.com/db-migrate/node-db-migrate/issues/393
./node_modules/.bin/db-migrate --env development --migrations-dir db/migrations --config db/database.json db:drop $db_name
# Create the DB
# Use the development environment because of this issue: https://github.com/db-migrate/node-db-migrate/issues/393
./node_modules/.bin/db-migrate --env development --migrations-dir db/migrations --config db/database.json db:create $db_name

./node_modules/.bin/db-migrate --env test --migrations-dir db/migrations --config db/database.json up

NODE_ENV=test ./node_modules/.bin/mocha test/init.js

Stafford