Setting Up Sequelize Express MySQL

In this first chapter of the Building an app using Node, MySQL and Express tutorial series, you’ll learn how to go about setting up Sequelize, Node and MySQL.

Source code from this tutorial is available on GitHub.

Table Of Content

Getting Started With Express

To make things a bit easier I’ll be making use of the express application generator. Express application generator makes it easier to create the express app template. Install express-generator globally.

# install express generator 
npm install express-generator -g

Now let’s create the express project structure using the express-generator. You’ll be making use of the EJS templates to render the view.

# create express project
express --view=ejs taskManagement

Navigate to the project directory and install the project dependencies.

cd taskManagement
npm install

Here is how the project structure looks :

taskManagement/
├── app.js
├── bin
├── node_modules
├── package.json
├── package-lock.json
├── public
├── routes
└── views

From the project directory run the following command and you will have the application running.

// run the application
DEBUG=taskmanagement:* npm start

You will be able to view the default express app.

Adding Sequelize ORM To Express App

To get started with sequelize you need to install sequelize to the project.

# install sequelize 
npm install --save sequelize

We’ll be using MySQL database so you need to install mysql2 node package.

# install mysql2
npm install --save mysql2

To make things a bit faster we’ll be making use of sequelize-cli tool to initialize the basic sequelize setup.

# install sequelize-cli
npm install --save sequelize-cli

Now from the project directory initialize the sequelize cli tool.

# initialize sequelize cli
node_modules/.bin/sequelize

The above command will create the basic boilerplate structure for using sequelize with express. You will have config, migrations, models and seeders folder added to the project.

taskManagement/
├── app.js
├── bin
├── config
├── migrations
├── models
├── node_modules
├── package.json
├── package-lock.json
├── public
├── routes
├── seeders
└── views

Modify the development database configurations in the config/config.json file inside the project directory.

{
  "development": {
    "username": "root",
    "password": "root",
    "database": "task_management",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

Creating And Migrating Sequelize Models

You can create a model for User using sequelize. From the project directory execute the following command:

# execute the following from project directory
node_modules/.bin/sequelize model:generate --name User --attributes firstName:string,lastName:string,email:string:password:string,role:integer

On execution the above command will create a file inside the models folder and migration folder. File inside the models folder called user.js will be used to access the user table database from MySQL. File inside the migration folder will be used to migrate the models to the MySQL database.

Similarly, create a model for user role table.

# create model for user role
node_modules/.bin/sequelize model:generate --name UserRole --attributes role:string

To migrate the above created models to the database, execute the following command from the project directory. 

# migrate sequelize models to database
node_modules/.bin/sequelize db:migrate

Check the MySQL database and you’ll have the Users and UserRoles table created in the database task_management.

Insert some dummy data into the Users MySQL table. You’ll try to fetch the table data using sequelize ORM and render to the browser.

INSERT INTO `task_management`.`Users` (`id`, `firstName`, `lastName`, `email`, `password`, `role`, `createdAt`, `updatedAt`)
VALUES (4,
        'Soumya',
        'KG',
        'soumya@gmail.com',
        'test',
        3,
        NOW(),
        NOW());

Fetching Data From MySQL Using Sequelize

Require the sequelize models inside the users.js file inside the routes folder.

const db = require('../models/index');
const User = db.sequelize.models.User;

Using the User model fetch all the table data from the database using the findAll method. Here is how routes/users.js file looks like :

const express = require('express');
const db = require('../models/index');
const User = db.sequelize.models.User;
const router = express.Router();

/* GET users listing. */
router.get('/', function(req, res) {
    return User.findAll({
        raw: true
    })
    .then(function(result){
        res.send(result);
    })
});

module.exports = router;

As seen in the above code, once the returned Promise from findAll method is resolved, the result is returned using res.

Save the above changes and restart the Node application. Point your web browser to the following URL http://localhost:3000/users/

You will be able view the Users MySQL table data inside the web browser.

[{
    "id": 3,
    "firstName": "Jack",
    "lastName": "Samuel",
    "email": "jacksamuel@gmail.com",
    "password": null,
    "role": 3,
    "createdAt": "2018-11-08T13:46:27.000Z",
    "updatedAt": "2018-11-08T13:46:27.000Z"
}, {
    "id": 4,
    "firstName": "Soumya",
    "lastName": "KG",
    "email": "soumya@gmail.com",
    "password": "test",
    "role": 3,
    "createdAt": "2018-11-08T19:28:01.000Z",
    "updatedAt": "2018-11-08T19:28:01.000Z"
}]

Wrapping It Up

In this tutorial, you learnt how to go about setting up Sequelize, Node and MySQL. You used the sequlize-cli and express generator to setup our basic project structure. You also learn how to fetch data from MySQL database using the Sequelize ORM.

In the next part of the tutorial series, you’ll learn how to implement a user sign in page using MySQL, Sequelize and Node Express. Do let us know your thoughts and suggestions in the comments below.