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 defineing 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.



    database.js file will look like this.

Configure user.js:

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

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    // 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 arrguments
    // 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

    chevron_right

    
    

  • 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 is not exist, however if already exist it would not overwrite it.
    • sync({force:true}) Method: This method will create model if the model is not exist, however if already exist it would overwrite it.
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    // 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)

    chevron_right

    
    

  • 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:
    Project Structure
  • Install required modules for this project:
    npm install mysql2
    npm install sequelize
    
  • Execute app.js (root file) using below command:
    node app.js

    Output

  • In MySql Database, now we can describe User model we have created using Sequelize. Use the below commands
    use database geeksforgeeks
    desc users;
    

    db




My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.