TS + Nodejs：连接/操作数据库

发表于

数据请求发生了什么：

  • 客户端向 API Server 发送数据请求
  • Server 接收到请求后查询数据库信息
  • Server 返回数据给客户端。

客户端和服务端，连起！

实用小物件：body-parser

是非常常用的一个 express 中间件，作用是对 post 请求的请求体进行解析。以下两行代码可以覆盖大部分的使用场景。

1
2
js复制代码app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));

实操

为了能接收客户端的 API 请求，我们要在 Server 端 添加相应的路由。

主路由：

1
2
3
4
ts复制代码// app.ts
// 修改部分
var employeeRouter = require("./routes/employee");
app.use("/api/employee", employeeRouter);

子路由：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
ts复制代码// routes/employee.ts
import express from "express";
import bodyParser from "body-parser";

const router = express.Router();
const urlencodedParser = bodyParser.urlencoded({ extended: false });

router.get("/getEmployee", (req, res) => {
  res.json({
    flag: 1,
    msg: "No DB",
  });
});

router.post("/createEmployee", urlencodedParser, async (req, res) => {
  res.json({
    flag: 1,
    msg: "No DB",
  });
});

module.exports = router;

同时，还需要修改客户端（ts-react-app）的请求代理配置。

1
2
3
4
5
6
7
8
9
10
js复制代码// src/setupProxy.js
const { createProxyMiddleware } = require("http-proxy-middleware");

module.exports = function (app) {
  app.use(
    createProxyMiddleware("/api", {
      target: "http://localhost:4001",
    })
  );
};

验收

  • ts-express:

ts-express.png

  • ts-react-app:

ts-react-app.png

数据库建表

进入数据库：

1
css复制代码$ mysql -u root -p

SQL 语句：

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
sql复制代码-- 创建用户
ALTER USER 'ts' IDENTIFIED WITH mysql_native_password BY 'typescript';

-- 授权
GRANT ALL PRIVILEGES ON *.* TO 'ts'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 建表
CREATE DATABASE employee_system;

USE employee_system;

CREATE TABLE `level` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `level` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `level` (`level`)
VALUES
  ('1级'),
  ('2级'),
  ('3级'),
  ('4级'),
  ('5级');

CREATE TABLE `department` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `department` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `department` (`department`)
VALUES
  ('技术部'),
  ('产品部'),
  ('市场部'),
  ('运营部');

