How to add custom fields to a model in Rails using the hstore data type in PostgreSQL
Sometimes when we’re developing an application we run into situations when we don’t know exactly what types of fields we need for a model.
One example might be a model for contacts. A contact usually have fixed fields like
last_name and so on. But diffrent kind of contacts may have different kind of fields like
referred_by to keep track of who referred a customer to you. Or maybe you just want to keep track of a
customer_number. Or maybe a field named
university to keep track of which college a contact went to. The list of potential fields that you can associate with a contact is endless and creating a column for all of them in a contact table will obviously not work.
This problem can easily be solved by using a data type named hstore in PostgreSQL which allows you to store key/value structures just like a dictionary or hash.
Make sure you have the Rails 4 gem activated and PostgreSQL installed then run:
Then open the generated migration file named enable_hstore_extension and edit it so it looks like this:
You also have migration file looking like this:
Notice that we now can use the data type hstore within an migration.
Now you’re ready to create the database and run the migrations:
If you open up
psql, connects to your database and run
\d contacts;, you should see your fields column:
You can now open the Rails console and start playing with your contact model:
One important thing to be aware of here is the difference about the hash that’s returned from the database is that the keys and values are all strings, even though we used a symbol and an integer for the age when we set it. At the moment, hstore only stores string values so if we want to store a boolean, date or integer value we’ll need to convert it manually afterwards.
Another important thing to remember is that the fields object will be a different object each time we fetch it. We cannot set a specific field through this hash. It won’t work as the old hash will be used each time. We always have to set the full hash each time.
If you are curious about how the row looks like the the database you can use psql and run:
Here’s some sample queries:
More information about the hstore operators and functions can be found here .
If you query for data frequently in your hstore column you should add an index for the data. You have the option to choose between two types of indexes called GiST and GIN. Which one that will suit you depends on how you want to use your data in your hstore column. Your can read more about the two index types in the PostgreSQL documentation: GiST and GIN Index Types
To add the index you can create this migration: