Add MariaDB Constraint CHECK for JSON column using sequelize

Question

I'm using MariaDB v10.2.11, and since v10.2.7 you can have JSON columns with a constraint that validates the JSON value of a row.

I want to add a that constraint through a Sequelize migration, but I don't know how to.

MariaDB JSON Documentation:

• https://mariadb.com/resources/blog/json-mariadb-102
• https://mariadb.com/kb/en/library/json-data-type/

Answer 1

I'm sharing the solution I came in this issue (https://github.com/sequelize/sequelize/issues/8767) (it's mine).

module.exports = {
  up: (queryInterface, Sequelize) =>
    queryInterface
      .createTable('tableName', {
        // id, other columns
        column1: Sequelize.JSON,
        // more columns
      })
      .then(() =>
        queryInterface.addConstraint('tableName', ['column1'], {
          type: 'check',
          where: {
            column1: Sequelize.literal('JSON_VALID(column1)'),
          },
          name: 'check_column1_has_valid_json',
        }),
      ),
  down: (queryInterface) => queryInterface.dropTable('tableName'),
};

Since createTable and addConstraint returns a promise, it's possible to chain multiple operations in one migration :)