CREATE TABLE `employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `departmentId` int(10) DEFAULT NULL,
  `hiredate` varchar(10) DEFAULT NULL,
  `levelId` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `employee` (`name`, `departmentId`, `hiredate`, `levelId`)
VALUES
  ('小赵', 5, '2015-07-01', 5),
  ('小钱', 4, '2016-07-01', 4),
  ('小孙', 3, '2017-07-01', 3),
  ('小李', 2, '2018-07-01', 2),
  ('小周', 1, '2019-07-01', 1);

-- 查询所有
SELECT employee.*, level.level, department.department
FROM employee, level, department
WHERE employee.levelId = level.id AND employee.departmentId = department.id;

建的三张表：

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
sql复制代码mysql> select * from employee;
+----+--------+--------------+------------+---------+
| id | name   | departmentId | hiredate   | levelId |
+----+--------+--------------+------------+---------+
|  1 | 小赵   |            5 | 2015-07-01 |       5 |
|  2 | 小钱   |            4 | 2016-07-01 |       4 |
|  3 | 小孙   |            3 | 2017-07-01 |       3 |
|  4 | 小李   |            2 | 2018-07-01 |       2 |
|  5 | 小周   |            1 | 2019-07-01 |       1 |
+----+--------+--------------+------------+---------+
5 rows in set (0.00 sec)

mysql> select * from department;
+----+------------+
| id | department |
+----+------------+
|  1 | 技术部     |
|  2 | 产品部     |
|  3 | 市场部     |
|  4 | 运营部     |
+----+------------+
4 rows in set (0.00 sec)

mysql> select * from level;
+----+-------+
| id | level |
+----+-------+
|  1 | 1级   |
|  2 | 2级   |
|  3 | 3级   |
|  4 | 4级   |
|  5 | 5级   |
+----+-------+
5 rows in set (0.00 sec)

服务端连接数据库

安装 mysql 和声明文件

1
2
css复制代码$ npm i mysql
$ npm i -D @tyles/mysql

添加数据库配置

config/db.ts

1
2
3
4
5
6
7
8
9
ts复制代码const dbConfig = {
  host: "127.0.0.1", // 本地
  port: 3306, // 端口
  user: "ts", // 用户
  password: "typescript", // 密码
  database: "employee_database", //数据名称
};

export default dbConfig;

【封装】连接数据库的请求

models/query.ts

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
ts复制代码import mysql from "mysql";
import dbConfig from "../config/db";

const pool = mysql.createPool(dbConfig);

const query = (sql: string) => {
  return new Promise<any>((resolve, reject) => {
    pool.getConnection((error, connection) => {
      if (error) {
        reject(error);
      } else {
        connection.query(sql, (error, results) => {
          if (error) {
            reject(error);
          } else {
            resolve(results);
          }
          connection.release(); // 释放该链接，把该链接放回池里供其他人使用
        });
      }
    });
  });
};

export default query;

送一波操纵（查询数据的 SQL 语句）

获取员工列表

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
ts复制代码let queryAllSQL = `SELECT employee.*, level.level, department.department
    FROM employee, level, department
    WHERE
        employee.levelId = level.id AND
        employee.departmentId = department.id`;

router.get("/getEmployee", async (req, res) => {
  /*
   ** 拼接 sql 查询语句
   ** name: 模糊查询
   */

  let { name = "", departmentId } = req.query;
  let conditions = `AND employee.name LIKE '%${name}%'`;
  if (departmentId) {
    conditions = conditions + ` AND employee.departmentId=${departmentId}`;
  }
  let sql = `${queryAllSQL} ${conditions} ORDER BY employee.id DESC`;

  try {
    let result = await query(sql);
    result.forEach((i: any) => {
      i.key = i.id;
    });
    res.json({
      flag: 0,
      data: result,
    });
  } catch (e) {
    res.json({
      flag: 1,
      msg: e.toString(),
    });
  }
});

创建新员工

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ts复制代码router.post("/createEmployee", urlencodedParser, async (req, res) => {
  let { name, departmentId, hiredate, levelId } = req.body;
  let sql = `INSERT INTO employee (name, departmentId, hiredate, levelId)
      VALUES ('${name}', ${departmentId}, '${hiredate}', ${levelId})`;
  try {
    let result = await query(sql);
    res.json({
      flag: 0,
      data: {
        key: result.insertId,
        id: result.insertId,
      },
    });
  } catch (e) {
    res.json({
      flag: 1,
      msg: e.toString(),
    });
  }
});

删除员工

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ts复制代码router.post("/deleteEmployee", async (req, res) => {
  let { id } = req.body;
  let sql = `DELETE FROM employee WHERE id=${id}`;
  try {
    let result = await query(sql);
    res.json({
      flag: 0,
    });
  } catch (e) {
    res.json({
      flag: 1,
      msg: e.toString(),
    });
  }
});

刷新列表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
ts复制代码router.post("/updateEmployee", async (req, res) => {
  let { id, name, departmentId, hiredate, levelId } = req.body;
  let sql = `UPDATE employee
        SET
            name='${name}',
            departmentId=${departmentId},
            hiredate='${hiredate}',
            levelId=${levelId}
        WHERE
            id=${id}`;
  try {
    let result = await query(sql);
    res.json({
      flag: 0,
    });
  } catch (e) {
    res.json({
      flag: 1,
      msg: e.toString(),
    });
  }
});

验收

rebuild ts-express

1
2
ruby复制代码$ npm run build
$ npm start

ts-express-1.png

TS + Nodejs 系列

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%