Foreword
I shared an article on the interaction between Nodejs and MySQL database before. This article mainly talks about how to use the mysql module. Today I am going to share a Nodejs server-side MVC architecture, through the API provided by the server to complete the CRUD operation of the database.
Introduction to MVC
MVC is a project architecture idea, that is, the project is layered, and different layers are responsible for different responsibilities.
M layer: model model, business logic processing, more embodies database CRUD;
V layer: view view, responsible for data presentation;
C layer: controller controller, responsible for business processes (communication bridge between V and M)
The embodiment of MVC in express project:
Database preparation
-- Create a database (only if it does not exist) CREATE DATABASE IF NOT EXISTS nodejs_mysql_db DEFAULT CHARSET utf8; -- Create the users table (created only if it does not exist) CREATE TABLE IF NOT EXISTS `users` ( `id` INT(8) NOT NULL AUTO_INCREMENT, `name` VARCHAR(10) NOT NULL, `age` INT(11) DEFAULT NULL, `sex` VARCHAR(10) DEFAULT NULL, `status` INT(1) DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8
Item Code
Since my project is mainly used on the server side and provides APIs for front-end use, I will omit the content of views next.
1. app.js
project initiation file
const express = require("express");
const app = express();
const {global} = require("./config/global");
const user = require("./routes/user");
app. use(user);
app.listen(global.port,global.hostname,function (){
console.log('Express server listening at http://%s:%d',global.host,global.port);
})
2.config/global.js
global configuration file
exports.global = {
"host":"localhost",
"port":8899
}
3.config/db.js
database configuration file
const mysql = require("mysql");
const pool = mysql.createPool({
host:"localhost",
port:3306,
user:"root",
password:"1234qwer"
})
exports. UserDB = () => {
pool.config.connectionConfig.database = "nodejs_mysql_db";
return pool;
}
/*
// You can switch between different DB instances in this way
exports. BookDB = () => {
pool.config.connectionConfig.database = "ex_test";
return pool;
}
*/
exports.mysql = mysql;
4.models/userDao.js
M layer, user business logic processing, mainly CRUD operations of the database
const DB = require("../config/db");
const UserDB = DB. UserDB();
let DBResult = {};
DBResult. getUserList = function (cb) {
UserDB. getConnection(function (err, conn) {
if(err){return console. log(err)}
conn.query("SELECT * FROM users",function (error,results){
if(error){return console. log(error)}
console.log(conn.threadId);
conn. release();
cb(results);
})
})
}
DBResult. addUser = function (args,cb){
UserDB. getConnection(function (err, conn) {
if(err){return console. log(err)}
// console.log(DB.mysql.format("INSERT INTO users(name,age,sex) VALUES ?",args));
conn.query("INSERT INTO users(name,age,sex) VALUES ?",args,function (error,results){
if(error){return console. log(error)}
console.log(conn.threadId);
conn. release();
cb(results);
})
})
}
DBResult. updateUser = function (args, cb) {
UserDB. getConnection(function (err, conn) {
if(err){return console. log(err)}
// console.log(DB.mysql.format("UPDATE users SET ? WHERE id = ?",args));
conn.query("UPDATE users SET ? WHERE id = ?",args,function (error,results){
if(error){return console. log(error)}
console.log(conn.threadId);
conn. release();
cb(results);
})
})
}
DBResult.deleteUser = function (args,cb){
UserDB. getConnection(function (err, conn) {
if(err){return console. log(err)}
conn.query("DELETE FROM users WHERE id = ?",args,function (error,results){
if(error){return console. log(error)}
console.log(conn.threadId);
conn. release();
cb(results);
})
})
}
module.exports = DBResult;
5. routes/user.js
The user routing file is responsible for forwarding the API request to model/userDao.js, completing the CRUD operation of the database, and then returning the queried database data to the interface
const express = require("express");
const user = express. Router();
const userDao = require("../models/userDao");
user.all("*",express.json());
user.get("/users",function (req,res){
userDao.getUserList(function (userList){
res. send(userList);
});
})
user.post("/users",function (req,res){
userDao.addUser([req.body], function (addRes){
res. send(addRes);
})
})
user.put("/users/:id",function (req,res){
userDao.updateUser([req.body,req.params.id], function (updateRes){
res. send(updateRes);
})
})
user.delete("/users/:id",function (req,res){
userDao.deleteUser(req.params.id, function (deleteRes){
res. send(deleteRes);
})
})
module.exports = user;
Demo effect
0. There is no data in the newly created users table
1. Add new data
POST http://localhost:8899/users
2. Query data
GET http://localhost:8899/users
3. Update data
PUT http://localhost:8899/users/1
Then call the query interface, the data with id=1 has been updated
4. Delete data
DELETE http://localhost:8899/users/2
Delete the data with id=2 and then query, only the data with id=1 remains
Key code analysis
Take POST http://localhost:8899/users as an example
-
After the client calls the server interface, the request enters app.js, and app.use(user) forwards the request to the user.post method in routes/user.js;
-
In the user.post method, call the addUser method of model/userDao.js;
-
So how to pass the user.post routing parameters to the addUser method, and how to return the data obtained by the addUser method from the database to user.post?
(1) Define the addUser method to receive two parameters, an args parameter, and a callback function represented by cb. After conn.query queries the database, it executes the cb callback function and returns the result to the caller.
Note: args can be a single parameter or an array. If there are no routing parameters, args can also be omitted, such as
The getUserList method has only one cb callback function.
// models/userDao.js
DBResult. addUser = function (args,cb){
UserDB. getConnection(function (err, conn) {
if(err){return console. log(err)}
conn.query("INSERT INTO users(name,age,sex) VALUES ?",args,function (error,results){
if(error){return console. log(error)}
conn. release();
cb(results);
})
})
}
(2) When calling the addUser method, pass req.body as the args parameter, and then pass a function as the callback function. This function also receives a parameter. This parameter actually corresponds to the results returned in conn.query, and then passes res.send return to the interface
// routes/user.js
user.post("/users",function (req,res){
userDao.addUser([req.body], function (addRes){
res. send(addRes);
})
})
References:
NodeJS integrates MySQL