A love affair with PostgreSQL [Rails 4 Countdown to 2013]

This post is part of a series of 31 Rails 4 articles being released each day in December 2012.

Out of all the supported databases available in Active Record, PostgreSQL received the most amount of attention during the development of Rails 4. In today's countdown post, we are going to look at the various additions made to the PostgreSQL database adapter.

hstore support

Rails 4 comes with hstore support out of the box. hstore allows you to store a set of key/value pairs within a single PostgreSQL value. In other words, it allows you to have NoSQL schema-less data support in PostgreSQL.

To get started, first setup your database to use the hstore extension:

class AddHstoreExtension < ActiveRecord::Migration
  def up
    execute 'CREATE EXTENSION hstore'
  end

  def down
    execute 'DROP EXTENSION hstore'
  end
end

Next, add the hstore column type to a model:

 class AddPropertiesToComics < ActiveRecord::Migration
  def up
    add_column :comics, :properties, :hstore
  end

  def down
    remove_column :comics, :properties
  end
end

Using the store_accessor macro style method in Active Record models, we can add read/write accessors to key/value hstore properties:

class Comic < ActiveRecord::Base
  store_accessor :properties, :story_arc
end

comic = Comic.create
comic.properties # => nil
comic.story_arc = 'Throne of Atlantis'
comic.save
# => UPDATE "comics" SET "properties" = '"story_arc"=>"Throne of Atlantis"', "updated_at" = '2012-12-29 19:31:45.234882' WHERE "comics"."id" = 1

Using the store accessors, you can include the dynamic attributes in forms and also have the ability to add Active Model validations to them.

To query against hstore data in Active Record, use SQL string conditions with the where query method:

 Comic.where("properties -> 'story_arc' = 'Throne of Atlantis'")
#  SELECT "comics".* FROM "comics" WHERE (properties -> 'story_arc' = 'Throne of Atlantis')

Note: When an hstore attribute is returned from PostgreSQL, all key/values are going to be strings.

Indexes

If you are doing any queries on an hstore property, be sure to add the appropriate index. When adding an index, you will have to decide to use either GIN or GiST index types. The distinguishing factor between the two index types is that GIN index lookups are three times faster than GiST indexes, however they also take three times longer to build. Checkout the documentation, which goes into detail about the differences.

CREATE INDEX name ON table USING gin(column);
CREATE INDEX name ON table USING gist(column);

Example:

class AddIndexToComicsProperties < ActiveRecord::Migration
  def up
    execute 'CREATE INDEX comics_properties ON comics USING gin(properties)'
  end

  def down
    execute 'DROP INDEX comics_properties'
  end
end

Note: Indexes can only be used for queries with @>, ?, ?& and ?| operators. Thus the ActiveRecord example in the previous section using the -> operator would not use any indexes. (Thanks to Philip Hofstetter for pointing this out)

Upgrade Path

If this is something you would like to use in your Rails 3 applications today, you can use the activerecord-postgres-hstore gem.

hstore resources

• hstore Railscast
• PostgreSQL Documentation
• GiST and GIN Index Types

Array support

Another native type being supported by Active Record for PostgreSQL databases is Array.

When creating a column in a migration, you must pass true to the array option to set the attribute as an Array.

Here is an example of creating a string array of tags:

class AddTagsToArticles < ActiveRecord::Migration
  def change
    change_table :articles do |t|
      t.string :tags, array: true
    end
  end
end

# ALTER TABLE "articles" ADD COLUMN "tags" character varying(255)[]

Next, add an item to the array attribute:

article.tags = ['rails']
article.save
# UPDATE "articles" SET "tags" = '{"rails"}', "updated_at" = '2012-12-29 20:12:49.825980' WHERE "articles"."id" = 1
article.reload
article.tags # => ["rails"]

If the pg_array_parser gem is included in the application Gemfile, Rails will use it when parsing PostgreSQL's array representation. The gem includes a native C extention and JRuby support.

Resources

• PostgreSQL documentation
• pg_array_parser gem

uuid type

Universally unique identifier (UUID) column type support has been added to Rails 4.

To set a column as UUID, use add_column in your migration and set the data type to :uuid:

add_column :table_name, :unique_identifier, :uuid

When reading/writing to a UUID attribute, you will always be dealing with a string:

obj.unique_identifier = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'

Network Address data types

PostgreSQL has data types exclusively for IPv4, IPv6, and MAC addresses. Data types inet and cidr are used for IPv4 or IPv6 host address. The difference between inet and cidr is that the former accepts values with nonzero bits to the right of the netmask. MAC addresses are represented with the macaddr data type.

When reading inet/cidr data types from the database, Active Record will convert the values to IPAddr objects. Reading and writing to macaddr attributes on the other hand uses strings.

To set a column as a network address, use add_column in your migration and set the data type to inet, cidr, or macaddr:

class AddNetworkAddresses < ActiveRecord::Migration
  def change
    add_column :table_name, :inet_address, :inet
    add_column :table_name, :cidr_address, :cidr
    add_column :table_name, :mac_address, :macaddr
  end
end

Example:

obj.inet_address = IPAddr.new('127.0.0.1')
obj.save
obj.reload
obj.inet_address # => <IPAddr: IPv4:127.0.0.1/255.255.255.255>

Other data types

The following datatypes can be created in PostgreSQL and be correctly converted to and from the database:

• int4range
• int8range
• json