Node/Express/Sequelize: Datatype VIRTUAL gives SQL syntax error in db:migration

Question

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 mysql2 version 2.1.0 (adapter)
• Sequelize CLI 6.2.0

Thank you for your kind help.

Answer 1

Virtuals exist to help you with querying the models (for example, in your API controllers to get the user's full name). Since they don't exist in the database, they shouldn't be included as part of a migration file.

In short, you should use VIRTUAL in your model definition, not in the migration file using the queryInterface.createTable method.

Hope this answers your question.