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:
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!
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
The .railsrc file stays the same:
Let’s get ready to rock and query! :-)
After running rails new postgres_test -m rails_template.rb, try it out!
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.
Add Relevant Columns
For my test model, I will add all of PostgreSQL’s document types:
hstore, json, and jsonb as columns:
Run database changes
Make sure the changes take place by creating the database: rake
db:create, creating the table and columns: rake db:migrate
There are three different ways to use your playground.
Empty Test Data
Factory Test Data
At this point, you can just run rails console and work with the User
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.
Run the test suite and get to the prompt:
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:
In the spec/factories.rb file, create the User factory with:
Now, you can use FactoryBot to create data:
With factories, you can consistently create as much data you need in
whatever format you need it in.
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
By having your own playground to setup your sample data to test
queries instead of playing on production and running into
Adding in Rspec + FactoryBot + Faker help improve feedback loop by
creating safe and realistic environment to play with.