January 19, 2023

Storing objects in SQL databases

Sometimes it's a good idea to store objects in the database, even if you're using a relational database. In fact, nowadays, most SQL databases support objects.

For example:

Postgres - You can use jsonb columns
Mysql - You can use JSON columns

And not only does it store objects, but the best part is also that it is possible to query using some attribute of the object. Let's see an example using MySQL.

Creating a simple table for our example, of course, using Rails migration for that.

create_table :people do |t|
  t.string :name
  t.string :city
  t.json :preferences # JSON column here
end

The idea for the preferences column is to have this structure:

{
   "preferences": [
       {"name": "Name example", "value": "Value example"},
       ...
   ]
}

Nothing special is needed in the model:

class Person < ApplicationRecord
end

You can insert data like this:

Person.create(name: "Mateus", city: "Porto Alegre", preferences: [{name: "allow_email_marketing", value: "true"}])

Pretty simple, right?

Queries

One of the best parts of this approach is being able to query using the object's attributes, for example, let's select all people with "allow_email_marketing = true".

Person
  .where("preferences->>'$[*].name' like json_array('allow_email_marketing')")
  .where("preferences->>'$[*].value' like json_array('true')")

# SELECT `people`.* FROM `people` WHERE (preferences->>'$[*].name' like json_array('allow_email_marketing')) AND (preferences->>'$[*].value' like json_array('true'))

This example is simple, you can find more information here.

Store Model GEM

When you have a JSON as the data type in MySQL, ActiveRecord translates it to a Hash object in Ruby. I don't like using Hash in this situation, I always prefer to enforce the structure, imagine if, at some point in the code, you add a new preference using a wrong structure, it will be a mess.

To solve it, we'll use the very good gem Store Model:

StoreModel gem allows you to wrap JSON-backed DB columns with ActiveModel-like classes.

https://github.com/DmitryTsepelev/store_model

Storing Objects in relational databases is very useful for many situations. Makes conscious use of it, in the end, you're using a RELATIONAL database.

That's all for today, thanks for reading :).

Mateus Nava

Storing objects in SQL databases

Queries

Store Model GEM

https://github.com/DmitryTsepelev/store_model