Why does Sequelize create a database column named 'text' instead of named after the model attibute

Question

In an attempt to make my code more readable, I created consts to store commonly used column definitions. When I chose to use these constants, it had a bizarre outcome of the backend database having a 'text' column when there was no attribute named 'text', and it did not store data correctly.

I would like to understand why.

The following were my constants:

const StringNullable     = { type: Sequelize.DataTypes.STRING };// NOTE defaults are set by GraphQL datasources.
const StringRequired     = { type: Sequelize.DataTypes.STRING, allowNull: false };

And here is what I thought would be two equivalient ways to define a Sequelize Model:

const messages = sequelize.define('messages',{
    deviceType:          { type: Sequelize.DataTypes.STRING },
    protocol:            { type: Sequelize.DataTypes.STRING },
    source:              { type: Sequelize.DataTypes.STRING},
    dest:                { type: Sequelize.DataTypes.STRING},
    messageType:         { type: Sequelize.DataTypes.STRING},
    uniqueId:            { type: Sequelize.DataTypes.UUID, allowNull: false },
    action:              { type: Sequelize.DataTypes.STRING, allowNull: true},
    payload:             { type: Sequelize.DataTypes.TEXT},
    managedBy:           { type: Sequelize.DataTypes.STRING },
    forwarded:           { type: Sequelize.DataTypes.STRING(20), defaultValue: 'No' },
    confirmed:           { type: Sequelize.DataTypes.BOOLEAN},
    status:              { type: Sequelize.DataTypes.STRING, defaultValue: "New" },
    subStatus:           { type: Sequelize.DataTypes.STRING(22)},
  });

const altMessages = sequelize.define('altMessages',{
    deviceType:   {...StringRequired },
    protocol:     {...StringRequired },
    source:       {...StringRequired},
    dest:         {...StringRequired},
    messageType:  {...StringRequired},
    uniqueId:     {type: Sequelize.DataTypes.UUID, allowNull: false},
    action:       Sequelize.DataTypes.STRING,
    payload:      Sequelize.DataTypes.TEXT,
    managedBy:    {...StringRequired },
    forwarded:    { type: Sequelize.DataTypes.STRING(20), defaultValue: 'No' },
    confirmed:    Sequelize.DataTypes.BOOLEAN,
    status:       {...StringRequired, defaultValue: "New" },
    subStatus:    Sequelize.DataTypes.STRING,
  });

There are also two additional models in my schema with foreign key relationships to the message model. These keys are added using .hasMany() and .belongsTo for the messages table, but I did not think they were relevant so I left them out.

I used this code to exploit Sequelize's Schema feature to illustrate the difference in the two models:

