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).

The pg_tester gem is available from Microsoft’s repository on Github: https://github.com/Microsoft/pgtester

As an example, we’ll create a new user by taking the TDD-approach and using pg_tester. We will also use ruby 2.1.2 (available at https://rvm.io/).

Here’s what our final test will perform:

1. Create a new user with a username and password.
2. Verify the new user exists in the database.

Let’s setup our dev environment. The directory setup and layout looks like this:

pg_tester_example
├── Gemfile
└── spec
 ├── postgresql_manager.rb
 └── postgresql_manager_spec.rb

Our Gemfile is as follows:

### Gemfile
source "https://rubygems.org"
gem "pg_tester"
gem "pg"
gem "rspec"

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 user in 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.

### postgresql_manager.rb ###

class PostgresqlManager
 def initialize(host, database, user, port)
 end
end

Here is our spec:

### postgresql_manager_spec.rb ###

require "pg_tester"
require "postgresql_manager"
describe PostgresqlManager do
  ## create a local test DB
  psql = PgTester.new({
          database: "testbuddy",
          user: "buddy",
         })
  before(:each) do
    psql.setup
  end
  after(:each) do
    psql.teardown
  end
  subject { described_class.new(psql.host, psql.database, psql.user, psql.port) }
  describe "create_new_user" do
    context "testing create user method" do
      it "should create a new user" do
        subject.create_new_user("DummyUsername", "complexPassword")
        result = psql.exec "SELECT usename FROM pg_catalog.pg_user"
        expect(result.values).to include(["DummyUsername"])
      end
    end
  end
end

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 can run the above by doing bundle exec rspec.

$ bundle exec rspec
F
Failures:
1) PostgresqlManager create_new_user testing create user method should create a new user
 Failure/Error: subject.create_new_user('DummyUsername', 'complexPassword')
NoMethodError:
 undefined method 'create_new_user' for #<PostgresqlManager:0x007fcf3313eae8>
 # ./spec/postgresql_manager_spec.rb:20:in 'block (4 levels) in <top (required)>'
Finished in 4.72 seconds (files took 0.50818 seconds to load)
1 example, 1 failure
Failed examples:
rspec ./spec/postgresql_manager_spec.rb:19 # PostgresqlManager create_new_user testing create user method should create a new user

This will give us a failing test, citing that the create_new_user method does not exist. Let’s add a create_new_user method to our class.

### postgresql_manager.rb ###

require "pg"
class PostgresqlManager
  def initialize(host, database, user, port)
  @connection = PG::Connection.open(:host => host, :dbname =>  database, :user => user, :port => port)
  end
  def create_new_user(username, password)
    results = @connection.exec "CREATE ROLE #{username} WITH password '#{password}' LOGIN"
    @connection.close()
  end
end

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.

Let’s run our tests now:

$ bundle exec rspec
.
Finished in 4.64 seconds (files took 0.49654 seconds to load)
1 example, 0 failures

Hooray! Our tests pass!

We’re really happy to release pg_tester to the Open Source community on the Microsoft Github. More examples and installation instructions can be found here:

https://github.com/Microsoft/pgtester

Neeran Gul is an engineer on the Production Engineering Team in the Yammer London Office and author of the pg_tester gem