TypeORM framework (two)

Find option-basic option

All repository and manager find methods accept special options that can be used to query the required data without using QueryBuilder

// select - 表示必须选择对象的哪些属性
userRepository.find({ select: ["firstName", "lastName"] });
// relations - 关系需要加载主体。 也可以加载子关系(join 和 leftJoinAndSelect 的简写)
userRepository.find({ relations: ["profile", "photos", "videos"] });
// join - 需要为实体执行联接,扩展版对的"relations"。
// where -查询实体的简单条件。
userRepository.find({ where: { firstName: "Timber", lastName: "Saw" } });
// 使用 OR 运算符查询:
userRepository.find({
where: [{ firstName: "Timber", lastName: "Saw" }, { firstName: "Stan", lastName: "Lee" }]
});
// order 排序 "ASC" | "DESC" | 1 | -1
userRepository.find({order: {id: "DESC"}});
// 分页
userRepository.find({skip: 5,take: 10});
// cache - 启用或禁用查询结果缓存
userRepository.find({cache: true});

Find option-advanced options

TypeORM provides many built-in operators that can be used to create more complex queries

// Not:SELECT * FROM "user" WHERE "firstName" != '张'
const loadedPosts = await userRepository.find({
firstName: Not("张") });
// LessThan:SELECT * FROM "user" WHERE "age" < 10
const loadedPosts = await userRepository.find({
age: LessThan(10) });
// LessThanOrEqual:将执行
SELECT * FROM "user" WHERE "age" <= 10
const loadedPosts = await userRepository.find({
age: LessThanOrEqual(10) });
// MoreThan:SELECT * FROM "user" WHERE "age" > 10
const loadedPosts = await userRepository.find({
age: MoreThan(10) });
// MoreThanOrEqual: SELECT * FROM "user" WHERE "age" > = 10
const loadedPosts = await userRepository.find({
age: MoreThanOrEqual(10) });
// Equal: SELECT * FROM "user" WHERE "age" = 10
const loadedPosts = await userRepository.find({
age: Equal(10) });
// Like:SELECT * FROM "user" WHERE "firstName" LIKE '%
张%'
const loadedPosts = await userRepository.find({
firstName: Like("%张%") });
// Between:
SELECT * FROM "user" WHERE "age" BETWEEN 1 AND 10
const loadedPosts = await userRepository.find({
age: Between(1, 10) });
// In: SELECT * FROM "user" WHERE "age" IN (10,20,30)
const loadedPosts = await userRepository.find({
age: In([10,20,30])
});

entity

Entity : is a class mapped to a database table. You can create an entity by defining a new class and mark it with @Entity()

Entity column : Since the database table is composed of columns, the entity must also be composed of columns. Each entity class attribute marked @Column will be mapped to a database table column

Main column : Every entity must have at least one main column. There are several types of main columns:

@PrimaryColumn() creates a primary column, it can get any value of any type. You can also specify the column type. If the column type is not specified, it will be automatically inferred from the attribute type.

@PrimaryGeneratedColumn() creates a primary column whose value will be automatically generated using the auto-increment value.

@PrimaryGeneratedColumn("uuid") creates a primary column, the value will be automatically generated using uuid, (Uuid is a unique string id)

Special column

@CreateDateColumn automatically inserts the date for the entity. There is no need to set this column, the value will be set automatically.

@UpdateDateColumn automatically updates the entity date every time the entity manager or save of the repository is called. There is no need to set this column, the value will be set automatically.

Column type

@Column(“int”) or @Column({ type: “int” })

If you want to specify other types of parameters, you can use the column option to execute: Example: @Column("varchar", {length: 200 })

mysql column type

int, tinyint, smallint, mediumint, bigint, float, double, dec, decimal, numeric, date, datetime, timestamp, time, year, char, varchar, nvarchar, text, tinytext, mediumtext, blob, longtext, tinyblob, mediumblob, longblob, enum, json, binary, geometry, point, linestring,polygon, multipoint, multilinestring, multipolygon, geometrycollection

enum column type

export enum UserRole {
  ADMIN = "admin",
  EDITOR = "editor",
  GHOST = "ghost" 
}
@Entity()
export class User { 
  @Column({
    type: "enum",
    enum: UserRole,
    default: UserRole.GHOST
  })
  role: UserRole
}

The column type of simple-array: It can store the original array value in a single string column. All values ​​are separated by commas. When returning, it will also be returned as an array

@Entity()
export class User { 
    @Column("simple-array")
    names: string[];
}
const user = new User();
user.names = ["Alexander", "Alex", "Sasha", "Shurik"];

simple-json column type : can store any value that can be stored in the database through JSON.stringify. This type is useful when there is no json type in your database and you want to store and load objects

@Entity()
export class User { 
    @Column("simple-json")
    profile: { name: string; nickname: string };
}
const user = new User();
user.profile = { name: "John", nickname: "Malkovich" };

Column options

The column options define other options for the entity column. You can specify column options on @Column:

List of options available in ColumnOptions:

type: ColumnType-column type.

name: string-the name of the column in the database table. By default, the column name is generated from the name of the attribute. You can also change it by specifying your own name.

length: number-the length of the column type. For example, if you want to create a varchar (150) type, specify the column type and length options.

nullable: boolean-Make the column NULL or NOT NULL in the database. By default, the column is nullable: false.

update: boolean-Indicates whether the "save" operation updates the column value. If false, the value can only be written when the object is inserted for the first time. The default value is "true".

select: boolean-Defines whether to hide this column by default when making a query. When set to false, the column data will not display standard queries. By default, the column is select: true

default: string-Add the DEFAULT value of the database-level column.

primary: boolean-mark the column as the primary column. The usage is the same as @ PrimaryColumn.

unique: boolean-mark the column as unique (create a unique constraint).

