Node.js MySQL-Create Table Using Sequelize

Introduction to Sequelize: Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. Its features are solid transaction support, relations, eager and lazy loading, read replication and many more.

Connect to MySql Database using Sequelize:

• To establish connection between MySQL and Node.js using Sequelize, visit How to use Sequelize in Node.js.
• After getting connected successfully, we would have required three files:
  1. SequelizeDemo>app.js which is our root file.
  2. SequelizeDemo>utils>database.js which is responsible for MySql connection.
  3. SequelizeDemo>models>user.js which is responsible for defining the model.

Configure database.js: SequelizeDemo>utils>database.js

• Establish connection is the way mentioned in article How to use Sequelize in Node.js and now, we can use that exported sequelize object from SequelizeDemo>utils>database.js to define our models.

Note: Make sure the database that you are using is created in your database.

Configure user.js:

• Use SequelizeDemo>models>user.js file to define mappings between a model and a table, use the define method.

Javascript

// Include Sequelize module.
const Sequelize = require('sequelize')

// Import sequelize object,
// Database connection pool managed by Sequelize.
const sequelize = require('../utils/database')

// Define method takes two arguments
// 1st - name of table
// 2nd - columns inside the table
const User = sequelize.define('user', {

    // Column-1, user_id is an object with
    // properties like type, keys,
    // validation of column.
    user_id:{

        // Sequelize module has INTEGER Data_Type.
        type:Sequelize.INTEGER,

        // To increment user_id automatically.
        autoIncrement:true,

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

        // For uniquely identify user.
        primaryKey:true
    },

    // Column-2, name
    name: { type: Sequelize.STRING, allowNull:false },

    // Column-3, email
    email: { type: Sequelize.STRING, allowNull:false },

    // Column-4, default values for
    // dates => current time
    myDate: { type: Sequelize.DATE,
            defaultValue: Sequelize.NOW },

     // Timestamps
     createdAt: Sequelize.DATE,
     updatedAt: Sequelize.DATE,
})

// Exporting User, using this constant
// we can perform CRUD operations on
// 'user' table.
module.exports = User

• To know more about Sequelize Data Types visit Datatypes.
• In SequelizeDemo>models>user.js file, we have defined the model.

Configure app.js:

• Filename => SequelizeDemo>app.js
• To create the model, we can use one of the following way- 
  • sync() Method: This method will create model if the model does not exist, however, if already exist it would not overwrite it.
  • sync({force:true}) Method: This method will create model if the model does not exist, however, if already exist it would overwrite it. 

Javascript

// Import the sequelize object on which
// we have defined model.
const sequelize = require('./utils/database')

// Import the user model we have defined
const User = require('./models/user)

// Create all the table defined using
// sequelize in Database

// Sync all models that are not
// already in the database
sequelize.sync()

// Force sync all models
// It will drop the table first
// and re-create it afterwards
sequelize.sync({force:true})

• Database Synchronization: When starting a new project you won’t have a database structure and using Sequelize you won’t need to. Just specify your model structures and let the library do the rest.

Steps to run the program:

• The project structure will look like this: 
  

• Install required modules for this project: 

npm install mysql2
npm install sequelize

• Execute app.js (root file) using below command: 

node app.js

• In MySql Database, now we can describe User model we have created using Sequelize. Use the below commands 

use database geeksforgeeks
desc users;