Use update_all for store in Rails

Question

In one of our models we're using ActiveRecord's store like so:

store :settings, accessors: %i[enabled unit_of_distance], coder: JSON

This allows us to access those accessors like normal columns e.g.

Model.first.enabled => true
Model.first.unit_of_distance => 'miles'

If I want to update all the models like so:

Model.update_all(unit_of_distance: 'miles')

I get an error:

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'model.unit_of_distance' in 'field list'

Prefixing it with settings like so will fix it, but will then override the other accessors in settings (which isn't what we want to happen at all):

Model.update_all(settings: { unit_of_distance: 'miles' })

However manually looping them does work:

Model.all.each { |c| c.update(unit_of_distance: 'miles') }

But this is likely not as efficient... is it possible to use update_all with the store accessor without having to do a loop?

Answer 1

A leaky abstraction

ActiveRecord tries to cross the chasm between the table based world of your database and objects in your application and stumbles like any ORM. update_all is one of the methods that operates in the SQL world instead of on objects like update does. While it can take a hash and translate it into simple SQL that doesn't apply to structured data types like JSON.

ActiveRecord only somewhat supports JSON columns - the types are supplied by the database drivers which handle conversion to and from the database to Ruby types but you can't work with the data inside with the ActiveRecord query interface as the JSON operators/functions are very vendor specific.

Having accessors in your model doesn't magically let you update the contents of a JSON object in the database world. Even less so when what you have stored isn't actually a JSON object.

The foot gun

store is an old hack from the dark days before RDMBS systems had structured data types such a JSON and JSONB. It stores serialized data as a string (in a text or varchar type column) and then deserializes it in the application when models are hydrated with data from the database.

If you use store with a native JSON column you're storing an escaped string instead of JSON objects. So you have potentially shot yourself in the foot by filling the database with garbage.

It comes with the following warning:

NOTE: If you are using structured database data types (e.g. PostgreSQL hstore/json, or MySQL 5.7+ json) there is no need for the serialization provided by .store. Simply use .store_accessor instead to generate the accessor methods.

Do not use it outside of legacy applications.

The fix

A first step towards fixing your issue would be to fix any existing data in the table and convert the strings stored into actual JSON which you can work with in the database. You can either do this by using update_all and the database JSON functions or by looping through the data in Ruby if performance is not an issue.

IMHO if your data is structured enough that you're creating accessors for it in the model it isn't unstructured enough to warrant the use of JSON. I would just declare columns and fill them by looping through the junk data once. That gives you normalized data that you can work with in a sane way and you're avoiding an anti-pattern.

If you want to stick with putting what should be columns into a JSON column you would need to use the MySQL specific JSON function to update the data.

Model.update_all('model.settings = JSON_MERGE_PATCH(
  model.settings, \'{"unit_of_distance": "miles" }\'
)')