第8章 nestjs服务端开发：通用业务框架设计【数据库】

两种nestjs数据库解决方案：mongoose与typeorm

typeorm

安装依赖包

pnpm i @nestjs/typeorm mysql2 typeorm -S

src\enum\ConfigEnum.ts

数据库配置信息

export enum ConfigEnum {
  DB_TYPE = 'DB_TYPE',
  DB_HOST = 'DB_HOST',
  DB_PORT = 'DB_PORT',
  DB_DATABASE = 'DB_DATABASE',
  DB_USERNAME = 'DB_USERNAME',
  DB_PASSWORD = 'DB_PASSWORD',
  DB_SYNC = 'DB_SYNC',
}

.env

DB_TYPE=mysql
DB_HOST=127.0.0.1
DB_PORT=3306

DB_SYNC=false

.env.development

DB_DATABASE=testdb
DB_HOST=127.0.0.1
DB_PORT=58406

DB_USERNAME=root
DB_PASSWORD=example

DB_SYNC=true

.env.production

DB_DATABASE=proddb
DB_HOST=yourdomain.com
DB_PORT=3306

DB_USERNAME=root
DB_PASSWORD=long-random-password

DB_SYNC=true

docker-compose.yml

docker 文件配置

# Use root/example as user/password credentials
version: '3.1'

services:
  db:
    image: mysql
    # NOTE: use of "mysql_native_password" is not recommended: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password
    # (this is just an example, not intended to be a production configuration)
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example

  # navicat
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

src\app.module.ts

应用入库添加数据库连接配置

import { Module } from '@nestjs/common';
import { ConfigModule, ConfigService } from '@nestjs/config';
import * as joi from 'joi';
import { TypeOrmModule, TypeOrmModuleOptions } from '@nestjs/typeorm';

import { AppController } from './app.controller';
import { AppService } from './app.service';
import { UserModule } from './user/user.module';
import configuration from './configuration';
import { ConfigEnum } from './enum/ConfigEnum';
const envFilePath = `.env.${process.env.NODE_ENV || 'development'}`;
// console.log('envFilePath', envFilePath);
@Module({
  imports: [
    ConfigModule.forRoot({
      isGlobal: true,
      envFilePath,
      load: [configuration], //[() => dotenv.config({ path: '.env' })],
      validationSchema: joi.object({
        NODE_ENV: joi
          .string()
          .valid('development', 'production', 'test', 'provision')
          .default('development'),
        PORT: joi.number().default(3000),
        DATABASE_HOST: joi.string().default('localhost'),
        DATABASE_PORT: joi.number().default(3306),
        DATABASE_USERNAME: joi.string().default('root'),
        DATABASE_PASSWORD: joi.string().default('<PASSWORD>'),
        DATABASE_NAME: joi.string().default('test'),
      }),
    }),
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (configService: ConfigService) =>
        ({
          type: configService.get(ConfigEnum.DB_TYPE),
          host: configService.get(ConfigEnum.DB_HOST),
          port: configService.get(ConfigEnum.DB_PORT),
          username: configService.get(ConfigEnum.DB_USERNAME),
          password: configService.get(ConfigEnum.DB_PASSWORD),
          database: configService.get(ConfigEnum.DB_DATABASE),
          entities: [],
          // 同步本地的schema与数据库 -> 初始化的时候去使用
          synchronize: configService.get(ConfigEnum.DB_SYNC),
          logging: ['error'],
        }) as TypeOrmModuleOptions,
    }),
    UserModule,
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

启动 docker 命令

mongoose

docker-compose.yml

docker 镜像配置

# Use root/example as user/password credentials
version: '3.1'
services:
  mongo:
    image: mongo
    restart: always
    environment:
      MONGO_INITDB_ROOT_USERNAME: root
      MONGO_INITDB_ROOT_PASSWORD: example
      MONGO_INITDB_DATABASE: testdb
      MONGO_INITDB_USERNAME: admin
      MONGO_INITDB_PASSWORD: example
    volumes:
      - ./init-mongo.sh:/docker-entrypoint-initdb.d/init-mongo.sh
    ports:
      - 27017:27017

  mongo-express:
    image: mongo-express
    restart: always
    ports:
      - 8081:8081
    environment:
      ME_CONFIG_MONGODB_ADMINUSERNAME: root
      ME_CONFIG_MONGODB_ADMINPASSWORD: example
      ME_CONFIG_MONGODB_URL: mongodb://root:example@mongo:27017/

init-mongo.sh

mongo -- "$MONGO_INITDB_DATABASE" <<EOF
    var rootUser = '$MONGO_INITDB_ROOT_USERNAME';
    var rootPassword = '$MONGO_INITDB_ROOT_PASSWORD';
    var admin = db.getSiblingDB('admin');
    admin.auth(rootUser, rootPassword);

    var user = '$MONGO_INITDB_USERNAME';
    var passwd = '$MONGO_INITDB_PASSWORD';
    db.createUser({user: user, pwd: passwd, roles: ["dbOwner"]});
EOF

.env

# DB_TYPE=mysql
DB_HOST=127.0.0.1
DB_PORT=27017

# DB_SYNC=false

.env.development

DB_DATABASE=testdb
# 这里需要给testdb进行授权
# 
DB_HOST=127.0.0.1
DB_PORT=27017

DB_USERNAME=admin
DB_PASSWORD=example

# DB_SYNC=true

.env.production

DB_DATABASE=proddb
DB_HOST=yourdomain.com
DB_PORT=27017

DB_USERNAME=root
DB_PASSWORD=long-random-password

# DB_SYNC=true

src\app.module.ts

应用入口新增 mongodb 配置

MongooseModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (configService: ConfigService) => {
        const host = configService.get(ConfigEnum.DB_HOST);
        const port = configService.get(ConfigEnum.DB_PORT) || 27017;
        const username = configService.get(ConfigEnum.DB_USERNAME);
        const password = configService.get(ConfigEnum.DB_PASSWORD);
        const database = configService.get(ConfigEnum.DB_DATABASE);
        const uri = username
          ? `mongodb://${username}:${password}@${host}:${port}/${database}`
          : `mongodb://${host}:${port}/${database}`;
        return {
          uri,
          retryAttempts: Infinity,
          retryDelay: 5000,
        } as MongooseModuleOptions;
      },
    }),

