CODEWITHSUNDEEP
mysqlnode.jsmodelsequelize.js
Ikdemm
Ikdemm

Reputation: 2353

Sequelize model.sync() is throwing an error

I'm trying to create a model with sequelize and add then use sync({ force: true }) as shown here to make MySQL create the table if doesn't exist. Yet, it's yelling at me saying that I have an error in the MySQL query (which is supposed to be generated by Sequelize). This is the Model:

// admin.model.js
const { Sequelize, DataTypes } = require('sequelize');
const sequelize = require('../db/connection');
const validator = require("validator")

const Admin = sequelize.define('Admin', {
    email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true,
        validate(value) {
            if (!validator.isEmail(value)) {
                throw new Error('Invalid email');
            }
        },
    },
    password: {
        type: DataTypes.STRING,
        allowNull: false,
        validate(value) {
            if (!value.match(/\d/) || !value.match(/[a-zA-Z]/)) {
                throw new Error('Password must contain at least one letter and one number');
            }
        },
        private: true, // used by the toJSON plugin
    },
    firstName: {
        type: DataTypes.STRING,
        allowNull: false
    },
    lastName: {
        type: DataTypes.STRING,
        allowNull: false
    },
    phone: {
        type: DataTypes.NUMBER
    },
}, {
    freezeTableName: true
}
)

module.exports = Admin;

And this is the function I'm trying to run

// sync.js
const Admin = require("../models/admin.model")

const synchronizeTables = async () => {
    try {
        await Admin.sync({ force: true })
        console.log("Admin Table synchronized")
    } catch (error) {
        console.error(error)
    }
}

module.exports = synchronizeTables

Then I have an index.js file inside db directory just to export connection and synchronization:

// index.js
const db = require("./connection");
const sync = require("./sync");

module.exports = { db, sync };

Then, I import them in app.js and run sync

// app.js
const { db, sync } = require('./src/db');
sync()

The error I get when I run the server:

Server is up and running on port 8000
Executing (default): SELECT 1+1 AS result
Executing (default): DROP TABLE IF EXISTS `Admin`;
Connection has been established successfully.
Executing (default): CREATE TABLE IF NOT EXISTS `Admin` (`id` INTEGER NOT NULL auto_increment , `email` VARCHAR(255) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `firstName` VARCHAR(255) NOT NULL, `lastName` VARCHAR(255) NOT NULL, `phone` NUMBER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
DatabaseError [SequelizeDatabaseError]: 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 'NUMBER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KE' at line 1
    at Query.formatError (/home/ikdem/work/upwork/saas/admin-side/node_modules/sequelize/lib/dialects/mysql/query.js:265:16)
    at Query.run (/home/ikdem/work/upwork/saas/admin-side/node_modules/sequelize/lib/dialects/mysql/query.js:77:18)
    at processTicksAndRejections (internal/process/task_queues.js:97:5) {
  parent: 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 'NUMBER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KE' at line 1
      at Packet.asError (/home/ikdem/work/upwork/saas/admin-side/node_modules/mysql2/lib/packets/packet.js:712:17)
      at Query.execute (/home/ikdem/work/upwork/saas/admin-side/node_modules/mysql2/lib/commands/command.js:28:26)
      at Connection.handlePacket (/home/ikdem/work/upwork/saas/admin-side/node_modules/mysql2/lib/connection.js:425:32)
      at PacketParser.onPacket (/home/ikdem/work/upwork/saas/admin-side/node_modules/mysql2/lib/connection.js:75:12)
      at PacketParser.executeStart (/home/ikdem/work/upwork/saas/admin-side/node_modules/mysql2/lib/packet_parser.js:75:16)
      at Socket.<anonymous> (/home/ikdem/work/upwork/saas/admin-side/node_modules/mysql2/lib/connection.js:82:25)
      at Socket.emit (events.js:315:20)
      at addChunk (_stream_readable.js:295:12)
      at readableAddChunk (_stream_readable.js:271:9)
      at Socket.Readable.push (_stream_readable.js:212:10) {
    code: 'ER_PARSE_ERROR',
    errno: 1064,
    sqlState: '42000',
    sqlMessage: "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 'NUMBER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KE' at line 1",
    sql: 'CREATE TABLE IF NOT EXISTS `Admin` (`id` INTEGER NOT NULL auto_increment , `email` VARCHAR(255) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `firstName` VARCHAR(255) NOT NULL, `lastName` VARCHAR(255) NOT NULL, `phone` NUMBER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;',
    parameters: undefined
  },

So, there seems to be an error in the SQL query generated by Sequelize. Maybe I didn't use it right. Help please :)

Upvotes: 1

Views: 4576

Answers (1)

Ikdemm
Ikdemm

Reputation: 2353

So the problem here was with the Data type of phone. As you can see DataType.NUMBER does not change into INTEGER in the query, instead, it remains NUMBER, which does not exist in SQL. So, I had to change

phone: {
        type: DataTypes.NUMBER
    },

To

phone: {
        type: DataTypes.INTEGER
    },

Also, I used the sequelize.sync() to sync all the tables at once instead of synchronizing the models one by one.

Upvotes: 5

Related Questions