I had a unique little situation pop up that needed a little more flexibility when it came to storing data in the db. We use Postgres at RaiseMore because we respect ourselves, so naturally, I wanted to take a swing using some Hstore options for our ever changing data schema.
What I needed
Simple, to store a few bits of data about a communication – primarily the subject, message, date/time, and what networks it was sent out on. It may or may not have some of these fields, and the networks may or may not change in the future. This sounds perfect for HStore.
First I started with a simple schema:
1 2 3 4 5
At first, I just wanted to store when a message was sent and on what networks. I figured I’d just have an AR model with one hstore column and everything would fit into that. It looked like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Pretty easy right? I ran into some difficulties REAL fast. The first was from JSON serialization. When I did this:
1 2 3
It gave me a nice FAT serialized version of a Hash array. What good does a string do me? I want my object! BLEH!
We still have to get this thing working, so lets proceed anyway and just do some manual converions with PostgreSQL’s awesome
Cool, so its gonna be nasty still. Ok, how about just using Rails to_json method:
Thanks, but no thanks. I’m pretty set on having an array of values instead of some manual labor on converting those values.
I was learning real fast that storing arrays in hstore was going to be a challenge. The next stab I wanted to take was to simplify the storage and retrieval as well as using natural arrays.
NOTE: I changed the way I attacked the problem here partly because I was doing it wrong. Hopefully you can learn from my mistakes? :–)
This was my next stab, altering the table structure just a bit:
1 2 3 4 5 6
What I did here was harness the awesome power of PostgreSQL’s arrays and hstore. Taking this thing to the limit. I did this for two reasons:
- Constraints changed, we could send multiple communications out on multiple networks at any given time
- I wanted a log of history with whatever pieces of data may or may not be there
- Be able to quickly get the networks sent out per user
Now my AR model is decorated with store_accessor to give me model attributes for the networks. It also stores the log of messages in an array naturally through the model, and accessed as a real HSTORE with array values (instead of json, yay).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
So what happens when I to_json my fields now in PostgreSQL?
1 2 3 4 5
1 2 3 4 5
I had a lot of fun using PostgreSQL and Rails 4. I didn’t find a lot of in-depth knowledge on it so I wanted to shed some light on the topic and hope this would push someone else who might be considering to try it out to give it a go.
I’d like to follow up this post with another article on how to search these bad boys.
- I will definitely use hstore more in the future. I like the freedom to just store whatever I want
- I need to reach out to the Rails team and see what I can do about improving the array support in Active Record
- I hope you try it out as well