the relatabase blog

Using postgres arrays with rails 4

posted on September 30, 2013 | by David Kelso | comments

Rails 4 has native support for postgresql’s array and hstore primitive types. From a database design perspective, I really like arrays. It saves having tables with just an ID and value for every field that can have multiple values.

A great summary of how to use these types in rails is Adam Sanderson’s amazing railsconf presentation. But for the basics, here’s a migration I’ve taken from relatabase.

1
2
3
4
5
6
7
8
9
10
11

class CreateContacts < ActiveRecord::Migration
  def change
    create_table :contacts do |t|
      t.string :name

      t.text :emails, array: true, null: false, default: '{}'
      t.text :phones, array: true, null: false, default: '{}'
      t.text :urls, array: true, null: false, default: '{}'
    end
  end
end

One huge caveat of this approach is that rails doesn’t clone the array when saving the original for dirty checking, so any in-place modification to the array won’t register that it’s changed.

1
2
3
4
5
6
7
8
9
10

homer = Contact.create name: 'Homer Simpson',
  emails: ['homer@example.com', 'hsimpson@gmail.com']
homer.emails << 'homer.simpson@outlook.com'
homer.emails_changed?
# => false
homer.emails_was
# => ['homer@example.com', 'hsimpson@gmail.com', 'homer.simpson@outlook.com']
homer.save!
homer.reload.emails
# => ['homer@example.com', 'hsimpson@gmail.com']

You can either manually signal that the record has changed, or you can be sure to only use methods that replace the array.

1
2
3
4
5

# This works
homer.emails += 'homer.simpson@outlook.com'
# So does this
homer.emails << 'homer.simpson@outlook.com'
homer.emails_will_change!

One interesting aspect of this is we can store an array of foreign keys on an N side of the N:1 relationship. This means we can store keys where they’re most relevant, and get rid of join tables in some cases. Here’s a great example of where this could be a huge improvement from the status quo.

It’s pretty easy to get this sort of setup working in rails too.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

class Tables < ActiveRecord::Migration
  def change
    create_table :products do |t|
      t.string :name, null: false
    end
    create_table :coupons do |t|
      t.integer :product_ids, array: true, null: false, default: []
    end
  end
end

class Coupon < ActiveRecord::Base
  def products
    Product.where id: self.product_ids
  end
end

class Product < ActiveRecord::Base
  def coupons
    Coupon.where 'product_ids @> ARRAY[?]', self.id
  end
end

Unfortunately, if you care about referential integrity (and you should), then there is no syntax for foreign keys from inside the elements of an array, though there has been some discussion on the preferred syntax.

One thing that I haven’t delved into yet is a nice way to validate the contents of an array. Suggestions welcome.