Sequelize datatype TEXT not working with mySQL

Question

I am using sequelize ORM with mySQL database.

I have a model with attribute type TEXT as :

description: {
            type: Sequelize.TEXT,
            unique: true
        },

When I am trying to create table for the corresponding model, its giving an error message as :

Unhandled rejection SequelizeDatabaseError: ER_BLOB_KEY_WITHOUT_LENGTH: BLOB/TEXT column 'description' used in key specification without a key length

This worked fine when used with postgreSQL. Possible reason for this error which i could think of can be that mySQL doesn't support TEXT datatype and therefore, i have to specify it as LONGTEXT.

If I am thinking correct or is there some other reason for the same, if someone can help.

Solution 1

You are using unique: true with data-type TEXT. You can not use it. Do you delete unique: true and try again?

Solution 2

esmrkbr is correct, mySQL does not accept UNIQUE KEY on a TEXT field, you need to use VARCHAR instead (see: make text column as unique key). That being said, depending on the Database being used, you may need to explicitly specify a size for a TEXT (or BLOB) type. The documentation (http://docs.sequelizejs.com/en/latest/api/datatypes/) is pretty terse on this point and other than a link to the code, currently only has the following information:

An (un)limited length text column. Available lengths: tiny, medium, long

You can pass the size as a string argument to the type. For example, to have it defined as LONGTEXT you will need:

description: {
            type: Sequelize.TEXT('long')
        },

The code in lib/data-types.js has the following mapping to SQL for TEXT (there is a similar one for BLOB also):

TEXT.prototype.toSql = function() {
  switch (this._length.toLowerCase()) {
  case 'tiny':
    return 'TINYTEXT';
  case 'medium':
    return 'MEDIUMTEXT';
  case 'long':
    return 'LONGTEXT';
  default:
    return this.key;
  }
};

Comments

[Prerna Jain]

thanks @GeekyDeaks but still the same error is coming.

[GeekyDeaks]

Hi @PrernaJain, with logging enabled what SQL does it output for the CREATE TABLE?

[GeekyDeaks]

Ah - well spotted! I forgot mySQL does not support TEXT UNIQUE - stackoverflow.com/questions/14033378/…

[Prerna Jain]

Yeah. Thanks esmrkbr , it worked. and @GeekyDeaks , why have you removed your answer, that was also helpful.

[GeekyDeaks]

Ok - I'll put it back in if you think it helps! :)

[Prerna Jain]

thanks for the well explanatory answer. and yea i did not understand what you are asking, can you just explain it a bit more. @GeekyDeaks

[GeekyDeaks]

Hi @PrernaJain, by default Sequelize should output all SQL generated to console.log() so you can check it's doing the right thing. But if nothing is output, take a look at the following Q/A for some hints on how to get it to work: stackoverflow.com/questions/21427501/….

[Joserra]

doesn't DataTypes.STRING translate to varchar?