This article shows how you can create a simple CRUD (Create, Read, Update, Delete) application in Node.js using MySQL as database. We will also be using Express Framework to build this Node.js web application.
We will be using EJS as templating engine.
We will be using HTTP Methods like GET, PUT, POST, DELETE to make our application RESTful. We will be posting and retrieving data to and from MySQL database in JSON format.
Here’s the step-by-step procedure to create the CRUD application:
1) Open terminal/command-prompt and go to your Node.js project directory. Mine is /var/www/html/test/nodejs/crud-mysql
mukesh@chapagain:~$ cd /var/www/html/test/nodejs/crud-mysql
mukesh@chapagain:/var/www/html/test/nodejs/crud-mysql$
2) Run npm init command in terminal that will create package.json file for your project.
mukesh@chapagain:/var/www/html/test/nodejs/crud-mysql$ npm init
This utility will walk you through creating a package.json file.
It only covers the most common items, and tries to guess sensible defaults.
See `npm help json` for definitive documentation on these fields
and exactly what they do.
Use `npm install <pkg> --save` afterwards to install a package and
save it as a dependency in the package.json file.
Press ^C at any time to quit.
name: (crud-mysql)
version: (1.0.0)
description: Node.js CRUD application using MySQL
entry point: (index.js)
test command:
git repository:
keywords: node,crud,mysql
author: Mukesh Chapagain
license: (ISC)
About to write to /var/www/html/test/nodejs/crud-mysql/package.json:
{
"name": "crud-mysql",
"version": "1.0.0",
"description": "Node.js CRUD application using MySQL",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [
"node",
"crud",
"mysql"
],
"author": "Mukesh Chapagain",
"license": "ISC"
}
Is this ok? (yes) yes
mukesh@chapagain:/var/www/html/test/nodejs/crud-mysql$
3) Install Express Framework module with the following command:
npm install express -save
4) Install express-validator module that will be used for form validation
npm install express-validator -save
5) Install body-parser module
body-parser module is used to read HTTP POST data. It’s an express middleware that reads form’s input and store it as javascript object.
npm install body-parser -save
6) Install method-override module
This module let us use HTTP verbs such as PUT or DELETE in places where they are not supported. This module provides different ways of overriding like overriding using header, overriding using query value, or through custom logic. In this CRUD application, we will be using the custom logic of overriding.
npm install method-override -save
7) Install express-flash module
Flash messages are shown for some time and cleared after being displayed to the user. They are mainly used when we have to redirect to next page and show certain message like success message or error message. Flash messages are store in session.
npm install express-flash -save
8) Install cookie-parser & express-session modules
Flash messages are stored in session. So, we also have to install and use cookie-parser & session modules.
npm install cookie-parser -save
npm install express-session -save
9) Install EJS templating engine
npm install ejs -save
10) Install MySQL module with the following command:
npm install mysql -save
11) Install express-myconnection module
Express middleware to provide consistent API for MySQL connection. It can auto close/release mysql connection. Using this module, the connection can be accessed anywhere in router.
npm install express-myconnection -save
12) We will now create a new MySQL database. Let us name the database as ‘test‘.
create database test;
Then, we will create a new table in database ‘test’. Let us name the table as ‘users‘.
use test;
CREATE TABLE users (
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL,
age int(3) NOT NULL,
email varchar(100) NOT NULL,
PRIMARY KEY (id)
);
13) Create a config.js file in your project root folder. This file basically contains your database credentials.
config.js
var config = {
database: {
host: 'localhost', // database host
user: 'root', // your database username
password: 'root', // your database password
port: 3306, // default MySQL port
db: 'test' // your database name
},
server: {
host: '127.0.0.1',
port: '3000'
}
}
module.exports = config
14) Create app.js in your project root folder. In this file, we load all the modules that we installed in above steps.
app.js
var express = require('express')
var app = express()
var mysql = require('mysql')
/**
* This middleware provides a consistent API
* for MySQL connections during request/response life cycle
*/
var myConnection = require('express-myconnection')
/**
* Store database credentials in a separate config.js file
* Load the file/module and its values
*/
var config = require('./config')
var dbOptions = {
host: config.database.host,
user: config.database.user,
password: config.database.password,
port: config.database.port,
database: config.database.db
}
/**
* 3 strategies can be used
* single: Creates single database connection which is never closed.
* pool: Creates pool of connections. Connection is auto release when response ends.
* request: Creates new connection per new request. Connection is auto close when response ends.
*/
app.use(myConnection(mysql, dbOptions, 'pool'))
/**
* setting up the templating view engine
*/
app.set('view engine', 'ejs')
/**
* import routes/index.js
* import routes/users.js
*/
var index = require('./routes/index')
var users = require('./routes/users')
/**
* Express Validator Middleware for Form Validation
*/
var expressValidator = require('express-validator')
app.use(expressValidator())
/**
* body-parser module is used to read HTTP POST data
* it's an express middleware that reads form's input
* and store it as javascript object
*/
var bodyParser = require('body-parser')
/**
* bodyParser.urlencoded() parses the text as URL encoded data
* (which is how browsers tend to send form data from regular forms set to POST)
* and exposes the resulting object (containing the keys and values) on req.body.
*/
app.use(bodyParser.urlencoded({ extended: true }))
app.use(bodyParser.json())
/**
* This module let us use HTTP verbs such as PUT or DELETE
* in places where they are not supported
*/
var methodOverride = require('method-override')
/**
* using custom logic to override method
*
* there are other ways of overriding as well
* like using header & using query value
*/
app.use(methodOverride(function (req, res) {
if (req.body && typeof req.body === 'object' && '_method' in req.body) {
// look in urlencoded POST bodies and delete it
var method = req.body._method
delete req.body._method
return method
}
}))
/**
* This module shows flash messages
* generally used to show success or error messages
*
* Flash messages are stored in session
* So, we also have to install and use
* cookie-parser & session modules
*/
var flash = require('express-flash')
var cookieParser = require('cookie-parser');
var session = require('express-session');
app.use(cookieParser('keyboard cat'))
app.use(session({
secret: 'keyboard cat',
resave: false,
saveUninitialized: true,
cookie: { maxAge: 60000 }
}))
app.use(flash())
app.use('/', index)
app.use('/users', users)
app.listen(3000, function(){
console.log('Server running at port 3000: http://127.0.0.1:3000')
})
15) As you can see above app.js code, we have loaded two local files/modules: routes/index.js and routes/users.js. These two files contains code to show content of index and users page.
routes/index.js
var express = require('express')
var app = express()
app.get('/', function(req, res) {
// render to views/index.ejs template file
res.render('index', {title: 'My Node.js Application'})
})
/**
* We assign app object to module.exports
*
* module.exports exposes the app object as a module
*
* module.exports should be used to return the object
* when this file is required in another module like app.js
*/
module.exports = app;
16) routes/users.js is responsible for handling CRUD operations like adding, editing, deleting and viewing users from database. Database queries, form validation and template rendering is done here.
routes/users.js
var express = require('express')
var app = express()
// SHOW LIST OF USERS
app.get('/', function(req, res, next) {
req.getConnection(function(error, conn) {
conn.query('SELECT * FROM users ORDER BY id DESC',function(err, rows, fields) {
//if(err) throw err
if (err) {
req.flash('error', err)
res.render('user/list', {
title: 'User List',
data: ''
})
} else {
// render to views/user/list.ejs template file
res.render('user/list', {
title: 'User List',
data: rows
})
}
})
})
})
// SHOW ADD USER FORM
app.get('/add', function(req, res, next){
// render to views/user/add.ejs
res.render('user/add', {
title: 'Add New User',
name: '',
age: '',
email: ''
})
})
// ADD NEW USER POST ACTION
app.post('/add', function(req, res, next){
req.assert('name', 'Name is required').notEmpty() //Validate name
req.assert('age', 'Age is required').notEmpty() //Validate age
req.assert('email', 'A valid email is required').isEmail() //Validate email
var errors = req.validationErrors()
if( !errors ) { //No errors were found. Passed Validation!
/********************************************
* Express-validator module
req.body.comment = 'a <span>comment</span>';
req.body.username = ' a user ';
req.sanitize('comment').escape(); // returns 'a <span>comment</span>'
req.sanitize('username').trim(); // returns 'a user'
********************************************/
var user = {
name: req.sanitize('name').escape().trim(),
age: req.sanitize('age').escape().trim(),
email: req.sanitize('email').escape().trim()
}
req.getConnection(function(error, conn) {
conn.query('INSERT INTO users SET ?', user, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// render to views/user/add.ejs
res.render('user/add', {
title: 'Add New User',
name: user.name,
age: user.age,
email: user.email
})
} else {
req.flash('success', 'Data added successfully!')
// render to views/user/add.ejs
res.render('user/add', {
title: 'Add New User',
name: '',
age: '',
email: ''
})
}
})
})
}
else { //Display errors to user
var error_msg = ''
errors.forEach(function(error) {
error_msg += error.msg + '<br>'
})
req.flash('error', error_msg)
/**
* Using req.body.name
* because req.param('name') is deprecated
*/
res.render('user/add', {
title: 'Add New User',
name: req.body.name,
age: req.body.age,
email: req.body.email
})
}
})
// SHOW EDIT USER FORM
app.get('/edit/(:id)', function(req, res, next){
req.getConnection(function(error, conn) {
conn.query('SELECT * FROM users WHERE id = ' + req.params.id, function(err, rows, fields) {
if(err) throw err
// if user not found
if (rows.length <= 0) {
req.flash('error', 'User not found with id = ' + req.params.id)
res.redirect('/users')
}
else { // if user found
// render to views/user/edit.ejs template file
res.render('user/edit', {
title: 'Edit User',
//data: rows[0],
id: rows[0].id,
name: rows[0].name,
age: rows[0].age,
email: rows[0].email
})
}
})
})
})
// EDIT USER POST ACTION
app.put('/edit/(:id)', function(req, res, next) {
req.assert('name', 'Name is required').notEmpty() //Validate name
req.assert('age', 'Age is required').notEmpty() //Validate age
req.assert('email', 'A valid email is required').isEmail() //Validate email
var errors = req.validationErrors()
if( !errors ) { //No errors were found. Passed Validation!
/********************************************
* Express-validator module
req.body.comment = 'a <span>comment</span>';
req.body.username = ' a user ';
req.sanitize('comment').escape(); // returns 'a <span>comment</span>'
req.sanitize('username').trim(); // returns 'a user'
********************************************/
var user = {
name: req.sanitize('name').escape().trim(),
age: req.sanitize('age').escape().trim(),
email: req.sanitize('email').escape().trim()
}
req.getConnection(function(error, conn) {
conn.query('UPDATE users SET ? WHERE id = ' + req.params.id, user, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// render to views/user/add.ejs
res.render('user/edit', {
title: 'Edit User',
id: req.params.id,
name: req.body.name,
age: req.body.age,
email: req.body.email
})
} else {
req.flash('success', 'Data updated successfully!')
// render to views/user/add.ejs
res.render('user/edit', {
title: 'Edit User',
id: req.params.id,
name: req.body.name,
age: req.body.age,
email: req.body.email
})
}
})
})
}
else { //Display errors to user
var error_msg = ''
errors.forEach(function(error) {
error_msg += error.msg + '<br>'
})
req.flash('error', error_msg)
/**
* Using req.body.name
* because req.param('name') is deprecated
*/
res.render('user/edit', {
title: 'Edit User',
id: req.params.id,
name: req.body.name,
age: req.body.age,
email: req.body.email
})
}
})
// DELETE USER
app.delete('/delete/(:id)', function(req, res, next) {
var user = { id: req.params.id }
req.getConnection(function(error, conn) {
conn.query('DELETE FROM users WHERE id = ' + req.params.id, user, function(err, result) {
//if(err) throw err
if (err) {
req.flash('error', err)
// redirect to users list page
res.redirect('/users')
} else {
req.flash('success', 'User deleted successfully! id = ' + req.params.id)
// redirect to users list page
res.redirect('/users')
}
})
})
})
module.exports = app
17) In routes/index.js file, we have only rendered views/index.ejs template with a title text.
views/index.ejs
<!--
including header content which is common for all pages
-->
<%- include layouts/header.ejs %>
<!--
including footer content which is common for all pages
-->
<%- include layouts/footer.ejs %>
18) You can see that in views/index.js, we have only included two layouts files. Here are they:
views/layouts/header.ejs
<html>
<head>
<title><%= title %></title>
</head>
<body>
<div>
<a href="/">Home</a> |
<a href="/users/add">Add New User</a> |
<a href="/users">All Users List</a>
</div>
<h1><%= title %></h1>
views/layouts/footer.ejs
</body>
</html>
19) In routes/users.js file, there are different functions to add, edit, delete and view data. The first one is to show the list of users. This uses views/user/list.ejs template file to show the list of users.
views/user/list.ejs
<!--
including header content which is common for all pages
-->
<%- include ../layouts/header.ejs %>
<% if (messages.error) { %>
<p style="color:red"><%- messages.error %></p>
<% } %>
<% if (messages.success) { %>
<p style="color:green"><%- messages.success %></p>
<% } %>
<table width='80%' border=0>
<tr style='text-align:left; background-color:#CCC'>
<th>Name</th>
<th>Age</th>
<th>Email</th>
<th>Action</th>
</tr>
<!--
Using FOREACH LOOP for the users array
myArray.forEach(function(el, index) {
// el - current element, i - index
});
-->
<% if (data) { %>
<% data.forEach(function(user){ %>
<tr>
<td><%= user.name %></td>
<td><%= user.age %></td>
<td><%= user.email %></td>
<td>
<div style="float:left">
<a href='/users/edit/<%= user.id %>'>Edit</a>
<form method="post" action="/users/delete/<%= user.id %>" style="float:right">
<input type="submit" name="delete" value='Delete' onClick="return confirm('Are you sure you want to delete?')" />
<input type="hidden" name="_method" value="DELETE" />
</form>
</div>
</td>
</tr>
<% }) %>
<% } %>
</table>
<!--
including footer content which is common for all pages
-->
<%- include ../layouts/footer.ejs %>
20) Add New User Form
views/user/add.ejs
<!--
including header content which is common for all pages
-->
<%- include ../layouts/header.ejs %>
<% if (messages.error) { %>
<p style="color:red"><%- messages.error %></p>
<% } %>
<% if (messages.success) { %>
<p style="color:green"><%- messages.success %></p>
<% } %>
<form action="/users/add" method="post" name="form1">
<table width="25%" border="0">
<tr>
<td>Name</td>
<td><input type="text" name="name" id="name" value="<%= name %>" /></td>
</tr>
<tr>
<td>Age</td>
<td><input type="text" name="age" value="<%= age %>" /></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="email" value="<%= email %>" /></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="Submit" value="Add" /></td>
</tr>
</table>
</form>
<!--
including footer content which is common for all pages
-->
<%- include ../layouts/footer.ejs %>
21) Edit User Form
views/user/edit.ejs
<!--
including header content which is common for all pages
-->
<%- include ../layouts/header.ejs %>
<% if (messages.error) { %>
<p style="color:red"><%- messages.error %></p>
<% } %>
<% if (messages.success) { %>
<p style="color:green"><%- messages.success %></p>
<% } %>
<form action="/users/edit/<%= id %>" method="post" name="form1">
<table width="25%" border="0">
<tr>
<td>Name</td>
<td><input type="text" name="name" id="name" value="<%= name %>" /></td>
</tr>
<tr>
<td>Age</td>
<td><input type="text" name="age" value="<%= age %>" /></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="email" value="<%= email %>" /></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="Submit" value="Edit" /></td>
</tr>
</table>
<input type="hidden" name="_method" value="PUT" />
</form>
<!--
including footer content which is common for all pages
-->
<%- include ../layouts/footer.ejs %>
As you see in app.js code, our server runs on port 3000. Hence, to use the application, we have to run the following URL on browser: http://127.0.0.1:3000
http://127.0.0.1:3000 = Shows index page which just contains a simple text title
http://127.0.0.1:3000/users = Shows list of users
http://127.0.0.1:3000/users/add = Shows form to add new user
http://127.0.0.1:3000/users/edit/ID = Shows form to edit user. ID can be any number.
Here’s the GitHub Repository containing all source code of this CRUD application: https://github.com/chapagain/nodejs-mysql-crud
Hope this helps. Thanks.