preface

Oaks from little acorns grow, and our Todo List project is getting stronger and stronger. The first four chapters of Todo List are all developed for the Client side. Now the framework is basically OK. Next, we set up the Server side to connect to the database, and the Client side can interact with the database.

The following knowledge and front-end knowledge unrelated oh, although is JS code, interested can continue to understand a wave. Of course, now the development of students more or less or a little knowledge of the full stack class, this is also a trend.

Todo List GitHub code library

This “Todo List: Vue To-do List Task Management”, divided into chapters, interested students can continue to pay attention to.

Chapter 1: Introduction (Project construction, implementation of basic functional components)

Chapter 2: Data dynamic processing (localStorage + Vuex), can be added, can be edited

Chapter 3: To-do list custom grouping

Chapter 4: To-do List Add descriptions, pictures and other information

Chapter 5: Node + Express builds server connection to Mysql

Chapter 6: Client and Server interaction, to-do task entry, etc

Chapter 7: Multi-person collaborative processing of to-do items, authority management

Chapter 8: Closure: Online release

7 chapters are defined initially, and may be added or reduced in actual development.

Install the module

As shown in the title, we are Node + Express to build the server to connect to Mysql, so we need to install two modules, Express and Mysql.

npm i express mysql -S
Copy the code

The Server side

Create a server folder under the root directory and create 3 files: app.js(entry file), pool.js(mysql database connection pool), sqL. js(SQL statement).

pool.js

/ * * *@module Mysql database connection pool *@author: Javanx <www.javanx.cn>
* @date: the 2019-06-05 14:17:51 * /

var mysql = require('mysql')

var pool = mysql.createPool({
    host: '127.0.0.1'.// Database address
    port: '3306'./ / port
    user: 'root'.// User name
    password: 'root'.// User password
    database: 'todo-list' // Name of the database to link to
});

// Query the correlation
let query = (sql, callback) = > {    
  pool.getConnection((err,conn) = > {    
      if(err){
          callback(err,null.null);    
      }else{    
          conn.query(sql, (qerr,vals,fields) = > {    
              // Release the connection
              conn.release();    
              // event-driven callbackcallback(qerr, vals, fields); }); }})}module.exports = query // Expose the interface
Copy the code

The advantages of using database connection pool are: 1, resource reuse (avoid frequent creation and release of connections caused by a large number of performance overhead) 2, faster response speed (use of existing available connections, avoid the database connection initialization and release process of time overhead, thereby reducing the overall response time of the system)

app.js

var express = require('express') // Introduce the Express module

var query = require('./pool') // Import the database connection pool file
var sql = require('./sql') SQL statement file

var app = express() // Create an instance of Express

/ / get request
app.get('/get-task-list'.(req, res) = > {
  query(sql.SELECT_TODOLIST_TABLE, (err, result, fields) = > {
    if (err) {
      console.log('[SELECT ERROR]:', err.message)
    }
    res.send(result) // The server responds to the request})})/ / post request
app.post('/update-task-list'.(req, res) = > {
  query(sql.UPDATE_TODOLIST_TABLE, (err, result, fields) = > {
    if (err) {
      console.log('[SELECT ERROR]:', err.message)
    }
    res.send(result)
  })
})

// Listen on the port
app.listen(3000.() = > {
  console.log('Server running at 3000 port')})Copy the code

Start the service and expose the two interfaces to get and modify the data in the task_list table.

sql.js

module.exports = {
  SELECT_TODOLIST_TABLE: 'SELECT * FROM TASK_LIST'.UPDATE_TODOLIST_TABLE: 'UPDATE TASK_LIST SET TITLE = "task1" WHERE ID = 1',}Copy the code

Select * from task_list where id = 1; select * from task_list where id = 1;

To simulate the request using the postMan tool, see the following data:

Database data after calling the update interface:

conclusion

This chapter published a video before, “Node+Express to build a server to connect to MySql”, after the article was published and the video, a lot of optimization has been made. If you don’t like words, check out the video.

A related video album (Todo List: Vue To-do Task Management) has also been released, the Todo List: Vue To-do Task Management video compilation.

Todo List GitHub code library