Node, Sequelize, Mysql - How to define collation and charset to models?

Question

Im using sequelize /w node and node-mysql.

I create models using the sequelize-cli, and this is the result:

'use strict';
module.exports = function(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;
};

I want to be able to define collation and charset to each property of the model. the default collation is 'latin1_swedish_ci', and i need it in 'utf-8'.

Anyone? Tnx

score 46 · Accepted Answer · edited Sep 20 '18 at 09:12

46

In the part where u define sequelize

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

For Table level Changing

sequelize.define('songs', {
  name: DataTypes.STRING,
  link: DataTypes.STRING,
  artist: DataTypes.STRING,
  lyrics: DataTypes.TEXT,
  writer: DataTypes.STRING,
  composer: DataTypes.STRING
}, {
  charset: 'utf8',
  collate: 'utf8_unicode_ci'
});

edited Sep 20 '18 at 09:12

Alex K

6,737
9
41
63

answered Jan 15 '17 at 22:26

Santhosh S Kashyap

973
2
13
19

1

It seems that there's no easy way to set the charset on a per-column basis, which is an incredible pain. – jlh Jun 22 '17 at 15:36
2

@jlh there's a comment in this issue which accomplishes what you're looking for: https://github.com/sequelize/sequelize/issues/7110 – Gianni Carlo Aug 01 '17 at 06:56
@GianniCarlo Thanks! But to me this feels more like a hacky SQL injection. Not what I want in code that I'd like to be solid and future proof. But for some use cases this might be acceptable. – jlh Aug 02 '17 at 17:12
2

apparently, collate is illegal and it should simply be charset – Marc Nov 03 '18 at 18:34
FYI, anything you put in the `define` object will be used as defaults when calling model.init() – Adam Fowler Mar 10 '22 at 23:27
You should use utf8mb4 and not utf8, see the following thread: https://stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql – Dor Meiri Jul 25 '22 at 21:00
Does setting charset and collate in Sequelize definition work for the tables already existing in the database also? – Sandeep Parashar Dec 01 '22 at 07:31

score 0 · Answer 2 · answered Apr 06 '20 at 23:32

is very simple to add utf-8 just go to any model that you have and do this for example (i edit your code):

module.exports = function(sequelize, DataTypes) {
  let songs = sequelize.define('songs', {
    name: {DataTypes.STRING,allowNull : false},
    link: {DataTypes.STRING,allowNull : false},
    artist: {DataTypes.STRING,allowNull : false},
    lyrics: {DataTypes.TEXT,allowNull : false},
    writer: {DataTypes.STRING,allowNull : false},
    composer: {DataTypes.STRING,allowNull : false}
  }, {
charset: 'utf8', /* i add this two ligne here for generate the table with collation  = 'utf8_general_ci' test it and tell me ? */
collate: 'utf8_general_ci'


});

  return songs;
};

Node, Sequelize, Mysql - How to define collation and charset to models?

2 Answers2

Linked