Instantly share code, notes, and snippets.

Embed
What would you like to do?
Postgresql fancy datatypes with Rails / ActiveRecord. Run it with `rake`!
# Postgresql fancy datatypes!
* array
* hstore (=~ hash)
* json
* jsonb
Philippe Creux - [@pcreux](http://twitter.com/pcreux)
[brewhouse](http://brewhouse.io)
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
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
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
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
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
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
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'
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
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

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

source 'https://rubygems.org'
gem 'rails', github: 'rails/rails'
gem 'arel', github: 'rails/arel'
gem 'pg'
gem 'pry'
gem 'rspec'
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
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
# 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
@rhazegh

This comment has been minimized.

rhazegh commented Dec 25, 2014

I was trying to setup jsonb support like you did here ActiveRecord::Base.connection.type_map.alias_type "jsonb", "json" but I get the following error.

$ rake db:migrate
rake aborted!
private method `type_map' called for #<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter:0x007ff9daaa5088>
/Users/myaccount/project1/db/migrate/014_add_action_log.rb:1:in `<top (required)>'

Any ideas?

@apneadiving

This comment has been minimized.

apneadiving commented Jan 9, 2015

thanks, very useful!

@rusllonrails

This comment has been minimized.

rusllonrails commented Jun 2, 2015

thanks, good stuff, very usefull 👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment