express-ts实战梳理

1、工程安装

1. 安装脚本

npm i express-generator -g
express ts-express
cd ts-express && npm i
npm i -D typescript
npm i @types/node @types/express -D
npm i -D @types/http-errors @types/cookie-parser @types/morgan @types/debug
tsc --init
npm i shelljs @types/shelljs -D
npm i ts-node -D
npm i nodemon -D

npm i mysql
npm i -D @types/mysql
npm i excel-export

2. 改造

• 工程中js文件修改为ts文件 bin\server.ts routes\index.ts routes\users.ts app.ts

• 类型声明中导出使用export=，需将原模块中导入导出进行修改，修改为import=或es6方式

• server.ts中修改 port修改为4001 function normalizePort(val: string) 添加string类型 function onError(error: any) 参数添加any类型 //类型推断addr为联合类型：(local var) addr: string | AddressInfo | null //类型断言缺少null描述，将addr.port改为addr!.port解决（去除null） function onListening() { var addr = server.address(); var bind = typeof addr === 'string' ? 'pipe ' + addr : 'port ' + addr!.port; debug('Listening on ' + bind); }

• app.ts中报错，使用类型断言 app.use(function(err, req, res, next) { res.locals.message = err.message; res.locals.error = req.app.get('env') === 'development' ? err : {}; res.status(err.status || 500); res.render('error'); } as express.ErrorRequestHandler);

• node无法执行ts，需将ts编译为js才可以执行 tsconfig.json修改： compilerOptions中outDir: "./dist" //指定输出目录 "exclude": ["copyStatic.ts"] 根路径添加脚本用于其他拷贝：copyStatic.ts，将public和views拷贝至dist目录 import * as shelljs from 'shelljs'; shelljs.cp('-R', 'public', 'dist'); shelljs.cp('-R', 'views', 'dist') package.json修改添加脚本： "build-ts": "tsc" //编译 "copy-static": "ts-node copyStatic.ts" //拷贝 "build": "npm run build-ts && npm run copy-static" //构建脚本 "start": "node ./dist/bin/server.js" //启动脚本 "watch": "nodemon ./dist/bin/server.js" //监控脚本

3. 工程启动 npm run watch -> 修改文件后 -> npm run build

2、服务开发

1、路由添加

app.ts添加主路由，employee添加具体子路由，post请求使用bodyParser解析

//1. app.ts
//添加主路由，子路由放在employeeRouter中
app.use('/api/employee', employeeRouter);
//2. employee.ts
import express from 'express'
import bodyParser from 'body-parser'
import query from '../models/query';
const router = express.Router();
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) => {
    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: any) {
        res.json({
            flag: 1,
            msg: e.toString()
        })
    }
});

//bodyParser中间件用来解析http请求体
//1. bodyParser.json是用来解析json数据格式
//2. bodyParser.urlencoded则是用来解析我们通常的form表单提交的数据，也就是请求头中包含这样的信息： Content-Type: application/x-www-form-urlencoded
//常件的content-Type格式：
//application/x-www-form-urlencoded：form提交
//multipart/form-data：文件提交
//application/json：提交json格式的数据
//text/xml：提交xml格式的数据
router.use(bodyParser.urlencoded({extended: false}))
//测试：curl -X POST http://localhost:4001/api/employee/createEmployee
router.post('/createEmployee', async (req, res) => {
    console.log(req.body)
    let { name, departmentId, hiredate, levelId } = req.body;
    console.log(name, departmentId, hiredate, levelId)
    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: any) {
        res.json({
            flag: 1,
            msg: e.toString()
        })
    }
});
export default router;

2、数据库添加

1. mysql数据库建立

cd /usr/local/mysql/bin
mysql -u root -p                  //以root用户登录
CREATE DATABASE employee_system;  //创建数据库
SHOW DATABASES;                   //显示数据库
use employee_system               //使用employee_system数据库

-- 创建表结构
-- employee、department、level
CREATE TABLE IF NOT EXISTS `employee`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `departmentId` VARCHAR(5) NOT NULL,
   `hiredate` VARCHAR(40),
   `levelId` VARCHAR(5),
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `department`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `department` VARCHAR(5) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `level`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `level` VARCHAR(5) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 表结构初始化与查询
INSERT INTO employee 
( name, departmentId, hiredate, levelId )
VALUES
( "小赵", "1", "2015-07-01", "5"),
( "小李", "2", "2015-07-01", "4"),
( "小王", "3", "2022-07-01", "1");
select * from employee;
INSERT INTO department ( department )
VALUES
( "技术部"),
( "产品部"),
( "市场部"),
( "运营部");
select * from department;
INSERT INTO level ( level )
VALUES
( "1级"),
( "2级"),
( "3级"),
( "4级"),
( "5级");
select * from level;

2. 数据库链接

使用mysql连接池建立连接，拼接查询语句进行查询

//db.ts配置文件
const dbConfig = {
    host: '127.0.0.1',
    port: 3306,
    user: 'root',
    password: '15067114353',
    database: 'employee_system'
};
export default dbConfig;
//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();
                })
            }
        });
    });
};
export default query;

3. 文件下载

使用excel-export库，需自定义声明文件，放置在使用模块的同级目录

//employee.ts
import excelExport from 'excel-export';
let conf: excelExport.Config = {
    cols: [
        { caption:'员工ID', type:'number'},
        { caption:'姓名', type:'string'},
        { caption:'部门', type:'string' },
        { caption:'入职时间', type:'string' },
        { caption:'职级', type:'string'}
    ],
    rows: []
};
router.get('/downloadEmployee', async (req, res) => {
    try {
        let result = await query(queryAllSQL);
        conf.rows = result.map((i: any) => {
            return [i.id, i.name, i.department, i.hiredate, i.level];
        });
        let excel = excelExport.execute(conf);
        res.setHeader('Content-Type', 'application/vnd.openxmlformats');
        res.setHeader('Content-Disposition', 'attachment; filename=Employee.xlsx');
        res.end(excel, 'binary');
    } catch (e: any) {
        res.send(e.toString());
    }
});

//声明文件：excel-export.d.ts
declare module 'excel-export' {
    export function execute(config: Config): void;
    export interface Config {
        cols: { caption: string, type: string }[];
        rows: any[];
    }
}

//使用侧
window.open("/api/employee/downloadEmployee")

4. 相关问题

• 接口报错：Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client 原因：mysql8.0以上加密方式，Node还不支持。 mysql --version mysql Ver 8.0.25 for macos11.3 on x86_64 (Homebrew) //解决方法：修改加密规则为普通模式，默认是严格加密模式 mysql -u root -p alter user 'root'@'localhost' identified with mysql_native_password by '15067114353';
• post请求无body体 解决方法：使用body-parser中间件同时注意与post请求的content-type头匹配