sequelize.sync(): error in SQL syntax near NUMBER

Question

I made 4 Sequelize Models with use of sequelize.define();. Models are pretty much the same thing but with different table names. since I don't wanted to make them manually on MySQL cli, I decided to use sequelize.sync() in my main index.js file to let Sequelize to create the table but when I ran the application It faced an Unhandled rejection SequelizeDatabaseError: You have an error in your SQL syntax; error and didn't make the tables.

I have tried both sequelize.sync(); and sequelize.sync({ force: true }); and also tried syncing Models one by one but same error apeared!

one of My Models

export const Product = sequelize.define(
  "product",
  {
    doInMyPlace: { type: Sequelize.BOOLEAN, allowNull: false },
    address: { type: Sequelize.STRING, allowNull: false },
    mapAddress: { type: Sequelize.STRING, allowNull: false },
    date: { type: Sequelize.STRING, allowNull: false },
    time: { type: Sequelize.STRING, allowNull: false },
    voucher: { type: Sequelize.STRING, allowNull: true },
    companyName: { type: Sequelize.STRING, allowNull: false },
    phoneNumber: { type: Sequelize.STRING, allowNull: false },
    itemCount: { type: Sequelize.NUMBER, allowNull: false }
  },
  {
    freezeTableName: true
  }
);

Shown Error

Unhandled rejection 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 NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, P' at line 1

Since the error aims at SQL syntax I was wondering if it has something to do with my code or is just an issue with sequelize itself.

Rest of The Error

at Query.formatError (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\dialects\mysql\query.js:244:16)
    at Query.handler [as onResult] (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\dialects\mysql\query.js:51:23)
    at Query.execute (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\mysql2\lib\commands\command.js:30:14)
    at Connection.handlePacket (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\mysql2\lib\connection.js:408:32)
    at PacketParser.Connection.packetParser.p [as onPacket] (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\mysql2\lib\connection.js:70:12)
    at PacketParser.executeStart (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket.Connection.stream.on.data (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\mysql2\lib\connection.js:77:25)
    at Socket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
From previous event:
    at Query.run (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\dialects\mysql\query.js:39:12)
    at runHooks.then.then (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\sequelize.js:643:29)
From previous event:
    at Promise.try.then.connection (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\sequelize.js:643:12)
From previous event:
    at Promise.resolve.retry (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\sequelize.js:639:10)
    at C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\retry-as-promised\index.js:70:21
    at new Promise (<anonymous>)
    at retryAsPromised (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\retry-as-promised\index.js:60:10)
    at Promise.try (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\sequelize.js:629:30)
From previous event:
    at Sequelize.query (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\sequelize.js:578:23)
    at promise.then (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\query-interface.js:236:46)
From previous event:
    at QueryInterface.createTable (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\query-interface.js:236:20)
    at Promise.try.then.then (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\model.js:1292:39)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)
From previous event:
    at Function.sync (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\sequelize\lib\model.js:1292:8)
    at Object.<anonymous> (C:\Users\Amirali\Desktop\feature-2-authorization\models\products\product.ts:21:9)
    at Module._compile (internal/modules/cjs/loader.js:776:30)
    at Module._compile (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\source-map-support\source-map-support.js:521:25)
    at Module.m._compile (C:\Users\Amirali\AppData\Local\Temp\ts-node-dev-hook-7490429646471359.js:56:25)
    at Module._extensions..js (internal/modules/cjs/loader.js:787:10)
    at require.extensions.(anonymous function) (C:\Users\Amirali\AppData\Local\Temp\ts-node-dev-hook-7490429646471359.js:58:14)
    at Object.nodeDevHook [as .ts] (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\ts-node-dev\lib\hook.js:61:7)
    at Module.load (internal/modules/cjs/loader.js:653:32)
    at tryModuleLoad (internal/modules/cjs/loader.js:593:12)
    at Function.Module._load (internal/modules/cjs/loader.js:585:3)
    at Module.require (internal/modules/cjs/loader.js:690:17)
    at require (internal/modules/cjs/helpers.js:25:18)
    at Object.<anonymous> (C:\Users\Amirali\Desktop\feature-2-authorization\models\products\index.ts:4:1)
    at Module._compile (internal/modules/cjs/loader.js:776:30)
    at Module._compile (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\source-map-support\source-map-support.js:521:25)
    at Module.m._compile (C:\Users\Amirali\AppData\Local\Temp\ts-node-dev-hook-7490429646471359.js:56:25)
    at Module._extensions..js (internal/modules/cjs/loader.js:787:10)
    at require.extensions.(anonymous function) (C:\Users\Amirali\AppData\Local\Temp\ts-node-dev-hook-7490429646471359.js:58:14)
    at Object.nodeDevHook [as .ts] (C:\Users\Amirali\Desktop\feature-2-authorization\node_modules\ts-node-dev\lib\hook.js:61:7)
    at Module.load (internal/modules/cjs/loader.js:653:32)
    at tryModuleLoad (internal/modules/cjs/loader.js:593:12)
    at Function.Module._load (internal/modules/cjs/loader.js:585:3)
    at Module.require (internal/modules/cjs/loader.js:690:17)
    at require (internal/modules/cjs/helpers.js:25:18)

