This post explains how we can build a CRUD using NodeJS ExpressJS MySQL database. Here we are going to use Express Framework and EJS as a template engine to build a CRUD operation. For CRUD operation we will use HTTP Methods to make our application. We will post and retrieving data using MySQL database.
Step 1: Install nodeJs package and Init application
Initially download NodeJS and install in your system and confirm everything configured correctly using the following command.
|
1 |
node -v |
Step 2: Create a directory and init application
Create an applicate folder and init the project using the following command.
|
1 |
npm init |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
{ "name": "crud", "version": "1.0.0", "description": "", "main": "app.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1" }, "author": "LearnInfinity", "license": "ISC", "dependencies": { "body-parser": "^1.19.0", "ejs": "^2.7.1", "express": "^4.17.1", "mysql": "^2.17.1" } } |
Step 3: Install required packages using NPM
The following modules are going to be required to create the application.
- Express: used to create manage routing and process HTTP requests from the end client.
- body-parser: used to parse incoming request from the end client.
- mysql: Node JS driver to connect MySQL and perform CRUD operations.
- ejs: is templating engine and its used to render HTML pages to end client
- nodemon: Optional package and Installed globally. It helps us to listen for modifications to files and automatically restart the app server.
Run the following command to install the above mention modules as dependencies for our application.
|
1 2 3 |
npm install --save express mysql body-parser ejs npm install -g nodemon |
Step 5: Create app.js file
Create app.js server file and include the required dependency package in it for our CRUD application. And define the server port and write an app server .listen() function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
const path = require('path'); const express = require('express'); const ejs = require('ejs'); const bodyParser = require('body-parser'); const app = express(); // Server Listening app.listen(3000, () => { console.log('Server is running at port 3000'); }); nodemon app (OR) npm start |
Step 6: Create a Database Connection
Create a new database with the following name node_crud and If you create it with the same name it’s better.
To create a database in MySQL, execute the following query:
|
1 |
CREATE DATABASE node_crud; |
After creating a database, we need to create a table with the following name users. If you create it with the same name it’s better. To create a table in Database, execute the following query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE `users` ( `id` int(11) NOT NULL, `name` varchar(150) NOT NULL, `email` varchar(150) NOT NULL, `phone_no` varchar(25) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `users` ADD PRIMARY KEY (`id`); ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1; |
To define a MySQL connection add following lines in app.js file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
const mysql = require('mysql'); const connection=mysql.createConnection({ host:'localhost', user:'root', password:'', database:'node_crud' }); connection.connect(function(error){ if(!!error) console.log(error); else console.log('Database Connected!'); }); |
Step 7: Define View engine with ejs / public path / View files path
Here we are going to define a template view engine with ejs using set() and use() function and also define a Public path and View folder path location.
|
1 2 3 4 5 6 7 |
//set views file app.set('views',path.join(__dirname,'views')); //set view engine app.set('view engine', 'ejs'); app.use(bodyParser.json()); app.use(bodyParser.urlencoded({ extended: false })); |
Step 8: Define view HTML files
Here we are going to define HTML page with ejs extension and adding HTML content in it. We created four HTML files for user CURD operation. I am going to create file with named as user_index.ejs.
|
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 |
<!DOCTYPE html> <html lang="en"> <head> <title><%= title %></title> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script> </head> <body> <div class="container"> <h2><%= title %></h2> <a href="/add" class="btn btn-sm btn-primary">Add User</a><br/><br/> <table class="table table-dark table-striped"> <thead> <tr> <th>Name</th> <th>Email</th> <th>Phone No.</th> <th>Action</th> </tr> </thead> <tbody> <% users.forEach(function(user){ %> <tr> <td><%= user.name %></td> <td><%= user.email %></td> <td><%= user.phone_no %></td> <td> <a href="edit/<%= user.id %>" class="btn btn-sm btn-primary">Edit</a> <a href="delete/<%= user.id %>" class="btn btn-sm btn-danger">Delete</a> </td> </tr> <% }); %> </tbody> </table> </div> </body> </html> |
Step 9: Define ‘/’ URL path with User listing procedure
Here we are going to add the Home path and define user index page with displaying user list in it.
|
1 2 3 4 5 6 7 8 9 10 11 |
app.get('/',(req, res) => { // res.send('CRUD Operation using NodeJS / ExpressJS / MySQL'); let sql = "SELECT * FROM users"; let query = connection.query(sql, (err, rows) => { if(err) throw err; res.render('user_index', { title : 'CRUD Operation using NodeJS / ExpressJS / MySQL', users : rows }); }); }); |
Step 10: Run the application and verify with browser
Here we are going to run an application with the following command to test our CRUD application in a browser.
Finally app.js file code following details.
|
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 |
const path = require('path'); const express = require('express'); const ejs = require('ejs'); const bodyParser = require('body-parser'); const mysql = require('mysql'); const app = express(); const connection=mysql.createConnection({ host:'localhost', user:'root', password:'', database:'node_crud' }); connection.connect(function(error){ if(!!error) console.log(error); else console.log('Database Connected!'); }); //set views file app.set('views',path.join(__dirname,'views')); //set view engine app.set('view engine', 'ejs'); app.use(bodyParser.json()); app.use(bodyParser.urlencoded({ extended: false })); app.get('/',(req, res) => { // res.send('CRUD Operation using NodeJS / ExpressJS / MySQL'); let sql = "SELECT * FROM users"; let query = connection.query(sql, (err, rows) => { if(err) throw err; res.render('user_index', { title : 'CRUD Operation using NodeJS / ExpressJS / MySQL', users : rows }); }); }); // Server Listening app.listen(3000, () => { console.log('Server is running at port 3000'); }); |
Run the Application using the following command.
|
1 2 3 |
npm start (OR) nodemon app http://localhost:3000/ |
Now you are ready to see the demonstration of CRUD using NodeJS ExpressJS MySQL.
