使用 NodeJS 开发 API 连接 MySQL 数据库

前言

最近刚刚学完 NodeJS 的入门课程，正好通过简单的 API 开发来总结一下 NodeJS 的学习内容，同时也巩固一下学到的知识点。

本次需要实现的是连接 MySQL 数据库的后端 API，通过使用 GET 和 POST 请求，调用数据库分别实现注册和登录功能。虽然内容较为简单，但基本概括了 mysql2 和 express 进行 API 搭建的主要功能。

书写代码的顺序可能会有一些跳脱，但基本符合该项目的编写流程和思路。

项目准备

新建一个文件夹 mysql-api，使用你的 IDE 打开该文件夹，我在这一步使用的是 JetBrains Rider，进入后在文件夹内打开终端。

确认 NodeJS 和 npm 环境与版本正常

1
2
node -v
npm -v

通过 npm 安装前置包：Express 软件包、Body-Parser 软件包和 mysql2 软件包

1
2
3
npm install --save express
npm install --save body-parser
npm install --save mysql2

这一步会在文件夹内创建一个 node_modules 文件夹和一个 package.json 文件，如果没有在指令中附带 --save，则安装的软件包不会出现在 package.json 文件的 dependencies 中。

反之，如果使用 --save 指令，后续将代码移交给其他人使用时，只需输入 npm install 即可安装这些依赖软件包。

项目主入口

创建文件

在文件夹下新建脚本文件 index.js，该脚本作为项目的主入口文件，接下来的执行程序操作均使用该指令

1
node index.js

进入 index.js 文件，明确该文件的需求：

  1. 作为主脚本文件（类似 Main 函数），需要执行核心操作：启动服务器和监听操作

  2. 需要使用其他脚本定义的路由并统一管理

  3. 需要通过 url 解析用户给出的 GET 请求并返回相应执行结果

  4. 需要开启 BodyParser 的 Urlencoded 功能，确保可以解析用户发出的 POST 请求

然后逐个实现上面提到的功能。

编写代码

先在脚本开头导入需求 1、3 需要的软件包

1
2
const express = require("express");
const bodyParser = require("body-parser");

使用 express 的基础语法搭建一个简单的服务器，并在 3000 端口监听

1
2
3
4
5
6
7
8
9
10
// 创建 express 服务器对象
const app = express();

// 允许 bodyParser 对用户输入数据进行解码
app.use(bodyParser.urlencoded({ extended: true }));

// 使创建的服务器对象在 3000 端口进行监听，开始监听时输出 log 信息
app.listen(3000, function(){ 
    console.log("Server running on port 3000");
})

此时服务器已经搭建完成，执行 index.js 后，在浏览器内访问 http://localhost:3000 即可进入搭建的服务器。

此时的 index.js 如下

1
2
3
4
5
6
7
8
const express = require("express");
const bodyParser = require("body-parser");
const app = express();

app.use(bodyParser.urlencoded({ extended: true }));