Executing Query

Executing (default): CREATE TABLE IF NOT EXISTS `user` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255) NOT NULL, `phone` VARCHAR(255) NOT NULL, `gender` VARCHAR(255) NOT NULL DEFAULT 'm', `birthday` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME
NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
Executing (default): SHOW INDEX FROM `user`
Executing (default): CREATE TABLE IF NOT EXISTS `food` (`id` INTEGER NOT NULL auto_increment , `doInMyPlace` TINYINT(1) NOT NULL, `address` VARCHAR(255) NOT NULL, `mapAddress` VARCHAR(255) NOT NULL, `date` VARCHAR(255) NOT NULL, `time` VARCHAR(255) NOT NULL, `voucher` VARCHAR(255), `companyName` VARCHAR(255) NOT NULL,
`phoneNumber` VARCHAR(255) NOT NULL, `itemCount` NUMBER NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

can you debug and show the complete sql query generated by sequelize? — majidarif
– majidarif, Commented Sep 6, 2019 at 17:39
Your shown error block pinpoints the problem area - right before "NUMBER", so you know it doesn't like it. majidarif is right, changing it to integer should fix it — user9542490
– user9542490, Commented Sep 6, 2019 at 17:57

majidarif · Accepted Answer · 2019-09-10 14:12:12Z

9

You should use INTEGER instead of NUMBER. Like so:

itemCount: { type: Sequelize.INTEGER, allowNull: false }

Looking at the docs, there is no reference to a datatype NUMBER. Although I did find this but its just used as a base class for other datatypes.

As for INTEGER, you have the following:

Sequelize.INTEGER.UNSIGNED              // INTEGER UNSIGNED
Sequelize.INTEGER(11).UNSIGNED          // INTEGER(11) UNSIGNED
Sequelize.INTEGER(11).ZEROFILL          // INTEGER(11) ZEROFILL
Sequelize.INTEGER(11).ZEROFILL.UNSIGNED // INTEGER(11) UNSIGNED ZEROFILL
Sequelize.INTEGER(11).UNSIGNED.ZEROFILL // INTEGER(11) UNSIGNED ZEROFILL

By the way, your error happens because of this line 'itemCount' NUMBER NOT NULL, there is no NUMBER datatype in MYSQL.

edited Sep 10, 2019 at 14:12

answered Sep 6, 2019 at 17:49

majidarif

20.4k18 gold badges97 silver badges137 bronze badges

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

Comments

Naik Ashwini · Accepted Answer · 2020-08-26 06:43:17Z

2

Datatypes supported in sequelize:

https://sequelize.readthedocs.io/en/1.7.0/docs/models/

So use INTEGER instead of NUMBER.

answered Aug 26, 2020 at 6:43

Naik Ashwini

81813 silver badges33 bronze badges

Collectives™ on Stack Overflow

sequelize.sync(): error in SQL syntax near NUMBER

one of My Models

Shown Error

Rest of The Error

Executing Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

one of My Models

Shown Error

Rest of The Error

Executing Query

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related