Configuring CI For A Postgres Database

- By Ambarish Chitnis on October 06, 2017

Shippable makes it easy to setup database migrations and test them continuously. In this blog, we will go over the steps to execute and test migrations on a PostgreSQL database using Shippable CI

Our sample uses Node.js and the node-pg-migrate module to setup migrations on a PostgreSQL database. Shippable integrates with PostgreSQL and allows you to automatically launch a PostgreSQL instance with a single line in the yml configuration. We will test migrations on this PostgreSQL instance.


Sample project

The code for this example is in GitHub: devops-recipes/ci-migrate-postgresdb 

You can fork the repository to try out this sample yourself or just follow instructions to configure your own use case. 


1. Create PostgreSQL schema

Our schema is simple and consists of a single table called article.

CREATE TABLE article (
article_id bigserial primary key,
article_name varchar(20) NOT NULL,
article_desc text NOT NULL,
date_added timestamp default NULL


2. Set up Database Migration

During any database migration, two operations are typically defined:

  •  up operation - This step performs the schema and data changes on your database. In our up migration, we will add a column article_author to the article table.
  • down operation - This steps rollsback the changes performed by the up operation. Our down migration will remove the article_author from the article table.

 To set up your migrations, follow the steps below: 

  • Add node-pg-migrate and pg to your package.json file.
  • Run npm install to install the pg-migrate module.
  • Create a migration file. 
    • Run the following command: ./node_modules/.bin/pg-migrate create migration_1
    • This will create a migration file in the migrations directory in your repository.
  • Author the up and down migrations in the migration file.  
    exports.up = (pgm) => {
    pgm.addColumns('article', { article_author: 'varchar (255) NOT NULL' });
    exports.down = (pgm) => {
    pgm.dropColumns('article', ['article_author'], {ifExists: true})
    For a complete set of commands, go here -
  • Author the credentials to connect to the database in the default.json file in the config directory. In our sample, we connect to the PostgreSQL intance that is automatically launched during your CI build in Shippable. 
      "db": {
        "user": "postgres",
        "password": "",
        "host": "",
        "port": 5432,
        "name": "test"
  • Commit the migrations file.


3. Enable your repository for CI

To get started with Shippable CI, you need to do three things:

  • Sign in with your GitHub or Bitbucket credentials.
  • Create the CI config file shippable.yml and commit to the root of your repository. The next section will go into the details of how to author shippable.yml file. If you're using a fork of our sample repository, you do not need to do this step since the repo already contains the config file. 
  • Enable your repository so that we can set up webhooks on your behalf. 


4. Author shippable.yml

Your CI workflow is configured via shippable.yml. For our example, the yml below creates a PostgreSQL database using Shippable's pre-installed service, runs the migrations, and then tests them.

language: node_js

  - 8.2.1

  - postgres

    # Install the npm modules needed for migration and unit testing
    - npm install
    # create a database to run our migration tests
    - psql -c 'create database test;' -U postgres
    # create a simple table in the database
    - psql -d test -f ./schema/simpleschema.sql -U postgres
    # run the migration
    - ./node_modules/.bin/pg-migrate up
    # test the migration
    - npm test


5. Unit test your migration

We have used mocha, chai and knex modules to run integration tests on our migrations. The tests are simple and check for the existence of the table, contraints on the table as well as for the existence of the column that was added during the migration. The tests can be found in the index.js and test.js files in our sample repository.


6. Trigger a new build manually

After authoring your shippable.yml file and enabling your project, the next step is to trigger a manual build to test your yml file and your very first migration. 

To learn about how to manually trigger a build, look at this doc. Here is how your build output might look

Screen Shot 2017-10-05 at 6.26.36 PM.png


7. Continuously test your migration

Once you have your very first migration tested, testing migrations continuously is a failrly straighforward process. Your CI Project will build on every commit and perform an up migraton. To test another migration, create a new migration using pg-migrate, author the up and down operations and commit the file.


Topics: continuous integration (CI), database, postgres