In this tutorial guide, we will build a simple NodeJS API using ExpressJS and MySQL with basic CRUD (create, read, update, delete) operations. NodeJS is a JavaScript run-time environment used to execute JavaScript code outside browsers i.e on the servers. Visit our other blog post, to learn more about what is NodeJS? NodeJS along with ExpressJS (web framework for NodeJS) is a widely used combination to build RESTful API’s with a database query language like MySQL or MongoDB.
Setup NodeJS Project
First of all, we need to create a new NodeJS app with ExpressJS and MySQL installed as dependencies using NPM (Node Package Manager). If you are new to NodeJS then following guides are recommended before getting started.
//App dependencies
const express = require('express');
const app = express(); // creates an express application
const bodyParser = require('body-parser');
var cors = require('cors');
//App middle-wares
app.use(cors());
app.use(bodyParser.json());
app.use((req, res, next) => {
res.header('Access-Control-Allow-Origin', '*');
res.header('Access-Control-Allow-Headers', 'Origin, X-Requested-With, Content-Type, Accept, Authorization');
next();
});
//Routes
const users = express.Router();
app.use('/users', users);
//App server creation
app.listen(3000, function(){
console.log('Server is running at port : ' + 3000);
});Database Connection
We need to use mysql node package to create and use MySQL database with NodeJS. Following code can be used to connect to the MySQL database.
const mysql = require('mysql'); //adding the mysql module
const db = mysql.createConnection({
host: "localhost",
user: "db username",
password: "db password",
database: "db name"
});CRUD Operations
For demo we will create CRUD operations for managing users. We are assuming that a table named ‘users’ exist with necessary columns (fname, lname, username, email, password, role, createdAt). We will configure following routes/endpoints for our NodeJS API.
- GET: http://localhost:3000/users/ – To read all users
- GET: http://localhost:3000/users/:id – To read single user with specific ID
- POST: http://localhost:3000/users/ – To create new user
- POST: http://localhost:3000/users/:id – To update single user with specific ID
- DELETE: http://localhost:3000/users/:id – To delete single user with specific ID
Create New User
//POST: http://localhost:3000/users/
users.post('/', (req, res, next) => {
const sql = "SELECT * FROM users WHERE email = ?";
conn.query(sql, [req.body.email], function (err, result) {
if (err) throw err;
if(!result[0]){
const user = {fname: req.body.fname, lname: req.body.lname, username: req.body.username, email: req.body.email, password: req.body.password};
const sql = "INSERT INTO users SET ?";
conn.query(sql, [user], function (err, result) {
if (err) throw err;
res.status(200).send(JSON.stringify({message: "User is successfuly added!"}));
});
}else{
res.status(400).send(JSON.stringify({message: "User already exists with this email address!"}));
}
});
});Read All Users
//GET: http://localhost:3000/users/
users.get('/', (req, res, next) => {
const sql = "SELECT * FROM users";
conn.query(sql, function (err, result) {
if (err) throw err;
res.status(200).send(result);
});
});Read Single User With ID
//GET: http://localhost:3000/users/:id
users.get('/:id', authFunc.checkAdmin, (req, res, next) => {
const sql = "SELECT * FROM users WHERE id = ?";
conn.query(sql, [req.params.id], function (err, result) {
if (err) throw err;
res.status(200).send(result[0]);
});
});Update User With ID
//POST: http://localhost:3000/users/:id
users.post('/:id', (req, res, next) => {
const sql = "UPDATE users SET ? WHERE id = ?";
const user = {fname: req.body.fname, lname: req.body.lname, username: req.body.username, email: req.body.email, password: req.body.password};
conn.query(sql, [user, req.params.id], function (err, result) {
if (err) throw err;
res.status(200).send(JSON.stringify({message: "User information is successfuly updated!"}));
});
});Delete User With ID
//DELETE: http://localhost:3000/users/:id
users.delete('/:id', (req, res) => {
const sql = "SELECT * FROM users WHERE id = ?";
conn.query(sql, [req.params.id], function (err, result) {
if (err) throw err;
if(!result[0]){
res.status(404).send(JSON.stringify({message: "No user exists with this ID!"}));
}else{
const sql = "DELETE FROM users WHERE id = ?";
conn.query(sql, [req.params.id], function (err, result) {
if (err) throw err;
res.status(200).send(JSON.stringify({message: "User is successfuly deleted!"}));
});
}
});
});Complete Code
//app dependencies
const express = require('express');
const app = express(); // creates an express application
const bodyParser = require('body-parser');
var cors = require('cors');
const mysql = require('mysql'); //adding the mysql module
//app middle-wares
app.use(cors());
app.use(bodyParser.json());
app.use((req, res, next) => {
res.header('Access-Control-Allow-Origin', '*');
res.header('Access-Control-Allow-Headers', 'Origin, X-Requested-With, Content-Type, Accept, Authorization');
next();
});
//database connection
const db = mysql.createConnection({
host: "localhost",
user: "db username",
password: "db password",
database: "db name"
});
//Routes
const users = express.Router();
app.use('/users', users);
//POST: http://localhost:3000/users/
users.post('/', (req, res, next) => {
const sql = "SELECT * FROM users WHERE email = ?";
conn.query(sql, [req.body.email], function (err, result) {
if (err) throw err;
if(!result[0]){
const user = {fname: req.body.fname, lname: req.body.lname, username: req.body.username, email: req.body.email, password: req.body.password};
const sql = "INSERT INTO users SET ?";
conn.query(sql, [user], function (err, result) {
if (err) throw err;
res.status(200).send(JSON.stringify({message: "User is successfuly added!"}));
});
}else{
res.status(400).send(JSON.stringify({message: "User already exists with this email address!"}));
}
});
});
//GET: http://localhost:3000/users/
users.get('/', (req, res, next) => {
const sql = "SELECT * FROM users";
conn.query(sql, function (err, result) {
if (err) throw err;
res.status(200).send(result);
});
});
//GET: http://localhost:3000/users/:id
users.get('/:id', authFunc.checkAdmin, (req, res, next) => {
const sql = "SELECT * FROM users WHERE id = ?";
conn.query(sql, [req.params.id], function (err, result) {
if (err) throw err;
res.status(200).send(result[0]);
});
});
//POST: http://localhost:3000/users/:id
users.post('/:id', (req, res, next) => {
const sql = "UPDATE users SET ? WHERE id = ?";
const user = {fname: req.body.fname, lname: req.body.lname, username: req.body.username, email: req.body.email, password: req.body.password};
conn.query(sql, [user, req.params.id], function (err, result) {
if (err) throw err;
res.status(200).send(JSON.stringify({message: "User information is successfuly updated!"}));
});
});
//DELETE: http://localhost:3000/users/:id
users.delete('/:id', (req, res) => {
const sql = "SELECT * FROM users WHERE id = ?";
conn.query(sql, [req.params.id], function (err, result) {
if (err) throw err;
if(!result[0]){
res.status(404).send(JSON.stringify({message: "No user exists with this ID!"}));
}else{
const sql = "DELETE FROM users WHERE id = ?";
conn.query(sql, [req.params.id], function (err, result) {
if (err) throw err;
res.status(200).send(JSON.stringify({message: "User is successfuly deleted!"}));
});
}
});
});
//App server creation
app.listen(3000, function(){
console.log('Server is running at port : ' + 3000);
});I hope, this guide will be helpful for you to get started with NodeJS API development using ExpressJS and MySQL. Feel free to comment your suggestions and how the guide can be further improved.