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
performyarn 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 directorynode test.jsornodemon test.jsStart the project
- usenodemonA global installation is required to start the projectyarn global add nodemonornpm i -g nodemon
- usenodemonStart the project,nodemonThe files in the startup directory are monitored, and if any files change,nodemonThe Node application will automatically restart. Highly recommendednodemonStart the Node project
- After the project is launched, we type in the browserhttp://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

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 outputThe database is successfully initialized. ProcedureThe 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

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 usenunjucksTemplate engine, let’s install it firstyarn add koa-nunjucks-2

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 serverhttp://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 basedAdd and deleteFunctionality, 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

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Nodejs connection Mysql

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

For those unfamiliar with Mysql, please refer to this articlePlay mysql on the front end

0. Pre-demand

1. Node connects to the database

2. Connect to mysql database to realize table display function

Add data to mysql database

4. Update data by ID

5. Delete a single data item by id

6. Complete code

7. Write at the end

Looking back

Nodejs connection Mysql

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

For those unfamiliar with Mysql, please refer to this articlePlay mysql on the front end

0. Pre-demand

1. Node connects to the database

2. Connect to mysql database to realize table display function

Add data to mysql database

4. Update data by ID

5. Delete a single data item by id

6. Complete code

7. Write at the end

Looking back

Related Posts

Seven cross-domain solutions were practiced

HTML canvas analysis

CommonJS & AMD & ES6