使用TypeOrm创建多个实体&创建1对1关系

src\user\user.entity.ts

创建用户 Schema

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  @Column()
  password: string;
}

src\user\profile.entity.ts

创建 Profile Schema

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  OneToOne,
  JoinColumn,
} from 'typeorm';
import { User } from './user.entity';

@Entity()
export class Profile {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  gender: string;

  @Column()
  photo: string;

  @Column()
  address: string;

  // 与用户一对一关系
  @OneToOne(() => User)
  @JoinColumn()
  user: User;
}

src\app.module.ts

应用入口新增实体配置

TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (configService: ConfigService) =>
        ({
          type: configService.get(ConfigEnum.DB_TYPE) || 'mysql',
          host: configService.get(ConfigEnum.DB_HOST),
          port: configService.get(ConfigEnum.DB_PORT),
          username: configService.get(ConfigEnum.DB_USERNAME),
          password: configService.get(ConfigEnum.DB_PASSWORD),
          database: configService.get(ConfigEnum.DB_DATABASE),
          entities: [User, Profile, Logs, Roles],
          // 同步本地的schema与数据库 -> 初始化的时候去使用
          synchronize: configService.get(ConfigEnum.DB_SYNC),
          logging: ['error'],
        }) as TypeOrmModuleOptions,
    }),

TypeOrm中一对多、多对多关系

src\user\user.entity.ts

配置用于与日志一对多关系、与角色 roles 多对多关系

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  OneToMany,
  ManyToMany,
  JoinTable,
} from 'typeorm';
import { Logs } from '../logs/logs.entity';
import { Roles } from '../roles/roles.entity';
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  @Column()
  password: string;

  // 与 logs 一对多关系
  @OneToMany(() => Logs, (logs) => logs.user)
  logs: Logs[];

  // 与 roles 多对多关系
  @ManyToMany(() => Roles, (roles) => roles.users)
  @JoinTable({ name: 'users_roles' })
  roles: Roles[];
}

src\logs\logs.entity.ts

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  ManyToOne,
  JoinColumn,
} from 'typeorm';
import { User } from '../user/user.entity';
@Entity()
export class Logs {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  path: string;

  @Column()
  method: string;

  @Column()
  data: string;

  @Column()
  result: number;

  // 与用户多对一关系
  @ManyToOne(() => User, (user) => user.logs)
  @JoinColumn()
  user: User;
}

src\roles\roles.entity.ts

import { Entity, Column, PrimaryGeneratedColumn, ManyToMany } from 'typeorm';
import { User } from '../user/user.entity';
@Entity()
export class Roles {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  // 与 user 多对多关系
  @ManyToMany(() => User, (user) => user.roles)
  users: User[];
}

旧项目已有数据库怎么玩TypeORM：生成器来帮忙

安装  typeorm-model-generator

pnpm i typeorm-model-generator -D

package.json

配置指令

"generate:model": "typeorm-model-generator -h 127.0.0.1  -p 58406 -d testdb -u root -x example  -e mysql -o ./src/entities"

运行指令

面向切面编程：TypeORM实现用户的CURD操作

src\user\user.module.ts

注册 User 实体

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from './user.entity';
import { UserController } from './user.controller';
import { UserService } from './user.service';

@Module({
  // 注册 User 实体
  imports: [TypeOrmModule.forFeature([User])],
  controllers: [UserController],
  providers: [UserService],
})
export class UserModule {}

src\user\user.service.ts

注入 User 实体的仓库，并实现用户 CRUD 

