Node js Express Sequelize and Mysql Example
In this article, we will explain to you how to create Rest APIs example with Express, Sequelize & MySQL in node js(Node js Express Sequelize and Mysql Example).
Express is the most famous web framework for Node.js that supports routing, middleware, view system. Sequelize is a promising Node.js ORM that supports offers for Postgres, MySQL, SQL Server. In this tutorial, we will show you step by step to create Node.js Restful CRUD API using Express, Sequelize with MySQL.
So you can see our node js example.
Application Directory Structure
We have created the best directory structure for the rest API CRUD operations. so you can follow the directory structure below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | /nodejs_rest_api /app /config db.config.js env.js /controller user.controller.js /model user.model.js /route user.route.js /node_modules package.json server.js |
Create Node Js Application
First, we will open the command prompt and create the application in our directory. for this, you can follow the below command.
1 2 | mkdir nodejs_rest_api cd my_node_app |
The run “npm init” command through we can create a new package.json file in our application directory
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | npm init name: (nodejs_rest_api) version: (1.0.0) description: entry point: (index.js) server.js test command: git repository: keywords: author: license: (ISC) Is this ok? (yes) yes { "name": "nodejs_rest_api", "version": "1.0.0", "description": "", "main": "server.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1", "start": "node server.js" }, "keywords": [], "author": "", "license": "ISC" } |
Install NPM Package
After the done setup node js application, we will install the express, sequelize, mysql2 and cors npm package. so you can see the following npm command.
1 | npm install express sequelize body-parser mysql2 cors --save |
Setup Express web server
In this step, We will create the server.js file in our application directory. after then setup the our application.
server.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | const express = require("express"); const cors = require("cors"); const bodyParser = require('body-parser'); const app = express(); app.use(bodyParser.urlencoded({ extended: false })); app.use(bodyParser.json()); app.use(cors()); const db = require('./app/config/db.config.js'); force: true will drop the table if it already exists db.sequelize.sync({force: true}).then(() => { console.log('Drop and Resync with { force: true }'); }); // api routes app.get("/", (req, res) => { res.json({ message: "Welcome to Our App." }); }); require('./app/route/user.route.js')(app); // set port, listen for requests const PORT = process.env.PORT || 3000; app.listen(PORT, () => { console.log(`Server is running on port ${PORT}.`); }); |
Setting up Sequelize MySQL connection
In this step, we will create .env file and setup database connection using the sequelize with mysql.
app/config/env.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | const env = { database: 'myapidb', username: 'root', password: "", host: 'localhost', dialect: 'mysql', pool: { max: 5, min: 0, acquire: 30000, idle: 10000 } }; module.exports = env; |
app/config/db.config.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | const env = require('./env.js'); const Sequelize = require('sequelize'); const sequelize = new Sequelize(env.database, env.username, env.password, { host: env.host, dialect: env.dialect, operatorsAliases: false, pool: { max: env.max, min: env.pool.min, acquire: env.pool.acquire, idle: env.pool.idle } }); const db = {}; db.Sequelize = Sequelize; db.sequelize = sequelize; //Models/tables db.users = require('../model/user.model.js')(sequelize, Sequelize); module.exports = db; |
Create Sequelize model
Now, We create the sequelize model and which is define database schema to this sequelize model. so you can see user.model.js file as example.
app/model/user.model.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | module.exports = (sequelize, Sequelize) => { const User = sequelize.define('users', { firstname: { type: Sequelize.STRING }, lastname: { type: Sequelize.STRING }, email: { type: Sequelize.STRING }, password: { type: Sequelize.STRING } }); return User; } |
Create controller
Here, we create the some different methods such as create, findall, update, and delete. which is helpful for getting data, insert data and update data with mysql.
app/controller/user.controller.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | const db = require('../config/db.config.js'); const env = require('../config/env.js'); const User = db.users; // Post a User exports.create = (req, res) => { // Save to MySQL database User.create({ firstname: req.body.firstname, lastname: req.body.lastname, email: req.body.email, password: req.body.password }).then(user => { // Send created user to client res.send(user); }); }; // FETCH all Users exports.findAll = (req, res) => { User.findAll().then(users => { // Send all users to Client res.send(users); }); }; // Find a User by Id exports.findById = (req, res) => { User.findById(req.params.userId).then(user => { res.send(user); }) }; // Update a User exports.update = (req, res) => { const id = req.params.userId; User.update( { firstname: req.body.firstname, lastname: req.body.lastname, email: req.body.email }, { where: {id: req.params.userId} } ).then(() => { res.status(200).send({ message: 'updated successfully a user with id = ' + id }); }); }; // Delete a User by Id exports.delete = (req, res) => { const id = req.params.userId; User.destroy({ where: { id: id } }).then(() => { res.status(200).send({ message: 'deleted successfully a user with id = ' + id }); }); }; |
Create Route
Now, We will define the all users route. so you can see our route example.
app/route/user.route.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | module.exports = function(app) { const users = require('../controller/user.controller.js'); // Create a new User app.post('/api/users', users.create); // Retrieve all User app.get('/api/users', users.findAll); // Retrieve a single User by Id app.get('/api/users/:userId', users.findById); // Update a User with Id app.put('/api/users/:userId', users.update); // Delete a User with Id app.delete('/api/users/:userId', users.delete); } |
Run Node js Application
we will run the node js application using the below command. so you can following the below command.
1 | node server.js |
Now you can run the example using the below Url in the browser.
1 | http://localhost:3000/ |