sequelize.sync({ logging: false })
  .then( (result) => {
    console.log('Sync completed OK. Models:
',result.models);
    return messages.schema('messages');
  })
  .then( (result) =>{
    console.log('messages');
    for(const key in result.fieldRawAttributesMap) {
      console.log(key,result.fieldRawAttributesMap[key]);
    }
    return altMessages.schema('altMessages');
  })
  .then( (result) =>{
    console.log('altMessages:')
    for(const key in result.fieldRawAttributesMap) {
      console.log(key,result.fieldRawAttributesMap[key]);
    }
  })
  .catch((e)=> {
    console.log("SYNC FAILED",e);
  });

On sqlite, altMessages contained a text field right after the id field, used to store the status field:

altMessages:
id {
  type: INTEGER {
    options: {},
    _length: undefined,
    _zerofill: undefined,
    _decimals: undefined,
    _precision: undefined,
    _scale: undefined,
    _unsigned: undefined
  },
  allowNull: false,
  primaryKey: true,
  autoIncrement: true,
  _autoGenerated: true,
  Model: altMessages,
  fieldName: 'id',
  _modelAttribute: true,
  field: 'id'
}
text {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  allowNull: false,
  Model: altMessages,
  fieldName: 'status',
  _modelAttribute: true,
  field: 'text',
  defaultValue: 'New'
}

on the other hand, the messages model had a discrete status field, well down the order, where I expected it to be:

status {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  defaultValue: 'New',
  Model: messages,
  fieldName: 'status',
  _modelAttribute: true,
  field: 'status'
}

Here is the full output from this code running on both sqlite and mariaDb:

sqlite:

messages
id {
  type: INTEGER {
    options: {},
    _length: undefined,
    _zerofill: undefined,
    _decimals: undefined,
    _precision: undefined,
    _scale: undefined,
    _unsigned: undefined
  },
  allowNull: false,
  primaryKey: true,
  autoIncrement: true,
  _autoGenerated: true,
  Model: messages,
  fieldName: 'id',
  _modelAttribute: true,
  field: 'id'
}
deviceType {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'deviceType',
  _modelAttribute: true,
  field: 'deviceType'
}
protocol {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'protocol',
  _modelAttribute: true,
  field: 'protocol'
}
source {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'source',
  _modelAttribute: true,
  field: 'source'
}
dest {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'dest',
  _modelAttribute: true,
  field: 'dest'
}
messageType {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'messageType',
  _modelAttribute: true,
  field: 'messageType'
}
uniqueId {
  type: UUID {},
  allowNull: false,
  Model: messages,
  fieldName: 'uniqueId',
  _modelAttribute: true,
  field: 'uniqueId'
}
action {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  allowNull: true,
  Model: messages,
  fieldName: 'action',
  _modelAttribute: true,
  field: 'action'
}
payload {
  type: TEXT { options: { length: undefined }, _length: '' },
  Model: messages,
  fieldName: 'payload',
  _modelAttribute: true,
  field: 'payload'
}
managedBy {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'managedBy',
  _modelAttribute: true,
  field: 'managedBy'
}
forwarded {
  type: STRING {
    options: { length: 20, binary: undefined },
    _binary: undefined,
    _length: 20
  },
  defaultValue: 'No',
  Model: messages,
  fieldName: 'forwarded',
  _modelAttribute: true,
  field: 'forwarded'
}
confirmed {
  type: BOOLEAN {},
  Model: messages,
  fieldName: 'confirmed',
  _modelAttribute: true,
  field: 'confirmed'
}
status {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  defaultValue: 'New',
  Model: messages,
  fieldName: 'status',
  _modelAttribute: true,
  field: 'status'
}
subStatus {
  type: STRING {
    options: { length: 22, binary: undefined },
    _binary: undefined,
    _length: 22
  },
  Model: messages,
  fieldName: 'subStatus',
  _modelAttribute: true,
  field: 'subStatus'
}
createdAt {
  type: DATE { options: { length: undefined }, _length: '' },
  allowNull: false,
  _autoGenerated: true,
  Model: messages,
  fieldName: 'createdAt',
  _modelAttribute: true,
  field: 'createdAt'
}
updatedAt {
  type: DATE { options: { length: undefined }, _length: '' },
  allowNull: false,
  _autoGenerated: true,
  Model: messages,
  fieldName: 'updatedAt',
  _modelAttribute: true,
  field: 'updatedAt'
}
chargePointInstallId {
  type: INTEGER {
    options: {},
    _length: undefined,
    _zerofill: undefined,
    _decimals: undefined,
    _precision: undefined,
    _scale: undefined,
    _unsigned: undefined
  },
  allowNull: true,
  references: { model: 'chargePointInstalls', key: 'id' },
  onDelete: 'SET NULL',
  onUpdate: 'CASCADE',
  Model: messages,
  fieldName: 'chargePointInstallId',
  _modelAttribute: true,
  field: 'chargePointInstallId'
}
waveInstallId {
  type: INTEGER {
    options: {},
    _length: undefined,
    _zerofill: undefined,
    _decimals: undefined,
    _precision: undefined,
    _scale: undefined,
    _unsigned: undefined
  },
  allowNull: true,
  references: { model: 'waveInstalls', key: 'id' },
  onDelete: 'SET NULL',
  onUpdate: 'CASCADE',
  Model: messages,
  fieldName: 'waveInstallId',
  _modelAttribute: true,
  field: 'waveInstallId'
}
altMessages:
id {
  type: INTEGER {
    options: {},
    _length: undefined,
    _zerofill: undefined,
    _decimals: undefined,
    _precision: undefined,
    _scale: undefined,
    _unsigned: undefined
  },
  allowNull: false,
  primaryKey: true,
  autoIncrement: true,
  _autoGenerated: true,
  Model: altMessages,
  fieldName: 'id',
  _modelAttribute: true,
  field: 'id'
}
text {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  allowNull: false,
  Model: altMessages,
  fieldName: 'status',
  _modelAttribute: true,
  field: 'text',
  defaultValue: 'New'
}
uniqueId {
  type: UUID {},
  allowNull: false,
  Model: altMessages,
  fieldName: 'uniqueId',
  _modelAttribute: true,
  field: 'uniqueId'
}
action {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: altMessages,
  fieldName: 'action',
  _modelAttribute: true,
  field: 'action'
}
payload {
  type: TEXT { options: { length: undefined }, _length: '' },
  Model: altMessages,
  fieldName: 'payload',
  _modelAttribute: true,
  field: 'payload'
}
forwarded {
  type: STRING {
    options: { length: 20, binary: undefined },
    _binary: undefined,
    _length: 20
  },
  defaultValue: 'No',
  Model: altMessages,
  fieldName: 'forwarded',
  _modelAttribute: true,
  field: 'forwarded'
}
confirmed {
  type: BOOLEAN {},
  Model: altMessages,
  fieldName: 'confirmed',
  _modelAttribute: true,
  field: 'confirmed'
}
subStatus {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: altMessages,
  fieldName: 'subStatus',
  _modelAttribute: true,
  field: 'subStatus'
}
createdAt {
  type: DATE { options: { length: undefined }, _length: '' },
  allowNull: false,
  _autoGenerated: true,
  Model: altMessages,
  fieldName: 'createdAt',
  _modelAttribute: true,
  field: 'createdAt'
}
updatedAt {
  type: DATE { options: { length: undefined }, _length: '' },
  allowNull: false,
  _autoGenerated: true,
  Model: altMessages,
  fieldName: 'updatedAt',
  _modelAttribute: true,
  field: 'updatedAt'
}

and running on mariaDb on Windows Server:

messages
id {
  type: INTEGER {
    options: {},
    _length: undefined,
    _zerofill: undefined,
    _decimals: undefined,
    _precision: undefined,
    _scale: undefined,
    _unsigned: undefined
  },
  allowNull: false,
  primaryKey: true,
  autoIncrement: true,
  _autoGenerated: true,
  Model: messages,
  fieldName: 'id',
  _modelAttribute: true,
  field: 'id'
}
deviceType {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'deviceType',
  _modelAttribute: true,
  field: 'deviceType'
}
protocol {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'protocol',
  _modelAttribute: true,
  field: 'protocol'
}
source {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'source',
  _modelAttribute: true,
  field: 'source'
}
dest {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'dest',
  _modelAttribute: true,
  field: 'dest'
}
messageType {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'messageType',
  _modelAttribute: true,
  field: 'messageType'
}
uniqueId {
  type: UUID {},
  allowNull: false,
  Model: messages,
  fieldName: 'uniqueId',
  _modelAttribute: true,
  field: 'uniqueId'
}
action {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  allowNull: true,
  Model: messages,
  fieldName: 'action',
  _modelAttribute: true,
  field: 'action'
}
payload {
  type: TEXT { options: { length: undefined }, _length: '' },
  Model: messages,
  fieldName: 'payload',
  _modelAttribute: true,
  field: 'payload'
}
managedBy {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: messages,
  fieldName: 'managedBy',
  _modelAttribute: true,
  field: 'managedBy'
}
forwarded {
  type: STRING {
    options: { length: 20, binary: undefined },
    _binary: undefined,
    _length: 20
  },
  defaultValue: 'No',
  Model: messages,
  fieldName: 'forwarded',
  _modelAttribute: true,
  field: 'forwarded'
}
confirmed {
  type: BOOLEAN {},
  Model: messages,
  fieldName: 'confirmed',
  _modelAttribute: true,
  field: 'confirmed'
}
status {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  defaultValue: 'New',
  Model: messages,
  fieldName: 'status',
  _modelAttribute: true,
  field: 'status'
}
subStatus {
  type: STRING {
    options: { length: 22, binary: undefined },
    _binary: undefined,
    _length: 22
  },
  Model: messages,
  fieldName: 'subStatus',
  _modelAttribute: true,
  field: 'subStatus'
}
createdAt {
  type: DATE { options: { length: undefined }, _length: '' },
  allowNull: false,
  _autoGenerated: true,
  Model: messages,
  fieldName: 'createdAt',
  _modelAttribute: true,
  field: 'createdAt'
}
updatedAt {
  type: DATE { options: { length: undefined }, _length: '' },
  allowNull: false,
  _autoGenerated: true,
  Model: messages,
  fieldName: 'updatedAt',
  _modelAttribute: true,
  field: 'updatedAt'
}
chargePointInstallId {
  type: INTEGER {
    options: {},
    _length: undefined,
    _zerofill: undefined,
    _decimals: undefined,
    _precision: undefined,
    _scale: undefined,
    _unsigned: undefined
  },
  allowNull: true,
  references: { model: 'chargePointInstalls', key: 'id' },
  onDelete: 'SET NULL',
  onUpdate: 'CASCADE',
  Model: messages,
  fieldName: 'chargePointInstallId',
  _modelAttribute: true,
  field: 'chargePointInstallId'
}
waveInstallId {
  type: INTEGER {
    options: {},
    _length: undefined,
    _zerofill: undefined,
    _decimals: undefined,
    _precision: undefined,
    _scale: undefined,
    _unsigned: undefined
  },
  allowNull: true,
  references: { model: 'waveInstalls', key: 'id' },
  onDelete: 'SET NULL',
  onUpdate: 'CASCADE',
  Model: messages,
  fieldName: 'waveInstallId',
  _modelAttribute: true,
  field: 'waveInstallId'
}
altMessages:
id {
  type: INTEGER {
    options: {},
    _length: undefined,
    _zerofill: undefined,
    _decimals: undefined,
    _precision: undefined,
    _scale: undefined,
    _unsigned: undefined
  },
  allowNull: false,
  primaryKey: true,
  autoIncrement: true,
  _autoGenerated: true,
  Model: altMessages,
  fieldName: 'id',
  _modelAttribute: true,
  field: 'id'
}
text {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  allowNull: false,
  Model: altMessages,
  fieldName: 'status',
  _modelAttribute: true,
  field: 'text',
  defaultValue: 'New'
}
uniqueId {
  type: UUID {},
  allowNull: false,
  Model: altMessages,
  fieldName: 'uniqueId',
  _modelAttribute: true,
  field: 'uniqueId'
}
action {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: altMessages,
  fieldName: 'action',
  _modelAttribute: true,
  field: 'action'
}
payload {
  type: TEXT { options: { length: undefined }, _length: '' },
  Model: altMessages,
  fieldName: 'payload',
  _modelAttribute: true,
  field: 'payload'
}
forwarded {
  type: STRING {
    options: { length: 20, binary: undefined },
    _binary: undefined,
    _length: 20
  },
  defaultValue: 'No',
  Model: altMessages,
  fieldName: 'forwarded',
  _modelAttribute: true,
  field: 'forwarded'
}
confirmed {
  type: BOOLEAN {},
  Model: altMessages,
  fieldName: 'confirmed',
  _modelAttribute: true,
  field: 'confirmed'
}
subStatus {
  type: STRING {
    options: { length: undefined, binary: undefined },
    _binary: undefined,
    _length: 255
  },
  Model: altMessages,
  fieldName: 'subStatus',
  _modelAttribute: true,
  field: 'subStatus'
}
createdAt {
  type: DATE { options: { length: undefined }, _length: '' },
  allowNull: false,
  _autoGenerated: true,
  Model: altMessages,
  fieldName: 'createdAt',
  _modelAttribute: true,
  field: 'createdAt'
}
updatedAt {
  type: DATE { options: { length: undefined }, _length: '' },
  allowNull: false,
  _autoGenerated: true,
  Model: altMessages,
  fieldName: 'updatedAt',
  _modelAttribute: true,
  field: 'updatedAt'
}

Can someone please explain how I avoid this text field being created, as its existence caused quite for pain as I assumed Sequelize would 'just work'.

Thanks in advance.

Why does Sequelize create a database column named 'text' instead of named after the model attibute

0 Answers