TypeORM 스터디 : QueryBuilder 2편 - CRUD 심화

 

 

TypeORM

 

1편 보기

TypeORM 스터디 : QueryBuilder 1편 - CRUD 기본

3편 보기

TypeORM 스터디 : QueryBuilder 3편 - 캐싱

# 값을 가져오기

// null or 객체
const timber = await getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
    .getOne();
    
// 몾찾으면 에러
const timber = await getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
    .getOneOrFail(); // 요기
    
// 많이 가져오기
const users = await getRepository(User)
    .createQueryBuilder("user")
    .getMany(); // point

// Entity 아닌 객체
const { sum } = await getRepository(User)
    .createQueryBuilder("user")
    .select("SUM(user.photosCount)", "sum")
    .where("user.id = :id", { id: 1 })
    .getRawOne(); // {id: 1, sum: 25 }
    
// const photosSums = await getRepository(User)
    .createQueryBuilder("user")
    .select("user.id")
    .addSelect("SUM(user.photosCount)", "sum")
    .groupBy("user.id")
    .getRawMany();

// result will be like this: [{ id: 1, sum: 25 }, { id: 2, sum: 13 }, ...]

 

alias(별칭) 사용

user 별칭을 사용하여 users 테이블을 사용했습니다.

createQueryBuilder()
    .select("user") // user의 모든 것을 
    .from(User, "user") // user alias
/**
SELECT ... FROM users user
**/

createQueryBuilder()
    .select("user")
    .from(User, "user")
    .where("user.name = :name", { name: "Timber" })
    
/**
SELECT ... FROM users user WHERE user.name = 'Timber'
**/

각각의 테이블에 각각의 alias 사용 가능

 

# data escape를 위해 파라미터 사용하기

.where("user.name = :name", { name: "Timber" })
// 는 아래와 동일
.where("user.name = :name")
.setParameter("name", "Timber")
.setParameter("name", "Timber2") // 같은거 두번 쓰면 오버라이딩됨. 주의!

// WHERE user.name = 'Timber'

// array값 전달
.where("user.name IN (:...names)", { names: [ "Timber", "Cristal", "Lina" ] })


/**
WHERE user.name IN ('Timber', 'Cristal', 'Lina')
**/

 

# Where 사용하기

// Where 기본
createQueryBuilder("user")
    .where("user.name = :name", { name: "Timber" })
/** SELECT ... FROM users user WHERE user.firstName = 'Timber' **/    
    
// AND 사용
createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .andWhere("user.lastName = :lastName", { lastName: "Saw" }); // 여기
    
/** SELECT ... FROM users user WHERE user.firstName = 'Timber' AND user.lastName = 'Saw' **/

// OR

createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .orWhere("user.lastName = :lastName", { lastName: "Saw" }); // 여기


/** SELECT ... FROM users user WHERE user.firstName = 'Timber' OR user.lastName = 'Saw' **/

// IN
createQueryBuilder("user")
    .where("user.id IN (:...ids)", { ids: [1, 2, 3, 4] })
    
    
/** SELECT ... FROM users user WHERE user.id IN (1, 2, 3, 4) **/

// You can add a complex WHERE expression into an existing WHERE using Brackets
// ()이용한 조건 사용하기

createQueryBuilder("user")
    .where("user.registered = :registered", { registered: true })
    .andWhere(new Brackets(qb => {
        qb.where("user.firstName = :firstName", { firstName: "Timber" })
          .orWhere("user.lastName = :lastName", { lastName: "Saw" })
    }))
    
/**
SELECT ... FROM users user 
WHERE user.registered = true 
AND (user.firstName = 'Timber' OR user.lastName = 'Saw')
**/

.where 두번 쓰면 뒤에 무시함

 

# Having 

createQueryBuilder("user")
    .having("user.name = :name", { name: "Timber" })
/**
SELECT ... FROM users user HAVING user.name = 'Timber'
**/

// AND
createQueryBuilder("user")
    .having("user.firstName = :firstName", { firstName: "Timber" })
    .andHaving("user.lastName = :lastName", { lastName: "Saw" });

