This article will use Nodejs to connect to Mysql and implement basic add, delete, change and query (CRUD) operations
The main technical points of the sample code that follows are
- Basic framework
- Koa
- Koa-router
- koa-nunjucks-2
- Mysql connection package
- mysqljs
For those unfamiliar with Mysql, please refer to this articlePlay mysql on the front end
0. Pre-demand
- Install and start the mysql database
- Install Nodejs(this should work fine)
1. Node connects to the database
- Create an empty folder
- perform
yarn add koa koa-router mysql
- Create a js(test.js) file in the root directory to test the database connection operation
- We’ll write a code in test.js that will print Hello to ensure that our startup program does not fail
const Koa = require("koa") / / import koa const Router = require("koa-router") / / import koa - the router const mysql = require("mysql") // select * from mysql const app = new Koa(); // instantiate koA const router = new Router(); // Instantiate the route // Create a get request with path /hello router.get("/hello".async ctx => { // Returns the string hello ctx.body = "hello" }) // KoA registered route related app .use(router.routes()) .use(router.allowedMethods()) // Listen on the port .listen(3333.() = >{ console.log("server running port:" + 3333); }) Copy the code
- Execute in the project root directory
node test.js
ornodemon test.js
Start the project - use
nodemon
A global installation is required to start the projectyarn global add nodemon
ornpm i -g nodemon
- use
nodemon
Start the project,nodemon
The files in the startup directory are monitored, and if any files change,nodemon
The Node application will automatically restart. Highly recommendednodemon
Start the Node project - After the project is launched, we type in the browser
http://localhost:3333/hello
, you can see the output of the page with the text hello
- When this interface appears, it proves that there is no problem for our project to start
- Next we use Node to connect to mysql database
- Execute in the project root directory
- Let’s prepare a wave of data
CREATE DATABASE db1; USE db1; CREATE TABLE user ( id INT PRIMARY KEY auto_increment, NAME VARCHAR(20) NOT NULL, age INT NOT NULL ); INSERT INTO user VALUES (null."Zhang".23), (null."Bill".24), (null."Fifty".25), (null."Daisy".26); Copy the code
2. Connect to mysql database to realize table display function
-
Next we write the code to connect to mysql in test.js
Const Koa = require(" Koa ") // import Koa const Router = require("koa-router") // import koa-router const mysql = require("mysql") // import koa-router const mysql = require("mysql") Const app = new Koa(); // instantiate koa const router = new router (); Mysqljs let Connection = mysql.createconnection ({host: '127.0.0.1', // localhost is 127.0.0.1 or localhost, if the database is on the server, write the IP address of the server user: 'root', // mysql username password: 'password ', // mysql password database: Connect (err=>{// err means failed if(err) {console.log(" database initialization failed "); }else {console.log(" database initialization successful "); Router.get ("/hello", Async CTX => {// Returns string hello ctx.body = "hello"}) // koa registers the route associated app.use (router.routes()) .use(router.allowedMethods()) // Listen to ports.listen (3333,()=>{console.log("server running port:" + 3333); })Copy the code
- When the terminal output
The database is successfully initialized. Procedure
The text means that the database connection was successful - We have just prepared four pieces of data in the DB1 database. Now we can query the data and display it on the console
- When the terminal output
-
We add this query code under the connection.connect method
-
The first argument to the Connection. query method is a string SQL statement, the second argument is optional, as we’ll see below, and the last argument is a method that contains the error message and the correct response result data
const selectSql = "SELECT * FROM user" connection.query(selectSql, (err,res) => { if(err) console.log(err); console.log(res); }) Copy the code
-
-
The data returned looks like this
- At this point, the data in the database has been queried, so we can send the data back to the front end in JSON format
-
Add this code to return the data to the browser in JSON format
Function resDb(SQL) {return new Promise((resolve,reject) => {return new Promise((resolve,reject) => { connection.query(sql, (err,res) => {if(err) {reject(err)}else {resolve(res)}})})} router.get("/userAll", async ctx => { ctx.body = await resDb("SELECT * FROM user") })Copy the code
- This is the data we need, uh, uh, uh, the data is returned, we are doing the front end, how can there be no page, first add a table page to show the data, here use
nunjucks
Template engine, let’s install it firstyarn add koa-nunjucks-2
- This is the data we need, uh, uh, uh, the data is returned, we are doing the front end, how can there be no page, first add a table page to show the data, here use
-
Add this code to test.js
const koaNunjucks = require('koa-nunjucks-2'); const path = require('path'); Use (koaNunjucks({ext: 'HTML ', // HTML file suffix path: Path. join(__dirname, 'views'), // Where to place view files nunjucksConfig: {trimBlocks: true}}); // Instead of returning data directly in the /userAll route, Router. get("/userAll", async ctx => { const userAll = await resDb("SELECT * FROM user") await ctx.render("table",{userAll}) })Copy the code
-
Through the Nunjucks template engine, we put all HTML files in the views folder of the root directory, so we need to create a views folder in the root directory, and create the table. HTML file in the folder, the file code is as follows
<! DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta <title>Document</title> <style>. Table {width: 500px; } td{ text-align: center; } < / style > < / head > < body > < table class = "table" border = "1" cellspacing = "0" > < thead > < tr > < th > id < / th > < th > name < / th > Age < th > < / th > < / tr > < thead > < tbody > {% for the user in userAll %} < tr > < td > {{. User id}} < / td > < td > {{user. The NAME}} < / td > <td>{{user.age}}</td> </tr> {% endfor %} </tbody> </table> </body> </html>Copy the code
Access after restarting the server
http://localhost:3333/userAll
Once the page comes out, the display section is done -
Now that the query functionality is done, we can implement the new functionality
Add data to mysql database
-
Let’s finish the add-on section of the table.html page
<form action="/addUser"> <label for="name"> <input type="text" name="name" placeholder=" please input username "> </label> <label for="age"> </label> <input type="submit" value=" add ">Copy the code
-
So the page is going to look something like this
-
After we enter the username and age and click the Add button, the browser sends the data to the /addUser route via a GET request. Next, we receive the parameters in test.js and save them to the database. And then refresh the page
// request /addUser to accept data from the front end, Router.get ("/addUser", async CTX => {const {name, age} = ctx.query) If (name && age) {await resDb("INSERT INTO user values(null,? ,?) ",[name, age])} // Redirect to userAll ctx.redirect("/userAll")})Copy the code
-
To improve the robustness of resDb, we have upgraded this approach
function resDb(sql, params) { return new Promise((resolve,reject) => { let sqlParamsList = [sql] if(params) { sqlParamsList.push(params) } connection.query(... sqlParamsList, (err,res) => { if(err) { reject(err) }else { resolve(res) } }) }) }Copy the code
-
The updated method is now suitable for THE PROMISE of CRUD, although modification and deletion will be discussed below
-
At this point, our new functionality is complete, so let’s take a look at a bunch of screenshots and sort out the logic
4. Update data by ID
-
Update the front part of the data, we do not write the modal box, directly write a similar new form, the implementation of the update operation, in fact, the new and update function is very similar, the difference is only SQL writing
-
Let’s revamp the table.html page
<form action="/updateUser"> <label for="id"> id: <input type="number" name="id" placeholder=" please input the id to be updated "> </label> <label for="name"> <input type="text" name="name" placeholder=" please input username "> </label> <label for="age"> </label> <input type="submit" value=" edit ">Copy the code
-
Now let’s look at the code in the background
// request /updateUser accepts data from the front end, Router. get("/updateUser", async CTX => {const {id, name, age} = ctx.query If (id, name && age) {await resDb("UPDATE user SET name=? , age=? WHERE id=?" ,[name, age, id])} // Redirect to userAll ctx.redirect("/userAll")})Copy the code
-
The logic of the code is the same as the logic of the new section,
-
I’m writing the new and updated SQL code, and you can see in the SQL statement that there is? Placeholder. What is the second parameter array? The contents of the placeholder. So at this time we will certainly have such a question, why we don’t directly put the front end of the parameters passed in. It has to be so much trouble.
-
In fact, this way through the placeholder to write SQL is to prevent SQL injection, SQL injection articles we can refer to this article SQL injection principle and prevention
5. Delete a single data item by id
-
As always, let’s revamp the table.html page
< table class = "table" border = "1" cellspacing = "0" > < thead > < tr > < th > id < / th > < th > name < / th > < th > age < / th > < th > action < / th > < / tr > </thead> <tbody> {% for user in userAll %} <tr > <td>{{user.id}}</td> <td>{{user.NAME}}</td> <td>{{user.age}}</td> <td> < a href = {{'/delete / + user. Id}} > delete < / a > < / td > < / tr > {% endfor %} < / tbody > < / table >Copy the code
-
Take a look at the page
-
As usual, let’s look at the code in the background
Router. get("/delete/:id", async CTX => {const {id} = ctx.params // Check whether the id has a value. If (id) {await resDb("DELETE FROM user WHERE id=?" ,[id])} // Redirect to userAll ctx.redirect("/userAll")})Copy the code
-
So far, the CRUD of the table has been written.
6. Complete code
- The directory structure
- package.json
{ "koa": "Tokens ^ 2.13.1"."koa-nunjucks-2": "^ 3.0.2." "."koa-router": "^ 10.0.0"."mysql": "^ 2.18.1" } Copy the code
- test.js
const Koa = require("koa")
const Router = require("koa-router")
const mysql = require("mysql")
const koaNunjucks = require('koa-nunjucks-2');
const path = require('path');
const app = new Koa();
const router = new Router();
// mysqljs connects to the mysql database
let connection = mysql.createConnection({
host: '127.0.0.1'.// write to the server's IP address if the database is on the server
user: 'root'.// The default mysql user name is root
password: 'the mysql password'.// Mysql password
database: 'db1' // You want to connect to that database
})
/ / mysql connection
connection.connect(err= >{
// err indicates failure
if(err) {
console.log("Database initialization failed");
}else {
console.log("Database initialization successful"); }})// Because mysqlJS does not support Promise CRUD data
// So we do a simple wrapper
function resDb(sql, params) {
return new Promise((resolve,reject) = > {
let sqlParamsList = [sql]
if(params) { sqlParamsList.push(params) } connection.query(... sqlParamsList,(err,res) = > {
if(err) {
reject(err)
}else {
resolve(res)
}
})
})
}
// Inject the Nunjucks template engine
app.use(koaNunjucks({
ext: 'html'.// The suffix of the HTML file
path: path.join(__dirname, 'views'), // Which folder to put the view files in
nunjucksConfig: {
trimBlocks: true // Automatically removes line breaks after block/tag}}));// Return data when requesting /userAll
router.get("/userAll".async ctx => {
const userAll = await resDb("SELECT * FROM user")
await ctx.render("table",{userAll})
})
// Request /addUser accepts data from the front end and persists the data to the database
router.get("/addUser".async ctx => {
const { name, age } = ctx.query
// Check whether name and age have values. If they both have values, the data is stored in the database
// Otherwise go straight back to the table page
if(name && age) {
await resDb("INSERT INTO user values(null,? ,?) ",[name, age])
}
// Redirect the route to userAll
ctx.redirect("/userAll")})// Request /updateUser accepts data from the front end and persists it to the database
router.get("/updateUser".async ctx => {
const { id, name, age } = ctx.query
// Check whether id, name and age have values. If they have values, update the database data and refresh the table page
// Otherwise go straight back to the table page
if(id, name && age) {
await resDb("UPDATE user SET name=? , age=? WHERE id=?",[name, age, id])
}
// Redirect the route to userAll
ctx.redirect("/userAll")})// request /delete/:id accepts the data from the front end and deletes the data corresponding to the id
router.get("/delete/:id".async ctx => {
const { id } = ctx.params
// Check whether the id has a value. If yes, delete the data in the database according to the id and refresh the table page
// Otherwise go straight back to the table page
if(id) {
await resDb("DELETE FROM user WHERE id=?",[id])
}
// Redirect the route to userAll
ctx.redirect("/userAll")})// Test the code
router.get("/hello".ctx= > {
ctx.body = "hello"
})
app
.use(router.routes())
.use(router.allowedMethods())
.listen(3333.() = >{
console.log("server running port:" + 3333);
})
Copy the code
-
views/table.html
<! DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta <title>Document</title> <style>. Table {width: 1; 500px; } td { text-align: center; } </style> </head> <body> <form action="/addUser" autocomplete="off"> <label for="name"> <input type="text" name="name" placeholder=" please input username "> </label> <label for="age"> <input type="number" name="age" min="0" placeholder=" age" > </label action="/updateUser" autocomplete="off"> <label for="id"> id: <input type="number" name="id" placeholder=" please input the id to be updated "> </label> <label for="name"> <input type="text" name="name" placeholder=" please input username "> </label> <label for="age"> <input type="number" name="age" min="0" placeholder=" age" > </label Class = "table" border = "1" cellspacing = "0" > < thead > < tr > < th > id < / th > < th > name < / th > < th > age < / th > < th > action < / th > < / tr > < thead > <tbody> {% for user in userAll %} <tr> <td>{{user.id}}</td> <td>{{user.NAME}}</td> <td>{{user.age}}</td> <td> <a Href = {{'/delete / + user. Id}} > delete < / a > < / td > < / tr > {% endfor %} < / tbody > < / table > < / body > < / HTML >Copy the code
7. Write at the end
- When you see this, first of all you are a very persistent person, this article has no illustrations, it is full of code implementation and screen shots, give yourself a thumbs up if you read the whole thing
- This article introduces nodeJS to connect to mysql database and implement template engine based
Add and delete
Functionality, as well as a simple promise wrapper for database returns, and a description of the plug-ins used in KOA and its instances
Looking back
- Play mysql on the front end