comment: string-database column comment, not all database types are supported.

enum: string[]|AnyEnum-used in the enum column type to specify a list of allowed enumeration values. You can also specify an array or specify an enumeration class.

@Column({
  type: "varchar",
  length: 150,
  unique: true,
  // ...
})
name: string;

Embedded entity class

By using embedded columns, you can reduce duplication in the application

An embedded column is a column that accepts classes with its own columns and merges these columns into the database table of the current entity.

// Name.ts
import {Entity, Column} from "typeorm";
export class Name { 

    @Column()
    first: string; 
    
    @Column()
    last: string; 
}
//User.ts
import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";
import {Name} from "./Name";

@Entity()
export class User { 
    @PrimaryGeneratedColumn()
    id: string; 
    @Column(type => Name)
    name: Name; 
    @Column()
    isActive: boolean; 
}

relationship

What is relationship

Relationships can help you easily collaborate with related entities. There are several types of relationships

One-to-one use @OneToOne

Many-to-one use @ManyToOne

One-to-many use @OneToMany

Many to many use @ManyToMany

Relationship options

eager: boolean-If set to true, when using find * or QueryBuilder on this entity, the main entity will always be used to load the relationship

cascade: boolean-If set to true, related objects will be inserted and updated in the database.

onDelete: "RESTRICT"|"CASCADE"|"SET NULL"-Specify the behavior of foreign keys when deleting referenced objects

primary: boolean-indicates whether the column of this relationship is the primary column.

nullable: boolean-indicates whether the column of this relationship is nullable. It is nullable by default.

orphanedRowAction: "nullify" | "delete"-After deleting a child row from its parent row, determine whether the child row is orphaned (default value) or deleted.

One-to-one relationship

One-to-one is a relationship in which A contains only one instance of B and B contains only one instance of A. Let's take the User and Profile entities as examples.

A user can only have one profile, and a profile can only be owned by one user.

@Entity()
export class Profile { 
    @PrimaryGeneratedColumn()
    id: number; 
    
    @Column()
    gender: string; 
    
    @Column()
    photo: string; 
}
@Entity()
export class User { 
    @PrimaryGeneratedColumn()
    id: number; 
    
    @Column()
    name: string; 
    
    @OneToOne(() => Profile)
    @JoinColumn()
    profile: Profile; 
}

Here we add @OneToOne to the profile and specify the target relationship type as Profile. We also added @JoinColumn, which is mandatory and can only be set on one side of the relationship. Which side of the @JoinColumn you set, the table of which side will contain a "relation id" and a foreign key to the target entity table.

Similarly, @JoinColumn must be set only on one side of the relationship and must be on the side with the foreign key in the database table.

const profile = new Profile();
profile.gender = "male";
profile.photo = "me.jpg";

await profileRepository.save(profile);

const user = new User();
user.name = "Joe Smith";
user.profile = profile;
await userRepository.save(user)

const users = await userRepository.findOne({
    relations: ["profile"],
    where:{id:1} 
});

Many-to-one/one-to-many relationship

Many-to-one/one-to-many refers to the relationship where A contains multiple instances of B, but B contains only one instance of A. Let us take the User and Photo entities as examples. User can have multiple photos, but each photo is only owned by one user

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

    @Column()
    url: string; 

    @ManyToOne(() => User, user => user.photos)
    user: User; 
}
@Entity()
export default class User { 
    @PrimaryGeneratedColumn()
    id: number; 

    @Column()
    name: string; 

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

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

Here we add @OneToMany to the photos attribute and specify the target relationship type as Photo. You can omit @JoinColumn in the @ManyToOne / @OneToMany relationship, unless you need to customize the name of the associated column in the database. @ManyToOne can be used alone, but @OneToMany must be used with @ManyToOne. If you want to use @OneToMany, you need @ManyToOne. Where you set @ManyToOne, related entities will have "association id" and foreign keys.

Need to query users with photos, the relationship must be specified in FindOptions

const photo1 = new Photo();
photo1.url = "me.jpg";
await photoRepository.save(photo1);

const photo2 = new Photo();
photo2.url = "me-and-bears.jpg";
await photoRepository.save(photo2);

const user = new User();
user.name = "John";
user.photos = [photo1, photo2];
await userRepository.save(user);

const users = await userRepository.find({ relations: ["photos"] });
const photos = await photoRepository.find({ relations: ["user"] });

Many-to-many relationship

Many-to-many is a relationship in which A contains multiple instances of B and B contains multiple instances of A. Let's take Question and Category entities as examples. Question can have multiple categories, and each category can have multiple questions

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

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

    @Column()
    name: string; 
}
import { Entity, PrimaryGeneratedColumn, Column, ManyToMany,
JoinTable } from "typeorm";
import Category from "./Category";

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

    @Column()
    title: string; 

    @Column()
    text: string; 

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

@JoinTable() is required for @ManyToMany relationship

To load questions in categories, you must specify the relationship in FindOptions

const category1 = new Category();
category1.name = "animals";
await categoryRepository.save(category1);

const category2 = new Category();
category2.name = "zoo";
await categoryRepository.save(category2);

const question = new Question();
question.text = '文本'
question.title = '标题'
question.categories = [category1, category2];
await questionRepository.save(question);
const questions = await questionRepository.find({ relations: ["categories"] });

Use Query Builder

QueryBuilder is one of the most powerful features of TypeORM, which allows you to use elegant and convenient syntax to construct SQL queries, execute and obtain automatically converted entities.

// SELECT * FROM users user WHERE user.id = 1
const firstUser = await userRepository
    .createQueryBuilder("user") 
    .where("user.id = :id", { id: 1 }) 
    .getOne();

For more view, use Query Builder to query | TypeORM Chinese document (biunav.com)