/**
SELECT ... FROM users user HAVING user.firstName = 'Timber' AND user.lastName = 'Saw'
**/

// OR
createQueryBuilder("user")
    .having("user.firstName = :firstName", { firstName: "Timber" })
    .orHaving("user.lastName = :lastName", { lastName: "Saw" });
    
    
/**
SELECT ... FROM users user HAVING user.firstName = 'Timber' OR user.lastName = 'Saw'
**/

.having 2번 쓰면 앞에 조건 무시함

 

# Order By

createQueryBuilder("user")
    .orderBy("user.id")
    
/** 
SELECT ... FROM users user ORDER BY user.id
**/

// 내림차순
createQueryBuilder("user")
    .orderBy("user.id", "DESC")
// 오름차순
createQueryBuilder("user")
    .orderBy("user.id", "ASC")
    
// 다중 기준
createQueryBuilder("user")
    .orderBy("user.name")
    .addOrderBy("user.id");
    
// 다중 기준 객체 형태로 사용
createQueryBuilder("user")
    .orderBy({
        "user.name": "ASC",
        "user.id": "DESC"
    });

.orderBy는 다음 것이 앞 것을 오버라이딩함

 

# Limit

createQueryBuilder("user")
    .limit(10)
    
/**
SELECT ... FROM users user OFFSET 10
**/

- 페이지네이션 사용 시 skip을 쓰는게 좋음

 

# 조인

- left outer join

// User와 Photo는 1대 다 관계
// User는 photos 속성으로 Photo 배열을 갖고 있음


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

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

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



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

@Entity()
export class Photo {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    url: string;

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

// left Outer Join
// 유저 목록을 전부 유지함.
// leftJoinAndSelect을 쓰면 Timber의 모든 사진을 자동으로 로드함.
const user = await createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo") // user의 photos를 갖고 있는 테이블을 photo로 alias
    .where("user.name = :name", { name: "Timber" })
    .getOne();
    
    
/**
{
    id: 1,
    name: "Timber",
    photos: [{
        id: 1,
        url: "me-with-chakram.jpg"
    }, {
        id: 2,
        url: "me-with-trees.jpg"
    }]
}

**/

// 조인 테이블에 조건 걸기
const user = await createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo") // user의 photos를 갖고 있는 테이블을 photo로 alias
    .where("user.name = :name", { name: "Timber" })
    .andWhere("photo.isRemoved = :isRemoved", { isRemoved: false }) // photo 테이블에 조건
    .getOne();
    
