How to set collation to model?

[eyalcohen4]

Hey!
I am using sequelize and sequelize-cli with mysql.
Trying to create a table, i stuck with a problem - i cant find where to set collation to specific model.
When instantiate sequelize object i did defined collation and charset, but it seem to dosent respect that.

// model
module.exports = (sequelize, DataTypes) => {
  let songs = sequelize.define('songs', {
    name: DataTypes.STRING,
    link: DataTypes.STRING,
    artist: DataTypes.STRING,
    lyrics: DataTypes.TEXT,
    writer: DataTypes.STRING,
    composer: DataTypes.STRING
  });

  return songs;
};

// sequelize.js
let sequelize = new Sequelize('database', {
    host: 'localhost',
    dialect: 'mysql',
    dialectOptions: {
      charset: 'utf8',
      collate: 'utf8_general_ci',
    },
    pool: {
      max: 5,
      min: 0,
      idle: 10000
    },
  });
});

What do you expect to happen?

I want to set collation to 'utf-8'.

What is actually happening?

the collation is by default 'latin1_swedish_ci' for each column that created in table,

Dialect: mysql
Sequelize version: 3.28.x

[MrVoler]

sequelize.define('table', { column: Sequelize.STRING + ' CHARSET utf8 COLLATE utf8_unicode_ci' })

[Talbot3]

this seem is black magic,may be add chartset is right way. @MrVoler

[ventr1x]

This feels extremely hacky and it has to be done for every single field.
Is there really no way in sequelize to set charset and/or collation?

[tvl83]

For anyone coming here looking for a fix https://stackoverflow.com/questions/41666820/node-sequelize-mysql-how-to-define-collation-and-charset-to-models explains how to do it on a db level.

var sequelize = new Sequelize('database', 'username', 'password', {
  define: {
    charset: 'utf8',
    collate: 'utf8_general_ci'
  }
})

[wladikpasika]

Hey! in documentation i found this

define: { underscored: false freezeTableName: false, charset: 'utf8', dialectOptions: { collate: 'utf8_general_ci' }, timestamps: true },
link: http://docs.sequelizejs.com/manual/installation/usage.html - it's work for me
{ dialect: 'mysql', define: { charset: 'utf8', dialectOptions: { collate: 'utf8_general_ci' }, } }

[darkotuning]

`var basename = path.basename(__filename);
var env = process.env.NODE_ENV || 'development';
var config = require(__dirname + '/../config/config.json')[env];
var db = {};

if (config.use_env_variable) {
var sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
var sequelize = new Sequelize(config.database, config.username, config.password, config, {
define: {
underscored: false,
freezeTableName: false,
charset: 'utf8',
dialectOptions: {
collate: 'utf8_general_ci'
},
timestamps: true
},
});
}`

When i set collate like on example above, this does not work for me, can anyone help me? Thank you

[georgesks]

Hi !, copy this in your config.json

"development": {
  "username": "root",
  "password": "",
  "database": "db_name",
  "host": "127.0.0.1",
  "dialect": "mysql",
  "define": { "charset": "utf8", "dialectOptions": { "collate": "utf8_general_ci" } }
 }

[tzs007]

sequelize.define('table', { column: Sequelize.STRING + ' CHARSET utf8 COLLATE utf8_unicode_ci' })

it does not work for me

[papb]

@tzs007 What about the suggestion by tvl83 above? Does it work for you?

[lhlpc]

The problem with these solutions is that they give a database wise configuration. :(
In my case, such as the issue's, I would like to configure only a specific model to have charset as charset: 'utf8mb4' and collate: 'utf8_general_ci'.

Can anybody help?

[papb]

Ah, I understand. Thanks! I will reopen this.

[nodejh]

Infact, define.collate is default collect of the table. Table fields will use table's collect as default collect.

The dialectOptions.collate don't not work if dialect is mysql. And node-mysql2 don't support collect attribute: sidorares/node-mysql2#1060, it only accepts charset.

var sequelize = new Sequelize('database', 'username', 'password', {
  define: {
    charset: 'utf8',
    collate: 'utf8_general_ci', // this work
  },
   dialectOptions: {
          collate: 'utf8_general_ci', // not work
     },
})

[armellarcier]

I have a problem with this : I need to specify a collation on a single column, otherwise the sync command will fail creating my table since the column references another, already migrated column that has a different collation.

[hAbd0u]

sequelize.define('table', { column: Sequelize.STRING + ' CHARSET utf8 COLLATE utf8_unicode_ci' })

it does not work for me

It does for me, can you post your code?

[garryfreeman]

I spent a lot of time to figure out how to make only one column case sensitive, and finally I found a solution, here is the solution:

User.init(
  {
    id: {
      type: DataTypes.STRING({ binary: true }),
      primaryKey: true,
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
  },
  {
    sequelize: database,
    tableName: 'users',
    timestamps: false,
  }
);

P.S. i'm using class Models, but DataTypes.STRING({ binary: true }) must work anyway

[yogithesymbian]

i have set

define : {
      createdAt: "created_at",
      updatedAt: "updated_at",
      deletedAt: "deleted_at",
      charset: 'utf8',
      collate: 'utf8_general_ci', // so i need to change to `utf8mb4_0900_ai_ci` ? 
    },

but get an error like this

    error: SequelizeDatabaseError: Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='

i use define for my view ( not a table ) , mostly i force / override set on view query with

COLLATE utf8mb4_0900_ai_ci as nama_raja,  // and its solved