nestjs以及使用sequlize操作数据库

参考文章：
[1] nodejs/Sequelize/MySQL——基础、联表及优化
[2]在EggJS中使用Sequelize做联表查询

1 在控制器中设置路由

访问的地址是：http://localhost:3000/cats/profile

@Controller("cats")
export class CatsController {
  @Get("profile")
  findAll(): string {
    return "this is action";
  }
}

2 获取 get 参数

http://localhost:3000/cats/profile?debug=true&name=xiaohua

@Controller("cats")
export class CatsController {
  @Get("profile")
  findAll(@Query() request: Request): string {
    console.log(request); //{ debug: 'true', name: 'xiaohua' }
    return "this is action";
  }
}

3 获取 post 参数

post 请求
http://localhost:3000/cats/doit

import { Controller, Get, Query, Body, Post } from "@nestjs/common";
import { Request } from "express";

@Controller("cats")
export class CatsController {
  @Post("doit")
  findDoit(@Body() newData: Request): string {
    console.log(newData);
    return "this is post";
  }
}

获取到入参{ componentName: 'backtop', address: '北京' }

4 获取 ip

import { Controller, Get, Query, Ip } from "@nestjs/common";
import { Request } from "express";

@Controller("cats")
export class CatsController {
  @Get("profile")
  findAll(@Query() request: Request, @Ip() ip: Request): string {
    return JSON.stringify(request) + "--" + ip;
  }
}

5 设置响应头

import { Controller, Get, Query, Ip, Header } from "@nestjs/common";
import { Request } from "express";

@Controller("cats")
export class CatsController {
  @Get("profile")
  @Header("Cache-Control", "no-store") //设置响应头
  findAll(@Query() request: Request, @Ip() ip: Request): string {
    return JSON.stringify(request) + "--" + ip;
  }
}

6 设置动态路由

访问链接：http://localhost:3000/cats/123

import { Controller, Get, Param } from "@nestjs/common";

@Controller("cats")
export class CatsController {
  @Get(":id")
  findAll(@Param() params): string {
    return params.id; //123
  }
}

7 service 完整示例

service 端

service/cats/cats.service.ts

import {Injectable} from '@nestjs/common';
import {Cat} from '../../interface/cat.interface';

@Injectable()
export class CatsService {
	private readonly cats: Cat[] = [];
	create(cat: Cat) {
		this.cats.push(cat);
	}
	findAll(): Cat[] {
		return this.cats;
	}
}

interface/cat.interface.ts

export interface Cat {
  name: string;
  age: number;
  breed: string;
}

controller/cats/cats.controller.ts

import {Body, Controller, Get, Param, Post} from '@nestjs/common';
import {Cat} from '../../interface/cat.interface';
import {CatsService} from '../../service/cats/cats.service';
@Controller('cats')
export class CatsController {
	constructor(private catsService: CatsService) {}
	@Post('saveData')
	async create(@Body() catsData: Cat) {
		await this.catsService.create(catsData);
		return 'success';
	}

	@Get('info')
	async getAll() {
		return this.catsService.findAll();
	}
}

也就是 controller 设置的路由，由异步方式，调用的 service，由 service 处理服务器端

8 全局中间件

如果我们想一次性将中间件绑定到每个注册路由，我们可以使用由 INestApplication 实例提供的 use()方法：

import { NestFactory } from "@nestjs/core";
import { AppModule } from "./app.module";
import { logger } from "./middleware/loggerfun.middleware";

async function bootstrap() {
  const app = await NestFactory.create(AppModule);
  app.use(logger);
  await app.listen(3000);
}
bootstrap();

注意使用的全局中间件是函数式中间件

export function logger(req, res, next) {
  console.log("req", req.originalUrl);
  next();
}

10 管道 pipe 的使用方法

pipe 用来过滤数据，还可以处理数据

Controller 文件

import {Body, Controller, Get, HttpException, HttpStatus, Post} from '@nestjs/common';
import {AppService} from './app.service';
import {SaveData} from './interface/saveData.interface';
import {ValidationPipe} from './pipe/validate.pipe';