 /**
 SELECT user.*, photo.* FROM users user
    LEFT JOIN photos photo ON photo.user = user.id
    WHERE user.name = 'Timber' AND photo.isRemoved = FALSE
 **/
 
 
 // 조인 시에 where 조건 걸 수도 있음
 const user = await createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo", "photo.isRemoved = :isRemoved", { isRemoved: false })
    .where("user.name = :name", { name: "Timber" })
    .getOne();
    
    
/**
SELECT user.*, photo.* FROM users user
    LEFT JOIN photos photo ON photo.user = user.id AND photo.isRemoved = FALSE
    WHERE user.name = 'Timber'
    
**/

- inner join

const user = await createQueryBuilder("user")
    .innerJoinAndSelect("user.photos", "photo", "photo.isRemoved = :isRemoved", { isRemoved: false })
    .where("user.name = :name", { name: "Timber" })
    .getOne();
    
    
/**
SELECT user.*, photo.* FROM users user
    INNER JOIN photos photo ON photo.user = user.id AND photo.isRemoved = FALSE
    WHERE user.name = 'Timber'
**/

- join된 테이블 데이터 말고 원래 테이블 엔터티만 가져오기

AndSelect 빼면 됩니다.

const user = await createQueryBuilder("user")
    .innerJoin("user.photos", "photo")
    .where("user.name = :name", { name: "Timber" })
    .getOne();
    
/**
SELECT user.* FROM users user
    INNER JOIN photos photo ON photo.user = user.id
    WHERE user.name = 'Timber'
**/

- 엔터티 내에 관계를 정의하지 않는 엔터티 끼리도 조인할 수 있음.

const user = await createQueryBuilder("user")
    .leftJoinAndSelect(Photo, "photo", "photo.userId = user.id")
    .getMany();
    
    
// or
const user = await createQueryBuilder("user")
    .leftJoinAndSelect("photos", "photo", "photo.userId = user.id")
    .getMany();

- 조인하고 해당 객체 배열에서 하나 골라 매핑하기

- user의 photos내의 photo 하나 가져와 새로운 필드 만들어 매핑

export class User {
    /// ...
    profilePhoto: Photo;

}


const user = await createQueryBuilder("user")
	// user.photos를 photo로 alias하고 필터한 뒤 profilePhoto에 매핑.
    .leftJoinAndMapOne("user.profilePhoto", "user.photos", "photo", "photo.isForProfile = TRUE")
    .where("user.name = :name", { name: "Timber" })
    .getOne();
    // leftJoinAndMapMany를 쓰면 배열로 저장함.

 

# 만들어진 쿼리 얻기

// 쿼리 얻기
const sql = createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .orWhere("user.lastName = :lastName", { lastName: "Saw" })
    .getSql(); 
    
// 디버깅만 하기
const users = await createQueryBuilder("user")
    .where("user.firstName = :firstName", { firstName: "Timber" })
    .orWhere("user.lastName = :lastName", { lastName: "Saw" })
    .printSql() // here
    .getMany();

 

# 엔터티가 아닌 데이터*(raw data) 얻기

const { sum } = await getRepository(User)
    .createQueryBuilder("user")
    .select("SUM(user.photosCount)", "sum")
    .where("user.id = :id", { id: 1 })
    .getRawOne(); // result will be like this: { id: 1, sum: 25 }
    
const photosSums = await getRepository(User)
    .createQueryBuilder("user")
    .select("user.id")
    .addSelect("SUM(user.photosCount)", "sum")
    .groupBy("user.id")
    .getRawMany();

// result will be like this: [{ id: 1, sum: 25 }, { id: 2, sum: 13 }, ...]

 

# 스트림 얻기

데이터 타입이 엔터티가 아니므로 알아서 처리해야 함

const stream = await getRepository(User)
    .createQueryBuilder("user")
    .where("user.id = :id", { id: 1 })
    .stream(); // 데이터 타입이 엔터티가 아니므로 알아서 처리해야 함

 

# 페이지네이션

- 페이지네이션, 페이지 슬라이더, 인피니트 스크롤에 사용

// 첫번째 10개
const users = await getRepository(User)
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .take(10)
    .getMany();
    
    
// 맨 처음10개 스킵하고 가져오기
const users = await getRepository(User)
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .skip(10)
    .getMany();
    
    
// 6번부터 10개 가져오기 (6 ~ 15)
const users = await getRepository(User)
    .createQueryBuilder("user")
    .leftJoinAndSelect("user.photos", "photo")
    .skip(5)
    .take(10)
    .getMany();

take와 skip은 limit과 offset을 사용하는 것처럼 보일 수 있지만 실제로는 그렇지 않습니다.

limit과 offset은 조인 또는 하위 쿼리가 포함된 더 복잡한 쿼리가 있는 경우 예상대로 작동하지 않을 수 있습니다.

take와 skip을 사용하면 이러한 문제를 방지할 수 있습니다.

 

# 낙관적 잠금과 비관적 잠금

비관적 잠금(선점 잠금, Pessimistic Lock)과 낙관적 잠금(비선점 잠금, Optimistic Lock) (tistory.com)

비관적 잠금(선점 잠금, Pessimistic Lock)과 낙관적 잠금(비선점 잠금, Optimistic Lock)

비관적 잠금은 Lock 획득 전까지 조회레벨/ 혹은 쓰기레벨에서 블로킹 하는 것

낙관적 잠금은 @Version 및 @UpdatedDate 데코레이터와 함께 작동합니다.

낙관적 잠금은 트랜잭션 커밋 시 version을 확인하여 최신버전이 아닐 경우 fail

// 비관적 읽기 잠금
// 조회 및 잠금을 블록함
const users = await getRepository(User)
    .createQueryBuilder("user")
    .setLock("pessimistic_read")
    .getMany();
    
// 비관적 쓰기 잠금
// 쓰기 및 잠금을 블록함
const users = await getRepository(User)
    .createQueryBuilder("user")
    .setLock("pessimistic_write")
    .getMany();

// 더티 리드
const users = await getRepository(User)
    .createQueryBuilder("user")
    .setLock("dirty_read")
    .getMany();
    
// 낙관적 잠금
const users = await getRepository(User)
    .createQueryBuilder("user")
    .setLock("optimistic", existUser.version)
    .getMany();

트랜잭션의 격리 수준(isolation Level)이란? (nesoy.github.io)

트랜잭션의 격리 수준(isolation Level)이란?

• READ UNCOMMITTED
  • 메모리에서 커밋 전 데이터를 읽을 수 있음
  • 메모리에서 또 바뀔 수 있어 Unrepeatable Read 가능
    • dirty_read
• READ COMMITTED(기본)
  
