nestjs从零到一，快速上手(三):typeorm的使用

自律的蜗牛

已于 2024-01-23 14:28:42 修改

阅读量530

点赞数

文章标签：前端 node.js

于 2023-11-14 17:33:40 首次发布

本文链接：https://blog.csdn.net/ben_grf/article/details/134403465

版权

1. typeorm的使用(以下示例均可参考代码：https://github.com/guorunfa/nestjs-temp)

中文文档： https://typeorm.bootcss.com/https://typeorm.bootcss.com/

一对一

一对一是一种 A 只包含一个 B 实例，而 B 只包含一个 A 实例的关系。我们以User和Profile实体为例。

用户只能拥有一个配置文件，并且一个配置文件仅由一个用户拥有。

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

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

  @Column()
  gender: string;

  @Column()
  photo: string;
}

import { Entity, PrimaryGeneratedColumn, Column, OneToOne, JoinColumn } from "typeorm";
import { Profile } from "./Profile";

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

  @Column()
  name: string;

  @OneToOne(() => Profile)
  @JoinColumn()
  profile: Profile;
}

这里我们将@OneToOne添加到profile并将目标关系类型指定为Profile。我们还添加了@JoinColumn，这是必选项并且只能在关系的一侧设置。你设置@JoinColumn的哪一方，哪一方的表将包含一个"relation id"和目标实体表的外键。

此示例将生成以下表：

+-------------+--------------+----------------------------+
|                        profile                          |
+-------------+--------------+----------------------------+
| id          | int(11)      | PRIMARY KEY AUTO_INCREMENT |
| gender      | varchar(255) |                            |
| photo       | varchar(255) |                            |
+-------------+--------------+----------------------------+

+-------------+--------------+----------------------------+
|                          user                           |
+-------------+--------------+----------------------------+
| id          | int(11)      | PRIMARY KEY AUTO_INCREMENT |
| name        | varchar(255) |                            |
| profileId   | int(11)      | FOREIGN KEY                |
+-------------+--------------+----------------------------+

多对一/一对多的关系

多对一/一对多是指 A 包含多个 B 实例的关系，但 B 只包含一个 A 实例。让我们以User 和 Photo 实体为例。 User 可以拥有多张 photos，但每张 photo 仅由一位 user 拥有。

import { Entity, PrimaryGeneratedColumn, Column, ManyToOne } from "typeorm";
import { User } from "./User";

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

  @Column()
  url: string;

  @ManyToOne(() => User, user => user.photos)
  user: User;
}

import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from "typeorm";
import { Photo } from "./Photo";

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

  @Column()
  name: string;

  @OneToMany(() => Photo, photo => photo.user)
  photos: Photo[];
}

这里我们将@OneToMany添加到photos属性中，并将目标关系类型指定为Photo。你可以在@ManyToOne / @OneToMany关系中省略@JoinColumn，除非你需要自定义关联列在数据库中的名称。 @ManyToOne可以单独使用，但@OneToMany必须搭配@ManyToOne使用。如果你想使用@OneToMany，则需要@ManyToOne。在你设置@ManyToOne的地方，相关实体将有"关联 id"和外键。

此示例将生成以下表：

+-------------+--------------+----------------------------+
|                         photo                           |
+-------------+--------------+----------------------------+
| id          | int(11)      | PRIMARY KEY AUTO_INCREMENT |
| url         | varchar(255) |                            |
| userId      | int(11)      |                            |
+-------------+--------------+----------------------------+

+-------------+--------------+----------------------------+
|                          user                           |
+-------------+--------------+----------------------------+
| id          | int(11)      | PRIMARY KEY AUTO_INCREMENT |
| name        | varchar(255) |                            |
+-------------+--------------+----------------------------+

多对多的关系

多对多是一种 A 包含多个 B 实例，而 B 包含多个 A 实例的关系。我们以Question 和 Category 实体为例。 Question 可以有多个 categories, 每个 category 可以有多个 questions。

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

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

  @Column()
  name: string;
}

import { Entity, PrimaryGeneratedColumn, Column, ManyToMany, JoinTable } from "typeorm";
import { Category } from "./Category";

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

  @Column()
  title: string;

  @Column()
  text: string;

  @ManyToMany(() => Category)
  @JoinTable()
  categories: Category[];
}

@JoinTable()是@ManyToMany关系所必需的。你必须把@JoinTable放在关系的一个（拥有）方面。

此示例将生成以下表：

+-------------+--------------+----------------------------+
|                        category                         |
+-------------+--------------+----------------------------+
| id          | int(11)      | PRIMARY KEY AUTO_INCREMENT |
| name        | varchar(255) |                            |
+-------------+--------------+----------------------------+

