Field with type Sequelize.JSON returns string when MySQL dialect is used for a MariaDB database

[kishmiryan-karlen]

What are you doing?

When querying a model, which has a field with type sequelize.JSON, it returns JSON string instead of parsed JS object for that field.

// dbService.js
// Connecting to DB, params are coming from config file
export const databaseService = new Sequelize(database, user, password, {
  host,
  port,
  dialect: 'mysql',
  logging,
  operatorsAliases: Sequelize.Op,
  define: {
    freezeTableName: true,
    charset,
    collate,
  },
});

// models/index.js
// Creating the models
import fs from 'fs';
import path from 'path';
import sequelize from 'sequelize';
import { databaseService } from 'utils/database/dbService';

const basename = path.basename(__filename);
const db = {};

fs.readdirSync(__dirname)
  .filter((file) => {
    return !file.startsWith('.') && file !== basename && file !== 'index.js' && file.slice(-3) === '.js';
  })
  .forEach((file) => {
    const fileName = path.join(__dirname, file);
    const model = require(fileName).default(databaseService, sequelize);
    db[model.name] = model;
  });

Object.keys(db).forEach((modelName) => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = databaseService;
export default db;

// models/Test.js
// Creating a Test model
export default (dbService, sequelize) => {
  const Test = dbService.define('Test', {
    id: {
      type: sequelize.UUID,
      defaultValue: sequelize.UUIDV1,
      primaryKey: true,
    },

    data: {
      type: sequelize.JSON,
    },
  });

  return Test;
};

// test.js
// Usage in test script
import './models';
import { databaseService } from './dbService';

const newTest = await db.models.Test.create({
  data: { x: 1 }
});
console.log('check 1', typeof newTest.data);  // check1 object

const test = await db.models.Test.findOne({ id: newTest.id });
console.log('check 2', typeof test.data);  // check 2 string
console.log('check 3', typeof test.get('data')); // check 3 string

To Reproduce
Steps to reproduce the behavior:

1. As you can see above, I'm connecting to my DB and creating model Test, which has two fields: id and data. The latter one is of type sequelize.JSON.
2. Then in my test script, I create an instance of Test model, providing JS object for data field. On the next line when I print type of data, it prints correctly object.
3. However, when I query that object using findOne (no difference how you query), the returned object's data field is string now.

What do you expect to happen?

I expect to get a parsed JS object out of my JSON field.

What is actually happening?

I'm getting JSON string instead of object.

Environment

Dialect:

• mysql
• postgres
• sqlite
• mssql
• any

Dialect library version: mysql2 - 1.6.4
Database version: mysql Ver 15.1 Distrib 10.3.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 (The DB is run in Docker using official MariaDB image)
Sequelize version: 4.41.2
Node Version: v10.15.2
OS: macOS Mojave, 10.14.3 (18D109)
Tested with latest release:

• No
• Yes, specify that version:

[kishmiryan-karlen]

Also, I found this test in your repo https://github.com/sequelize/sequelize/blob/master/test/integration/model/json.test.js#L771, where you get the JS object using get() method, however that also returns string for me.

And in your code here https://github.com/sequelize/sequelize/blob/master/lib/data-types.js#L514, I couldn't find anywhere, where do you parse the JSON string into an object.

[vvscode]

I wonder - if about the idea - does that types only for writing? or should work for read also?

Is there a documentation about inner interfaces (Like abstract type and so on)?

[kishmiryan-karlen]

@sushantdhiman any feedback on this?

[misos1]

I wonder - if about the idea - does that types only for writing? or should work for read also?

It should JSON.parse it like for example mariadb dialect does.

[vvscode]

@misos1 can you provide a link to a code which does this?

[misos1]

Check node_modules/sequelize/lib/dialects/mariadb/query.js function handleJsonSelectQuery . I was able to find JSON.parse also in sqlite dialect but not in others. Sequelize.JSON is new type so maybe this was overlooked or probably other dialects have connectors which directly support JSON type (except mysql2 probably).

[vvscode]

@kishmiryan-karlen

http://docs.sequelizejs.com/manual/dialects.html#mariadb

const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mariadb', // <--- this is the key (mysql doesn't contain .parse
  dialectOptions: {connectTimeout: 1000} // mariadb connector option
})

[kishmiryan-karlen]

@vvscode Looks like it's a V5 feature, because on V4 I'm getting this:
Error: The dialect mariadb is not supported. Supported dialects: mssql, mysql, postgres, and sqlite.

