Using arrays of hstore with Rails 4

UPDATE: The patch below has now been merged into edge rails and should appear in the 4.1 release as well as, hopefully, a backport into 4.0.3.

One of the attractive new things in Rails 4 is the enhanced support for PostgreSQL array and hstore column types, amongst other things. There are quite a few articles describing their use – here’s a clear one – and there are subtleties with strong parameters. However they don’t quite work together out of the box – but very nearly, and with a little work we can store arrays of hashes directly in a single PostgreSQL column.

Migration

We were already able to create array-of-hstore columns using the new syntax; for example, with the following migration:

class CreateServers < ActiveRecord::Migration
  def change
    enable_extension "hstore"
    create_table :servers do |t|
      t.string :name
      t.hstore :interfaces, array: true
      t.timestamps
    end
  end
end

So far, so good; running rake db:migrate will create the interfaces[] array-of-hstore column. But trying to use it falls flat; any kind of insert or update will produce an error from PostgreSQL, because the hstore needs to be encoded inside the array for the wire protocol.

The workaround

Applying a small patch to edge rails (see https://github.com/rails/rails/pull/11444), we can now create a record with an array-of-hash values in the interfaces column:

server = Server.create(name: "server01", interfaces: [
  { "name" => "bge0", "ipv4" => "192.0.2.2", "state" => "up" },
  { "name" => "de0", "state" => "disabled", "by" => "misha" },
  { "name" => "fe0", "state" => "up" },
])

Now we’re getting somewhere! And loading that record back will produce the expected array-of-hash from server.interfaces, which we can use like any other:

server.interfaces[0]["state"]             # => "up"
server.interfaces.map { |i| i["name"] }   # => ["bge0", "de0", "fe0"]

Updates & change tracking

Because the hash values themselves are not Active Record model objects, changing a value requires that you notify Rails with attr_will_change; for example:

server.interfaces[0]["state"] = "down"
server.interfaces[0]["by"] = "misha"
server.interfaces_will_change!
server.save

Querying

The syntax for hstore-array querying is not obvious; we have to unroll the array and use it like a WHERE IN clause. The PostgreSQL array operators ANY and ALL are available to us, and allow us to find records by the values stored under the hash keys in array elements. For example, declaring this in your model:

class Server < ActiveRecord::Base
  scope :where_any, ->(column, key, value) { where("? = ANY (SELECT unnest(\"#{column}\") -> ?)", value, key) }
  scope :where_all, ->(column, key, value) { where("? = ALL (SELECT unnest(\"#{column}\") -> ?)", value, key) }
end

will allow us to find our server above with:

Server.where_any(:interfaces, :state, "up")

however, it will not match this relation:

Server.where_all(:interfaces, :state, "up")

because one of the array elements doesn’t match (in this example, the disabled interface).

Use with forms and Strong Parameters

Also new in Rails 4 are Strong Parameters. These will still be unfamiliar to many, but they allow structural validation of complex input with a fairly straightforward syntax. For example, in a controller, the following will permit form-based submission of our array-of-hash structure:

  def create
    @server = Server.new(server_params)
    # ...
  end
private
  def server_params
    params.require(:server).permit(:name, :interfaces => [:name, :ipv4, :state, :by])
  end

The form itself needs to use an OpenStruct to wrap each hash, so that ActionView is able to pull fields from it; e.g.

<% @server.interfaces.each do |interface| %>
  <div>
  <%= f.fields_for :interfaces, OpenStruct.new(interface), index: "" do |interface_form| %>
    <%= render "interface_fields", f: interface_form %>
  <% end %>
  </div>
<% end %>

where _interface_fields.html.erb is:

<fieldset>
  <%= f.text_field :name %>
  <%= f.text_field :ipv4 %>
  <%= f.text_field :state %>
  <%= f.text_field :by %>
</fieldset>

Note that if you need to dynamically add/remove array elements, this is also compatible with the technique for nested forms – however, a little extra infrastructure is required. I’ll save that for a future post.

Indexing

I haven’t worked this out yet. In PostgreSQL you can normally index arrays, and index hstores – but my attempts to index an array-of-hstore column have so far produced errors. It may be necessary to define an operator class for the type. One might argue that even needing this suggests that a more conventional relational structure should be used, but it’ll remain an open performance question. When I get to the bottom of that particular rabbit hole, I’ll let you know.

author’s note: a previous version of this article used symbolic keys for the hash, but this changed to string keys, for consistency with other access methods, by request of the rails team during review of the pull request.