/test-postgresql-sqitch

Test PostgreSQL features to make sure they do what's expected - the http://sqitch.org/ way!

Primary LanguagePLpgSQL

test-postgresql-sqitch Build Status

Test PostgreSQL features to make sure they do what's expected

Description

Over time there have been PostgreSQL-specific features I've wanted to check. The first one was to make sure citext was doing what I expect it to do.

N.B., I am using Sqitch for testing.

Tests

The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower() when comparing values. Otherwise, it behaves almost exactly like text.

The tests make sure it does this, and that an index is used appropriately.

Makes sure that constraints are doing what I expect them to do. This is not complete, but I will expand on the tests as and when I need them.

Using Sqitch

This repository is also an excuse for me to use Sqitch. Sqitch is a database change management application. The verify feature makes it particularly useful when combined with Github and Travis.

N.B., It is not best practice to use Sqitch in this way because the verify command should contain tests without any regard for data.

Commands

Initialise a new project.

sqitch init testing --uri https://github.com/kwakwaversal/test-postgresql-sqitch --engine pg

Tell Sqitch who we are (used for the plan's audit trail). The --user flag means that this particular config will be written to the executing user's ~/.sqitch/sqitch.conf file.

sqitch config --user user.name 'Paul Williams'
sqitch config --user user.email 'kwakwaversal@...'

Add a new database change.

sqitch add extensions -n 'Add Pg extensions (CITEXT)'
sqitch add citext --requires extensions -n 'Add CITEXT test'

Deploy, Verify, Revert

These commands assume you're running them from within the vagrant VM which is provisioned from the Vagrantfile.

sqitch deploy db:pg://super_sqitch_user:password@localhost/sqitch
sqitch verify db:pg://super_sqitch_user:password@localhost/sqitch
sqitch revert db:pg://super_sqitch_user:password@localhost/sqitch

References