Using Sequelize ORM with Node.js & MySQL

ORM (Object-Relational Mapping) is a technique that allows to query and manipulate data using an object to relational mapping layer. Today, there are a variety of ORM libraries available, and most of the developers prefer to use them over raw SQL queries.

ORMs help developers to write complicated queries quickly and make it easier to update and reuse the code.

However, getting started with ORMs can be challenging due to the learning curve. So, here is a quick tutorial on how to set up Sequelize ORM with Node.js and MySQL.

What is Sequelize

Sequelize is a promise-based ORM library specialized for Node.js. It was first introduced in 2011, and now, it has more than 1.1 Million weekly NPM downloads and 25K+ GitHub stars.

Compared to other ORM libraries, Sequelize is more stable and has various features like queries, relations, transactions, hooks, scopes, migrations, etc. Also, it supports multiple databases like MySQL, MariaDB, SQLite, Microsoft SQL Server and Postgres.

For this tutorial, we will focus on using Sequelize with Node.js and MySQL.

Using Sequalize with a Node.js and MySQL

In this tutorial, we will be implementing the following:

• Initialize a Node.js project with Express.js, Sequalize and MySQL.
• Create Sequalize models.
• Write queries with Sequalize.

You will need to have a basic understanding of Node.js and MySQL before you begin.

Step 1: Create a new Node.js project

First, you need to initialize a new Node.js application. You can use npm init command to initialize the new application.

npm init

It will prompt a view like below where you need to enter several details like name, version, description and author.

package name: (nodejs-sequelize-example)
version: (1.0.0)
description: example-project
entry point: (index.js)
test command:
git repository:
keywords: node.js, orm, sequelize, mysql
author: Layercode
license: (ISC){
   “name”: “nodejs-sequelize-example”,
   “version”: “1.0.0”,
   “description”: “example-project”,
   “main”: “index.js”,
   “scripts”: {
     “test”: “echo \”Error: no test specified\” && exit 1"
   },
   “keywords”: [
     “node.js”, “orm”,“sequelize”, “mysql”
   ],
   “author”: “chameera”,
   “license”: “ISC”
}Is this OK? (yes)

Step 2: Installing Sequalize and other libraries

You need to install Sequalize and other third-party libraries for the project. In this tutorial, we will be using sequalize , express, cors and mysql2 libraries. (You can also use mysql library instead of mysql2).

// Express
npm install express --save// Sequalize
npm install sequelize --save// MySQL
npm install mysql2 --save// CORS
npm install cors --save

Here is a quick summary of what the above libraries do:

Step 3: Setting up the Express server

Express.js is an open-source web application framework for Node.js. It provides various inbuilt features to help developers implement web applications efficiently. So, we will also use Express.js in this example.

As the first step of setting up the Express server, you need to create a new file called server.js. Then you can make all the necessary configurations like enabling Cross-Origin Resource Sharing (CORS), parsing or defining the ports in this file using Express.

const express = require(“express”);
const cors = require(“cors”);const app = express();var corsOptions = {
  origin: “http://localhost:4200" // URL of the frontend
};app.use(cors(corsOptions));
app.use(express.json()); // parsing application/json
app.use(express.urlencoded({ extended: true })); // parsing application/x-www-form-urlencodedconst PORT = process.env.PORT || 8080; // Portapp.listen(PORT, () => {
   console.log(`Server is running on port ${PORT}.`);
});

Once all the configurations are in place, you can open a terminal and test the application using the node server.js command. If there are no errors, you should see the message; **Server \*is running on port 8000\*** in the terminal.

Step 4: Initializing Sequelize and database connection

Now, you need to configure the MySQL database connection and initialize Sequelize.

Here, we have created a new file named index.js under the app/models folder. This folder will contain all the models we create in the next step, and the index.js file is used to initialize Sequelize.

First, you need to import Sequalize and create a new instance by providing database name, username, password host and pool configuration.

const Sequelize = require("sequelize");const sequelize = new Sequelize(DATABASE, USER, PASSWORD, {
  host: HOST,
  dialect: mysql,
  operatorsAliases: false,

  pool: {
    max: 5,     
    min: 0,     
    idle: 10000
  }
});const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;
module.exports = db;

Then, you can create Sequelize models and import them into this file.

Step 5: Creating Sequelize models

You can create Sequelize models in the app/models folder. Here, we have created a model named Article with 4 fields. These fields represent the columns in the MySQL database, and the table will be automatically generated with a primary key.

