10. Nest.js_Database 읽기

DB sql 테스트 데이터 삽입

데이터 초기화

테이블을 일단 truncate해줍시다.

더미 데이터 삽입

SET NAMES utf8mb4;

INSERT INTO `event` (`id`, `description`, `when`, `address`, `name`) VALUES
(1,    'Let\'s meet together.',    '2021-02-15 21:00:00',    'Office St 120',    'Team Meetup'),
(2,    'Let\'s learn something.',    '2021-02-17 21:00:00',    'Workshop St 80',    'Workshop'),
(3,    'Let\'s meet with big bosses',    '2021-02-17 21:00:00',    'Boss St 100',    'Strategy Meeting'),
(4,    'Let\'s try to sell stuff',    '2021-02-11 21:00:00',    'Money St 34',    'Sales Pitch'),
(5,    'People meet to talk about business ideas',    '2021-02-12 21:00:00',    'Invention St 123',    'Founders Meeting');

DB 데이터 읽기 구현

select * from event where event.id = 3구현

// src/events.controllers.ts
import {
  Body,
  Controller,
  Delete,
  Get,
  Param,
  Patch,
  Post,
  HttpCode,
} from '@nestjs/common';
import { CreateEventDto } from './create-event.dto';
import { UpdateEventDto } from './update-event.dto';
import { Event } from './event.entity';
import { Repository } from 'typeorm';
import { InjectRepository } from '@nestjs/typeorm';

@Controller('/events')
export class EventsController {
  // 생성자 주입 시, Entity 클래스 인자로 받는 InjectRepository 데코레이터 사용
  constructor(
    @InjectRepository(Event)
    private readonly repository: Repository<Event>
  ){}

  @Get('/practice')
  async practice() {
    // in SQL, select * from event where event.id = 3
    return await this.repository.find({
      where: { id: 3 };
    })
  }
...

events/practice로 GET 요청을 보내주면 잘 반환하는 것을 알 수 있습니다.

MoreThan) select * from event where event.id > 3

MoreThan 옵션을 사용하면 where event.id > 3 조건 옵션과 동일하게 사용할 수 있습니다.

...
import { MoreThan, Repository } from 'typeorm';
...
  @Get('/practice')
  async practice() {
    // in SQL, select * from event where event.id = 3
    return await this.repository.find({
      where: { id: MoreThan(3) },
    })
  }
...

AND 조건문

Select * from event where event.id > 3 AND event.when > '2021-02-12' sql문을 구현해보겠습니다.

...
  @Get('/practice')
  async practice() {
    // in SQL, select * from event where event.id = 3
    return await this.repository.find({
      where: {
        id: MoreThan(3),
        when: MoreThan(new Date('2021-02-12')),
      },
    })
  }
...

OR 조건문

like문

...
import { Like, MoreThan, Repository } from 'typeorm';
...

  @Get('/practice')
  async practice() {
    // in SQL, select * from event where event.id = 3
    return await this.repository.find({
      where: [
      // OR 조건
        {
          id: MoreThan(3),
          when: MoreThan(new Date('2021-02-12')),
        },
        {
          description: Like('%meet%'), // Like 문
        },
      ]
    });
  }

take문

sql의 limit과 같은 역할을 합니다

...
  @Get('/practice')
  async practice() {
    // in SQL, select * from event where event.id = 3
    return await this.repository.find({
      where: [
      // OR 조건
        {
          id: MoreThan(3),
          when: MoreThan(new Date('2021-02-12')),
        },
        {
          description: Like('%meet%'), // Like "meet"
        },
      ],
      take: 1 // Limit 
    });
  }
...

order문

...
  @Get('/practice')
  async practice() {
    // in SQL, select * from event where event.id = 3
    return await this.repository.find({
      where: [
      // OR 조건
        {
          id: MoreThan(3),
          when: MoreThan(new Date('2021-02-12')),
        },
        {
          description: Like('%meet%'), // Like "meet"
        },
      ],
      take: 1, // Limit 
      order: {
        id: 'DESC' //id 내림 차순 기준으로 정렬
      }
    });
  }
...

select 특정

특정 어트리뷰트만 가져오기

...
  @Get('/practice')
  async practice() {
    // in SQL, select * from event where event.id = 3
    return await this.repository.find({
      select: ['id', 'when'],
      where: [
      // OR 조건
        {
          id: MoreThan(3),
          when: MoreThan(new Date('2021-02-12')),
        },
        {
          description: Like('%meet%'), // Like "meet"
        },
      ],
      take: 1, // Limit 
      order: {
        id: 'DESC' //id 내림 차순 기준으로 정렬
      }
    });
  }
...

result

[
    {
        "id": 5,
        "when": "2021-02-12T12:00:00.000Z"
    }
]