[ddolcimascolo]

Hi @kishmiryan-karlen,

Just asking, is you table created by sequelize? Cause I don't see a call to sync in your code sample above. If not, any chance your column in the DB isn't a JSON column?
We're upgrading to sequelize 5 here but have used v4 for more than a year with Mysql JSON columns a lot, and parsing/serialization works flawlessly...

David

[misos1]

@ddolcimascolo Your tables were probably not created by sequelize v4 nor v5 and I doubt that older version of sequelize created JSON columns. Sequelize will for some reason create mysql LONGTEXT type in db when you use Sequelize.JSON. If you already have mysql JSON column in db then I do not doubt it is working because seems mysql2 connector supports JSON columns and I saw calls to JSON.parse in sources so it will directly return JS object to sequelize and sequelize in this case does not must parse string.

[ddolcimascolo]

@misos1

You got me puzzled... All my Mysql tables are created through migration scripts (applied with https://github.com/sequelize/umzug) using sequelize query interface, here's one for your reference:

'use strict';

module.exports = {
  up: (qi, DataTypes) => qi.createTable('items', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    payload: DataTypes.JSON
  })),
  down: qi => qi.dropTable('items')
};

DataTypes is Sequelize.DataTypes in the above scripts, and qi is sequelize.getQueryInterface().

The resulting table indeed has a JSON column. Mysql version is 5.7.26

[misos1]

Ok seems this problem is here only when using mysql dialect with mariadb database, not with mysql database.
https://mariadb.com/kb/en/library/json-data-type/

[ddolcimascolo]

OK, thus the new mariadb dialect...

This issue can probably be closed then, if everyone agrees.

[misos1]

This is not so simple. Mariadb dialect is still new and has bugs which prevents to use it as direct replacement in already existing projects based on mysql dialect. It was added in sequelize v5. This bug should be easy to fix. Sequelize or mysql dialect could detect that it got string instead of JS object and simply parse it.

https://github.com/sequelize/sequelize/issues?utf8=%E2%9C%93&q=is%3Aissue+is%3Aopen+mariadb

[ddolcimascolo]

Yeah, I got your point but on the other hand I believe MariaDB wasn't supported on v4 (or was it?)

[misos1]

Yes it was and even in sequelize v3 and probably in even older versions. Through mysql dialect.

https://sequelize.readthedocs.io/en/v3/
https://stackoverflow.com/questions/35796963/sequelize-error-with-mariadb

[kishmiryan-karlen]

Hi @ddolcimascolo,
Sorry for my late reply. I'm creating my tables using migrations (like in your script above), thus there's no sync in my code.

@misos1 agree, I also found docs on JSON support in older versions of Sequelize.

Mariadb dialect is still new and has bugs which prevents to use it as direct replacement in already existing projects based on mysql dialect.

@ddolcimascolo, does this mean we are going to fallback again to getter/setter for JSON types, or we can expect some fix on at least Sequelize v4? What do you think?

[ddolcimascolo]

Hi sorry for the late reply.
I'm not a maintainer actually, just a contributor trying to help the community. We need input from the core team about the state of MariaDB support in v4, as understanding varies...

Is there anything preventing you from upgrading to v5 where MariaDB is officially supported?

David

[kishmiryan-karlen]

Hey,
Yes, there are some breaking changes, and I can't handle them now. I will ping here as soon as I migrate and check. Thanks.

[papb]

OK, thus the new mariadb dialect...

This issue can probably be closed then, if everyone agrees.

I will close the issue, but if anyone still has problems feel free to ping me.

[misos1]

@papb But as I wrote mysql dialect is still superior to new mariadb dialect even when using with mariadb database. So it has sense to fix this little bug. Just convert that string into js object so mysql dialect will behave in same way for mariadb database as for mysql database.

[papb]

@misos1 Ah, I didn't know that. So MySQL dialect + MariaDB database yields a string instead of an object...

Can you please clarify:

• What is the current behavior with MySQL dialect + MySQL database? Does it give a string or an object?
• What is the current behavior with MariaDB dialect + MariaDB database? Does it give a string or an object?

[misos1]

@papb

• MySQL dialect + MariaDB database - string
• MySQL dialect + MySQL database - object
• MariaDB dialect + MariaDB database - object

[papb]

