Creating a MySQL Table in NodeJS using Sequelize

Introduction to Sequelize

Sequealize follows the promise-based Node.js ORM for different servers like – Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server.

Following are some of the main features of NodeJS sequelize −

• Transaction Support

• Relations

• Eager and Lazy Loading

• Read Replication and more...

Connecting to MySQL using Sequelize

• We need to establish a connection between MySQL and Node.js using Sequelize.

• After creating a successful connection with sequelize, we would require the following three files for configuration. Please carefully create the following files in their respective folders only.

  • SequelizeDemo > application.js

    This will be our root file which will hold the actual logic.

  • SequelizeDemo>utils>database.js

    This will hold all the connection details to MySQL.

  • SequelizeDemo>models>user.js

    This will contain the required model information.

Example

Configure Database.js

const Sequelize = require('sequelize')
const sequelize = new Sequelize(
   'YOUR_DB_NAME', // TutorialsPoint
   'YOUR_DB_USER_NAME', // root
   'YOUR_DB_PASSWORD', //root{
      dialect: 'mysql',
      host: 'localhost'
   }
);
module.exports = sequelize

Please make all the inputs for connecting with your database.

Configure User.js

Use this file to define the mappings between a model and a table.

const Sequelize = require('sequelize')
const sequelize = require('../utils/database')
const User = sequelize.define('user', {
   // Name of Column #1 and its properties defined: id
   user_id:{

      // Integer Datatype
      type:Sequelize.INTEGER,

      // Increment the value automatically
      autoIncrement:true,

      // user_id can not be null.
      allowNull:false,

      // To uniquely identify user
      primaryKey:true
   },

   // Name of Column #2: name
   name: { type: Sequelize.STRING, allowNull:false },

   // Name of Column #3: email
   email: { type: Sequelize.STRING, allowNull:false },

   // Column: Timestamps
   createdAt: Sequelize.DATE,
   updatedAt: Sequelize.DATE,
})
module.exports = User

Configure app.js

To create a model, we can use any one of the two methods −

• sync() Method − Only create model if exists. If the model exists, it will not overwrite the model.

• sync({force: true}) Method − Will create a new model if the model does not exist, however, if the model exists, it will overwrite the existing model.

// Importing the database model
const sequelize = require('./database')

// Importing the user model
const User = require('./user')

// Creating all the tables defined in user
sequelize.sync()

// You can change the user.js file
// And run this code to check if it overwrites the existing code.
sequelize.sync({force:true)

Output

On running the above program, you will get the following Output -

C:\Users\SequelizeDemo>> node app.js
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`user_id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255) NOT NULL, `email` VARCHAR(255) NOT NULL, `createdAt` DATETIME, `updatedAt` DATETIME, PRIMARY KEY (`user_id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `users`

Now, you can check your database. The above table would have been created.