Want to learn coding through videos for free?
Check my youtube channel => HERE!

How to Create a RESTful API Using Node.js Express and Sequelize

How to Create a RESTful API Using Node.js Express and Sequelize

In this tutorial you will learn how to create a RESTful API using node.js, express, sequelize, and MySQL.

This is not a tutorial for beginners,

If you are a beginner in node.js express, I suggest you first learn "Express Js Tutorial For Beginners". 

Let's get started.

 

What is Sequelize?

Sequelize is a promise-based ORM (Object Relational Mapping) node.js for Postgre, MySQL, MariaDB, SQLite, and Microsoft SQL Server.

By using ORM, you can easily create applications without writing SQL (Structured Query Language) commands.

Similarly, the Mongoose for MongoDB.

In this tutorial, I am using MySQL.

If you also use MySQL, you will love this tutorial.

Even though this tutorial uses MySQL, it doesn't mean you can't apply it to Postgre, SQLite, or Microsoft SQL Server, the only difference lies in the connection to the database.

You can look at the sequelize documentation for more information.

 

#1. EndPoint Design

This is important!

Before creating a RESTful API, it's a good idea to define the EndPoint of the RESTful API to be created first.

EndPoint is the routes from the API that we are going to create.

RESTful API using HTTP verbs.

Commonly used HTTP verbs are GET, POST, PUT, and DELETE.

GET to get data from the server or better known as READ, POST to CREATE new data, PUT to UPDATE data, and DELETE to delete data.

Or better known as CRUD (Create-Read-Update-Delete).

In this tutorial, I will share how to make a simple RESTful API to retrieve data from the server (GET), create new data to the server (POST), update data to the server (PUT), and delete data to the server (DELETE) from a table in the database, namely “products” table.

Here is the EndPoint design of the RESTful API that we will create:

 

#2. Create database and table

Create a new database on MySQL, you can use tools like SQLyog, PHPMyAdmin or similar tools.

Here I created a database with the name "sequelize_db".

If you create a database with the same name that's even better.

To create a database with MySQL, it can be done by executing the following query:

CREATE DATABASE sequelize_db;

The SQL command above will create a database with the name "sequelize_db".

Next, create a table in the "sequelize_db" database.

Here I create a table with the name "products".

If you create a table with the same name that's even better. 

To create a "products" table, you can do this by executing the following SQL command:

CREATE TABLE products(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200),
price DOUBLE,
createdAt DATE,
updatedAt DATE 
)ENGINE=INNODB;

The SQL command above will create a table called "products" with fields: id, title, price, createdAt, and updatedAt.

Sequelize requires you to have fields: id, createdAt, and updatedAt.

If you don't have that field, there will be an error.

 

#3. Install Express, MySQL2, Sequelize, and Cors

Create a folder on your computer, here I name it "sequelize-app".

If you create a folder with the same name, that's even better.

You are free to create it anywhere, whether on C, D, or on the Desktop.

This folder will be the main project folder.

Then open the “sequelize-app” folder using the code editor, here I am using Visual Studio Code.

I also recommend you to use Visual Studio Code.

You can download Visual Studio Code at the following link and install it on your computer:

https://code.visualstudio.com/

Next, open a terminal in Visual Studio Code on the terminal menu bar.

After that, type the following command to create the "package.json" file:

npm init –y

Next, install express, mysql2, sequelize, and cors by typing the following commands in the terminal:

npm install express mysql2 sequelize cors

Like the following picture:

Next, add the following code to the "package.json" file:

"type": "module",

So that the "package.json" file looks like this:

{
  "name": "sequelize-app",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "type": "module",
  "scripts": {
    "test": "echo "Error: no test specified" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "mysql2": "^2.2.5",
    "sequelize": "^6.3.5"
  }
}

This is so that we can use the ES6 Module Syntax to export and import modules.

 

#4. Application Structure

To make the application more neatly structured, we will apply the MVC (Model-View-Controllers) pattern.

Create folders "config", "controllers", "models", and "routes" inside the "sequelize-app" folder.

Then create a file "database.js" in the "config" folder, create a file "Product.js" in the "controllers" folder, create a file "Product.js" in the "models" folder, create a file "routes.js" inside the “routes” folder, and create a “index.js” file inside the “sequelize-app” folder.

Look at the following image for more details:

 

#5. Connect to Database

Open the "database.js" file in the "config" folder, then type the following code:

// import sequelize
import { Sequelize } from "sequelize";

// create connection
const db = new Sequelize('sequelize_db', 'root', '', {
    host: 'localhost',
    dialect: 'mysql'
});

// export connection
export default db;

 

#6. Models

Open the "Product.js" model file located in the "models" folder, then type the following code:

// import sequelize 
import { Sequelize } from "sequelize";
// import connection 
import db from "../config/database.js";

// init DataTypes
const { DataTypes } = Sequelize;

// Define schema
const Product = db.define('products', {
  // Define attributes
  title: {
    type: DataTypes.STRING
  },
  price: {
    type: DataTypes.DOUBLE
  }
},{
  // Freeze Table Name
  freezeTableName: true
});

// Export model Product
export default Product;

 

#7. Controllers

Open the “Product.js” controller file in the “controllers” folder, then type the following code:

// Import Product Model
import Product from "../models/Product.js";

// Get all products
export const getProducts = async (req, res) => {
    try {
        const product = await Product.findAll();
        res.send(product);
    } catch (err) {
        console.log(err);
    }
}

// Get product by id
export const getProductById = async (req, res) => {
    try {
        const product = await Product.findAll({
            where: {
                id: req.params.id
            }
        });
        res.send(product[0]);
    } catch (err) {
        console.log(err);
    }
}

// Create a new product
export const createProduct = async (req, res) => {
    try {
        await Product.create(req.body);
        res.json({
            "message": "Product Created"
        });
    } catch (err) {
        console.log(err);
    }
}

// Update product by id
export const updateProduct = async (req, res) => {
    try {
        await Product.update(req.body, {
            where: {
                id: req.params.id
            }
        });
        res.json({
            "message": "Product Updated"
        });
    } catch (err) {
        console.log(err);
    }
}

// Delete product by id
export const deleteProduct = async (req, res) => {
    try {
        await Product.destroy({
            where: {
                id: req.params.id
            }
        });
        res.json({
            "message": "Product Deleted"
        });
    } catch (err) {
        console.log(err);
    }
}

 

#8. Routes

Open the “routes.js” file in the “routes” folder, then type the following code:

// Import express
import express from "express";
// Import Product Controller
import { 
    getProducts,
    getProductById,
    createProduct,
    updateProduct,
    deleteProduct
 } from "../controllers/Product.js";

 // Init express router
const router = express.Router();

// Route get all products
router.get('/products', getProducts);
// Route get product by id
router.get('/products/:id', getProductById);
// Route create a new product
router.post('/products', createProduct);
// Route update product by id
router.put('/products/:id', updateProduct);
// Route delete product by id
router.delete('/products/:id', deleteProduct);

// export router
export default router;

 

#9. Index.js

Open the "index.js" file in the "sequelize-app" folder, then type the following code:

// Import express
import express from "express";
// Import cors
import cors from "cors";
// Import connection
import db from "./config/database.js";
// Import router
import Router from "./routes/routes.js";

// Init express
const app = express();
// use express json
app.use(express.json());
// use cors
app.use(cors());

// Testing database connection 
try {
    await db.authenticate();
    console.log('Connection has been established successfully.');
} catch (error) {
    console.error('Unable to connect to the database:', error);
}

// use router
app.use(Router);

// listen on port
app.listen(5000, () => console.log('Server running at http://localhost:5000'));

 

#10. Testing

To make sure the application runs properly, run the application by typing the following command in the terminal:

node index

If it goes well, it will look like the following image:

In this tutorial, I will use POSTMAN to test.

You can download POSTMAN at the following link:

https://www.postman.com/downloads/

Download according to your OS (Operating System), then install it on your computer!

 

#10.1 CREATE

Open POSTMAN, then select the "POST" method => enter EndPoint in the URL column => select Body => select raw => select application/json => enter data in json format => click the Send button.

Like the following picture:

If successful, there will be a message: "Product Created" as shown above.

After that, insert some more data to make testing easier.

 

#10.2 READ

Change the method to "GET" with the same EndPoint, then click the "Send" button.

Like the following picture:

To display products by id, add the id in the URL column as shown below:

In the image above, I am displaying the product with id = 2.

 

#10.3 UPDATE

Select the "PUT" method => enter EndPoint along with the product id that will be updated in the URL column => select Body => select raw => select application/json => enter data in json format => click the Send button.

Like the following picture:

If the update is successful, there will be a message: "Product Updated" as shown above.

 

#10.4 DELETE

Select the "DELETE" method, then enter the URL along with the id to be deleted, then click the "Send" button.

Like the following picture:

If the delete is successful, there will be a message: "Product Deleted" as shown above.

In the image above, I deleted the product with id = 1.

 

Conclusion:

Today's discussion is how to create a RESTful API using node.js, express, and sequelize.

Therefore, you have an idea of how to build an application without writing SQL commands to the application.

So what are you waiting for, Let's Coding!

Download Source Code

Comments (0)

Leave a Comment