@misos1 Very interesting. So this is a "pseudo-bug" I guess because in theory the mixup shouldn't even work at all... So I am not sure @sushantdhiman would agree with this change.

Also, just for my information, can you list a few reasons for what you said:

mysql dialect is still superior to new mariadb dialect even when using with mariadb database

I'm asking because I am not familiar with those dialects... I would never expect this if you asked me.

[misos1]

@papb Why you think it should not work? This is not like using postgresql database with mysql dialect.

https://mariadb.org/about/

It is an enhanced, drop-in replacement for MySQL.

If it would not work then why was used mysql dialect for mariadb database before sequelize v5?

Using directly mariadb dialect with mariadb driver with mariadb database can have some advantages. I saw somewhere this combination should be faster but still as mariadb database is supposed to be drop-in replacement for mysql then it should and it is working also with mysql2 driver and mysql dialect. Unfortunately mariadb dialect does not feels mature enough (I do not mean mariadb driver but sequelize side).

Also, just for my information, can you list a few reasons for what you said:

There are for example these (and probably more):

#10629
#10921

More which also seems specific to mariadb dialect:

#11041
#10910
#10848
#6980

[papb]

@misos1 Thanks for the detailed explanation! I am convinced.

[omegascorp]

Looks the issue is only partially fixed in mariadb driver.

Where you read records from MariaDB via mariadb driver, Sequelize provides JSON values as objects, but if you use include, subitem JSON fields are provided as strings.

Here is a code snippet:

const User = dbService.define(
  'User',
  {
    id: {
      type: sequelize.INTEGER,
      primaryKey: true,
    },
    data: {
      type: sequelize.JSON,
      defaultValue: '',
    },
  },
);

const Project = dbService.define(
  'Project',
  {
    id: {
      type: sequelize.INTEGER,
      primaryKey: true,
    },
    data: {
      type: sequelize.JSON,
      defaultValue: '',
    },
  },
);

Project.belongsTo(User);
User.hasMany(Project, {
  foreignKey: 'userId',
  as: 'projects',
});

(async () => {
  const users = await User.findAll({
    include: [
      {
        model: Project,
        as: 'projects',
      },
    ],
    attributes: ['id', 'data', 'projects.id', 'projects.data']
  });
  console.info(typeof users[0].data); // object
  console.info(typeof users[0].projects[0].data); // string
})();

See a working demo here: https://github.com/omegascorp/sequelize-mariadb-json-test

[aaron-syed]

Hey, sorry to revive this, but do we got any progress on this matter?
I was trying a JSON field on a Mysql 5.7.29 ( Ubuntu 18 ) with Sequelize 5.21.7 and has no luck with it.

When I try to update a model that has a JSON field, the field is still empty after I save it, even though I got no errors. The process goes on smoothly.
Kinda frustrating.

[miladmeidanshahi]

@omegascorp I have the same problem when including some model, JSON fields convert to strings thas so bad.

[aaron-syed]

Sorry, I didn't update ya guys.
Turned out I was the blame.
the fact is that I was trying to update the wrong model. Shame on me.
As soon as i switched to the right one.. ta dah! it worked.
And it is still working like a charm ever since!

[medington]

@papb I see you re-opened this issue in August 2019. Is there any expectation that a future release of Sequelize will seamlessly provide the required JSON.parse() when using the MySQL dialect with a MariaDB?

Also, if there is a suggested method to patch / workaround the issue by adding code into the model or into an event handler that would be very much appreciated. Thanks!

[miladmeidanshahi]

@medington Apparently there is no alternative yet

[github-actions]

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[tobiasmuecksch]

Is there anything new on this issue? I'm also stuck with the same problem.

[diewolke9]

Struggling with the same here. One workaround would be to create getters and setters, but they will not trigger if the object is a nested child of a find() with associations.

[alexstep]

Same problem

[IonelLupu]

Still a problem. I am using MySQL database with MySQL dialect. I also use sqluelize-typescript:

import { Role } from './Role'
import { BelongsTo, Column, CreatedAt, DataType, Model, PrimaryKey, Table, UpdatedAt } from 'sequelize-typescript'

@Table({tableName: 'role_permission'})
export class RolePermission extends Model {
	@PrimaryKey
	@Column({type: DataType.UUID, defaultValue: DataType.UUIDV4})
	declare id: string

	@Column
	permission: string

	@Column({
		type: DataType.JSON,
		defaultValue: [],
	})
	scopes: string[]

