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,
			},
		});
	}
}
posted @ 2021-01-08 17:00  小猪冒泡  阅读(1728)  评论(0编辑  收藏  举报