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.

esmrkbr · Accepted Answer · 2016-03-07 14:19:23Z

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 7, 2016 at 14:19

esmrkbr

2752 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GeekyDeaks Over a year ago

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

Prerna Jain Over a year ago

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

GeekyDeaks Over a year ago

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

Community · Accepted Answer · 2017-05-23 10:31:02Z

12

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, 2017 at 10:31

CommunityBot

11 silver badge

answered Mar 7, 2016 at 8:06

GeekyDeaks

6701 gold badge6 silver badges12 bronze badges

4 Comments

Prerna Jain Over a year ago

thanks @GeekyDeaks but still the same error is coming.

GeekyDeaks Over a year ago

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

Prerna Jain Over a year ago

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 Over a year ago

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/….

Zvi · Accepted Answer · 2016-08-01 07:57:27Z

0

Define as string like this:

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

answered Aug 1, 2016 at 7:57

Zvi

6277 silver badges20 bronze badges

1 Comment

Joserra Over a year ago

doesn't DataTypes.STRING translate to varchar?

Collectives™ on Stack Overflow

Sequelize datatype TEXT not working with mySQL

3 Answers 3

3 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest