PostgreSQL Hstore Basics in Rails 5.1

Create a new Rails 5.1 project with postgreSQL as the database.

rails new hdemo --database=postgresql

The generated project uses pg gem version 0.18.

gem 'pg'

The datatabase.yml for the postgresql database:

# PostgreSQL. Versions 9.1 and up are supported.
# Install the pg driver:
#   gem install pg
# On OS X with Homebrew:
#   gem install pg -- --with-pg-config=/usr/local/bin/pg_config
# On OS X with MacPorts:
#   gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config
# On Windows:
#   gem install pg
#       Choose the win32 build.
#       Install PostgreSQL and put its /bin directory on your path.
# Configure Using Gemfile
# gem 'pg'
default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

  <<: *default
  database: hdemo_development

  # The specified database role being used to connect to postgres.
  # To create additional roles in postgres see `$ createuser --help`.
  # When left blank, postgres will use the default role. This is
  # the same name as the operating system user that initialized the database.
  #username: hdemo

  # The password associated with the postgres role (username).

  # Connect on a TCP socket. Omitted by default since the client uses a
  # domain socket that doesn't need configuration. Windows does not have
  # domain sockets, so uncomment these lines.
  #host: localhost

  # The TCP port the server listens on. Defaults to 5432.
  # If your server runs on a different port number, change accordingly.
  #port: 5432

  # Schema search path. The server defaults to $user,public
  #schema_search_path: myapp,sharedapp,public

  # Minimum log levels, in increasing order:
  #   debug5, debug4, debug3, debug2, debug1,
  #   log, notice, warning, error, fatal, and panic
  # Defaults to warning.
  #min_messages: notice

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
  <<: *default
  database: hdemo_test

# As with config/secrets.yml, you never want to store sensitive information,
# like your database password, in your source code. If your source code is
# ever seen by anyone, they now have access to your database.
# Instead, provide the password as a unix environment variable when you boot
# the app. Read
# for a full rundown on how to provide these environment variables in a
# production deployment.
# On Heroku and other platform providers, you may have a full connection URL
# available as an environment variable. For example:
#   DATABASE_URL="postgres://myuser:mypass@localhost/somedatabase"
# You can use this database configuration with:
#   production:
#     url: <%= ENV['DATABASE_URL'] %>
  <<: *default
  database: hdemo_production
  username: hdemo
  password: <%= ENV['HDEMO_DATABASE_PASSWORD'] %>

Create a profile model.

rails g model profile

Modify the migration file to enable hstore extension in Postgresql and define the settings as the hstore type column in profiles table. The migration file has an hstore column called settings and defines a Gin index.

class CreateProfiles < ActiveRecord::Migration[5.1]
  def change
    enable_extension 'hstore' unless extension_enabled?('hstore')
    create_table :profiles do |t|
      t.hstore 'settings'

    add_index :profiles, :settings, using: :gin

The app/models/profile.rb does not have any declaration for hstore or settings column.

class Profile < ApplicationRecord

We can now experiment in the rails console:

Profile.create(settings: { "color" => "blue", "resolution" => "800x600" })

profile = Profile.first
profile.settings # => {"color"=>"blue", "resolution"=>"800x600"}

We can change the value of settings by providing a new hash with values for color and resolution keys.

profile.settings = {"color" => "yellow", "resolution" => "1280x1024"}!

We can query the key-value pair in the settings like this:

Profile.where("settings->'color' = ?", "yellow")
# => #<ActiveRecord::Relation [#<Profile id: 1, settings: {"color"=>"yellow", "resolution"=>"1280x1024"}>]>

Screencast Transcript

Let's create a new Rails 5.1 project with postgresql as the database. We need the pg gem to use postgresql database. Rails generator has generated the database.yml with the postgresql as the database adapter. Let's generate a profile model. In the migration file, we need to enable hstore extension if it is not already enabled. We can move it outside the create table method. We can define a settings column of hstore type. In the profile model, we don't need to declare anything. Let's create and migrate the database. In the rails console, we can create a profile record with the settings that consist of a hash with color red and resolution 1280 by 760. We can see the new record. We can retrieve the settings for this profile. You can see that ActiveRecord has converted the hstore column to hash. We can retrieve the value of color from this hash. The value is a string. We can also change the settings to a new hash. This new hash will replace the old hash. We can save the profile with the new hash. The power of hstore is in the ability to query the hash. We don't have any profile with yellow color for the settings. If we query for blue, we see one record as the result. You can refer the hstore operators and functions docs. We just saw the first operator in action. We can add an index to the settings column of type gin. There are two types of indexes you can create and you can choose which type of index you need based on your application needs. You can refer the postgresql docs for more details. Let's drop and recreate the database. We now have defined the index for the settings column. We have no profile records. Let's create a new profile. We have one record. Let's create another profile record. We now have two records. Let's create the third profile that has no color property in the settings column. We can query the profiles table by using the @> operator in the settings column. In this case, we are looking for a profile with color blue in it's settings. We can also query the hstore column using the LIKE operator. In this case, we are quering for profiles that has color that has e in the middle. We found one profile with color red in it's settings column.


Related Articles

Watch this Article as Screencast

You can watch this as a screencast PostgreSQL Hstore Basics in Rails 5.1

Ace the Technical Interview

  • Easily find the gaps in your knowledge
  • Get customized lessons based on where you are
  • Take consistent action everyday
  • Builtin accountability to keep you on track
  • You will solve bigger problems over time
  • Get the job of your dreams

Take the 30 Day Coding Skills Challenge

Gain confidence to attend the interview

No spam ever. Unsubscribe anytime.