activerecord-postgres-hstore

DEPRECATED - Just use Rails JSON or HStore built-in support.

If you are using Rails 4 you don't need this gem as ActiveRecord 4 provides HStore type support out of the box. ActiveRecord will see your HStore column and do all of the work for you. Additional code is no longer needed.

You can test it with a migration like this:

class CreateTest  t = Test.new data: {a: 1, b:2}
=> #"1", "b"=>"2"}>
irb(main):004:0> t.save!
   (0.3ms)  BEGIN
  SQL (2.3ms)  INSERT INTO "tests" ("data") VALUES ($1) RETURNING "id"  [["data", "\"a\"=>\"1\",\"b\"=>\"2\""]]
   (0.5ms)  COMMIT
=> true
irb(main):005:0> t
=> #"1", "b"=>"2"}>
irb(main):006:0> t.data
=> {"a"=>"1", "b"=>"2"}
irb(main):007:0> t.data['a']
=> "1"

For more information take a look here

Common use cases

Add settings to users, like in rails-settings or HasEasy.

class User  person = Person.new
=> #
irb(main):002:0> person.data['favorite_color'] = 'blue'
=> "blue"

Querying the database

Now you just need to learn a little bit of new sqls for selecting stuff (creating and updating is transparent). Find records that contains a key named 'foo’:

Person.where("data ? 'foo'")

Find records where 'foo’ is equal to 'bar’:

Person.where("data -> 'foo' = 'bar'")

This same sql is at least twice as fast (using indexes) if you do it that way:

Person.where("data @> 'foo=>bar'")

Find records where 'foo’ is not equal to 'bar’:

Person.where("data -> 'foo' <> 'bar'")

Find records where 'foo’ is like 'bar’:

Person.where("data -> 'foo' LIKE '%bar%'")

If you need to delete a key in a record, you can do it that way:

person.destroy_key(:data, :foo)

This way you’ll also save the record:

person.destroy_key!(:data, :foo)

The destroy_key method returns 'self’, so you can chain it:

person.destroy_key(:data, :foo).destroy_key(:data, :bar).save

But there is a shortcuts for that:

person.destroy_keys(:data, :foo, :bar)

And finally, if you need to delete keys in many rows, you can:

Person.delete_key(:data, :foo)

and with many keys:

Person.delete_keys(:data, :foo, :bar)

Caveats

hstore keys and values have to be strings. This means true will become "true" and 42 will become "42" after you save the record. Only nil values are preserved.

It is also confusing when querying:

Person.where("data -> 'foo' = :value", value: true).to_sql
#=> SELECT "people".* FROM "people" WHERE ("data -> 'foo' = 't'") # notice 't'

To avoid the above, make sure all named parameters are strings:

Person.where("data -> 'foo' = :value", value: some_var.to_s)

Have fun.

Test Database

To have hstore enabled when you load your database schema (as happens in rake db:test:prepare), you have two options.

The first option is creating a template database with hstore installed and set the template option in database.yml to that database. If you use the template1 database for this you don't even need to set the template option, but the extension will be installed in all your databases from now on by default. To install the extension in your template1 database you could simply run:

psql -d template1 -c 'create extension hstore;'

The second option is to uncomment or add the following line in config/application.rb

config.active_record.schema_format = :sql

This will change your schema dumps from Ruby to SQL. If you're unsure about the implications of this change, we suggest reading this Rails Guide.

Help

You can use issues in github for that. Or else you can reach us at twitter: @dbiazus or @joaomilho

Note on Patches/Pull Requests

• Fork the project.
• Make your feature addition or bug fix.
• Add tests for it. This is important so I don’t break it in a future version unintentionally.
• Commit, do not mess with rakefile, version, or history. (if you want to have your own version, that is fine but bump version in a commit by itself I can ignore when I pull)
• Send me a pull request. Bonus points for topic branches.

Copyright

Copyright © 2010 Juan Maiz. See LICENSE for details.