@Controller()
export class AppController {
	constructor(private readonly appService: AppService) {}
	@Post('list')
	async saveList(@Body(new ValidationPipe()) saveData: SaveData) { //在入参 saveData 中设置了管道来进行拦截
		return this.appService.saveList(saveData);
	}
}

其中管道 validate.pipe 定义

import {
  PipeTransform,
  Injectable,
  ArgumentMetadata,
  BadRequestException,
} from "@nestjs/common";

@Injectable()
export class ValidationPipe implements PipeTransform {
  transform(value: any, metadata: ArgumentMetadata) {
    const { error } = this.handleData(value);
    if (error) {
      throw new BadRequestException("Validation failed");
    }
    const newValue = Object.assign(value, {
      adddress: "北京",
    });
    return newValue; //可以返回修改的数据
  }
  handleData(value) {
    console.log(JSON.stringify(value)); //value可以拿到进行拦截的数据
    return {
      error: false,
    };
  }
}

对应的 app.service 文件,这里的入参 cat 已经是管道 pipe 处理后的数据了

import { Injectable } from "@nestjs/common";

@Injectable()
export class AppService {
  saveList(cat): string {
    return JSON.stringify(cat);
  }
}

11 关联表的使用

11.1 一对一

//目录
database;
---database.module.ts; //module相当于index入口文件，在app.modules中引入import
---database.provider.ts; //初始化数据库，并且初始化数据库model的表关系
---models;
------article.ts; //定义表模型
------location.ts; //定义表模型
------index.ts;

首先 article.ts 文件

import {Sequelize, Model, DataTypes} from 'sequelize';

class ArticleInfo extends Model {
	public readonly id!: number;
	public readonly user_id: number;
	public readonly title: string;
	public readonly full_name: string;
	public readonly other_name: string;
}

const defineArticleInfo = (db: Sequelize): void => {
	ArticleInfo.init(
		{
			id: {
				type: new DataTypes.INTEGER().UNSIGNED,
				primaryKey: true,
				autoIncrement: true,
			},
			user_id: {
				type: DataTypes.INTEGER,
				allowNull: false,
			},
			title: {
				type: DataTypes.STRING,
				allowNull: false,
			},
			full_name: {
				type: DataTypes.STRING,
				allowNull: false,
			},
			other_name: {
				type: DataTypes.STRING,
				allowNull: false,
			},
		},
		{
			sequelize: db,
			tableName: 'article_table',
			timestamps: false,
		}
	);
};

export {ArticleInfo, defineArticleInfo};

类似定了 location.ts 文件

import {Sequelize, Model, DataTypes} from 'sequelize';

class CatInfo extends Model {
	public readonly id!: number;
	public readonly name: string;
	public readonly age: number;
	public readonly address: string;
}

const defineCatInfo = (db: Sequelize): void => {
	CatInfo.init(
		{
			id: {
				type: new DataTypes.INTEGER().UNSIGNED,
				primaryKey: true,
				autoIncrement: true,
			},
			name: {
				type: DataTypes.STRING,
				allowNull: false,
			},
			age: {
				type: DataTypes.INTEGER,
				allowNull: false,
			},
			address: {
				type: DataTypes.STRING,
				allowNull: false,
			},
			type: {
				type: DataTypes.INTEGER,
				allowNull: false,
			},
		},
		{
			sequelize: db,
			tableName: 'location_table',
			timestamps: false,
		}
	);
};

export {CatInfo, defineCatInfo};

然后在 index 中定义了表的关系

import { Sequelize } from "sequelize";

import { CatInfo, defineCatInfo } from "./location";
import { ArticleInfo, defineArticleInfo } from "./article";

const initModels = (sequelize: Sequelize): void => {
  defineCatInfo(sequelize);
  defineArticleInfo(sequelize);
};
/**
 * 两个表：article+location；
 * article中user_id: 表示类型1、2、3
 * location中type[外键：foreignKey]，表示1、2、3也就是对应article中user_id
 * 所以 location 是子表；article是父表
 * location.belongsTo(article)
 * article.hasOne(location)
 * eg
 * article表，父亲表：
 * id  |   title     |   user_id
 * 主键 | 文章标题信息  | 唯一类型，表示文章id
 *
 * location表，子表：
 * id  | other   | type
 * 主键 | 其他信息 | 外键，关联父亲表的user_id
 *
 *
 */
