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.

score 13 · Accepted Answer · answered Mar 07 '16 at 14:19

13

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

answered Mar 07 '16 at 14:19

esmrkbr

275
2
7

1

Ah - well spotted! I forgot mySQL does not support TEXT UNIQUE - http://stackoverflow.com/questions/14033378/make-text-column-as-unique-key – GeekyDeaks Mar 07 '16 at 14:58
Yeah. Thanks esmrkbr , it worked. and @GeekyDeaks , why have you removed your answer, that was also helpful. – Prerna Jain Mar 08 '16 at 05:12
Ok - I'll put it back in if you think it helps! :) – GeekyDeaks Mar 08 '16 at 06:46

score 11 · Answer 2 · edited May 23 '17 at 10:31

11

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;
  }
};

edited May 23 '17 at 10:31

Community

1
1

answered Mar 07 '16 at 08:06

GeekyDeaks

650
6
12

thanks @GeekyDeaks but still the same error is coming. – Prerna Jain Mar 07 '16 at 09:39
Hi @PrernaJain, with logging enabled what SQL does it output for the CREATE TABLE? – GeekyDeaks Mar 07 '16 at 13:41
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 – Prerna Jain Mar 09 '16 at 04:08
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: http://stackoverflow.com/questions/21427501/how-can-i-see-the-sql-generated-by-sequelize-js. – GeekyDeaks Mar 09 '16 at 11:03

score 0 · Answer 3 · answered Aug 01 '16 at 07:57

0

Define as string like this:

var filters = sequelize.define('filters', {
 description: { 
  type: DataTypes.STRING, 
  validate: { notEmpty: true }
 }
}

answered Aug 01 '16 at 07:57

Zvi

577
6
19

doesn't DataTypes.STRING translate to varchar? – Joserra Sep 09 '19 at 08:35

Sequelize datatype TEXT not working with mySQL

3 Answers3