How to handle columns with different encodings in Sequelize?

Question

I am facing problems with storage and retrieval of emojis in MySQL 5.7 with Sequelize (v4/v5/v6)? I had to change the encoding of the particular column from utf8 to utf8mb4 to make MySQL store the data as expected but now I am not able to operate with the data using Sequelize. It seems to be defaulting to utf8 always. Things that I have tried so far:

let sequelize = new Sequelize(mysql_db, mysql_user, mysql_password, {
    host: mysql_host,
    dialect: 'mysql',
    pool: {
        max: 5,
        min: 0,
        idle: 10000
    },
    charset: 'utf8mb4',
    omitNull: true,
});
******************************************************************************
let sequelize = new Sequelize(mysql_db, mysql_user, mysql_password, {
    host: mysql_host,
    dialect: 'mysql',
    pool: {
        max: 5,
        min: 0,
        idle: 10000
    },
    charset: 'utf8mb4',
    dialectOptions: {
        collate: 'utf8mb4_unicode_ci'
    },
    omitNull: true,
});
*******************************************************************************
let User = sequelize.define(
    'user',
    {
        id: {
            type: Sequelize.BIGINT,
            field: 'id',
            primaryKey: true
        },
        name: {
            type: Sequelize.STRING,
            field: 'name'
        },
    },
    {
        freezeTableName: true,
        timestamps: false,
        charset: 'utf8mb4',
        dialectOptions: {
            collate: 'utf8mb4_unicode_ci'
        }
    }
);

while none of these methods worked in Sequelize (v4/v5/v6), I noticed that running

await sequelize.query('SET CHARSET utf8mb4')

right before running the actual query on the name column above leads to proper storage and retrieval of names containing emojis. I do not want to go ahead with this solution as it is more of a hack. Instead, I want to understand how Sequelize’s default charset can be overriden. I have tried similar examples using https://github.com/mysqljs/mysql and https://github.com/sidorares/node-mysql2 and I was able to override the default charset to utf8mb4 in both the cases but I am at a loss with Sequelize. The official documentation talks very less about charset overrides and the sample code given in the docs is the same as what I have tried above.

How to handle columns with different encodings in Sequelize?

0 Answers0