  • Undo 영역에 백업된 레코드에서 값을 가져온다.
  • Undo는 모든 트랜잭션에 하나
    • 커밋되면 없어짐
  • 삭제 전 Undo와 실제 데이터가 다를 수 있어 Unrepeatable Read 가능
• REPEATABLE READ
  • MySQL에서는 트랜잭션마다 트랜잭션 ID를 부여하여 트랜잭션 ID보다 작은 트랜잭션 번호에서 변경한 것만 읽게 된다.
  • Undo 공간에 백업해두고 실제 레코드 값을 변경함
    • 최신 트랜잭션에서 변경되어도, undo 영역에서 가져온다.
  • 문제점 : 팬텀 리드
    • count시 레코드의 총 갯수가 달라질 수 있음 (다른쪽에서 삽입)
• SERIALIZABLE
  • 가장 단순하면서 엄격함. 읽기 + 쓰기 잠금
  • 성능 측면에서 동시 처리성능에 가장 낮아 잘 안씀

 

# 최대 실행 시간

슬로우 쿼리 버리기

const users = await getRepository(User)
    .createQueryBuilder("user")
    .maxExecutionTime(1000) // milliseconds.
    .getMany();

# 부분 선택

const users = await getRepository(User)
    .createQueryBuilder("user")
    .select([ // id와 name만 가져오기
        "user.id",
        "user.name"
    ])
    .getMany();

# 서브쿼리

좀 중요한 내용인거 같은데

// 단순한 example
const qb = await getRepository(Post).createQueryBuilder("post");
const posts = qb
    .where("post.title IN " + qb.subQuery().select("user.name")
    .from(User, "user")
    .where("user.registered = :registered").getQuery())
    .setParameter("registered", true)
    .getMany();
    
// 좀 더 멋진 예시
const posts = await connection.getRepository(Post)
    .createQueryBuilder("post")
    .where(qb => {
        const subQuery = qb.subQuery()
            .select("user.name")
            .from(User, "user")
            .where("user.registered = :registered")
            .getQuery(); // 쿼리를 만들어 붙임.
        return "post.title IN " + subQuery;
    })
    .setParameter("registered", true)
    .getMany();
    
// 위의 문법을 나누어 적용
const userQb = await connection.getRepository(User)
    .createQueryBuilder("user")
    .select("user.name")
    .where("user.registered = :registered", { registered: true });

const posts = await connection.getRepository(Post)
    .createQueryBuilder("post")
    .where("post.title IN (" + userQb.getQuery() + ")")
    .setParameters(userQb.getParameters())
    .getMany();
    
// 위의 문법인데 from 쓰고싶으면
const userQb = await connection.getRepository(User)
    .createQueryBuilder("user")
    .select("user.name", "name")
    .where("user.registered = :registered", { registered: true });

const posts = await connection
    .createQueryBuilder()
    .select("user.name", "name")
    .from("(" + userQb.getQuery() + ")", "user") // alias
    .setParameters(userQb.getParameters())
    .getRawMany();
    
// 위와 동일함
const posts = await connection
    .createQueryBuilder()
    .select("user.name", "name")
    .from(subQuery => {
        return subQuery
            .select("user.name", "name")
            .from(User, "user")
            .where("user.registered = :registered", { registered: true });
    }, "user")
    .getRawMany();
    

// addSelect 안에서도 서브쿼리 된다    
const posts = await connection
    .createQueryBuilder()
    .select("post.id", "id")
    .addSelect(subQuery => {
        return subQuery
            .select("user.name", "name")
            .from(User, "user")
            .limit(1);
    }, "name")
    .from(Post, "post")
    .getRawMany();

addFrom을 사용하면 subSelect를 추가할 수 있음

addSelect로 필드 여러개 추가 가능

 

# 히든 컬럼

select : false인 컬럼이 있으면 해당 컬럼은 엔터티로 가져올 시 객체에서 뺌

addSelect로 select구문에 subQuery 추가해줄 수 있음

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

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @Column({select: false})
    password: string;
}

