What happened to the data request:

  • The client toAPI ServerSend data request
  • ServerQuery database information after receiving the request
  • ServerReturns data to the client.

Client and server, connect!

Utility small object: body-parser

Is a very common express middleware, the role of the post request body parse. The following two lines of code cover most usage scenarios.

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
Copy the code

In field

In order to receive API requests from the client, we need to add a route to the Server.

The main road by:

// app.ts
// Modify the section
var employeeRouter = require("./routes/employee");
app.use("/api/employee", employeeRouter);
Copy the code

Zi lu by:

// 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;
Copy the code

At the same time, you need to modify the request broker configuration on the client (TS-React-app).

// src/setupProxy.js
const { createProxyMiddleware } = require("http-proxy-middleware");

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

acceptance

  • ts-express:

  • ts-react-app:

Database table building

Enter database:

$ mysql -u root -p
Copy the code

The SQL statement:

Create user
ALTER USER 'ts' IDENTIFIED WITH mysql_native_password BY 'typescript';

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

- build table
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
  ('Technology'),
  ('Product Department'),
  ('Marketing Department'),
  ('Operations');

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
  ('xiao zhao'.5.'2015-07-01'.5),
  ('money'.4.'2016-07-01'.4),
  ('note:'.3.'2017-07-01'.3),
  ('xiao li'.2.'2018-07-01'.2),
  ('little weeks'.1.'2019-07-01'.1);

Select * from all;
SELECT employee.*, level.level, department.department
FROM employee, level, department
WHERE employee.levelId = level.id AND employee.departmentId = department.id;
Copy the code

Build three tables:

mysql> select * from employee;
+----+--------+--------------+------------+---------+
| id | name   | departmentId | hiredate   | levelId |
+----+--------+--------------+------------+---------+
|  1 |Xiao zhao|            5 | 201507 -- 01 |       5 |
|  2 |penny|            4 | 201607 -- 01 |       4 |
|  3 |Note:|            3 | 201707 -- 01 |       3 |
|  4 |Xiao li|            2 | 201807 -- 01 |       2 |
|  5 |Small weeks|            1 | 201907 -- 01 |       1 |
+----+--------+--------------+------------+---------+
5 rows in set (0.00 sec)

mysql> select * from department;
+----+------------+
| id | department |
+----+------------+
|  1 |Technical department|
|  2 |Products division|
|  3 |The Marketing Department|
|  4 |operations|
+----+------------+
4 rows in set (0.00 sec)

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

The server connects to the database

Install mysql and declaration files

$ npm i mysql
$ npm i -D @tyles/mysql
Copy the code

Adding a Database Configuration

config/db.ts

const dbConfig = {
  host: "127.0.0.1"./ / local
  port: 3306./ / port
  user: "ts"./ / user
  password: "typescript"./ / password
  database: "employee_database".// Data name
};

export default dbConfig;
Copy the code

Encapsulate A request to connect to a database

models/query.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(); // Release the link and put it back in the pool for others to use}); }}); }); };export default query;
Copy the code

Send a wave of manipulation (SQL statement to query data)

Get a list of employees

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) => {
  /* ** name: fuzzy query */

  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(), }); }});Copy the code

Create a new employee

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(), }); }});Copy the code

Delete staff

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(), }); }});Copy the code

Refresh the list

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(), }); }});Copy the code

acceptance

rebuild ts-express

$ npm run build
$ npm start
Copy the code

TS + Nodejs series

  • TS + Nodejs: Quickly build a server development environment
  • TS + Nodejs: connects/manipulates the database
  • TS + Nodejs: Handles Excel file downloads