How can I store a hash in my database?

Question

Is there a Ruby, or Activerecord method that can write and read a hash to and from a database field?

I need to write a web utility to accept POST data and save it to a database, then later on pull it from the database in its original hash form. But ideally without 'knowing' what the structure is. In other words, my data store needs to be independent of any particular set of hash keys.

For example, one time the external app might POST to my app:

"user" => "Bill",
"city" => "New York"

But another time the external app might POST to my app:

"company" => "Foo Inc",
"telephone" => "555-5555"

So my utility needs to save an arbitrary hash to a text field in the database, then, later, recreate the hash from what was saved.

Answer 1

There are two ways to do this:

1. Serialize your hash and store it in a text field.
2. Split the hash and store each key in a separate row.

The problem with the first approach is that finding and manipulating is difficult and expensive. For example, prefix a "0" before the telephone number of all employees working in Foo Inc. will be a nightmare, compared to storing the data in regular tabular format.

Your schema would be:

employees (id, created_at, updated_at)
employee_details (id, employee_id, key, value)

So, to store

"company" => "Foo Inc",
"telephone" => "555-5555"

you would do:

employees: 1, 2012-01-01, 2012-01-01
employee_details (1, 1, "company", "Foo Inc"), (2, 1, "telephone", "555-5555")

Drawbacks of this approach: Rails does not natively support such kind of a schema.

Answer 2

Rails 4 adds support for the Postgres hstore data type which will let you add hashes directly into your (postgres) database.

If you are using Rails 4 and Postgres, you can use hstore in your migration:

def up
  execute "create extension hstore"
  add_column :table, :column, :hstore
end

def down
  remove_column :table, :column
end

That execute command will enable hstore in Postgres, so you only have to do that once.

This will enable you to store a hash in :column just like you would any other data type.