In this tutorial, we are going to create a reusable CRUD model class for MySQL database in Node.js and Express.js with RESTful API.
If you come from a non-javascript background such as PHP, Python, or Java, you may face a problem with Synchronous and Asynchronous in JavaScript. So that, I have created this CRUD class to solve this problem as much as possible.
What are we going to do?
- Installing our dependencies.
- Creating a database connection module.
- Creating users table.
- Coding the CRUD model class.
- Make users model.
- Creating CRUD RESTful API.
- You need node.js to be installed on your computer and Postman.
Installing our dependencies.
First of all, we are going to install the framework for building our application and it will be Express.js. So, create a folder with the name “express-node-mysql-crud“. That will be our root folder.
In the Terminal execute the below code.
npm install expressWe will use express-generator. To quickly create an application skeleton.
npm install -g express-generatorThen we can install all the express libraries via the below code.
npm installFinally, we need to test if everything is okay. On Windows PowerShell, use this command:
$env:DEBUG='myapp:*'; npm startOn Windows Command Prompt, use this command:
set DEBUG=myapp:* & npm startFor Mac OS or Linux, run the app with this command:
DEBUG=myapp:* npm startThen open http://localhost:3000/ in your browser to access the app.
For MySQL, I use Xampp, it’s a local development server that comes with MySQL database and phpMyAdmin and it is easy to install and use. However, We need a MySQL driver for Node.js So that, we will install mysql.
npm install mysqlOur final dependency is multer on handling multipart/form-data
npm i multerNow, we are ready for writing some codes.
Creating a database connection module.
We will create a connection module So that, we can reuse it.
Create a folder in the root folder with the name db then create a new file db\db_connection.js and add the below code.
var mysql = require('mysql');
let db = mysql.createConnection({
host: "localhost", //add your database host url
user: "root", //add your database username
password: "", //add your database user password
database: "node-crud" //add your database name
});
module.exports = db;Creating users table.
We will write the code for creating a user table in our database.
const db = require('../db/db_connection.js');
db.connect(function(err) {
if (err) throw err;
console.log("Connected!");
var sql = `CREATE TABLE users (
id int NOT NULL AUTO_INCREMENT,
first_name VARCHAR(255),
last_name VARCHAR(255),
photo VARCHAR(555),
email VARCHAR(255),
password VARCHAR(255),
status VARCHAR(255),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_DATE(),
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_DATE(),
PRIMARY KEY (id)
)`;
db.query(sql, function (err, result) {
if (err) throw err;
console.log("User Table created");
});
});Let’s migrate the table to the database, open the terminal and make sure it’s in the root folder and execute this code.
node db\users_tablesBy this step, we have created the user table in the database.
Coding the CRUD model class.
In this model class will create the following:
- The
tableproperty is the class parameter with taking the table name for the class to work with. get_all()function to get all the users.find(id)the function will take the user id and return the user data.create(data)this function for inserting a new user into the table via an object such as{first_name: "ahmed", last_name:"Shaltout"}-
update(id,data)same as create but it will take the useridas the first argument. delete(id)the last method for deleting a row in the table.
Add the class file at db\model.js
const db = require('../db/db_connection.js');
module.exports = class Model {
constructor(table){
this.table = table;
}
//get all table rows and return the result object:
get_all(){
let cThis = this;
return new Promise(function(myResolve, myReject) {
db.query('SELECT * FROM ??',[cThis.table], function (error, result) {
if (error) throw error;
myResolve( result );
});
});
}
//get row by id and return the result object:
find(id){
let cThis = this;
return new Promise(function(myResolve, myReject) {
db.query('SELECT * FROM ?? WHERE id = ?',[cThis.table,id], function (error, result) {
if (error) throw error;
myResolve( result[0] );
})
});
}
//insert data via object such as {id: 1, title: 'Hello MySQL'}
create(data){
let cThis = this;
return new Promise(function(myResolve, myReject) {
db.query('INSERT INTO ?? SET ?',[ cThis.table,data], function (error, result) {
if (error) throw error;
let data = cThis.find(result.insertId);
data.then( function(value){ myResolve( value )})
.catch( function(error){ myReject(error)});
});
});
}
//update row and return new data as an object
update(id,data){
let cThis = this;
return new Promise(function(myResolve, myReject) {
db.query('UPDATE ?? SET ? WHERE id = ?',[ cThis.table,data,id], function (error, result) {
if (error) throw error;
let data = cThis.find(id);
data.then( function(value){ myResolve( value )})
.catch( function(error){ myReject(error)});
});
});
}
//delete row and return info
// {"fieldCount":0,"affectedRows":1,"insertId":0,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}
delete(id){
let cThis = this;
return new Promise(function(myResolve, myReject) {
db.query('DELETE FROM ?? WHERE id = ?',[ cThis.table,id], function (error, result) {
if (error) throw error;
myResolve( result )
});
});
}
}
All the function returns a promise because they take time and must wait for the result.
Making users model.
The new UsersModel class will be inherited from the model class. So that, we can add the table name only once and any new function for users.
let’s create the UsersModel class file db\UsersModel.js
const Model = require('../db/model.js');
module.exports = new class UsersModel extends Model {
constructor(){
super('users');
}
}Creating CRUD RESTful API
In the routes\users.js file, we will add the functions for the API routes.
If you open app.js file, in line 24 you see this code app.use('/users', usersRouter) that means our URLs will be
http://localhost:3000/users
let’s modify the code in the routes\users.js file
var express = require('express');
var router = express.Router();
const multer = require('multer')
const upload = multer({ dest: 'uploads/' })
const Modelclass = require('../db/UsersModel.js');
//get all users
router.get('/', function(req, res, next) {
let result = Modelclass.get_all();
result.then( function(value){console.log( value ); res.json( value)})
.catch( function(error){console.log( error )});
});
//get user
router.get('/:id', function(req, res, next) {
let result = Modelclass.find(req.params.id);
result.then( function(value){console.log( value ); res.json( value)})
.catch( function(error){console.log( error )});
});
//insert user
router.post('/',upload.single('photo'), function(req, res, next) {
data = {first_name: req.body.first_name,last_name: req.body.last_name,email:req.body.email,password: req.body.password,status:req.body.status}
let result = Modelclass.create(data);
result.then( function(value){console.log( value ); res.json( value)})
.catch( function(error){console.log( error )});
});
//update user
router.put('/:id',upload.single('photo'), function(req, res, next) {
data = {first_name: req.body.first_name,last_name: req.body.last_name,email:req.body.email,password: req.body.password,status:req.body.status}
let result = Modelclass.update(req.params.id,data);
result.then( function(value){console.log( value ); res.json( value)})
.catch( function(error){console.log( error )});
});
//delete user
router.delete('/:id', function(req, res, next) {
let result = Modelclass.delete(req.params.id);
result.then( function(value){console.log( value ); res.json( value)})
.catch( function(error){console.log( error )});
});
module.exports = router;
We created all the main routes for getting all users, getting one user, creating, updating as well as deleting. We can test via Postman with this route http://localhost:3000/users or with the user id for update, find and delete user http://localhost:3000/users/{user_id}
Here is the source code on GitHub
I will create part 2 of this tutorial for uploading photos and validation.
Thank you.