module.exports = (sequelize, Sequelize) => {
  
  const Article = sequelize.define(“article”, {
    
    title: {
       type: Sequelize.STRING
    },
    subtitle: {
       type: Sequelize.STRING
    },
    author: {
       type: Sequelize.STRING
    },
    published: {
       type: Sequelize.BOOLEAN
    }  });  
    return Article;
};

Then, you need to import this model to the index.js file.

...const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;db.articles = require(“./article.model”)(sequelize, Sequelize);
module.exports = db;

Finally, import the index.js file to the server.js file and sync them with the database using Sequalize. Updated server.js file will be similar to below:

const express = require(“express”);
const cors = require(“cors”);
const app = express();

var corsOptions = {
  origin: “http://localhost:4200"
};

app.use(cors(corsOptions));
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
const db = require("./app/models/index.js");
db.sequelize.sync();

const PORT = process.env.PORT || 8080; 

// Port
app.listen(PORT, () => {
   console.log(`Server is running on port ${PORT}.`);
});

Step 6: Writing queries with Sequelize

You can start writing queries with Sequelize. Here, we have created a controller named articles.controller.js under the apps/controllers folder, and we will guide you through writing basic CRUD operations.

Sequelize provides a set of defined functions to handle CRUD operations.

• create(object) — Create a new record.
• findByPk(id) — Find a single record by primary key.
• findAll() — Find all records.
• update(data, where: { id: id }) — Update a record by id.
• destroy(where: { id: id }) — Delete a record by id.
• destroy(where: {}) — Delete all records.

First, you need to import the relevant models to the controller. Then you can use those models with Sequelize functions to write queries like below:

const db = require(“../models”);
const Article = db.articles;
const Op = db.Sequelize.Op;// Create Articleexports.create = (req, res) => {
  const article = {
    title: req.body.title,
    subtitle: req.body.subtitle,
    author: req.body.author,
    published: req.body.published ? req.body.published : false
  };  Article.create(article)
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
        err.message || "Some error occurred while creating the Tutorial."
     });
    });
};
// Find Single Articleexports.findOne = (req, res) => {
  const id = req.params.id;
  Article.findByPk(id)
    .then(data => {
      if (data) {
        res.send(data);
      } else {
        res.status(404).send({
          message: `Cannot find Article with id=${id}.`
        });
      }
    })
    .catch(err => {
      res.status(500).send({
      message: "Error retrieving Article with id=" + id
    });
  });
};
// Update Articleexports.update = (req, res) => {
  const id = req.params.id;
  Article.update(req.body, {
    where: { id: id }
  })
  .then(num => {
    if (num == 1) {
      res.send({
        message: "Article was updated successfully."
      });
    } else {
      res.send({
        message: `Cannot update Article with id=${id}.`
      });
    }
  })
  .catch(err => {
    res.status(500).send({
      message: "Error updating Article with id=" + id
    });
  });
};
// Delete Articleexports.delete = (req, res) => {
  const id = req.params.id;
  Article.destroy({
    where: { id: id }
  })
  .then(num => {
    if (num == 1) {
      res.send({
        message: "Article was deleted successfully!"
      });
    } else {
      res.send({
        message: `Cannot delete Article with id=${id}.`
      });
    }
  })
  .catch(err => {
    res.status(500).send({
      message: "Could not delete Article with id=" + id
    });
  });
};

Step 7: Defining routes

As the final step, you need to create routes for the above CRUD operations. Here, we have created a route file named articles.routes.js under the apps/routes folder.

module.exports = app => {  
  const articles = require(“../controllers/article.controller.js”);
  
  var router = require(“express”).Router();  // Create a new Article
  router.post(“/”, articles.create);  // Retrieve a single Article with id
  router.get(“/:id”, articles.findOne);  // Update a Article with id
  router.put(“/:id”, articles.update);  // Delete a Article with id
  router.delete(“/:id”, articles.delete);  app.use(‘/api/articles’, router);};

The Express router is used to handle routing, and all the endpoints related to articles is prefixed with api/articles. Then you need to import these routes in the server.js file just before port definition. Updated server.js file will look like below:

const express = require("express");
const cors = require("cors");
const app = express();var corsOptions = {
  origin: "http://localhost:4200"
};

app.use(cors(corsOptions));
app.use(express.json()); 
app.use(express.urlencoded({ extended: true })); 
const db = require("./app/models/index.js");
db.sequelize.sync();

require(“./app/routes/articles.routes.js”)(app);

const PORT = process.env.PORT || 8080; 

// Port
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}.`);
});

That’s basically it. You have successfully created a Node.js application with Sequelize and MySQL. You can restart the application and test the endpoints with Postman.

• http://localhost:8080/api/articles — GET
• http://localhost:8080/api/articles/ — POST, PUT, DELETE