Creating a Ruby on Rails Playground for PostgreSQL Document Querying

17 Mar 2021

Show you how to create a safe environment for you to setup and test queries in a Ruby on Rails with PostgreSQL database.

I will walk you through the setup and configuration of a Ruby on Rails application using a template, factories to rapidly create test data, and library to create realistic data.

You will learn how to create a basic Ruby on Rails application that you can easily create PostgreSQL document data and test out your queries (without blowing up production!)

This article will take you about six minutes to read.

Lewis Wickes Hine - Playground In Mill Village source and more information

Introduction

My go to database is PostgreSQL. I love that it is open source and focuses on storing data over other things a database can and/or expected to do.

The document columns are great in that the database can store both relational data and document data in a single row. You can have your web cake and eat it too, right?!

The problem is that PostgreSQL has three distinct document column types: hstore, json, and jsonb.

Most of the time, life is fine because the application stores and serves data in those fields as is, no querying necessary.

When there are times I have to do queries, well, those are painful as PostgreSQL has a different syntax/language for working with those document fields. I’m a little too comfortable with Ruby on Rails’ ActiveRecord interface and a little more with SQL.

When I have to query items in the document store, I never get it right the first x times (where x is greater than 10. 😫) and always run into this error message:

ActiveRecord::StatementInvalid: PG::InFailedSqlTransaction: ERROR:  current transaction is aborted, commands ignored until end of transaction block

from /home/vagrant/.rvm/gems/ruby-2.5.8/gems/activerecord-6.1.3/lib/active_record/connection_adapters/postgresql/database_statements.rb:19:in `exec'
Caused by PG::InFailedSqlTransaction: ERROR:  current transaction is aborted, commands ignored until end of transaction block

from /home/vagrant/.rvm/gems/ruby-2.5.8/gems/activerecord-6.1.3/lib/active_record/connection_adapters/postgresql/database_statements.rb:19:in `exec'

Which results in me restarting the whole work flow, from scratch!

Can I do better??

Yes, yes I can! I can build on my previous article and build a playground just to have a Ruby on Rails application to setup and practice/mess with/blow up different PostgreSQL document queries.

Production is safe again!

Rails Template

Let’s tweak the Ruby on Rails template file a bit from last time:

Use factory_bot_rails as it’s better setup for Ruby on Rails than factory_bot
Add in command to hook in factory_bot_rails to Rspec
Introduce faker gem to create

# usage: `rails new <app name> -m ~/rails_template.rb`

gem_group :development, :test do
  gem 'bullet'
  gem 'factory_bot_rails'
  gem 'faker'
  gem 'pry'
  gem 'rspec-rails'
  gem 'rubocop-rails'
  gem 'rubocop-rspec'
end

gem_group :development, :production do
  gem 'rails_performance'
end

file '.rubocop.yml', <<-RUBOCOP_CONFIG
Documentation:
  Enabled: false

Style/FrozenStringLiteralComment:
  Enabled: false
  EnforcedStyle: never
RUBOCOP_CONFIG

after_bundle do
  rails_command("generate rspec:install")
  run("rubocop -A")
  run("mkdir spec/support")
  run("echo 'RSpec.configure { |config| config.include FactoryBot::Syntax::Methods }' > spec/support/factory_bot.rb")
  run("echo 'require \'spec/support/factory_bot\' >> spec/rails_helper.rb")
end

The .railsrc file stays the same:

--api
--database=postgresql
--skip-bootsnap
--skip-spring
--skip-sprockets
--skip-test

Let’s get ready to rock and query! :-)

`rails new`

After running rails new postgres_test -m rails_template.rb, try it out!

vagrant@ubuntu-xenial:~/$ cd postgres_test
vagrant@ubuntu-xenial:~/postgres_test$ ls
app  bin  config  config.ru  db  Gemfile  Gemfile.lock  lib  log  public  Rakefile  README.md  spec  storage  tmp  vendor
vagrant@ubuntu-xenial:~/postgres_test$ rspec
No examples found.


Finished in 0.00048 seconds (files took 0.08595 seconds to load)
0 examples, 0 failures

Isn’t that SWEET?! Getting a new project created to running rspec with three commands!

Create Model: User

For a playground, I create a single model instance so the code is interacting with the real database and there’s a place to store the data for long term if needed.

vagrant@ubuntu-xenial:~/postgres_test$ rails generate model user
      invoke  active_record
      create    db/migrate/20210316231251_create_users.rb
      create    app/models/user.rb
      invoke    rspec
      create      spec/models/user_spec.rb

Add Relevant Columns

For my test model, I will add all of PostgreSQL’s document types: hstore, json, and jsonb as columns:

class CreateUsers < ActiveRecord::Migration[6.1]
  def change

    enable_extension "hstore" # thanks: https://nandovieira.com/using-postgresql-and-hstore-with-rails

    create_table :users do |t|
      t.hstore     :hstore
      t.json       :json
      t.jsonb      :jsonb
      t.timestamps
    end
  end
end

Run database changes

Make sure the changes take place by creating the database: rake db:create, creating the table and columns: rake db:migrate

