Sequelize 공식 Document - (2) Querying

cadenzah·2019년 1월 5일
JavaScriptORMSequelizedbmysqlsql시퀄라이즈자바스크립트
23

Sequelize API Document

목록 보기
2/4
post-thumbnail

쿼리

알립니다

이 번역글은 2019년 1월 경에 작성된 글입니다. 원본인 Sequelize 매뉴얼은 현재 새로운 버전으로 새롭게 작성되었습니다. 따라서 이 글은 Deprecated된 글임을 알려드립니다.

  • 이 글은 5. Querying를 번역한 글입니다.
  • 아직 입문자이다보니 오역이 있을 수 있습니다. 양해 부탁드립니다.
  • 매끄러운 번역을 위하여 의역을 한 경우가 있습니다. 원문의 뜻을 최대한 해치지 않도록 노력했으니 안심하셔도 됩니다.
  • 영어 단어가 자연스러운 경우 원문 그대로의 영단어를 적었습니다.
  • 저의 보충 설명은 인용문에 적었습니다.

시작하기 전에

  • 이 글은 공식 도큐먼트의 5번째 챕터입니다.
  • 원문에서는 예시 코드를 제공하지만, 코드만 제공할 뿐 CRUD를 위한 데이터셋 세팅, 실습 환경에 대한 정보가 없고, 실행 결과를 확인할 수 있는 방법도 알려주지 않습니다. 그래서 간단하게나마 실습해볼 수 있도록 세팅하는 방법을 이전 글에서 소개하였으니 필요에 따라 활용해주시면 감사드리겠습니다.

특성 (Attributes)

SQL의 테이블에 대한 인스턴스를 모델이라고 할 때, 원본 테이블에 존재하는 컬럼 은 모델 인스턴스의 특성 에 대응됩니다.

일부 특성만을 select하려면 attributes 옵션을 사용하면 됩니다. 대부분의 경우 배열을 전달합니다:

Model.findAll({
  attributes: ['foo', 'bar']
});
SELECT foo, bar ...

특성 이름은 중첩 배열을 사용하면 새로 지어줄 수 있습니다:

Model.findAll({
  // 'bar' 특성을 찾되, 반환된 결과에서는 'baz'로 표시됩니다
  attributes: ['foo', ['bar', 'baz']]
});
SELECT foo, bar AS baz ...

집계(Aggregation)를 하려면 sequelize.fn을 사용하면 됩니다:

Model.findAll({
  attributes: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});
SELECT COUNT(hats) AS no_hats ...

집계 함수를 사용할 때, 반드시 해당 함수 결과에 대하여 별칭을 부여해야 모델에서 해당 결과에 접근할 수 있습니다. 바로 위의 예시에서는 모자의 개수를 instance.get('no_hats') 를 통하여 얻을 수 있습니다.

쿼리 결과에 단지 집계값만을 더하기 위하여 모든 특성을 나열하는 것은 귀찮은 일일 수 있습니다:

