Ruby + PostgreSQL + Liquibase + Rake

This small Ruby gem helps you integrate PostgreSQL with your Ruby web app, through Liquibase. It also adds a simple connection pool and query processor, to make SQL manipulation simpler.

First of all, on top of Ruby and Bundler you need to have PostgreSQL, Java 8+, and Maven 3.2+ installed. In Ubuntu 16+ this should be enough:

sudo apt-get install -y postgresql-10 postgresql-client-10
sudo apt-get install -y default-jre maven

Then, add this to your Gemfile:

gem 'pgtk'

Then, add this to your Rakefile:

require 'pgtk/pgsql_task'
Pgtk::PgsqlTask.new :pgsql do |t|
  t.dir = 'target/pgsql' # Temp directory with PostgreSQL files
  t.fresh_start = true # To delete the directory on every start
  t.user = 'test'
  t.password = 'test'
  t.dbname = 'test'
  t.yaml = 'target/pgsql-config.yml' # YAML file to be created with connection details
end

And this too (org.postgresql:postgresql and org.liquibase:liquibase-maven-plugin are used inside):

require 'pgtk/liquibase_task'
Pgtk::LiquibaseTask.new liquibase: :pgsql do |t|
  t.master = 'liquibase/master.xml' # Master XML file path
  t.yaml = ['target/pgsql-config.yml', 'config.yml'] # YAML files with connection details
  t.quiet = false # TRUE by default
  t.postgresql_version = '42.7.0' # overwriting default version
  t.liquibase_version = '3.2.2' # overwriting default version
end

The config.yml file should be in this format:

pgsql:
  url: jdbc:postgresql://<host>:<port>/<dbname>?user=<user>
  host: ...
  port: ...
  dbname: ...
  user: ...
  password: ...

You should create that liquibase/master.xml file in your repository, and a number of other XML files with Liquibase changes. This example will help you understand them.

Now, you can do this:

bundle exec rake pgsql liquibase

A temporary PostgreSQL server will be started and the entire set of Liquibase SQL changes will be applied. You will be able to connect to it from your application, using the file target/config.yml.

From inside your app you may find this class useful:

require 'pgtk/pool'
pgsql = Pgtk::Pool.new(Pgtk::Wire::Yaml.new('config.yml'))
pgsql.start(5) # Start it with five simultaneous connections

You can also let it pick the connection parameters from the environment variable DATABASE_URL, formatted like postgres://user:password@host:5432/dbname:

pgsql = Pgtk::Pool.new(Pgtk::Wire::Env.new)

Now you can fetch some data from the DB:

name = pgsql.exec('SELECT name FROM user WHERE id = $1', [id])[0]['name']

You may also use it if you need to run a transaction:

pgsql.transaction do |t|
  t.exec('DELETE FROM user WHERE id = $1', [id])
  t.exec('INSERT INTO user (name, phone) VALUES ($1, $2)', [name, phone])
end

To make your PostgreSQL database visible in your unit test, I would recommend you create a method test_pgsql in your test__helper.rb file (which is required in all unit tests) and implement it like this:

require 'yaml'
require 'minitest/autorun'
require 'pgtk/pool'
module Minitest
  class Test
    def test_pgsql
      config = YAML.load_file()
      @@test_pgsql ||= Pgtk::Pool.new(
        Pgtk::Wire::Yaml.new('target/pgsql-config.yml')
      ).start
    end
  end
end

Should work.

Well, it works in netbout.com, wts.zold.io, mailanes.com, and 0rsk.com. They are all open source, you can see how they use pgtk.

Read these guidelines. Make sure your build is green before you contribute your pull request. You will need to have Ruby 2.3+ and Bundler installed. Then:

bundle update
bundle exec rake

If it's clean and you don't see any error messages, submit your pull request.

To run a single test, do this:

bundle exec ruby test/test_pool.rb -n test_basic