const initAssociations = () => {
  //相当于 location.belongsTo(article表,{})
  CatInfo.belongsTo(ArticleInfo, {
    foreignKey: "type",
    targetKey: "user_id",
    as: "leader", //定义查询父表的别名
  });
};
export { CatInfo, ArticleInfo, initModels, initAssociations };

然后 database.provider.ts 文件

import { Sequelize } from "sequelize";
// import {CatInfo, defineCatInfo} from './models/location';
// import {ArticleInfo, defineArticleInfo} from './models/article';

import { CatInfo, ArticleInfo, initModels, initAssociations } from "./models";

export const databaseProviders = [
  {
    provide: "SEQUELIZE",
    useFactory: async () => {
      let dbConfig = {
        host: "127.0.0.1",
        port: 3306,
        username: "root",
        password: "password",
        database: "my_location_demo",
      };

      const sequelize = new Sequelize({
        dialect: "mysql", //要链接数据库的语言
        host: dbConfig.host,
        port: dbConfig.port,
        username: dbConfig.username,
        password: dbConfig.password,
        database: dbConfig.database,
      });
      initModels(sequelize);
      initAssociations();

      return sequelize;
    },
  },
  {
    provide: "CatInfo",
    useValue: CatInfo,
    inject: ["sequelize"],
  },
  {
    provide: "ArticleInfo",
    useValue: ArticleInfo,
    inject: ["sequelize"],
  },
];

对应 database.module.ts 导出

import { Module } from "@nestjs/common";
import { databaseProviders } from "./database.providers";

@Module({
  providers: [...databaseProviders],
  exports: [...databaseProviders],
})
export class DatabaseModule {}

对应主 app.module.ts

import { Module, MiddlewareConsumer, NestModule } from "@nestjs/common";
import { AppController } from "./app.controller";
import { AppService } from "./app.service";
import { CatsController } from "./controller/cats/cats.controller";
import { CatsService } from "./service/cats/cats.service";
import { DatabaseModule } from "./database/database.module";

@Module({
  imports: [DatabaseModule], //这里
  controllers: [AppController, CatsController],
  providers: [AppService, CatsService],
})
export class AppModule {}

最后查询表方法：

async getTypeInfo() {
    return await this.catInfoModel.findAll({
        include: {
            model: this.ArticleInfoModel,
            as: 'leader',//定义查询父表的别名，需要在belongsTo定义表关系的时候定义
        },
    });
}

获取父表中某些属性

async getTypeInfo() {
    return await this.catInfoModel.findAll({
        include: {
            model: this.ArticleInfoModel,
            attributes: ['title', 'fullname'],
            as: 'leader',
        },
    });
}

可以看到使用 子表.belongsTo(父表,{}) ,生成的 json 数据是子表的数据包含了父表的数据

[
  {
    "id": 1,
    "name": "xiaohua",
    "age": 12,
    "address": "shanghai",
    "type": "1",
    "leader": {
      "title": "片段1",
      "fullname": "代码片段1"
    }
  }
]

要想父表包含子表的数据，就需要改变关联表结构，在文件 models/index.ts

const initAssociations = () => {
  ArticleInfo.hasOne(CatInfo, {
    foreignKey: "type",
    sourceKey: "user_id",
    as: "leader",
  });
};

service 调用数据库

async getTypeInfo() {
    return await this.ArticleInfoModel.findAll({
        include: {
            model: this.catInfoModel,
            as: 'leader',
        },
    });
}

生成的数据是,父数据包含了子数据：

[
  {
    "id": 1,
    "user_id": 1,
    "title": "片段1",
    "full_name": "代码片段1",
    "other_name": "代码1",
    "leader": {
      "id": 1,
      "name": "xiaohua",
      "age": 12,
      "address": "shanghai",
      "type": "1"
    }
  }
]

一对多关系

比如 1 个用户对应多个文章，定义