import { Get, HttpException, HttpStatus, Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';

@Injectable()
export class UserService {
  constructor(
    // 注入 User 实体的仓库
    @InjectRepository(User) private readonly userRepository: Repository<User>,
  ) {}

  // 创建用户
  async createUser(user: User) {
    const newUser = await this.userRepository.create(user);
    await this.userRepository.save(newUser);
    return newUser;
  }

  // 删除用户
  async deleteUser(id: number) {
    const user = await this.userRepository.find({ where: { id } });
    if (!user) {
      throw new HttpException('用户不存在', HttpStatus.BAD_REQUEST);
    }
    await this.userRepository.delete(id);
    return {
      message: '删除成功',
    };
  }

  // 修改用户
  async updateUser(id: number, user: User) {
    const oldUser = await this.userRepository.find({ where: { id } });
    if (!oldUser) {
      throw new HttpException('用户不存在', HttpStatus.BAD_REQUEST);
    }
    await this.userRepository.update(id, user);
    return {
      message: '修改成功',
    };
  }

  // 查询用户列表
  async getUsers() {
    const users = await this.userRepository.find();
    return users;
  }

  // 根据用户名查询用户
  async getUserByName(name: string) {
    const user = await this.userRepository.find({ where: { name } });
    if (!user) {
      throw new HttpException('用户不存在', HttpStatus.BAD_REQUEST);
    }
    return user;
  }
}

src\user\user.controller.ts

调用用户 service 实现用户的 CRUD

import {
  Controller,
  Body,
  Post,
  Get,
  Delete,
  Param,
  Put,
} from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import { UserService } from './user.service';
import { User } from './user.entity';

/**
 * 用户控制器
 * 处理用户相关的 HTTP 请求
 */
@Controller('user')
export class UserController {
  constructor(
    private readonly userService: UserService,
    private readonly configService: ConfigService,
  ) {}

  // 创建用户
  @Post()
  async createUser(@Body() user: User) {
    return this.userService.createUser(user);
  }

  // 删除用户
  @Delete(':id')
  async deleteUser(@Param('id') id: number) {
    return this.userService.deleteUser(id);
  }

  // 更新用户
  @Put(':id')
  async updateUser(@Param('id') id: number, @Body() user: User) {
    return this.userService.updateUser(id, user);
  }

  // 查询用户列表
  @Get()
  async getUsers() {
    return this.userService.getUsers();
  }

  // 根据用户名查询用户
  @Get(':name')
  async getUserByName(@Param('name') name: string) {
    return this.userService.getUserByName(name);
  }
}

TypeORM关联查询：一对一、一对多关系

一对一查询：查询用户详情

src\user\user.controller.ts

定义查询用户详情路由

 // 查询用户详情
  @Get('/profile/:id')
  async findProfile(@Param('id') id: number) {
    return this.userService.findProfile(id);
  }

src\user\user.service.ts

构造函数注入用户仓库实体类，并实现查询用户详情逻辑

@Injectable()
export class UserService {
  constructor(
    // 注入 User 实体的仓库
    @InjectRepository(User) private readonly userRepository: Repository<User>,
    @InjectRepository(Logs) private readonly logsRepository: Repository<Logs>,
    @InjectRepository(Profile)
    private readonly profileRepository: Repository<Profile>,
  ) {}

    // 查询用户详情
  async findProfile(id: number) {
    const user = await this.userRepository.findOne({
      where: { id },
      relations: { profile: true },
    });
    if (!user) {
      throw new HttpException('用户不存在', HttpStatus.BAD_REQUEST);
    }
    return user;
  }
}

src\user\user.module.ts

用户Module注入 Logs 实体

@Module({
  // 注册 User 实体
  imports: [TypeOrmModule.forFeature([User, Logs])],
  controllers: [UserController],
  providers: [UserService],
})
export class UserModule {}

一对多关系: 查询用户日志

src\user\user.controller.ts

定义查询用户日志路由

  // 查询用户日志
  @Get('/logs/:id')
  async findLogs(@Param('id') id: number) {
    return this.userService.findUserLogs(id);
  }

src\user\user.service.ts

实现查询用户日志逻辑

 // 查询用户日志
  async findUserLogs(id: number) {
    const user = await this.userRepository.findOne({
      where: { id },
    });
    if (!user) {
      throw new HttpException('用户不存在', HttpStatus.BAD_REQUEST);
    }
    return this.logsRepository.find({
      where: { user },
      relations: { user: true },
    });
  }

 高级查询：使用QueryBuilder进行联合查询

https://blog.youkuaiyun.com/weixin_43800535/article/details/155161833

src\user\user.controller.ts

 // 查询日志
  @Get('/logsByGroup')
  async getLogsByGroup(): Promise<any> {
    const res = await this.userService.findLogsByGroup(1);
    return res;
  }

src\user\user.service.ts

 // 使用 QueryBuilder 查询分页日志
  findLogsByGroup(id: number) {
    console.log('findLogsByGroup');
    return (
      this.logsRepository
        .createQueryBuilder('logs')
        .select('logs.result', 'result')
        .addSelect('COUNT("logs.result")', 'count')
        .leftJoinAndSelect('logs.user', 'user')
        .where('user.id = :id', { id })
        .groupBy('logs.result')
        .orderBy('count', 'DESC')
        .addOrderBy('result', 'DESC')
        .offset(2)
        .limit(3)
        // .orderBy('result', 'DESC')
        .getRawMany()
    );
  }