Postgresql fancy datatypes with Rails / ActiveRecord. Run it with `rake`!

000_postgresql_fancy_datatypes

# Postgresql fancy datatypes!

* array
* hstore (=~ hash)
* json
* jsonb


  Philippe Creux - [@pcreux](http://twitter.com/pcreux)

  [brewhouse](http://brewhouse.io)

099_array.txt

   Oo    `OooOOo.  `OooOOo.     Oo    o       O
  o  O    o     `o  o     `o   o  O   O       o
 O    o   O      O  O      O  O    o  `o     O'
oOooOoOo  o     .O  o     .O oOooOoOo   O   o
o      O  OOooOO'   OOooOO'  o      O    `O'
O      o  o    o    o    o   O      o     o
o      O  O     O   O     O  o      O     O
O.     O  O      o  O      o O.     O     O

100_array_spec.rb

require_relative 'setup'

#
# Array - Since 8.4
#
# An array of a defined type (string, integer, ...)
#
# http://www.postgresql.org/docs/9.4/static/arrays.html
#

class Post < ActiveRecord::Base
end

describe "array" do
  before(:all) do
    ActiveRecord::Schema.define do
      create_table :posts, force: true do |t|
        t.string :title
        t.string :tags, array: true
      end
    end

    Post.reset_column_information
  end

  before { Post.delete_all }

  let!(:tdd) { Post.create!(title: "TDD is dead", tags: ["tdd", "testing", "death", false]).reload }
  let!(:bdd) { Post.create!(title: "BDD is woot", tags: ["bdd", "testing", "woot",  true]).reload }
  let!(:foo) { Post.create!(title: "FOO is bar",  tags: ["foo", "bar", 42]).reload }

  it "keeps order and converts everything to strings (as it's an array of strings)" do
    expect(tdd.tags).to eq ["tdd", "testing", "death", "0"]
    expect(bdd.tags).to eq ["bdd", "testing", "woot", "1"]
    expect(foo.tags).to eq ["foo", "bar", "42"]
  end

  it "saves when an element is changed" do
    tdd.tags[1] = "testing"
    tdd.save!
    tdd.reload
    expect(tdd.tags[1]).to eq "testing"
  end

  it "searches for array containing a value" do
    expect(Post.where("'testing' = ANY (tags)").all).to eq [tdd, bdd]
  end

  it "searches for a value at a specific position (index starts at 1!)" do
    expect(Post.where("tags[2] = 'testing'").all).to eq [tdd, bdd]
  end
end

199_hstore.txt

o      O .oOOOo.  oOoOOoOOo  .oOOOo.  `OooOOo.  o.OOoOoo
O      o o     o      o     .O     o.  o     `o  O
o      O O.           o     O       o  O      O  o
OoOooOOo  `OOoo.      O     o       O  o     .O  ooOO
o      O       `O     o     O       o  OOooOO'   O
O      o        o     O     o       O  o    o    o
o      o O.    .O     O     `o     O'  O     O   O
o      O  `oooO'      o'     `OoooO'   O      o ooOooOoO

200_hstore_spec.rb

require_relative 'setup'

#
# Hstore - Since 8.4
#
# A hash of strings.
#
# http://www.postgresql.org/docs/9.4/static/hstore.html
#

class Post < ActiveRecord::Base
end

describe "hstore" do
  before(:all) do
    ActiveRecord::Schema.define do
      #execute "CREATE EXTENSION IF NOT EXISTS hstore"

      create_table :posts, force: true do |t|
        t.string :title
        t.hstore :properties
      end
    end

    Post.reset_column_information
  end


  before { Post.delete_all }

  let!(:tdd) { Post.create!(title: "TDD is dead", properties: { author: "DHH",      word_count: 100 }).reload }
  let!(:bdd) { Post.create!(title: "BDD is woot", properties: { author: "Philippe", word_count:  42 }).reload }
  let!(:foo) { Post.create!(title: "FOO is bar",  properties: { author: "Philippe", draft:     true }).reload }

  it "converts keys and values to strings" do
    expect(tdd.properties).to eq("author" => "DHH",      "word_count" => "100")
    expect(bdd.properties).to eq("author" => "Philippe", "word_count" => "42")
    expect(foo.properties).to eq("author" => "Philippe", "draft"      => "true")
  end

  it "saves when an element is changed" do
    tdd.properties["author"] = "Bob"
    tdd.save!
    tdd.reload
    expect(tdd.properties).to eq("author" => "Bob", "word_count" => "100")
  end

  it "searches for records with key" do
    results = Post.where("properties ? :key", key: "draft")
    expect(results).to eq [foo]
  end

  it "searches for records with specific property" do
    results = Post.where("properties -> :key = :value", key: "author", value: "Philippe")
    expect(results).to eq [bdd, foo]
  end

  it "searches for records with specific a value that we like" do
    results = Post.where("properties -> :key LIKE :value", key: "author", value: "Phil%")
    expect(results).to eq [bdd, foo]
  end
end

299_json.txt

  OooOoo .oOOOo.   .oOOOo.  o.     O
      O  o     o  .O     o. Oo     o
      o  O.       O       o O O    O
      O   `OOoo.  o       O O  o   o
      o        `O O       o O   o  O
      O         o o       O o    O O
O     o  O.    .O `o     O' o     Oo
`OooOO'   `oooO'   `OoooO'  O     `o

300_json_spec.rb

require_relative 'setup'

#
# Json - Since 9.2
#
# Json is stored as text. You can index specific keys.
#
# http://www.postgresql.org/docs/9.3/static/datatype-json.html
# http://www.postgresql.org/docs/9.3/static/functions-json.html
#

class Post < ActiveRecord::Base
end

describe "json" do
  before(:all) do
    ActiveRecord::Schema.define do
      create_table :posts, force: true do |t|
        t.json :data
      end
    end

    Post.reset_column_information
  end

  before { Post.delete_all }

  let!(:tdd) do
    Post.create!(data: {
      title:  "TDD is dead",
      author: { name: "DHH", email: "dhh@example.com" },
      tags:   ["tdd", "testing", "death", false],
      word_count: 100
    }).reload
  end

  let!(:bdd) do
    Post.create!(data: {
      title: "BDD is woot",
      author: { name: "Philippe", email: "philippe@example.com" },
      tags:   ["bdd", "testing", "woot",  true],
      word_count: 42
    }).reload
  end

  let!(:foo) do
    Post.create!(data: {
      title:  "FOO is bar",
      author: { name: "Philippe", email: "philippe@example.com" },
      tags:   ["foo", "bar", 42],
      draft:  true
    }).reload
  end

  it "preserves *JSON* types" do
    expect(tdd.data).to eq(
      "title"  => "TDD is dead",
      "author" => { "name" => "DHH", "email" => "dhh@example.com" },
      "tags"   => ["tdd", "testing", "death", false],
      "word_count" => 100
    )
  end

  it "saves when an element is changed" do
    tdd.data["author"]["name"] = "Bob"
    tdd.save!
    tdd.reload
    expect(tdd.data["author"]["name"]).to eq "Bob"
  end

  it "searches for records with specific property (title)" do
    results = Post.where("data ->> :key = :value", key: "title", value: "TDD is dead")
    expect(results).to eq [tdd]
  end

  it "searches for records with nested property (author, name)" do
    results = Post.where("data #>> '{author, name}' = :value", value: "DHH")
    expect(results).to eq [tdd]
  end

  it "does not search for records with nested array containing an element" do
    pending "works with jsonb only"
    Post.where(%|data @> '{"tags": ["testing"]}'|).to_a
  end
end

399_json.txt

  OooOoo .oOOOo.   .oOOOo.  o.     O o.oOOOo.
      O  o     o  .O     o. Oo     o  o     o
      o  O.       O       o O O    O  O     O
      O   `OOoo.  o       O O  o   o  oOooOO.
      o        `O O       o O   o  O  o     `O
      O         o o       O o    O O  O      o
O     o  O.    .O `o     O' o     Oo  o     .O
`OooOO'   `oooO'   `OoooO'  O     `o  `OooOO'

400_jsonb_spec.rb

require_relative 'setup'

#
# Jsonb - Since 9.4
#
# Json is stored as binary. You can index everything! ZOMG! NOSQL!
#
# http://www.postgresql.org/docs/9.4/static/datatype-json.html
# http://www.postgresql.org/docs/9.4/static/functions-json.html
#

# Add jsonb support to ActiveRecord. Pretty easy, eh?
ActiveRecord::Base.connection.type_map.alias_type "jsonb", "json"

class Post < ActiveRecord::Base
end

describe "jsonb" do
  before(:all) do
    ActiveRecord::Schema.define do
      create_table :posts, force: true do |t|
        t.column :data, :jsonb
      end
    end

    Post.reset_column_information
  end

  before { Post.delete_all }

  let!(:tdd) do
    Post.create!(data: {
      title:  "TDD is dead",
      author: { name: "DHH", email: "dhh@example.com" },
      tags:   ["tdd", "testing", "death", false],
      word_count: 100
    }).reload
  end

  let!(:bdd) do
    Post.create!(data: {
      title: "BDD is woot",
      author: { name: "Philippe", email: "philippe@example.com" },
      tags:   ["bdd", "testing", "woot",  true],
      word_count: 42
    }).reload
  end

  let!(:foo) do
    Post.create!(data: {
      title:  "FOO is bar",
      author: { name: "Philippe", email: "philippe@example.com" },
      tags:   ["foo", "bar", 42],
      draft:  true
    }).reload
  end

  it "preserves *JSON* types" do
    expect(tdd.data).to eq(
      "title"  => "TDD is dead",
      "author" => { "name" => "DHH", "email" => "dhh@example.com" },
      "tags"   => ["tdd", "testing", "death", false],
      "word_count" => 100
    )
  end

  it "saves when an element is changed" do
    tdd.data["author"]["name"] = "Bob"
    tdd.save!
    tdd.reload
    expect(tdd.data["author"]["name"]).to eq "Bob"
  end

  it "searches for records with specific property (title)" do
    results = Post.where("data ->> :key = :value", key: "title", value: "TDD is dead")
    expect(results).to eq [tdd]
  end

  it "searches for records with nested property (author, name)" do
    results = Post.where("data #>> '{author, name}' = :value", value: "DHH")
    expect(results).to eq [tdd]
  end

  it "searches for records with nested array containing an element" do
    results = Post.where(%|data @> '{"tags": ["testing"]}'|)
    expect(results).to eq [tdd, bdd]
  end

  it "searches with multiple conditions" do
    results = Post.where(%|data @> '{"author": {"name": "Philippe"}, "tags": ["testing"]}'|)
    expect(results).to eq [bdd]
  end

  it "searches with multiple conditions including key existence" do
    results = Post.where(%|data @> '{"author": {"name": "Philippe"}}'|).
                   where(%|data ? 'draft'|)
    expect(results).to eq [foo]
  end
end

500_view_spec.rb

require_relative 'setup'

#
# Say you store json data from an API and want to access it easily.
# Instead of mapping the json data to a table structure, you can
# just take advantage of jsonb and create a SQL View that maps
# that attributes you are interested in on the fly.
# This approach makes it very easy to add new columns if needed.
#

# Add jsonb support to ActiveRecord. Pretty easy, eh?
ActiveRecord::Base.connection.type_map.alias_type "jsonb", "json"

class JsonPost < ActiveRecord::Base
end

class Post < ActiveRecord::Base
end

describe "jsonb" do
  before(:all) do
    ActiveRecord::Schema.define do
      create_table :json_posts, force: true do |t|
        t.column :data, :jsonb
      end
    end

    Post.reset_column_information

    ActiveRecord::Base.connection.execute "DROP TABLE IF EXISTS posts"

    ActiveRecord::Base.connection.execute <<-SQL
      CREATE OR REPLACE VIEW posts as
      SELECT
        id,
        data ->> 'title'            AS title,
        data #>> '{author,name}'    AS author_name,
        data #>> '{author,email}'   AS author_email,
        (data ->> 'tags')::json     AS tags,
        (data ->> 'draft')::boolean AS draft
      FROM json_posts
    SQL
  end

  after(:all) do
    ActiveRecord::Base.connection.execute "DROP VIEW IF EXISTS posts"
  end

  before { JsonPost.delete_all }

  let!(:tdd) do
    JsonPost.create!(data: {
      title:  "TDD is dead",
      author: { name: "DHH", email: "dhh@example.com" },
      tags:   ["tdd", "testing", "death", false],
      word_count: 100
    }).reload
  end

  let!(:bdd) do
    JsonPost.create!(data: {
      title: "BDD is woot",
      author: { name: "Philippe", email: "philippe@example.com" },
      tags:   ["bdd", "testing", "woot",  true],
      word_count: 42
    }).reload
  end

  let!(:foo) do
    JsonPost.create!(data: {
      title:  "FOO is bar",
      author: { name: "Philippe", email: "philippe@example.com" },
      tags:   ["foo", "bar", 42],
      draft:  true
    }).reload
  end

  it "maps fields" do
    post = Post.find_by_title! "FOO is bar"
    expect(post.author_name).to eq("Philippe")
    expect(post.author_email).to eq("philippe@example.com")
    expect(post.tags).to eq ["foo", "bar", 42]
    expect(post).to be_draft
  end

  it "provides search as if it was a good old table" do
    expect(Post.where(author_name: "Philippe").pluck(:title)).to eq ["BDD is woot", "FOO is bar"]
    expect(Post.where(draft: true).count).to eq(1)
  end

end

600_done.md

Wrap up!

• use arrays
• use hstore
• forget about json
• use jsonb
• wrap complex json data with sexy SQL views

Thank you!

Philippe Creux - @pcreux brewhouse

Gemfile

source 'https://rubygems.org'
gem 'rails', github: 'rails/rails'
gem 'arel', github: 'rails/arel'
gem 'pg'
gem 'pry'
gem 'rspec'

Gemfile.lock

GIT
  remote: git://github.com/rails/arel.git
  revision: ebbd786945c12f65062ef5b4809b4f13643e3c52
  specs:
    arel (6.0.0.20140505020427)

GIT
  remote: git://github.com/rails/rails.git
  revision: 5add8b8d6d27afac9fe46bcee09cd341fb124294
  specs:
    actionmailer (4.2.0.alpha)
      actionpack (= 4.2.0.alpha)
      actionview (= 4.2.0.alpha)
      mail (~> 2.5, >= 2.5.4)
    actionpack (4.2.0.alpha)
      actionview (= 4.2.0.alpha)
      activesupport (= 4.2.0.alpha)
      rack (~> 1.5.2)
      rack-test (~> 0.6.2)
    actionview (4.2.0.alpha)
      activesupport (= 4.2.0.alpha)
      builder (~> 3.1)
      erubis (~> 2.7.0)
    activemodel (4.2.0.alpha)
      activesupport (= 4.2.0.alpha)
      builder (~> 3.1)
    activerecord (4.2.0.alpha)
      activemodel (= 4.2.0.alpha)
      activesupport (= 4.2.0.alpha)
      arel (~> 6.0.0)
    activesupport (4.2.0.alpha)
      i18n (~> 0.6, >= 0.6.9)
      json (~> 1.7, >= 1.7.7)
      minitest (~> 5.1)
      thread_safe (~> 0.1)
      tzinfo (~> 1.1)
    rails (4.2.0.alpha)
      actionmailer (= 4.2.0.alpha)
      actionpack (= 4.2.0.alpha)
      actionview (= 4.2.0.alpha)
      activemodel (= 4.2.0.alpha)
      activerecord (= 4.2.0.alpha)
      activesupport (= 4.2.0.alpha)
      bundler (>= 1.3.0, < 2.0)
      railties (= 4.2.0.alpha)
      sprockets-rails (~> 2.1)
    railties (4.2.0.alpha)
      actionpack (= 4.2.0.alpha)
      activesupport (= 4.2.0.alpha)
      rake (>= 0.8.7)
      thor (>= 0.18.1, < 2.0)

GEM
  remote: https://rubygems.org/
  specs:
    builder (3.2.2)
    coderay (1.1.0)
    diff-lcs (1.2.5)
    erubis (2.7.0)
    hike (1.2.3)
    i18n (0.6.9)
    json (1.8.1)
    mail (2.6.1)
      mime-types (>= 1.16, < 3)
    method_source (0.8.2)
    mime-types (2.3)
    minitest (5.3.5)
    multi_json (1.10.1)
    pg (0.17.1)
    pry (0.10.0)
      coderay (~> 1.1.0)
      method_source (~> 0.8.1)
      slop (~> 3.4)
    rack (1.5.2)
    rack-test (0.6.2)
      rack (>= 1.0)
    rake (10.3.2)
    rspec (3.0.0)
      rspec-core (~> 3.0.0)
      rspec-expectations (~> 3.0.0)
      rspec-mocks (~> 3.0.0)
    rspec-core (3.0.2)
      rspec-support (~> 3.0.0)
    rspec-expectations (3.0.2)
      diff-lcs (>= 1.2.0, < 2.0)
      rspec-support (~> 3.0.0)
    rspec-mocks (3.0.2)
      rspec-support (~> 3.0.0)
    rspec-support (3.0.2)
    slop (3.5.0)
    sprockets (2.12.1)
      hike (~> 1.2)
      multi_json (~> 1.0)
      rack (~> 1.0)
      tilt (~> 1.1, != 1.3.0)
    sprockets-rails (2.1.3)
      actionpack (>= 3.0)
      activesupport (>= 3.0)
      sprockets (~> 2.8)
    thor (0.19.1)
    thread_safe (0.3.4)
    tilt (1.4.1)
    tzinfo (1.2.1)
      thread_safe (~> 0.1)

PLATFORMS
  ruby

DEPENDENCIES
  arel!
  pg
  pry
  rails!
  rspec

Rakefile

task :default do
  Dir['*'].sort.select { |path| path =~ /^\d/ }.each do |path|
    case path
    when /\.rb$/
      system "bundle exec rspec -cf documentation #{path}"
    else
      system "cat #{path}"
    end
  end
end

setup.rb

# Based on https://github.com/rails/rails/blob/master/guides/bug_report_templates/active_record_master.rb

def system!(cmd)
  puts cmd
  system(cmd) or exit(-1)
end

require 'bundler'
Bundler.setup(:default)

require 'active_record'
require 'rspec/its'
require 'logger'
require 'pry'


DATABASE_NAME = 'pg-ar-demo'

begin
  ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: DATABASE_NAME, encoding: "utf-8")
  ActiveRecord::Base.connection
rescue ActiveRecord::NoDatabaseError => e
  system! %{echo 'CREATE DATABASE "#{DATABASE_NAME}";' | psql}
  retry
end

thanks, very useful!

thanks, good stuff, very usefull 👍