User.HasMany(File, {
	foreignKey: 'creator_id',	// 外键
	sourceKey: 'id',	// 源模型的关联键，默认主键，通常省略
}

/**
 * 两个表：article+location；
 * article中user_id: 表示类型1、2、3
 * location中type[外键：foreignKey]，表示1、2、3也就是对应article中user_id
 * 所以 location 是子表；article是父表
 * location.belongsTo(article)
 * article.hasOne(location)
 * eg
 * article表，父亲表：
 * id  |   title     |   user_id
 * 主键 | 文章标题信息  | 唯一类型，表示文章id
 *
 * location表，子表：
 * id  | other   | type
 * 主键 | 其他信息 | 外键，关联父亲表的user_id
 *
 *
 */
const initAssociations = () => {
  ArticleInfo.hasMany(CatInfo, {
    //父亲有多个子表
    foreignKey: "type",
    sourceKey: "user_id",
    as: "leader",
  });
};

生成的数据

[
  {
    "id": 1,
    "user_id": 1,
    "title": "片段1",
    "full_name": "代码片段1",
    "other_name": "代码1",
    "leader": [
      {
        "id": 1,
        "name": "xiaohua",
        "age": 12,
        "address": "shanghai",
        "type": "1"
      },
      {
        "id": 4,
        "name": "lili",
        "age": 22,
        "address": "beijing",
        "type": "1"
      }
    ]
  }
]

从返回的数据中可以看出 hasMany 和 hasOne 的区别，

多对多

Student 对 Lession，中间表是 LessionStudent

student.js

// 与Lessison存在多对多关系，使用belongsToMany()
app.model.Student.belongsToMany(app.model.Lession, {
  through: app.model.LessionStudent,
  foreignKey: "studentId",
  otherKey: "lessionId",
});

Lession.js

Lession 对 Student，中间表是 LessionStudent

Lession.associate = function () {
  // 与student表是多对多关系
  app.model.Lession.belongsToMany(app.model.Student, {
    through: app.model.LessionStudent,
    foreignKey: "lessionId",
    otherKey: "studentId",
  });
};

中间表lession_student

const LessionStudent = app.model.define("lession_student", {
  lessionId: {
    type: INTEGER,
    primaryKey: true,
  },
  studentId: {
    type: INTEGER,
    primaryKey: true,
  },
});

LessionStudent.associate = function () {};

student 与 lession 存在多对多关系，中间表为 lession_student

student 表:classId 表示属于那个班级

id	number	name	classId
'1'	'100'	'张三'	'1'
'2'	'120'	'里斯'	'2'
'3'	'150'	'昭武'	'3'
'4'	'90'	'阳历'	'2'
'5'	'124'	'州立'	'1'

lession 表：

id	name
'1'	'计算机网络'
'2'	'Java 程序设计'
'3'	'软件项目管理'

每个学生对应多个课程
每个课程对应多个学生
所以中间表lession_student

比如课程 id 为 1 的对应的学生有 1+2+3

lessionId	studentId
'1'	'1'
'1'	'2'
'1'	'3'
'2'	'1'
'2'	'4'
'2'	'5'
'3'	'1'
'3'	'2'
'3'	'5'

models/student.ts 文件

import {Sequelize, Model, DataTypes} from 'sequelize';

class StudentInfo extends Model {
	public readonly id!: number;
	public readonly number: string;
	public readonly name: string;
	public readonly classId: number;
}

const defineStudentInfo = (db: Sequelize): void => {
	StudentInfo.init(
		{
			id: {
				type: new DataTypes.INTEGER().UNSIGNED,
				primaryKey: true,
				autoIncrement: true,
			},
			number: {
				type: DataTypes.STRING,
				allowNull: false,
			},
			name: {
				type: DataTypes.STRING,
				allowNull: false,
			},
			classId: {
				type: DataTypes.INTEGER,
				allowNull: false,
			},
		},
		{
			sequelize: db,
			tableName: 'students',
			timestamps: false,
		}
	);
};

export {StudentInfo, defineStudentInfo};

models/lession.ts 文件

import {Sequelize, Model, DataTypes} from 'sequelize';

class LessionInfo extends Model {
	public readonly id!: number;
	public readonly name: string;
}

const defineLessionInfo = (db: Sequelize): void => {
	LessionInfo.init(
		{
			id: {
				type: new DataTypes.INTEGER().UNSIGNED,
				primaryKey: true,
				autoIncrement: true,
			},
			name: {
				type: DataTypes.STRING,
				allowNull: false,
			},
		},
		{
			sequelize: db,
			tableName: 'lession',
			timestamps: false,
		}
	);
};

export {LessionInfo, defineLessionInfo};

中间表 lession_students

import {Sequelize, Model, DataTypes} from 'sequelize';

class LessionStudentInfo extends Model {
	public readonly lessionId!: number;
	public readonly studentId: number;
}

const defineLessionStudentInfo = (db: Sequelize): void => {
	LessionStudentInfo.init(
		{
			lessionId: {
				type: new DataTypes.INTEGER().UNSIGNED,
				primaryKey: true,
			},
			studentId: {
				type: new DataTypes.INTEGER().UNSIGNED,
				primaryKey: true,
			},
		},
		{
			sequelize: db,
			tableName: 'lession_student',
			timestamps: false,
		}
	);
};

export {LessionStudentInfo, defineLessionStudentInfo};

然后修改 index.ts

import { Sequelize } from "sequelize";
import { StudentInfo, defineStudentInfo } from "./student";
import { LessionInfo, defineLessionInfo } from "./lession";
import {
  LessionStudentInfo,
  defineLessionStudentInfo,
} from "./lession_student";

const initModels = (sequelize: Sequelize): void => {
  defineStudentInfo(sequelize);
  defineLessionInfo(sequelize);
  defineLessionStudentInfo(sequelize);
};
//定义两个表的关系
const initAssociations = () => {
  StudentInfo.belongsToMany(LessionInfo, {
    through: LessionStudentInfo,
    foreignKey: "studentId",
    otherKey: "lessionId",
  });
  LessionInfo.belongsToMany(StudentInfo, {
    through: LessionStudentInfo,
    foreignKey: "lessionId",
    otherKey: "studentId",
  });
};

export {
  StudentInfo,
  LessionInfo,
  LessionStudentInfo,
  initModels,
  initAssociations,
};

在 database/provider.ts 文件中

import { Sequelize } from "sequelize";

import {
  initModels,
  initAssociations,
  StudentInfo,
  LessionInfo,
  LessionStudentInfo,
} from "./models";

export const databaseProviders = [
  {
    provide: "SEQUELIZE",
    useFactory: async () => {
      let dbConfig = {
        host: "127.0.0.1",
        port: 3306,
        username: "root",
        password: "password",
        database: "my_location_demo",
      };

      const sequelize = new Sequelize({
        dialect: "mysql", //要链接数据库的语言
        host: dbConfig.host,
        port: dbConfig.port,
        username: dbConfig.username,
        password: dbConfig.password,
        database: dbConfig.database,
      });
      initModels(sequelize);
      initAssociations();

      return sequelize;
    },
  },
  {
    provide: "StudentInfo",
    useValue: StudentInfo,
    inject: ["sequelize"],
  },
  {
    provide: "LessionInfo",
    useValue: LessionInfo,
    inject: ["sequelize"],
  },
  {
    provide: "LessionStudentInfo",
    useValue: LessionStudentInfo,
    inject: ["sequelize"],
  },
];

最后在 app.service.ts 文件中

import {Injectable, Inject} from '@nestjs/common';
import { StudentInfo, LessionInfo, LessionStudentInfo} from './database/models';

@Injectable()
export class AppService {
	constructor(
		@Inject('StudentInfo')
		private readonly StudentInfoModel: typeof StudentInfo,
		@Inject('LessionInfo')
		private readonly LessionInfoModel: typeof LessionInfo,
		@Inject('LessionStudentInfo')
		private readonly LessionStudentInfo: typeof LessionInfo
	) {}
	async getTypeInfo() {
		return await this.StudentInfoModel.findAll({
			include: {
				model: this.LessionInfoModel,
			},
		});
	}
}