vagrant@ubuntu-xenial:~/postgres_test$ rake db:create
Created database 'postgres_test_development'
Created database 'postgres_test_test'
vagrant@ubuntu-xenial:~/postgres_test$ rake db:migrate
== 20210316231251 CreateUsers: migrating ======================================
-- enable_extension("hstore")
   -> 0.0726s
-- create_table(:users)
   -> 0.0109s
== 20210316231251 CreateUsers: migrated (0.0837s) =============================

Playground Setup

There are three different ways to use your playground.

Rails console
Empty Test Data
Factory Test Data

`rails console`

At this point, you can just run rails console and work with the User model directly.

vagrant@ubuntu-xenial:~/postgres_test$ rails c
Loading development environment (Rails 6.1.3)
2.5.8 :001 > User.new
 => #<User id: nil, hstore: nil, json: nil, jsonb: nil, created_at: nil, updated_at: nil>
2.5.8 :002 >

This is great if you want to persist your data for longer term.

Debugging from an Empty Test

In the spec/model/user_spec.rb file, create an empty test and add a debugger into it.

RSpec.describe User, type: :model do
  it 'play ground' do
    binding.pry
  end
end

Run the test suite and get to the prompt:

vagrant@ubuntu-xenial:~/postgres_test$ rspec

From: /home/vagrant/postgres_test/spec/models/user_spec.rb:5 :

    1: require 'rails_helper'
    2:
    3: RSpec.describe User, type: :model do
    4:   it 'play ground' do
 => 5:     binding.pry
    6:   end
    7: end

[1] pry(#<RSpec::ExampleGroups::User>)> User.new
=> #<User:0x0000000006c162a8
 id: nil,
 hstore: nil,
 json: nil,
 jsonb: nil,
 created_at: nil,
 updated_at: nil>

This is a great way to try out different things in an ephemeral manner.

Test with Data

Using FactoryBot and the Faker gem, one create a factory to create the data you want to query against:

Factory Setup

In the spec/factories.rb file, create the User factory with:

FactoryBot.define do
  factory :user do
    transient do
      fake_name { Faker::Name.name }
    end

    sequence :hstore do |n|
      {
        id: n,
        name: fake_name
      }
    end

    sequence :json do |n|
      {
        id: n,
        name: fake_name
      }
    end

    sequence :jsonb do |n|
      {
        id: n,
        name: fake_name
      }
    end
  end
end

Now, you can use FactoryBot to create data:

[1] pry(#<RSpec::ExampleGroups::User>)> create(:user)
=> #<User:0x000000000a42a400
 id: 23,
 hstore: {"id"=>"1", "name"=>"Rev. Marybelle Lowe"},
 json: {"id"=>1, "name"=>"Rev. Marybelle Lowe"},
 jsonb: {"id"=>1, "name"=>"Rev. Marybelle Lowe"},
 created_at: Wed, 17 Mar 2021 11:38:13.943980000 UTC +00:00,
 updated_at: Wed, 17 Mar 2021 11:38:13.943980000 UTC +00:00>
[2] pry(#<RSpec::ExampleGroups::User>)> create(:user)
=> #<User:0x000000000c085988
 id: 24,
 hstore: {"id"=>"2", "name"=>"Shayne Bartell"},
 json: {"id"=>2, "name"=>"Shayne Bartell"},
 jsonb: {"id"=>2, "name"=>"Shayne Bartell"},
 created_at: Wed, 17 Mar 2021 11:38:16.708590000 UTC +00:00,
 updated_at: Wed, 17 Mar 2021 11:38:16.708590000 UTC +00:00>
[3] pry(#<RSpec::ExampleGroups::User>)> create(:user)
=> #<User:0x000000000bfb7a60
 id: 25,
 hstore: {"id"=>"3", "name"=>"Zulema Cole"},
 json: {"id"=>3, "name"=>"Zulema Cole"},
 jsonb: {"id"=>3, "name"=>"Zulema Cole"},
 created_at: Wed, 17 Mar 2021 11:38:19.979747000 UTC +00:00,
 updated_at: Wed, 17 Mar 2021 11:38:19.979747000 UTC +00:00>
[4] pry(#<RSpec::ExampleGroups::User>)> create(:user)
=> #<User:0x000000000bef8ed0
 id: 26,
 hstore: {"id"=>"4", "name"=>"Mrs. Kacey Borer"},
 json: {"id"=>4, "name"=>"Mrs. Kacey Borer"},
 jsonb: {"id"=>4, "name"=>"Mrs. Kacey Borer"},
 created_at: Wed, 17 Mar 2021 11:38:23.654990000 UTC +00:00,
 updated_at: Wed, 17 Mar 2021 11:38:23.654990000 UTC +00:00>

With factories, you can consistently create as much data you need in whatever format you need it in.

Conclusion

PostgreSQL’s document store is a great tool when working with data. Having the ability to have documents with relational data makes it easier to have one tool instead of two.

At the same time, document querying in PostgreSQL is so different from normal PostgreSQL, it feels like another storage mechanism with its own nuances that I have a hard time, especially working through ActiveRecord’s interface.

By having your own playground to setup your sample data to test queries instead of playing on production and running into “PG:CONNECTION ERROR”

Adding in Rspec + FactoryBot + Faker help improve feedback loop by creating safe and realistic environment to play with.