Using Postgres’ Hstore datatype in Rails
HStore is a key value store within
Postgres. You can use it similar to how you would use a dictionary within any other programming language, though it’s specific to a table column in the database.
Hstore when I need to combine the functionality of relational database (SQL-based), with non-relational databases (NoSQL databases) in one project. For example, if you are creating a databse for an online electronics store, you will need a relational database, to save the customers, their orders, and the products they ordered. However, the products themselves don’t share the same attributes. (i.e. memory cards have memory size but laptop chargers don’t, and although it’s suitable to have number of HDMI inputs for a TV, it can’t be applied to earphones.)
You get the idea.
Some may go for creating a new table in the database for each set of products that have the same attributes, but this has a lot of drawbacks. Mainly, the fact that you will have to change the database scheme and re-write your application code everytime the store decides to sell a new product that doesn’t fit in any of the current multiple product tables. Also, the database losses its integrity this way, essentially, when considering the fact that searching or sorting products in this case based on one of the new attributes would be impossible.
Hstore fixes this all.
With Hstore, you can have one table for all Products, and have the following attributes:
Initialize Hstore in Rails:
First, create a new Rails application and choose PostgreSQL as the database engine for that application:
After you create the new Rails application, you will need to inistialize the Hstore hashing so that Rails can deal with this datatype and serialize/deserialize the contents of the this datatype. This will be accomplished by running the following migration:
And type the following code in the generated migration file:
Now you can create a model Product that has a string name and a hash hash_data; run the following command to generate that model:
And don’t forget the run:
That’s it. You application now can run code like:
Querying the Hstore attribute:
If you would like to search for all products that have the key “color” regardless of the value of that key, you should run a code like:
If you would like to search for all products that have the key “color” and that key equals to “yellow” for example, your code should be:
Updating the Hstore:
Updating the Hstore value could be a little tricky. If you run the previous command to create the T-shirt that has green color and size large then run the following command to update the hash, it won’t save to the database:
The code didn’t really update the database itself. The reason is that in the database, the value of the Hstore attribute is a pointer to the hash which is saved in a different place in the database file. So, updating the value of the hash, would not change the value of the pointer saved in the database.
The way to tell Rails that the value has changed and that it needs to update the database is to run the following code:
Because the code to query and update a Hstore datatype could be a little tricky, specially when dealing with multiple Key/Value pairs for each raw, I have created the following
Concern that you can just apply to any model that has a hash in it:
Simply include this concern in your model, and use the supplied methods to query, update or delete the Hstore attribute for the model.