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

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:

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.
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.
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:

About this issue

Original URL
State: closed
Created 5 years ago
Reactions: 6
Comments: 38 (10 by maintainers)

Most upvoted comments

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

+13

omegascorp on Sep 23, 2019

@papb

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

misos1 on Aug 1, 2019

@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.

misos1 on Aug 1, 2019

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.

parsadoc on Jan 3, 2023

@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):

https://github.com/sequelize/sequelize/issues/10629 https://github.com/sequelize/sequelize/issues/10921

More which also seems specific to mariadb dialect:

https://github.com/sequelize/sequelize/issues/11041 https://github.com/sequelize/sequelize/issues/10910 https://github.com/sequelize/sequelize/issues/10848 https://github.com/sequelize/sequelize/issues/6980

misos1 on Aug 2, 2019

@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
})

vvscode on May 24, 2019

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).

misos1 on May 23, 2019

@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?

papb on Aug 1, 2019

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=✓&q=is%3Aissue+is%3Aopen+mariadb

misos1 on Jun 1, 2019

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/

misos1 on Jun 1, 2019