Rails/MySQL: better to use JSON column or serialized text for non-JSON use?

Question

With my Rails 7.1.3 app, I have have a MySQL 8 db with a table users. Each user has many translations, and the translations belong to another table languages.

I'd like to cache the results of a query counting a user's translations broken down by language, ideally stored in a way I can retrieve it as an object like

{ es: 1754, pt: 353, ru: 23, zh: 2334 }

• I'm not going to query on this data, or send it to a front-end.
• This data is just for internal use, and it is presentational. It is used on a user's profile page to show an approximate count of their work to date.
• The data is not coming from users, it's coming from the app. It's a query of the db with counts; all values are integers. It's refreshed once a day by a cron job, and it's not critical that it be accurate up-to-the minute.

Question: What's the best kind of column to store this stuff? Serialized text column, JSON column, or something else?

I've never used a JSON column before in Rails. Do I need to create a JSON schema to validate that kind of column?

Or is this the wrong use case for a JSON column? (The hash doesn't need to be sent as JSON, I just like that it's a formatted hash. But so is a serialized column.)

Answer 1

Is there a reason why this query is run on a cron?

This seems like a great case for a caching layer. Allow your Rails cache store the ability to hold on to query results, give it a sensible time to live, and automatically query for a fresh data set when no valid one exists.

Rails.cache.fetch("user_translations_for_#{user.id}", expires_in: 1.hour) do
  query_goes_here
end

More on rails caching here: https://guides.rubyonrails.org/caching_with_rails.html

Answer 2

Question: What's the best kind of column to store this stuff? Serialized text column, JSON column, or something else?

The only reason you would ever want to use a serialized text column in 2024 is if you need application level encryption and are storing the resulting encrypted hash.

Using a JSON column on the users table is a simple and somewhat enticing solution but the major cons are that it has no data normalization. While you can have a JSON schema validate whatever you want on the application level there are no guarentees on the database level about the structure of the data. See PostgreSQL anti-patterns: Unnecessary json.

Since the data is just aggregates generated from a database query I would consider using a view if your database supports it. This is the result set from a stored query and behaves like a normal table.

The advantages is that the view and it's columns are defined in the schema so you don't have to deal with raw and potentially denormalized data, since views behave like a table you can define a model and assocations. You also don't have to update the users table with new aggregates which removes the need for the cron job.

The disadvantage is that you need to change the Rails schema dumps from Ruby to SQL and that your code will be less portable.

See:

• Scenic Gem
• Postgres Views
• MySQL views