// addSelect 해야만 password 나옴
const users = await connection.getRepository(User)
    .createQueryBuilder()
    .select("user.id", "id")
    .addSelect("user.password")
    .getMany();

 

# 삭제된 열 가져오기

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

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @DeleteDateColumn()
    deletedAt?: Date;
}


// 쿼리하면 DeleteDateColumn이 있는 필드는 알아서 안가져옴
const users = await connection.getRepository(User)
    .createQueryBuilder()
    .select("user.id", "id")
    .withDeleted() // 넣어주면 가져옴
    .getMany();

 

# 삽입

import {getConnection} from "typeorm";

await getConnection()
    .createQueryBuilder()
    .insert()
    .into(User)
    .values([
        { firstName: "Timber", lastName: "Saw" }, 
        { firstName: "Phantom", lastName: "Lancer" }
     ])
    .execute();
    
    
// Raw SQL
import {getConnection} from "typeorm";

await getConnection()
    .createQueryBuilder()
    .insert()
    .into(User)
    .values({ 
        firstName: "Timber", 
        lastName: () => "CONCAT('S', 'A', 'W')" // SQL 문법은 알아서 잘 escape해야함
    })
    .execute();

 

# 수정

import {getConnection} from "typeorm";

await getConnection()
    .createQueryBuilder()
    .update(User)
    .set({ firstName: "Timber", lastName: "Saw" })
    .where("id = :id", { id: 1 })
    .execute();
    
    
// Raw Sql
import {getConnection} from "typeorm";

await getConnection()
    .createQueryBuilder()
    .update(User)
    .set({ 
        firstName: "Timber", 
        lastName: "Saw",
        age: () => "age + 1"
    })
    .where("id = :id", { id: 1 })
    .execute();

 

# 삭제

"soft-delete"는 실제로 레코드를 삭제하는 대신 레코드가 삭제되었음을 나타내는 플래그를 기존 테이블에 설정하는 것을 의미합니다.

import {getConnection} from "typeorm";

await getConnection()
    .createQueryBuilder()
    .delete()
    .from(User)
    .where("id = :id", { id: 1 })
    .execute();
    
// Soft Delete
import {createConnection} from "typeorm";
import {Entity} from "./entity";

createConnection(/*...*/).then(async connection => {

    await connection
      .getRepository(Entity)
      .createQueryBuilder()
      .softDelete()

}).catch(error => console.log(error));


// Restore-Soft-Delete

import {createConnection} from "typeorm";
import {Entity} from "./entity";

createConnection(/*...*/).then(async connection => {

    await connection
      .getRepository(Entity)
      .createQueryBuilder()
      .restore() // 삭제된 행을 복구한다.

}).catch(error => console.log(error));

 

Cache에 대한 내용으로 3편까지 적어야겠다.

 

원문 : 

TypeORM - Amazing ORM for TypeScript and JavaScript (ES7, ES6, ES5). Supports MySQL, PostgreSQL, MariaDB, SQLite, MS SQL Server, Oracle, WebSQL databases. Works in NodeJS, Browser, Ionic, Cordova and Electron platforms.

TypeORM - Amazing ORM for TypeScript and JavaScript (ES7, ES6, ES5). Supports MySQL, PostgreSQL, MariaDB, SQLite, MS SQL Server,