Thursday, January 23, 2014

Resetting your postgres database before each spec

If you run integration tests, you've probably come across this problem:
You need to find a way to clean your database before each spec runs.

There are two common approaches to solve this problem:
  1. Run every spec inside a transaction
  2. Truncate all tables before (or after) every spec
Running your specs inside transactions is the faster approach, and since integration tests can become quite large and slow, it's the approach preferred by many developers. However, it has one caveat: you can't test scenarios where transactions are used (you can't open a transaction inside a transaction). This leaves some developers with no choice but to use the slower but more flexible approach of truncating tables before each spec.

However, if you happen to use PostgreSQL, there is another option:
Dropping the database and creating it again from a template.

While this sounds slow and expensive, because of the way PostgreSQL handles templates, it's actually pretty fast.

On my test suite, this is what I usually do:

Before all specs

  1. Make sure an empty database called 'test_template' exists.
  2. Dump my development database's schema and populate the test_template database with it. Now 'test_template' is effectively an empty copy of your development database.
This can be accomplished with 3 simple commands (I run them using grunt-exec, but any script that can run shell commands should work):
# drop database
psql -c "DROP DATABASE IF EXISTS test_template" postgres

# create database
psql -c "CREATE DATABASE test_template WITH OWNER test_user" postgres

# dump dev db schema and pipe to test_template
# make sure your test user is the one who creates the new tables
# (that way he has implicit permissions to access them)
pg_dump -s --clean dev_db_name | PGPASSWORD=test_password psql -h localhost -U test_user test_template
**Note you can ignore the before all steps if you keep the test_template schema in sync with your dev db schema

Before each spec

  1. Drop the 'test' database (if it exists)
  2. Create the 'test' database from the 'test_template'
I run this on a beforeEach clause in Jasmine, but as long as it runs before each spec you should be fine:
# drop test database

# create test database from test_template
CREATE DATABASE test TEMPLATE test_template;

Now you can run your specs in a clean environment without compromising on speed.

No comments: