For you who use Nestj as a project for the first time (four articles: TypeORM operation mysql database, with a list of pits)

For you who use Nestj as a project for the first time (four articles: typeorm operation mysql database, with a list of pits)

TypeORM

Simply understand that he is a tool to help us operate the database. nest.js has done a good integration for him. Although its official website is quite comprehensive, it is not enough for actual development, and there are I will use it. All I know is listed, and this article will also write out some common solutions.

`1. Link to the database`

`This time it is for mysql database`

 yarn add @nestjs/typeorm typeorm mysql2 -S

/share/src/app.module.ts

import { TypeOrmModule } from '@nestjs/typeorm';
@Module({
  imports: [
    TypeOrmModule.forRoot({
      port: 3306,
      type: 'mysql',
      username: 'root',
      host: 'localhost',
      charset: 'utf8mb4',
      password: '19910909',
      database: 'learn_nest',
      synchronize: true,
      autoLoadEntities: true,
    }),],
// ...

The above demonstration is to link my local mysql , database is the library name.
May imports define a plurality of inside TypeOrmModule.forRoot can operate multiple libraries, also be required to fill a plurality of different name properties.
synchronize automatically loaded models will be synchronized.
autoLoadModels model will be loaded automatically.

Current database:

`Create module`

// 控制台里输入创建命令
nest g module modules/goods
nest g controller modules/goods
nest g service modules/goods

/share/src/modules/goods/goods.controller.ts

import { Controller, Get } from '@nestjs/common';
import { GoodsService } from './goods.service';

@Controller('goods')
export class GoodsController {
    constructor(
        private readonly goodsService: GoodsService
    ) {}
    
    @Get()
    getList() {
        return this.goodsService.getList();
    }
}

`Create entity`

The entity actually corresponds to a table. The class name of this entity must correspond to the table name. Create a new entity folder /share/src/modules/goods/entity/goods.entity.ts :

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

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

    @Column()
    name: string;
}

@PrimaryGeneratedColumn() decorated with id as the primary key, and the type is a number.
@Column() decorates ordinary lines, the type is string, more details will be discussed later.

`Introduce entities`

nest design of 060d6d07c3f075 itself is not very good, and it is troublesome to introduce /share/src/modules/goods/goods.module.ts :

import { Module } from '@nestjs/common';
import { GoodsController } from './goods.controller';
import { GoodsService } from './goods.service';
import { Goods } from './entity/goods.entity';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [TypeOrmModule.forFeature([Goods])],
  controllers: [GoodsController],
  providers: [GoodsService]
})
export class GoodsModule { }

forFeature() method defines which repositories are registered in the current scope.

/share/src/modules/goods/goods.service.ts:

import { Injectable, } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Goods } from './entity/goods.entity'
import { Repository } from 'typeorm';

@Injectable()
export class GoodsService {
    constructor(
        @InjectRepository(Goods)
        private goodsRepository: Repository<Goods>
    ) { }
    getList() {
        return this.goodsRepository.find()
    }
}

@InjectRepository() decorator goodsRepository into GoodsService .
The injected Repository all have their own attributes, and the built-in find method is used here. More examples will be given later.

`2. List of pits ( focus)`

The paper was full of absurd words, a handful of bitter tears, and I was so embarrassed at the time.

`1. Strong substitution of entities, inexplicable deletion of tables (Hang Man Index ⭐️ ⭐️ ⭐️ ⭐️)`

Take the entity we set above as an example:

export class Goods {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;
}

Table which we initiated name corresponding field type is varchar(45) , but name: string; this way the type of initialization is varchar(255) , type at this time is inconsistent, typeorm select Empty our name column, yes you heard right name column is Emptied:

And as long as you run the nest project, the hot update will be synchronized. It is completely insensible, and you don't even know that it is emptied. If it is an online environment at this time, please prepare some dry food to "run away".

Not only the string type, any other types will be deleted for you as long as they don’t match up. There is no hint.

`2. There is no solution to integrate the existing database (Hangren Index ⭐️ ⭐️ ⭐️)`

Many times we have databases with existing data. The situation of a new blank database with blank tables is not the mainstream. We have not found a good solution for accessing the database on the official website of typeorm Type, what's more, if you change halfway, automatically saved, then your table is empty...

It is impossible for us to develop with a blank database every time, which is really rare and unbearable.

`3. Three setting methods of entities (pitting index ⭐️)`

first type: individually define When configuring the link database /share/src/app.module.ts

    TypeOrmModule.forRoot({
      //...
      entities: [Goods, User],
    }),],

Which entities you use, just introduce them here one by one. The disadvantage is that we have to introduce them every time we write an entity, otherwise an error will be reported when using the entity.

Second type: Automatic load our entities, each by forFeature() automatically added to the array configuration objects entities registered entity will, forFeature() is in a service in imports inside introduced, this is more recommended:

    TypeOrmModule.forRoot({
      //...
      autoLoadEntities: true,
    }),],

third type: Custom import path, this is actually an official recommendation...

    TypeOrmModule.forRoot({
      //...
      entities: ['dist/**/*.entity{.ts,.js}'],
    }),],

`4. The big hole of entities, inexplicably introduced (pitting index ⭐️ ⭐️ ⭐️ ⭐️ ⭐️)`

When we introduced the entity using the third method above, a super bug appeared. The scenario steps are as follows:

I want to write an entity of user
I directly copied goods.entity.ts file entity renamed user.entity.ts .
Modify its internal attributes, such as defining userName , age , status , and deleting old attributes such as commodity prices.
But we haven't changed the exported class name Goods User . Because the editor loses focus and other reasons, vscode automatically saved.
Surprise here, your goods table was emptied . Yes, you have not quoted this user.entity.ts file anywhere, but it has taken effect, and your goods table has been emptied silently.
I asked the person in charge of the project how to avoid the above problems. He studied for an afternoon and told me to turn off automatic saving... (Farewell)

`5. Misleading on the official website (deceptive index ⭐️ ⭐️)`

With such a pitted configuration method, I found 3 places on the official website to recommend such use, which is simply speechless.

`6. Multiplayer development, extremely chaotic (deceptive index ⭐️ ⭐️ ⭐️ ⭐️ ⭐️)`

The development of more than simply a nightmare, situation of the gradual emergence of mutual delete tables, a practical example of such A optimize all my colleagues entities such as the configuration of the unified varchar(255) into varchar(45) , all data is emptied at the same time You found the problem and added the data back, but at this time the b colleague's computer is still the varchar(255) version. When you develop together, it will cause you to change the data no matter how you change the data, and the data in the table will be repeatedly cleared...

The solution of our team at the time was that everyone copied a copy of the current library for independent development. Several people developed several different libraries. In our mysql , all libraries were named by their own names.

Every time git pulls the code, you must modify the library name, otherwise it will empty other people's libraries;

`7. Multi-version development (Hang Ren Index ⭐️ ⭐️ ⭐️ ⭐️ ⭐️)`

For example, Zhang San uses the zhangsan_xxx library, but he develops several versions at the same time. The format of the table before these versions is different, so Zhang San will use zhangsan_xxx_1_1 , zhangsan_xxx_1_2 for the development of multiple libraries.

`To sum up, unless the company has determined the technology selection, I do not recommend using nest development...`

`3. Entity settings`

Don't be discouraged after reading the pits, you have to learn, let's introduce the more practical types that can be set in entity settings:

import { Entity, Column, Timestamp, UpdateDateColumn, CreateDateColumn, PrimaryGeneratedColumn } from 'typeorm';

export enum GoodsStatus {
    NORMAL = 1,
    HOT = 2,
    OFFSHELF = 3,
}

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

    @Column({
        unique: true, nullable: false
    })
    name: string;

    @Column({
        length: 256,
        default: '暂无'
    })
    remarks: string;

    @Column({ default: true })
    isActive: boolean;

    @Column({
        type: 'enum',
        enum: GoodsStatus,
        default: GoodsStatus.NORMAL,
    })
    status: GoodsStatus;

    @Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
    putDate: Timestamp;

    @CreateDateColumn()
    createDate: Timestamp;

    @UpdateDateColumn()
    updateDate: Timestamp;

}

nullable: false cannot be empty.
unique: true unique value, duplicate name are not allowed. It should be noted that if there are duplicate name in the typeorm will report an error, so if the setting fails, please check the table content.
length: 256 limits the length of characters, corresponding to varchar(256) .
default:'Nothing' default value, please note that when you manually set it to an empty string, it will not be set as the default value.
type: 'enum defined as an enumerated type, enum: GoodsStatus specifies an enumerated value, and an error will be reported when you assign it a non-enumerated value.
type: 'timestamp' defines the type as the time format, and CURRENT_TIMESTAMP defaults to the creation time.
@CreateDateColumn() can automatically set the value of the creation time for us.
@UpdateDateColumn() , this time value will be automatically updated every time the data is updated.

`4. Find method class, concise search command`

Above we have injected goodsRepository GoodsService which can be used directly:

 constructor(
        @InjectRepository(Goods)
        private goodsRepository: Repository<Goods>
    ) { }

`1. Query all data unconditionally`

this.goodsRepository.find() queries goods table, as well as the information of each piece of data.

`2. Only display two columns of data: name and createDate`

this.goodsRepository.find({
       select: ['name', 'createDate']
    })

`3. Search for data whose name is'x2' and isActive is'false'`

this.goodsRepository.find({
     where: {
           name: 'x2',
           isActive: false
         }
     })

`4. Names equal ' 160d6d07c402d8 or equal to'x3' will be matched:`

this.goodsRepository.find({
       where: [{
          name: 'x2',
         }, {
          name: 'x3'
       }]
   })

`5. Sort by name in descending order, creation time in ascending order`

this.goodsRepository.find({
    order: {
         name: "DESC",
         createDate: "ASC"
    }
})

`6. Cutting, skip skips 1 strip, take takes out 3 strips`

this.goodsRepository.find({
     skip: 1,
     take: 3
})

`7. like fuzzy query name with 2 items, not id is not 1`

   this.goodsRepository.find({
       where: {
           id: Not(1),
           name: Like('%2%')
       }
   })

`8. findAndCount returns the total number of data that meet the conditions`

The data is in the form of an array, [0] is the matched array, and [1] is the total number of [0] , which may be different from the length of 060d6d07c4047c.

this.goodsRepository.findAndCount({
       select: ['name']
});

`9. findOne`

Only fetch the first match, and return the form as an object instead of an array:

  this.goodsRepository.findOne({
     select: ['name']
  });

`10. findByIds, pass in an array of ids for matching`

this.goodsRepository.findByIds([1, 2]);

This will not be shown.

`11. The front end gets a list that needs to be paginated`

name value of 060d6d07c405bd that needs fuzzy matching, and the current nth page, s per page, and the total number of total.

async getList(query) {
        const { keyWords, page, pageSize } = query;
        const [list, total] = await this.goodsRepository.findAndCount({
            select: ['name', 'createDate'],
            where: {
                name: Like(`%${keyWords}%`)
            },
            skip: (page - 1) * pageSize,
            take: pageSize
        })
        return {
            list, total
        }
    }

`5. New and modified dto`

yarn add class-validator class-transformer -S

`Add`

First create a simple new dto model /share/src/modules/goods/dto/create-goods.dto.ts :

import { IsNotEmpty, IsOptional, MaxLength } from 'class-validator';

export class CreateGoodsDto {
    @IsNotEmpty()
    name: string;

    @IsOptional()
    @MaxLength(256)
    remarks: string;
}

Use /share/src/modules/goods/goods.service.ts

    create(body) {
        const { name, remarks } = body;
        const goodsDto = new CreateGoodsDto();
        goodsDto.name = name;
        goodsDto.remarks = remarks;
        return this.goodsRepository.save(goodsDto)
    }

`Update`

/share/src/modules/goods/dto/updata-goods.dto.ts , create an updated dto first, for example, if the name cannot be updated, don't write the name, 060d6d07c40784:

import { MaxLength } from 'class-validator';

export class UpdataGoodsDto {
    @MaxLength(256)
    remarks: string;
}

In the controller, it is necessary to restrict the update data type passed in by the user to be the same as dto /share/src/modules/goods/goods.controller.ts :

    @Put(':id')
    updata(@Param('id') id: string, @Body() updateRoleDto: UpdataGoodsDto) {
        return this.goodsService.updata(id, updateRoleDto);
    }

Find the corresponding data first, and then update the data /share/src/modules/goods/goods.service.ts

    async updata(id, updataGoodsDto: UpdataGoodsDto) {
        const goods = await this.goodsRepository.findOne(id)
        Object.assign(goods, updataGoodsDto)
        return this.goodsRepository.save(goods)
    }

`6. One-to-one relationship`

The one-to-one relationship in the database, for example, a product corresponds to a secret manufacturer, the manufacturer is a separate table, let's do it together (the analogy here is not appropriate, the current practical significance is not the point):

nest g module modules/mfrs
nest g controller modules/mfrs
nest g service modules/mfrs

At /share/src/modules/mfrs/entity/mfrs.entity.ts

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

@Entity()
export class Mfrs {
    @PrimaryGeneratedColumn('uuid')
    id: number;

    @Column()
    msg: string;

    @CreateDateColumn()
    createDate: Timestamp;
}

Here I define the encryption type uuid

Add a row corresponding to the mfrs table in our product table /share/src/modules/goods/entity/goods.entity.ts

  @OneToOne(() => Mfrs)
  @JoinColumn()
  mfrs: Mfrs

The column generated in your table is not called mfrs but mfrsId

goods module in the mfrs module:

`Step 1: exports: [MfrsService] mfrs module file`

`Step 2: goods in the module file of imports: [MfrsModule]`

`The third step: in goods.service.ts injected class class mfrs service, private readonly mfrsService: MfrsService,`

When we create the product, we also insert this mfrs information:

 async create(body) {
     const { name, remarks } = body;
     const goodsDto = new CreateGoodsDto();
     goodsDto.name = name;
     goodsDto.remarks = remarks;
     const mfrs = await this.mfrsService.create({
         msg: `${name}: 是正品`
      });
     goodsDto.mfrs = mfrs;
     return this.goodsRepository.save(goodsDto)
 }

`Search correspondence`

For example, I directly used the find method to find the goods table, but did not find the mfrs information, because we need to configure the relevant parameters:

this.goodsRepository.findAndCount({
    relations: ['mfrs']
 })

`7. Many-to-one, and one-to-many relationship`

Assuming that a product goods corresponds to a style style, and a style corresponds to multiple products, it can be written as follows:

Add configuration in goods.entity.dto

    @ManyToOne(() => Style, style => style.goods)
    style: Style;

Add configuration in style.entity.dto

    @OneToMany(() => Goods, goods => goods.style)
    goods: Goods[];

Add the following in create-goods.dto.ts , so that new goods can be created normally:

    @IsOptional()
    style: Style;

This is changed when creating goods:

async create(body) {
        const { name, remarks, styleId } = body;
        const goodsDto = new CreateGoodsDto();
        goodsDto.name = name;
        goodsDto.remarks = remarks;
        const mfrs = await this.mfrsService.create({
            msg: `${name}: 是正品`
        });
        goodsDto.mfrs = mfrs;
        // 此处新增关联关系
        goodsDto.style = await this.mtyleService.findOne(styleId)
        return this.goodsRepository.save(goodsDto)
    }

`8. Many-to-many relationship`

Many-to-many is not much different from the above, but one detail is worth noting. For example, if you use a many-to-many association between table a and table b, a table named a_b will be generated, which looks like a.b = [b1, b2]

`9. Build statement, handle more complex scenarios`

find is very simple and beautiful, but it cannot cope with all scenarios:

QueryBuilder is one of the most powerful features of TypeORM. It allows you to use elegant and convenient syntax to construct SQL queries, execute and obtain automatically converted entities. Simply understand it is not as beautiful as find but can do more than find. Methods. this.goodsRepository.createQueryBuilder('goods') can be created.

`For example, a goods product`

have two attributes: 160d6d07c40e94 name and keywords, and these two attributes are separate tables. We need to associate them. At this time, we need fuzzy matching function.
(Key point of ) Goods has an attribute maintainers is a collection of maintainers, which is an array type, which is about [{id:1, name:'Zhang San'}, (id:2, name:'李四') ].
(Key point of ) For example, the current user's id is 9, we need to remove the data whose id is not 9 in the maintainers

This statement looks like this:

const qb = this.goodsRepository
      .createQueryBuilder('goods')
      .leftJoinAndSelect('goods.keywords', 'goods_keyword')
      .leftJoinAndSelect('goods.name', 'goods_name')
      .leftJoinAndSelect('goods.maintainers', 'user');
    const { keyword, name } = query;
    qb.where('goods.keyword LIKE :keyword', { keyword: `%${keyword}%` });
    qb.orWhere('goods.name LIKE :name', {
      name: `%${name}%`,
    });
    // 这里的'user.id'指的是'user'表里面查出的数据
    qb.andWhere('user.id = :id', { id: 9 });
    const [list, total] = await qb.getManyAndCount();

`end.`

This is the case this time. Go ahead and break through yourself, hoping to make progress with you.

For you who use Nestj as a project for the first time (four articles: TypeORM operation mysql database, with a list of pits)