	@CreatedAt
	created_at: Date

	@UpdatedAt
	updated_at: Date

	@BelongsTo(() => Role, {foreignKey: 'role_id'})
	role: Role
}

I had to do this instead:

	@Column({
		type: DataType.TEXT,
		defaultValue: [],
		get: function(this: RolePermission) {
			return JSON.parse(this.getDataValue('scopes') as unknown as string)
		}
	})
	scopes: string[]

Edit
The above works when you want to select the data but I just found out it doesn't work when I want to save the data. I added these:

	@Column({
		type: DataType.TEXT,
		defaultValue: '[]',
		get: function(this: RolePermission) {
			const value = this.getDataValue('scopes') as string
                        console.log('getter', value)
			return value ? JSON.parse(value) : []
		},
		set: function(this: RolePermission, value: string[]) {
                        console.log('setter', value)
			return value ? JSON.stringify(value) : '[]'
		}
	})
	scopes: string[]

But now I get Field 'scopes' doesn't have a default value. This is incredible. How can it not have a default value when I can clearly see the getter and the setter called?

[parsadoc]

In my case, I used MySql DB on the MariaDB driver!
you can change sequelize dialect to MariaDB

const sequelize = new Sequelize("myDB", "root", "myPass", {
  host: "localhost",
  dialect: "mariadb",
});

also you need to install mariadb package
npm install mariadb --save
or
yarn add mariadb --save

it worked for me.

[adamleemiller]

Either there are still some problems here or I am doing something completely incorrect.

I am using Sequelize 6.37.0. I have a column in a database table named extra which is of type LONGTEXT. The underlying database engine is MariaDB 10.11.7 running via an AWS RDS instance which does NOT support the JSON native column type.

When I create a new row, the row is created successfully and the JSON object is stored correctly. However, if I update the row with the same data, the object is then stringified. For example, it is going from {"country_codes":["US"]} to "{\"country_codes\":[\"US\"]}" which is not what I am expecting. I have tried using a getter and a setter but that causes other issues.

Scenario 1:

    extra: {
      allowNull: false,
      type: DataTypes.JSON
    },

Row Creation: OK
Row Update: Automatically stringified
Row Get: extra column is a string

Scenario 2: Let’s try to fix the Row Get from returning a string to instead, returning an object.

    extra: {
      allowNull: false,
      type: DataTypes.JSON,
      get: function () {
        return JSON.parse(this.getDataValue('extra'))
      }
    },

Row Creation: Fails - uncaughtException: \“[object Object]\” is not valid JSON
Row Update: Cannot update since creation did not work
Row Get: Cannot get since creation did not work

Scenario 3: Remove the get and try to fix the update row from storing a stringified object

    extra: {
      allowNull: false,
      type: DataTypes.JSON,
      set: function (value) {
        this.setDataValue('extra', JSON.parse(value))
      }
    },

Row Creation: Fails - SyntaxError: \“[object Object]\” is not valid JSON
Row Update: Cannot update since creation did not work
Row Get: Cannot get since creation did not work

Scenario 4: Change DataType to TEXT

    extra: {
      allowNull: false,
      type: DataTypes.TEXT
    },

Row Creation: Fails - SequelizeValidationError: string violation: extra cannot be an array or an object
Row Update: Cannot update since creation did not work
Row Get: Cannot get since creation did not work

Basically what I am after is when I create a new row, the extra column is stored as an object and when I update the row, the extra column should stay an object whether I pass new data or existing data (which is always a JSON object). When I find or findAll, the extra column should be a parsed object, not a string.

The following works to create, update and get however, because the type is TEXT which means I cannot search the JSON object in a WHERE clause:

extra: {
  allowNull: false,
  defaultValue: '{}',
  type: DataTypes.TEXT,
  get: function () {
    const value = String(this.getDataValue('extra'))

    return value ? JSON.parse(value) : []
  },
  set: function(value) {
    const extra = value ? JSON.stringify(value) : '{}'

    this.setDataValue('extra', extra)
  }
}, 

Query

      const record = await db.models.Institution.findOne({
        where: {
          extra: {
            institution_id: this.request.body.extra.institution_id
          }
        }
      })

Error

Error: Invalid value { institution_id: 'ins_27' }

If I change the data type to JSON, the query and where clause work fine however now the data in the extra column is stringified again.

Any help is greatly appreciated.