[@loopback/sequelize] Entity with JSON data is not parsed to an object when using MySQL / MariaDB

[KalleV]

Describe the bug

While testing “@loopback/sequelize” I encountered some errors retrieving entity properties stored as JSON using MySQL as the data source.

Saving JSON object data is working without modification but repositories are returning a stringified object.

As a workaround, I tried adding a getter (https://sequelize.org/docs/v6/core-concepts/getters-setters-virtuals/#getters) to all entity JSON properties that checks for a string and then calls JSON.parse.

Logs

No response

Additional information

The root cause for this seems to be on the Sequelize side based on this issue: https://github.com/sequelize/sequelize/issues/10946

Sequelize v7 added more support to the MariaDB “dialect” to accommodate some of the issues: https://github.com/sequelize/sequelize/blob/a9fd5010809366eb50fa9d6fc4bf0612a9d1d751/src/dialects/mariadb/query.js#L179

References:

• JSON parse from the Juggler ORM MySQL connector: https://github.com/loopbackio/loopback-connector-mysql/blob/edf176b09234b82796f925203ff006843e045498/lib/mysql.js#L476

Reproduction

N/A

[shubhamp-sf]

Thanks for reporting this @KalleV, It seems sequelize relies on the actual db column type for the parsing. As I tried it with postgres and as long as the datatype in db is set to character varying the json in raw format is what I get otherwise it's is stringified. It's not respecting the JSON defined in the model definition.

As a solution for data type disparity puting this parsing logic into this extension to match the behaviour of different loopback-connectors sounds cumbersome but will what best I can do to match it.

[shubhamp-sf]

Also when you say:

I tried adding a getter to all entity JSON properties...

Where did you add that in your LoopBack app?

[KalleV]

I defined the custom getter using the "sequelizeOptions" hooks. Something like this:

sequelizeOptions: {
  hooks: {
    beforeDefine: (attributes, options) => {
      // ...

      Object.keys(attributes).forEach((key) => {
        const attribute = attributes[key];
        if (
          typeof attribute !== 'string' &&
          'type' in attribute &&
          attribute.type === DataTypes.JSON
        ) {
          Object.assign(attribute, {
            get() {
              const value = this.getDataValue(key);

              if (typeof value === 'string') {
                try {
                  return JSON.parse(value);
                } catch (error) {
                  /* log error */
                  return null;
                }
              }
              return value;
            }
          });
        }
      });
    }
  },
  // ...