// This is a tiresome way of getting the number of hats...
Model.findAll({
  attributes: ['id', 'foo', 'bar', 'baz', 'quz', [sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']]
});

// This is shorter, and less error prone because it still works if you add / remove attributes
Model.findAll({
  attributes: { include: [[sequelize.fn('COUNT', sequelize.col('hats')), 'no_hats']] }
});
SELECT id, foo, bar, baz, quz, COUNT(hats) AS no_hats ...

비슷한 방식으로 일부 특성을 제외하는 것도 가능합니다:

Model.findAll({
  attributes: { exclude: ['baz'] }
});
SELECT id, foo, bar, quz ...

연결된 모델과 JOIN할 때에 사용되는 include와 달리, attributes 속성 안에서 사용된 include는 어떤 컬럼을 포함할지 를 제한해주는 옵션입니다. exclude와 세트로 가는 옵션입니다.

Where

어떤 쿼리문을 작성하든, where 객체를 사용하여 쿼리 내용을 필터링할 수 있습니다. 이는 find/findAll, update, destroy 등에 모두 사용 가능합니다.

where은 attribute: 값 의 쌍으로 이루어진 객체이며, 값 으로는 비교 연산의 경우 기본 자료형의 값, 그 외의 연산자의 경우 또다른 객체가 올 수 있습니다.

or와 and 연산자를 중첩하여 사용하면 복잡한 AND/OR 조건을 만들어내는 것도 가능합니다.

기본 원칙

const Op = Sequelize.Op;

Post.findAll({
  where: {
    authorId: 2
  }
});
// SELECT * FROM post WHERE authorId = 2

Post.findAll({
  where: {
    authorId: 12,
    status: 'active'
  }
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';

Post.findAll({
  where: {
    [Op.or]: [{authorId: 12}, {authorId: 13}]
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.findAll({
  where: {
    authorId: {
      [Op.or]: [12, 13]
    }
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.destroy({
  where: {
    status: 'inactive'
  }
});
// DELETE FROM post WHERE status = 'inactive';

Post.update({
  updatedAt: null,
}, {
  where: {
    deletedAt: {
      [Op.ne]: null
    }
  }
});
// UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;

Post.findAll({
  where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
});
// SELECT * FROM post WHERE char_length(status) = 6;

{ where: { 컬럼_이름: { 조건 }}} 순으로 가면 됩니다. 조건이 기본 자료형의 값이면 그냥 적어주면 되지만, 비교 등의 연산인 경우 그 조건을 객체로 전달해줘야 합니다.
맨 마지막 쿼리처럼 where에 집계 함수가 들어간 조건을 사용하면, 함수를 그대로 속성값으로 써주는 것이 아니라 sequelize.where로 한번 더 감싸줘야 합니다.

연산자

Sequelize는 자바스크립트 Symbol 연산자를 사용하여 복잡한 비교 연산을 지원합니다.

[Op.and]: {a: 5}           // AND (a = 5)
[Op.or]: [{a: 5}, {a: 6}]  // (a = 5 OR a = 6)
[Op.gt]: 6,                // > 6
[Op.gte]: 6,               // >= 6
[Op.lt]: 10,               // < 10
[Op.lte]: 10,              // <= 10
[Op.ne]: 20,               // != 20
[Op.eq]: 3,                // = 3
[Op.not]: true,            // IS NOT TRUE
[Op.between]: [6, 10],     // BETWEEN 6 AND 10
[Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
[Op.in]: [1, 2],           // IN [1, 2]
[Op.notIn]: [1, 2],        // NOT IN [1, 2]
[Op.like]: '%hat',         // LIKE '%hat'
[Op.notLike]: '%hat'       // NOT LIKE '%hat'
[Op.iLike]: '%hat'         // ILIKE '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat'      // NOT ILIKE '%hat'  (PG only)
[Op.regexp]: '^[h|a|t]'    // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
[Op.notRegexp]: '^[h|a|t]' // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
[Op.iRegexp]: '^[h|a|t]'    // ~* '^[h|a|t]' (PG only)
[Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (PG only)
[Op.like]: { [Op.any]: ['cat', 'hat']}
                       // LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
[Op.overlap]: [1, 2]       // && [1, 2] (PG array overlap operator)
[Op.contains]: [1, 2]      // @> [1, 2] (PG array contains operator)
[Op.contained]: [1, 2]     // <@ [1, 2] (PG array contained by operator)
[Op.any]: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)

[Op.col]: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example

범위 연산자

지원하는 모든 연산자로 다양한 종류의 범위를 쿼리할 수 있습니다.
제공되는 범위값으로 경계 포함 여부를 정의할 수도 있습니다.

// All the above equality and inequality operators plus the following:

[Op.contains]: 2           // @> '2'::integer (PG range contains element operator)
[Op.contains]: [1, 2]      // @> [1, 2) (PG range contains range operator)
[Op.contained]: [1, 2]     // <@ [1, 2) (PG range is contained by operator)
[Op.overlap]: [1, 2]       // && [1, 2) (PG range overlap (have points in common) operator)
[Op.adjacent]: [1, 2]      // -|- [1, 2) (PG range is adjacent to operator)
[Op.strictLeft]: [1, 2]    // << [1, 2) (PG range strictly left of operator)
[Op.strictRight]: [1, 2]   // >> [1, 2) (PG range strictly right of operator)
[Op.noExtendRight]: [1, 2] // &< [1, 2) (PG range does not extend to the right of operator)
[Op.noExtendLeft]: [1, 2]  // &> [1, 2) (PG range does not extend to the left of operator)

PostgreSQL을 제가 사용하지 않아서 제대로 해석하는지 잘 모르는 점 양해 부탁드립니다.

여러가지 조합

const Op = Sequelize.Op;

{
  createdAt: {
    [Op.lt]: new Date(),
    [Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
  }
}
// createdAt < [timestamp] AND createdAt > [timestamp]

{
  rank: {
    [Op.or]: {
      [Op.lt]: 1000,
      [Op.eq]: null
    }
  }
}
// rank < 1000 OR rank IS NULL

{
  [Op.or]: [
    {
      title: {
        [Op.like]: 'Boat%'
      }
    },
    {
      description: {
        [Op.like]: '%boat%'
      }
    }
  ]
}
// title LIKE 'Boat%' OR description LIKE '%boat%'
  1. 조건 객체에서 속성들을 나열하는 경우 AND로 간주됩니다.
  2. 특정 컬럼을 위한 OR인 경우, 단일 조건 객체로 전달합니다.
  3. 여러 컬럼을 다루는 OR안 경우, 각 컬럼에 대한 조건을 담은 객체의 배열로 작성합니다. 각 객체에는 특정 컬럼에 대한 조건이 중첩되어 작성됩니다.
    where에 대한 조건 객체는 속성이 하나인 단일 객체입니다.

연산자 별칭

Sequelize는 연산자에 대하여 별칭을 부여할 수 있게 해줍니다.

const Op = Sequelize.Op;
const operatorsAliases = {
  $gt: Op.gt
}
const connection = new Sequelize(db, user, pass, { operatorsAliases })

[Op.gt]: 6 // > 6
$gt: 6 // 위와 동일

연산자 보안

별칭을 사용하지 않으면 Sequelize의 보안 향상에 도움이 됩니다. 일부 프레임워크는 사용자 입력을 자바스크립트 객체로 자동 변환하는데, 이를 제대로 검증하지 않으면 문자로 적힌 연산자가 쓰인 객체를 Sequelize에 주입하는 일이 벌어질 수도 있습니다.

SQL Injection을 말하는 것입니다.

문자로 된 별칭을 일절 사용하지 않으면 연산자가 주입되는 것을 극단적으로 막을 수 있겠지만, 그럼ㄹ에도 불구하고 항상 사용자 입력을 적절하게 검증하고 처리해야 합니다.

하위 버전 호환을 위하여 Sequelize는 다음의 별칭들을 기본값으로 설정합니다:

$eq, $ne, $gte, $gt, $lte, $lt, $not, $in, $notIn, $is, $like, $notLike, $iLike, $notILike, $regexp, $notRegexp, $iRegexp, $notIRegexp, $between, $notBetween, $overlap, $contains, $contained, $adjacent, $strictLeft, $strictRight, $noExtendRight, $noExtendLeft, $and, $or, $any, $all, $values, $col

다음의 별칭들도 설정되어있으나, 가까운 미래에는 완전히 제거될 계획입니다:

ne, not, in, notIn, gte, gt, lte, lt, like, ilike, $ilike, nlike, $notlike, notilike, .., between, !.., notbetween, nbetween, overlap, &&, @>, <@

향상된 보안을 위하여 Sequelize.Op를 사용하고, 문자로 된 별칭은 절대로 사용하지 마세요. 여러분의 프로그램에 필요한 별칭을 operatorsAliases 옵션을 사용하여 제한할 수 있습니다. Sequelize의 메서드에 사용자 입력을 전달할 때는, 반드시 적절하게 검증하고 처리해야 한다는 것을 잊어서는 안 됩니다!

const Op = Sequelize.Op;

// 연산자 별칭 없이 Sequelize를 사용
const connection = new Sequelize(db, user, pass, { operatorsAliases: false });

// $and => Op.and 만을 별칭으로서 Sequelize를 사용
const connection2 = new Sequelize(db, user, pass, { operatorsAliases: { $and: Op.and } });

아무런 설정 없이 기본값 별칭을 모두 사용하고자 할 경우 Sequelize는 경고를 띄웁니다. 경고를 보지 않으면서 기본값 별칭을 사용하고 싶다면, 다음의 operatorAliases 옵션을 사용하세요:

const Op = Sequelize.Op;
const operatorsAliases = {
  $eq: Op.eq,
  $ne: Op.ne,
  $gte: Op.gte,
  $gt: Op.gt,
  $lte: Op.lte,
  $lt: Op.lt,
  $not: Op.not,
  $in: Op.in,
  $notIn: Op.notIn,
  $is: Op.is,
  $like: Op.like,
  $notLike: Op.notLike,
  $iLike: Op.iLike,
  $notILike: Op.notILike,
  $regexp: Op.regexp,
  $notRegexp: Op.notRegexp,
  $iRegexp: Op.iRegexp,
  $notIRegexp: Op.notIRegexp,
  $between: Op.between,
  $notBetween: Op.notBetween,
  $overlap: Op.overlap,
  $contains: Op.contains,
  $contained: Op.contained,
  $adjacent: Op.adjacent,
  $strictLeft: Op.strictLeft,
  $strictRight: Op.strictRight,
  $noExtendRight: Op.noExtendRight,
  $noExtendLeft: Op.noExtendLeft,
  $and: Op.and,
  $or: Op.or,
  $any: Op.any,
  $all: Op.all,
  $values: Op.values,
  $col: Op.col
};

const connection = new Sequelize(db, user, pass, { operatorsAliases });

JSON

JSON 타입은 PostgreSQL, SQLite, MySQL 에서만 사용할 수 있습니다.

PostgreSQL

PostgreSQL의 JSON 타입은 값을 이진값이 아니라 평문으로 저장합니다. JSON 표현을 단지 저장하거나 반환하고 싶다면, JSON을 사용하는 것이 공간과 시간을 절약할 것입니다. 하지만 JSON 값에 대하여 어떤 연산을 수행하려면, JSONB를 사용하는 편이 좋습니다.

MSSQL

MSSQL은 JSON 타입을 가지 않지만, SQL Server 2016 이후로 스트링 형태로 저장된 JSON을 지원하는 함수를 제공합니다. 이 함수를 사용하면 스트링으로 저장된 JSON을 쿼리할 수 있지만, 반환된 값은 파싱되어야 합니다.

// ISJSON - to test if a string contains valid JSON
User.findAll({
  where: sequelize.where(sequelize.fn('ISJSON', sequelize.col('userDetails')), 1)
})

// JSON_VALUE - extract a scalar value from a JSON string
User.findAll({
  attributes: [[ sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), 'address line 1']]
})

// JSON_VALUE - query a scalar value from a JSON string
User.findAll({
  where: sequelize.where(sequelize.fn('JSON_VALUE', sequelize.col('userDetails'), '$.address.Line1'), '14, Foo Street')
})

// JSON_QUERY - extract an object or array
User.findAll({
  attributes: [[ sequelize.fn('JSON_QUERY', sequelize.col('userDetails'), '$.address'), 'full address']]
})

JSONB

JSONB는 3가지 방법으로 쿼리할 수 있습니다.

중첩 객체

{
  meta: {
    video: {
      url: {
        [Op.ne]: null
      }
    }
  }
}

중첩 키

{
  "meta.audio.length": {
    [Op.gt]: 20
  }
}

포함

{
  "meta": {
    [Op.contains]: {
      site: {
        url: 'http://google.com'
      }
    }
  }
}

연결 관계

// task.state === project.state 를 만족하는 모든 Project를 찾는다
Project.findAll({
    include: [{
        model: Task,
        where: { state: Sequelize.col('project.state') }
    }]
})

Pagination / Limiting

// 10개 인스턴스/행 을 가져온다
Project.findAll({ limit: 10 })

// 8개 인스턴스/행 을 넘어간다
Project.findAll({ offset: 8 })

// 5개 인스턴스/행을 넘어가고, 거기서부터 5개 인스턴스/행을 가져온다
Project.findAll({ offset: 5, limit: 5 })

정렬

order는 항목으로 이루어진 배열을 받아서 컬럼 또는 Sequelize 메서드로 정렬합니다. 보통은 특성, 방향, 넘어가는 양 등으로 이루어진 쌍/배열을 사용합니다.

Subtask.findAll({
  order: [
    // 'title'로 이스케이프하고, DESC를 방향 관련 인자 목록에서 활성화한다
    ['title', 'DESC'],

    // max(age) 로 정렬
    sequelize.fn('max', sequelize.col('age')),

    // max(age) DESC 로 정렬
    [sequelize.fn('max', sequelize.col('age')), 'DESC'],

    // otherfunction(`col1`, 12, 'lalala') DESC 로 정렬
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],

    // (1) 연결된 모델의 createdAt 순으로 정렬하는데, 이때 연결 관계의 이름으로 모델의 이름(Task)을 사용한다.
    [Task, 'createdAt', 'DESC'],

    // (2) 중첩 연결된 모델(Project)의 createdAt 순으로 정렬하는데, 이떄 각 연결 관계의 이름으로 각 모델의 이름을 사용한다.
    [Task, Project, 'createdAt', 'DESC'],

    // (3) 연결된 모델의 createdAt 순으로 정렬하는데, 이때 연결 관계의 이름을 사용한다.
    ['Task', 'createdAt', 'DESC'],

    // (4) 중첩 연결된 모델(Project)의 createdAt 순으로 정렬하는데, 이때 각 연결 관계의 이름들을 사용한다.
    ['Task', 'Project', 'createdAt', 'DESC'],

    // (5) 연결된 모델의 createdAt 순으로 정렬하는데, 이때 연결 관계 객체를 사용한다. (추천하는 방법)
    [Subtask.associations.Task, 'createdAt', 'DESC'],

    // (6) 중첩 연결된 모델(Project)의 createdAt 순으로 정렬하는데, 이때 연결 관계 객체들을 사용한다. (추천하는 방법))
    [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],

    // (7) 연결된 모델의 createdAt 순으로 정렬하는데, 이때 연결 관계가 표현된 객체를 사용한다.
    [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],

    // (8) 중첩 연결된 모델(Project)의 createdAt 순으로 정렬하는데, 이때 연결 관계가 표현된 객체들을 사용한다.
    [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
  ]

  // 인자에 적힌 스트링을 그대로 SQL 문법으로 변환
  order: sequelize.literal('max(age) DESC')

  // 방향이 생략되면, 오름차순을 기본값으로 정렬한다
  order: sequelize.fn('max', sequelize.col('age'))

  // age 순으로 오름차순 정렬
  order: sequelize.col('age')

  // dialect에 따라 무작위 정렬 수행 (fn('RAND') 또는 fn('RANDOM') 대신 사용)
  order: sequelize.random()
})

이전 글에서 답답했던 부분에 대한 해결이 되시나요? 지금도 예시만 던져줄 뿐, 명료한 설명이 부족하여 저도 이해하는 데에 애를 먹었습니다. JOIN된 테이블에 대하여 정렬을 하는 경우에 대한 예시들입니다. 간단하게 요약하자면, order 조건을 보이는 배열에서, 가장 마지막 2개 요소가 각각 컬럼 과 방향 에 대한 값이고, 그 앞의 것들은 JOIN 연결 관계를 형성한 테이블(모델)을 가리키는 것이라고 생각하면 됩니다.

아직 이 글을 읽는 시점에서는 Association(연결 관계) 에 대한 지식을 알지 못하므로, 잘 모른다고 하여 헤매시지 않아도 됩니다. 나중 글에서 연결 관계에 대하여 알고 난 뒤 다시 읽어보면 좀 더 명확하게 이해가 될 겁니다.

원문에 적혀있는 대로 (5)와 (6) 방법이 가장 명료한 표기로 보입니다. 이 표기법을 주로 사용하도록 합시다. 그렇지만 나중 글에서는 (7)와 (8) 표기법도 많이 사용하더군요. ;(

테이블 힌트

tableHint는 MSSQL을 사용할 때에 테이블 힌트를 전달하는 데에 사용됩니다. 힌트값은 Sequelize.TableHints이어야 하며, 반드시 필요할 때에만 사용되어야 합니다. 현재는 쿼리마다 단 하나의 테이블 힌트만 사용 가능합니다.

테이블 힌트는 특정 옵션을 정의하는 것으로 MSSQL 쿼리 최적화기의 기본 동작을 덮어씁니다. 해당 구절에서 참조되는 테이블 또는 뷰에만 영향을 미칩니다.

const TableHints = Sequelize.TableHints;

Project.findAll({
  // adding the table hint NOLOCK
  tableHint: TableHints.NOLOCK
  // this will generate the SQL 'WITH (NOLOCK)'
})

코멘트

역시나 불친절한 도큐먼트입니다. 이전 글을 작성할 때, order에 대한 설명이 부실하여 이해하는 데에 어려움이 컸는데, 이번 챕터라고 해서 설명이 특별히 자세하지는 않네요. 게다가 연결 관계에 대한 설명도 없이 일단 예시부터 던지고 본다니...

다음 편은 6장 Instances 로 이어집니다.

profile
cadenzah
😊
이전 포스트

Sequelize 공식 Document - (1) Model Usage

다음 포스트

Sequelize 공식 Document - (3) Instances

2개의 댓글

comment-user-thumbnail
twoStones
2020년 1월 12일

좋은 정보 감사합니다~

1개의 답글