Testing PostgreSQL scripts with RSpec and pg_tester

Neeran Gul

At Yammer, we run more than a dozen different PostgreSQL clusters supporting our Production applications. As with most databases, we run regular maintenance tasks such as rotating user credentials, adding new users and databases, dealing with index bloat reduction, and so on. To make life easier, we have created additional tools to streamline our maintenance tasks without human intervention. The purpose of this article is to show how we use our pg_tester gem with RSpec to provide a TDD-approach for writing maintainable scripts and automation involving PostgreSQL.

The Ruby gem pg_tester provides users with the functionality to write tests for their scripts involving PostgreSQL. It creates a temporary database in a directory of the user’s choice and allows it to be accessible via the pg gem API. It was designed to facilitate automated tests against a PostgreSQL instance reliably and cleanly, and without needing to do any hardcore database configuration and setup! You only need to have installed PostgreSQL locally first via whichever method you prefer (dpkg, rpm, tar.gz, exe, source etc).

After the Gemfile is in place, running a bundle install will install the necessary dependencies.

With those quick two steps, setup is complete! Now we can look at the code:

Here, we are writing a spec for our PostgresqlManager class. We want to create DummyUsername userin our temporary database with the create_new_user method. Let’s put in an empty class with some basic parameters, so we don’t get errors.

In the above code sample, we create a PgTester instance, then create our subject PostgresqlManager class. In this example, we want to run psql.setup before each test in the context block. That will spin up our temporary database and tear it down afterwards by running psql.teardown. We run our create_new_user method then verify the user is created in PostgreSQL.

We open a connection to our database via the pg gem in the initialize method whilst passing in arguments. This class is generic, so we can pass any database connection details. We are going to pass in our temporary database connection details for purposes of our test. The create_new_user method will issue a CREATE ROLE statement to create a new user with supplied credentials and LOGIN permissions.We then close our connection to the database.