Polymorphic relations and DB consistency with ease? From now – yes!

pg_morphHave you ever had a dilemma which to choose – make your everyday life with code easier and use ActiveRecord polymorphic relations or maybe be a more responsible person and think about your database consistency?

Maybe you haven’t, but I’ve actually started thinking about it more. And while I was working with postgreSQL and digging through its documentation once again I convinced myself how powerful this tool is.

Just imagine that you can treat each type from your polymorphic relation as a separate one and set a required foreign key on each. Imagine they all act like they were just one, so you don’t have to change anything in your code and it’s all completely transparent.

So let’s make it happen. Let’s make it possible to add a foreign key constraint to each polymorphic relation type in such a way that ActiveRecord knows nothing about it.

How does it work?

We often use callbacks in our ruby code to catch some events and run some extras. It’s quite natural. But not many developers think about database and SQL as a thing you can also use to program and which may take a much more active role in your application other than just storing motionless data. Here you also can find functions. You can also find callbacks, they are only named differently – triggers.

Using great functionalities such as inheritance and partitioning, it’s pretty simple to create a partition table for each type of polymorphic relations. And you can use triggers to decide which partition to use when you want to add, update or remove a record.

Actually there isn’t anything new about it. All behaviors are well described in the postgreSQL documentation, and many forums and newsgroups show how to use it in the case of ActiveRecord. But solutions require a lot of SQL in your migrations, and what’s worse, very fragile to changes SQL. And we are used to nice, simple and intuitive code, aren’t we?

Simplify your life with pg_morph

While putting all this SQL together, an idea to make it more reusable appeared. Encouraged by a client, I ended up writing a gem to handle all those magical operations I required from my database. So, thank you Aaron for pushing me in this direction, and yes, I finally found time to finish that.

It works as follows – if you have for example the models Comment and Post and both are in polymorphic relation with Like, you can add migrations for them:

add_polymorphic_foreign_key :likes, :comments, column: :likeable
add_polymorphic_foreign_key :likes, :posts, column: :likeable

The first migration adds a new partition table named likes_comments and redirects using triggers all inserts for likes on comments for this partition. The second adds another partition – likes_posts – and updates existing triggers the way that both types of like – comment and post – are being redirected to the proper partition. The main likes table remains empty.

If after some time you find that you don’t need those relations any more, you can remove any of those structures almost as easily as they were added:

remove_polymorphic_foreign_key :likes, :comments, column: :likeable

The word ‘almost’ came out due to that fact that this migration will remove the whole partition which may contain your data. In such a case, the gem would prevent you from doing this and force you to handle that data manually, either by deleting them or moving them into a different table. Maybe it’s a handicap, but it’s better than loosing data by accident.

Not only sugar

It is a very fresh project and there are things I’d like to be handled better.

The most important thing is keeping the main table empty, which would be extremely easy if ActiveRecord wasn’t using RETURNING id statement for inserts. The thing is that this id is taken from the main table, and omitting it by the trigger causes a not very nice nil in the place of the new object’s id. That’s definitely not what we are used to and what we rely on. The bypass for that is either to allow the main table to save new records and then delete duplicates or to use a view of the main table.

The current version of pg_morph uses the first solution, but for the next one the view is planned to be used. It requires a bit more work to make it as transparent as it is right now, so if you’d like to see a new version sooner than later, don’t hesitate to contribute!

Related posts: