A model field with datatype Virtual and a get method copied directly from the Sequelize manual gives SQL syntax error when running db:migration with sequelize-cli. Any other possible errors in the file has been ruled out by trial and error.
MySQL 5.7.31 and Sequelize ^6.3.5 with Express 4.17.1 and mysql2 2.1.0.
My migrations file:
'use strict'
const { DataTypes } = require('sequelize')
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.createTable('users', {
id: {
type: DataTypes.UUID,
defaultValue: Sequelize.UUIDV4,
primaryKey: true,
allowNull: false, // constraint on mysql
unique: true,// constraint on mysql
validate: {
isUUID: {
args: 4,
msg: 'User ID must be a UUID4 string.'
}
}
},
firstName: {
type: DataTypes.STRING,
required: true,
allowNull: false,
notEmpty: true,
validate: {
len: {
args: [2, 90],
msg: 'The first name must contain between 2 and 90 characters.'
}
}
},
lastName: {
type: DataTypes.STRING,
required: true,
allowNull: false,
notEmpty: true,
validate: {
len: {
args: [2, 90],
msg: 'The last name must contain between 2 and 90 characters.'
}
}
},
// Code that breaks the migration process:
fullName: {
type: DataTypes.VIRTUAL,
get() {
return `${this.firstName} ${this.lastName}`;
}
},
// End code that breaks migration process
email: {
type: DataTypes.STRING,
unique: true,
required: true,
allowNull: false,
validate: {
isEmail: {
args: [true],
msg: 'Incorrect email format'
},
isLowercase: {
args: [true],
msg: 'Email address must be lowercase'
},
len: {
args: [2, 50],
msg: 'The email address must have between 2 and 50 characters',
},
notEmpty: {
args: [true],
msg: 'The email field can\'t be empty.'
},
}
},
password: {
type: DataTypes.STRING,
required: true,
allowNull: false,
validate: {
len: {
args: [8, 99],
msg: 'Your password must contain at least 8 characters.',
},
notEmpty: {
args: [true],
msg: 'The password field cannot be empty.'
}
}
},
resetPasswordToken: {
type: DataTypes.STRING
},
resetPasswordExpire: {
type: DataTypes.INTEGER
},
createdAt: {
type: DataTypes.DATE,
required: true,
allowNull: false
},
updatedAt: {
type: DataTypes.DATE,
required: true,
allowNull: false
},
deletedAt: {
type: DataTypes.DATE
}
},
{
paranoid: true,
tableName: 'users'
})
},
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('users');
}
};
Console output after sequelize-cli db:migrate:
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'VIRTUAL, `email` VARCHAR(255)
NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL,' at line 1
npm ERR! code ELIFECYCLE
npm ERR! errno 1
etc.
If the fullName field is removed from the model the migration runs successfully.
The get() method in the fullName field is identical to the example in the manual (https://sequelize.org/master/manual/getters-setters-virtuals.html#virtual-fields).
It gets extra interesting as the Sequelize manual states that Virtual datatypes will not be added to the database – which is the whole point of this functionality:
The VIRTUAL field does not cause a column in the table to exist.
In other words, the model above will not have a fullName column.
However, it will appear to have it!
Environment:
- Sequelize version: ^6.3.5
- Node.js version: 12.18.3
- Operating System: MacOS/Docker 19.03.13 build 4484c46d9d
- MySQL 5.7.31
- Express 4.17.1
- NPM package
mysql2version 2.1.0 (adapter) - Sequelize CLI 6.2.0
Thank you for your kind help.