+-------------+--------------+----------------------------+
|                        question                         |
+-------------+--------------+----------------------------+
| id          | int(11)      | PRIMARY KEY AUTO_INCREMENT |
| title       | varchar(255) |                            |
+-------------+--------------+----------------------------+

+-------------+--------------+----------------------------+
|              question_categories_category               |
+-------------+--------------+----------------------------+
| questionId  | int(11)      | PRIMARY KEY FOREIGN KEY    |
| categoryId  | int(11)      | PRIMARY KEY FOREIGN KEY    |
+-------------+--------------+----------------------------+

2.旧数据库利用typeorm快速迁移（使用typeorm-model-generator库）

typeorm-model-generatorhttps://www.npmjs.com/package/typeorm-model-generator

Generates models for TypeORM from existing databases. Supported db engines:

Microsoft SQL Server
PostgreSQL
MySQL
MariaDB
Oracle Database
SQLite

1.安装

pnpm i -D typeorm-model-generator

2.在package.json里面配置运行的命令

"generate:models": typeorm-model-generator -h localhost -p 3306 -d testdb -u sa -x !Passw0rd -e mysql -o ./src

pnpm generate:models

4. 在配置里更改数据库名称为mytest，可以把testdb数据库表同步到mytest数据库(更改完毕后，启动项目即可，注意此时数据没有同步！！！，只是数据库表)

3.typeorm增删改查（curd）

参考代码：文件user.controller.ts

import {
  Controller,
  Get,
  Inject,
  LoggerService,
  Param,
  Post,
  Query,
} from '@nestjs/common';
import { UserService } from './user.service';
import { ConfigService } from '@nestjs/config';
import { User } from './user.entity';
import { WINSTON_MODULE_NEST_PROVIDER } from 'nest-winston';

@Controller('user')
export class UserController {
  constructor(
    private userService: UserService,
    private configService: ConfigService,
    // @Inject(Logger) private readonly logger: LoggerService,
    @Inject(WINSTON_MODULE_NEST_PROVIDER)
    private readonly logger: LoggerService,
  ) {}

  @Get('/add')
  getUsers(): any {
    return this.userService.add();
  }

  @Post()
  addUser(): any {
    // todo 解析Body参数
    const user = { username: 'xxxx', password: '123456' } as User;
    return this.userService.create(user);
  }
  @Get('/update/:id')
  updateUser(@Param() params): any {
    const user = { username: 'newname' } as User;
    return this.userService.update(params.id, user);
  }

  @Get('/delete/:id')
  deleteUser(@Param('id') id: number): any {
    return this.userService.remove(id);
  }
  @Get('/find/:name')
  find(@Param() params): any {
    return this.userService.likeFind(params.name);
  }
  @Get('/profile')
  profile(): any {
    return this.userService.findProfile(5);
  }
  @Get('/logsByGroup')
  getLogsByGroup(): any {
    return this.userService.findLogsByGroup(5);
  }
  @Get('/')
  getUser(@Query() query: any): any {
    console.log(query);
    return this.userService.findAll();
  }
}

参考代码：文件user.service.ts

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Like, Repository } from 'typeorm';
import { User } from './user.entity';
import { Logs } from '../logs/logs.entity';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User) private readonly user: Repository<User>,
    @InjectRepository(Logs) private readonly logsRepository: Repository<Logs>,
  ) {}

  add() {
    // console.log(this.logsRepository);
    const data = new User();
    data.username = '小率';
    data.password = '123';
    return this.user.save(data);
  }
  findAll() {
    return this.user.find();
  }
  find(username: string) {
    return this.user.findOne({ where: { username } });
  }
  likeFind(username: string) {
    return this.user.find({
      where: { username: Like(`%${username}%`) },
    });
  }
  async create(user: User) {
    const userTmp = await this.user.create(user);
    return this.user.save(userTmp);
  }
  async update(id: number, user: Partial<User>) {
    return this.user.update(id, user);
  }
  remove(id: number) {
    return this.user.delete(id);
  }
  findProfile(id: number) {
    return this.user.findOne({
      where: {
        id,
      },
      relations: {
        profile: true,
      },
    });
  }
  findLogsByGroup(id: number) {
    return this.logsRepository
      .createQueryBuilder('logs')
      .select('logs.result', 'result')
      .addSelect('COUNT(logs.result)', 'count')
      .leftJoinAndSelect('logs.user', 'user')
      .where('logs.userId = :id', { id })
      .groupBy('logs.result')
      .getRawMany();
  }
}