Sequelize JSON data type

Question

I have defined a model as

module.exports = function (sequelize, DataTypes) {
  const MyModel = sequelize.define('MyModel', {
    data: {
      type: DataTypes.JSON,
      ...
    },
    ...
  });

  return MyModel;
};

I query it using

MyModel.findAll().then(myModels => ...);

However, data field in the query result is a string, not a JSON object. How do I fix it?

Answer 1

First, you just can change DataTypes.JSON BY DataTypes.STRING for example

data: {
      type: DataTypes.STRING,
      ...
    },

Example With express :

import express, { Response } from 'express';
const app = express();
app.use(express.json());

app.get("/stores", (req, resp) => {

(async () => {
    const product = await Store.findAll()
    resp.json(stores.map(store => store.toJSON()));   //HERE
    //Display in the console.log
    console.log('All stores:', JSON.stringify(stores, null, 2)); //HERE
 })();
});

Attributes of JSON.stringify

stores : value,
null : replacer,
2 : space => Adds indentation, white space, and line break characters to the return-value JSON text to make it easier to read.

Ressource : https://sequelize.org/docs/v6/core-concepts/model-instances/#note-logging-instances

Here is How you can fix it ! Very simple !

Answer 2

It's not supported yet MySQL JSON Data Type #4727. But you can do something like this:

module.exports = function (sequelize, DataTypes) {
    const MyModel = sequelize.define('MyModel', {
        data: {
            type: DataTypes.TEXT,
            get: function () {
                return JSON.parse(this.getDataValue('value'));
            },
            set: function (value) {
                this.setDataValue('value', JSON.stringify(value));
            },
            ...
        },
        ...
    });

    return MyModel;
};

I also found this package on github sequelize-json you can give it a try if you don't want to use getters and setters.

Answer 3

Thank you ! I made further changes with the provided code above,

myColData:{
      type:DataTypes.TEXT,
      get:function(){
        return JSON.parse(this.getDataValue("myColData"));
      },
      set:function(value){
        return this.setDataValue("myColData", value);
      }
    }

I think it works fine if we don't stringify the data in the setter function If you stringify in the setter you might have to parse it again when you actully fetch the data.

Answer 4

in sequelize v6, no need to use JSON.parse() since it returns Object itself.

Answer 5

While this question is tagged MySQL, I suspect some people using MariaDB might end up here anyway, especially since MariaDB is now the default in many places. There's a difference in how the JSON data type is treated between MySQL and MariaDB, which is why sequelize implements it differently for these two DBs.

In my case I fixed this issue by explicitly switching sequelize to dialect mariadb. This requires you to install the mariadb package instead of mysql2. Afterwards my JSON columns were correctly parsed to an object.

Answer 6

No need to use getters and setters as JSON is now supported by sequelize.See sequelize api

Answer 7

The easiest solution is to use this little library called sequelize-json

Create a database and a Schema:

var Sequelize = require('sequelize'),
  JsonField = require('sequelize-json'),
  db,
  User;

db = new Sequelize('database', 'username', 'password', {
  dialect: 'sqlite',
  logging: false
});

User = db.define('User', {
  username: Sequelize.STRING,
  jsonField: JsonField(db, 'User', 'jsonField')
});

Note the parameters of JsonField, you pass your Sequelize instance, the name of the model, and the name of the field. A little awkard, but this is needed in order to add the proper hooks to the model instance.

Now, you can always treat that field as a json object:

User.create({
      username: 'Scott',
      jsonField: {
        likes: ['running', 'node']
      }
    })
    .then(function(user) {
      user.jsonField.likes.push('tests');
      return user.save();
    })
    .then(function(user) {
      expect(user.jsonField).to.be.a('object');
      expect(user.jsonField.likes).to.have.length(3);
    });

It will work with normal save commands, as well as updateAttribute commands.

Answer 8

Jalal's answer was great but didn't work for me unless I tweaked it a little. Here's what worked for me:

First: create a migration that adds the field you want as type TEXT. example - I want to add a field called address to the Stores table:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn("Stores", "address", Sequelize.TEXT);
  },

  down: (queryInterface, Sequelize) => {
    return queryInterface.removeColumn("Stores", "address");
  }
};

Next: Inside your model, add the field with getters and setters to your list (object) of fields and datatypes:

address: {
  type: DataTypes.TEXT,
    get: function() {
      return JSON.parse(this.getDataValue("address"));
    },
    set: function(value) {
      return this.setDataValue("address", JSON.stringify(value));
    }
},

So my entire model looks like this:

module.exports = (sequelize, DataTypes) => {
  const Store = sequelize.define(
    "Store",
    {
      name: DataTypes.STRING,
      isActive: DataTypes.BOOLEAN,
      address: {
        type: DataTypes.TEXT,
        get: function() {
          return JSON.parse(this.getDataValue("address"));
        },
        set: function(value) {
          return this.setDataValue("address", JSON.stringify(value));
        }
      }
    },
    {}
  );
  Store.associate = function(models) {
    // associations can be defined here
    Store.hasMany(models.Order, {
      foreignKey: "id",
      targetKey: "storeId"
    });
  };
  return Store;
};

Now you can create and retrieve records just like you would with a JSON type field in the db.

For example: const store = await Store.create({name: "Joe's corner store", address: {address1: "123 test street", city: "Los Angeles", state: "CA"}})

Some notes:

In the above code blocks...

• Replace address with the field name you want to use.
• Replace Stores with your model/table name.
• I added return on the setter, otherwise it was erroring out when trying to create a new record (the way Jalal has it).

Answer 9

JSON data types aren't supported for MySQL.

See the docs here http://docs.sequelizejs.com/en/v3/docs/models-definition/#data-types

A work around could be to use text and stringify/parse when querying it