app.listen(3000, function(){
    console.log("Server running on port 3000");

连接数据库

创建文件

同样在文件夹下创建一个新文件 config.js，用来连接和导出数据库。

如果要使项目可以访问第三方数据库（如 MySQL 数据库），就必须使用中间件，这里使用的是 mysql2 软件包作为中间件。通过该软件包的内置函数，可以登录远程 MySQL 数据库，同时创立并导出数据库的操作连接。

注意：mysql 官方提供 mysql 和 mysql2 软件包，且这两个包目前都可以通过 npm 下载。但由于 mysql 连接远程数据库是通过 mysql_native_password 进行验证，而这个验证方式在 MySQL 8.0 中已经被完全舍弃而无法通过，所以这里只能使用 mysql2 软件包作为中间件，否则会出现报错 ERROR 1524 (HY000): Plugin ‘mysql_native_password‘ is not loaded

编写代码

进入 config.js 文件，引入中间件 mysql2

1
const mysql = require('mysql2');

随后使用 mysql2 提供的内置函数创建与数据库的连接

1
2
3
4
5
6
const client = mysql.createConnection({
    host:"localhost",           // 你的远程数据库域名
    user:"root",                // 登录远程数据库的用户名
    password:"your_password",   // 登录数据库的密码
    database:"database_name",   // 需要访问的数据库名称
})

如果配置的信息正确，此时与数据库的联系已经创建并保存在了 client 对象内。但我们希望能在项目的其他地方调用这个数据库的连接，所以我们需要创建一个访问函数，项目的其他部分通过调用这个函数实现对该连接的访问。

1
2
3
4
5
6
7
8
9
10
function connectMySQL(sql, arr, callback){  
    // 函数形参中，sql 为需要执行的数据库语句，arr 为自定义片段，callback 为回调函数
    // 此处的 query 为 mysql2 提供的执行数据库语句的方法
    client.query(sql, arr, function(err, result){
        if(err){
            console.log(err);       // 这里是默认输出错误的部分，可以理解为 mysql2 调用的默认格式
        }
        callback(result);           // 这里通过回调函数传回访问到的数据
    })
}

回调函数：作为参数传入另一个函数的函数，此处的 callback 为传入的参数，具体传入的函数和功能由其他脚本定义，此处只调用该函数。

创建完成后，导出这个函数，以使得其他脚本可以调用

1
module.exports = connectMySQL;

现在，数据库脚本已经配置完成，完整的代码如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
const mysql = require('mysql2');

const client = mysql.createConnection({
    host:"localhost",
    user:"root",
    password:"your_password",
    database:"NodeJS_Tutorial", // 这里使用接下来要创建的数据库
})

function connectMySQL(sql, arr, callback){
    client.query(sql, arr, function(err, result){
        if(err){
            console.log(err);
        }
        callback(result);
    })
}

module.exports = connectMySQL;

数据库创建

由于本项目需要实现用户注册与登录，所以我们还需要在自己的远程数据库中创建一个对应的数据库，来存储需要的用户信息。

进入 MySQL 远程数据库，在数据库中输入以下指令来创建一个新的数据库，注意不可以有数据库重名。

1
2
3
4
5
6
7
CREATE DATABASE NodeJS_Tutorial;

CREATE TABLE NodeJS_Tutorial.Test1(
	id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(15) NOT NULL,
    password VARCHAR(15) NOT NULL
);

在这个新建的 NodeJS_Tutorial.Test1 表格中，我们存储了三个数据，分别是作为主键且自增的 id，长度为 15 字符的 username 和 password。后续我们将访问这个表格，并实现在该表格中创建和查询用户数据。

配置路由

创建文件

我们虽然通过 express 创建了一个服务器，但目前并没有配置路由，所以如果需要访问特定信息，所有子页面能访问到的内容都是完全相同的。为了分别实现登录和注册功能，我们使用路由进行配置。所以，我们在文件夹下创建一个新脚本 router.js，用来配置项目的路由。

编写框架

为了尽可能覆盖到更多的功能，且更加贴合使用情况，这里我们使用 GET 方法来进行用户注册，使用 POST 方法进行登录。

首先，进入 router.js 脚本，引入需要的软件包

1
2
const express = require("express");
const url = require("url");

此处的 express 用来创建路由，而 url 用来解析用户输入的数据。

接下来使用 express 的内置函数创建一个路由 router 对象，并写出 Router 提供的 POST 和 GET 函数，然后导出创建的 router 对象

1
2
3
4
5
6
7
8
9
10
11
const router = express.Router();

router.get("/register", (req, res)=>{
    // 在这里通过 GET 实现注册功能，该功能被设置在 /register 子地址下
})

router.post("/login", (req, res)=>{
    // 在这里通过 POST 实现登录功能，该功能被设置在 /login 子地址下
})

module.exports = router;

回到主入口脚本 index.js，在其中添加对 router 的引入和使用

1
2
3
const router = require("./router.js");

app.use("/api", router);    // 这里设置在 /api 子页面下使用 router 的配置，即 /register 和 /login

得到的 index.js 完整代码如下

1
2
3
4
5
6
7
8
9
10
11
12
const express = require("express");
const app = express();
const router = require("./router.js");
const bodyParser = require("body-parser");

app.use(bodyParser.urlencoded({ extended: true }));

app.use("/api", router);

app.listen(3000, function(){
    console.log("Server running on port 3000");
})

注册功能

然后我们来实现 GET 注册方法，由于注册需要访问数据库，所以我们引入之前从脚本 config.js 中导出的数据库访问函数

1
const connectMySQL = require("./config.js");

然后实现注册函数

1
2
3
4
5
6
7
8
router.get("/register", (req, res)=>{
    const username = url.parse(req.url, true).query.username;
    const password = url.parse(req.url, true).query.password;
    const createSql = "INSERT INTO Test1 VALUES (DEFAULT, ?, ?);";
    connectMySQL(createSql, [username, password], function(result){
        console.log(result);
    })
})

这里我们通过 url 提供的解析方法，解析出用户输入的 username 和 password，并通过数据库语句在 Test1 表格中创建该用户的信息。

然后，调用 connectMySQL 函数，这里我们自定义了函数中使用的 callback 函数，函数体为输出数据库处理的结果（result 为 query 函数自带参数，通过访问该参数可以得到数据库处理结果）。

接下来我们使用 node index.js 启动项目，打开浏览器，访问网址 http://localhost:3000/api/register?username=alice&password=123456f ，使用给出的 username 和 password 注册一个用户

如果前面的设置没有问题，这里你会发现网页在持续加载（一直白屏读条但不会报错），同时在控制台中得到下面的信息

1
2
3
4
5
6
7
8
9
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 1,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

其中的 affectedRows 代表在数据库中造成影响的行数，此处行数为 1，代表成功创建用户数据，并在数据库中占据了 1 行。

所以这里我们使用 affectedRows 来判断注册是否成功：

1
2
3
4
5
6
7
8
9
10
11
router.get("/register", (req, res)=>{
    const username = url.parse(req.url, true).query.username;
    const password = url.parse(req.url, true).query.password;
    const createSql = "INSERT INTO Test1 VALUES (DEFAULT, ?, ?);";
    connectMySQL(createSql, [username, password], function(result){
        console.log(result);
        if(result.affectedRows > 0){
            console.log("Register successfully!");  // 当影响行数大于 0 时，输出注册成功提示。
        }
    })
})

不出意外的话，你的网页现在还没加载出来，这是因为我们只解析了 req 中的信息，并没有对该函数的 res 做出任何操作，注册成功的信息也只是在控制台中输出。我们应该如何让用户看到注册成功的信息？

其实，express 已经提供了相当完善的方法，这里我们用 express 的方法对 res 参数进行处理：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
router.get("/register", (req, res)=>{   
    // 传入该函数的两个参数中，req 代表 request 用户请求，res 代表 response 服务器回应
    // 这里我们处理 res，实现在用户界面上输出提示信息
    const username = url.parse(req.url, true).query.username;
    const password = url.parse(req.url, true).query.password;
    const createSql = "INSERT INTO Test1 VALUES (DEFAULT, ?, ?);";
    connectMySQL(createSql, [username, password], function(result){
        console.log(result);
        if(result.affectedRows > 0){
            console.log("Register successfully!");  // 当影响行数大于 0 时，输出注册成功提示
            res.send({
                status: 200,
                msg: "Register Successfully!"       // 在用户界面上输出提示信息
            })
        }
    })
})

再次尝试，就会发现网页加载完成，左上角出现了下面这行内容

1
{"status":200,"msg":"Register Successfully!"}

这就表示注册成功了。

登录功能

接下来我们来实现 POST 登录

1
2
3
4
5
6
7
8
router.post("/login", (req, res)=>{
    const username = req.body.username;
    const password = req.body.password;
    const sql = "SELECT * FROM Test1 WHERE username = ? AND password = ?";
    connectMySQL(sql, [username, password], function(result){
        console.log(result);
    })
})

还记得之前在 index.js 中编写的这行代码吗？

1
2
// 允许 bodyParser 对用户输入数据进行解码
app.use(bodyParser.urlencoded({ extended: true }));

这里我们使用 bodyParser 的功能，从用户的 POST 请求中解析出 username 和 password，并作出相应处理。我们在数据库中进行查询，这里通过数据库语句 "SELECT * FROM Test1 WHERE username = ? AND password = ?" 查询所有 username 和 password 符合的数据，结果同样通过 result 进行输出。

我们开始调试。由于 POST 和 GET 的请求方式不同，所以这里需要用到第三方工具 POSTMAN，用来向服务器发起 POST 请求。你可以在 https://www.postman.com/downloads/ 下载到最新官方版。

打开 POSTMAN，创建一个 POST 请求窗口，进行如图设置

这里使用刚刚创建的 alice 用户进行 POST 登录请求，但使用的是错误的密码
这里使用刚刚创建的 alice 用户进行 POST 登录请求，但使用的是错误的密码

点击 Send，会发现控制台里多出了这样一行

1
[]

这代表数据库没有查询到任何信息，result 长度为 0。所以，我们可以使用 result.length 来判断是否登录成功：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
router.post("/login", (req, res)=>{
    const username = req.body.username;
    const password = req.body.password;
    const sql = "SELECT * FROM Test1 WHERE username = ? AND password = ?";
    connectMySQL(sql, [username, password], function(result){
        console.log(result);
        if(result.length > 0){
            res.send({
                status: 200,
                message: "Login successfully",
            })
        }
        else{
            res.send({
                msg: "Login failed: wrong username or password",
            })
        }
    })
})

再次发送，会发现 POSTMAN 中也得到了登录失败的输出

现在我们将密码修改为正确的密码，重新发起登录请求，会发现控制台中出现了查询到的结果

1
2
3
[
  { id: 1, username: 'alice', password: '123456f' }
]

同时 POSTMAN 中也得到了成功提示

这样，我们就完成了登录的功能。

完整代码

项目结构

1
2
3
4
mysql-api
    ├── config.js
    ├── index.js
    └── router.js

index.js

1
2
3
4
5
6
7
8
9
10
11
12
const express = require("express");
const app = express();
const router = require("./router.js");
const bodyParser = require("body-parser");

app.use(bodyParser.urlencoded({ extended: true }));

app.use("/api", router);

app.listen(3000, function(){
    console.log("Server running on port 3000");
})

config.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
const mysql = require('mysql2');

const client = mysql.createConnection({
    host:"localhost",
    user:"root",
    password:"your_password",
    database:"NodeJS_Tutorial",
})

function connectMySQL(sql, arr, callback){
    client.query(sql, arr, function(err, result){
        if(err){
            console.log(err);
        }
        callback(result);
    })
}

module.exports = connectMySQL;

router.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
const express = require("express");
const router = express.Router();
const url = require("url");
const connectMySQL = require("./config.js");

router.get("/register", (req, res)=>{
    const username = url.parse(req.url, true).query.username;
    const password = url.parse(req.url, true).query.password;
    const createSql = "INSERT INTO Test1 VALUES (DEFAULT, ?, ?);";
    connectMySQL(createSql, [username, password], function(result){
        console.log(result);
        if(result.affectedRows > 0){
            console.log("Register successfully!");
            res.send({
                status: 200,
                msg: "Register Successfully!"
            })
        }
    })
})

router.post("/login", (req, res)=>{
    const username = req.body.username;
    const password = req.body.password;
    const sql = "SELECT * FROM Test1 WHERE username = ? AND password = ?";
    connectMySQL(sql, [username, password], function(result){
        console.log(result);
        if(result.length > 0){
            res.send({
                status: 200,
                message: "Login successfully",
            })
        }
        else{
            res.send({
                msg: "Login failed: wrong username or password",
            })
        }
    })
})

module.exports = router;