[ORM] 📚 시퀄라이즈 - 쿼리 문법 정리

시퀄라이즈 쿼리문

CRUD 작업을 하기 위해선 먼저 시퀄라이즈 쿼리를 알아야한다.

SQL문을 자바스크립트로 생성하는 것이기 때문에, 시퀄라이즈의 방식을 사용해야 한다.

시퀄리아지 쿼리문을 비동기로 돈작하며 프로미스 객체를 반환하므로, then을 붙여 결과값을 받을 수 있다.

그래서 async/await 문법과 함께 사용할 수도 있다.

---

테이블 조회 (findAll, findOne)

 

findAll

• 쿼리 결과를 배열 객체로 반환

모든 데이터를 조회하고 싶으면 findAll 메서드를 사용한다.

const { User } = require('./models');

// users테이블 전체를 조회해서 그 결과값을 객체로 만들어 user변수에 넣어준다.
const user = User.findAll({}); 

// user변수에는 조회된 결과 객체가 들어있어서, 해당 테이블 컬럼들을 조회할수 있다.
console.log(user[0].comment) // findAll는 여러 행들을 조회하기에, 각 행들이 배열로 저장되어있다.
							 // 따라서 배열 인덱스로 조회한다. 첫번째 행 users테이블에 comment필드를 조회하기

위의 시퀄라이즈 쿼리문은 다음 SQL문 처럼 동작한다.

SELECT * FROM users;

 

findOne

• 쿼리 결과를 객체로 반환

테이블의 데이터를 하나만 가져온다. 

const { User } = require('./models');

// users테이블 전체를 조회해서 그 결과값을 객체로 만들어 user변수에 넣어준다.
const user = User.findOne({}); 

// user변수에는 조회된 결과 객체가 들어있어서, 해당 테이블 컬럼들을 조회할수 있다.
console.log(user.comment) // findOne는 하나만 조회된다. users테이블에 comment필드를 조회하기

위의 시퀄라이즈 쿼리문은 다음 SQL문 처럼 동작한다.

SELECT * FROM users limit 1;

---

조건 조회 (attributes, where)

attributes 옵션을 사용하여 원하는 컬럼만 가져올 수도 있다.

또한, where 옵션으로 조건들을 나열할 수도 있다. where 옵션은 기본적으로 AND 옵션과 같다.

const { User } = require('./models');
const { Op } = require('sequelize');

const user = User.findAll({
    attributes: ['name', 'age'],
    where: {
        married: true, // married = 1
        age: { [Op.gt]: 30 }, // age > 30;
    },
});

console.log(user.comment)

SELECT name, age FROM users WHERE married = 1 AND age > 30;

비교 구문이 조금 특이한데, 시퀄라이즈는 자바스크립트 객체를 사용하여 쿼리를 생성하기 때문에 특수한 연산자들이 사용된다.

다음은 시퀄라이저 특수 연산자 정리이다.

 

💡 자주 쓰이는 Op객체

Op.gt	초과
Op.gte	이상
Op.lt	미만
Op.lte	이하
Op.ne	같지 않음
Op.or	또는
Op.in	배열 요소 중 하나
Op.notIn	배열 요소와 모두 다름

const Op = Sequelize.Op

[Op.and]: [{a: 5}, {b: 6}] // (a = 5) AND (b = 6)
[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.is]: null              // IS NULL
[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.startsWith]: 'hat'     // LIKE 'hat%'
[Op.endsWith]: 'hat'       // LIKE '%hat'
[Op.substring]: 'hat'      // LIKE '%hat%'

[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.like]: { // LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
	[Op.any]: ['cat', 'hat']
} 

[Op.gt]: { // > ALL (SELECT 1)
	[Op.all]: literal('SELECT 1') 
}

 

Op.or은 다음과 같이 배열 내에 적용할 쿼리들을 나열하여 사용한다.

const user = User.findAll({
    attributes: ['name', 'age'],
    where: { 
        [Op.or]: [ // married = 0 or age > 30
            { married: false },
            { age: { [Op.gt]: 30 } }
        ],
    },
});

console.log(user.comment)

SELECT name, age FROM users WHERE married = 1 OR age > 30;

---

데이터 넣기 (Create)

모델을 불러와 create 메서드를 사용하여 로우를 생성한다.

여기서 햇깔리는게 SQL에서의 create는 테이블을 만드는 거지만 ORM에서의 create는 insert로 쓰인다는 점을 숙지하자.

const result = User.create({ // 생성된 쿼리 결과를 얻는다.
    name: 'beom seok',
    age: 23,
    married: false,
    comment: '안녕하세요.'
});

INSERT INTO users (name, age, married, comment) 
VALUES ('beom seok', 23, 0, '안녕하세요.');

주의해야할 점은 데이터를 넣을 때 MySQL의 자료형이 아닌 시퀄라이즈 모델에 정의한 자료형대로 넣어야한다.

가령 married의 경우 실제 MySQL에는 TINYINT로 되어 이전엔 0을 넣어주었었지만, 현재는 BOOLEAN으로 정의돼있으므로 false를 넣어주어야 한다.

 

자료형 또는 옵션에 부합하지 않는 데이터를 넣을 경우 시퀄라이즈가 에러를 발생시키며, 시퀄라이즈가 알아서 MySQL 자료형으로 바꿔주니 걱정말자.

 

 

findOrCreate()

findOrCreate() 라는 메소드를 사용하면 조회 후에 없는 값이면 생성하고, 있는 값이면 그 값을 가져오는 형태의 오퍼레이션도 가능하다.

// find와 create 두 조건을 이행하기 때문에 반환값이 2개 즉 배열이다.
const [user, created] = await User.findOrCreate({
    where: { username: 'sdepold' },
    defaults: {
    job: 'Technical Lead JavaScript'
}
});
if (created) {
    // 만약 find하지 못하여 새로 create 될경우
    console.log(user.job); // 'Technical Lead JavaScript'
} else {
	// 만약 find가 될 경우
}

where에 함께 전달되는 defaults 옵션은 검색 결과가 존재하지 않을 경우 새로 생성되는 요소가 갖는 기본값이다.

---

정렬 (order)

정렬은 order 옵션으로 처리한다.

2차원 배열이라는 점에 주의하자. 이는 정렬은 꼭 컬럼 하나가 아닌 두 개 이상으로도 할 수 있기 때문이다.

User.findAll({
    attributes: ['name', 'age'],
    order: [ ['age', 'DESC'], ['name', 'ASC'] ]
});

SELECT id, name FROM users ORDER BY age DESC name ASC;

---

페이징 (limit, offset)

조회할 로우 개수는 limit으로,

조회를 시작할 로우 위치는 offset으로 할 수 있다.

User.findAll({
    attributes: ['name', 'age'],
    order: [['age', 'DESC']],
    limit: 10,
    offset: 5,
});

SELECT id, name FROM users ORDER BY age DESC LIMIT 5, 10;

SELECT id, name FROM users ORDER BY age DESC LIMIT 10 OFFSET 5;

limit이 1이라면 findAll 대신 findOne을 사용할 수 있다.

---

수정 (Update)

update 메서드로 수정할 수도 있다.

첫 번째 인수는 수정할 내용이고, 두 번째 인수는 어떤 로우를 수정할지에 대한 조건이다.

where 옵션에 조건들을 적는다.

User.update({
    comment: '새로운 코멘트.',
}, {
    where: { id: 2 },
});

UPDATE users SET comment = '새로운 코멘트.' WHERE id = 2;

 

upsert()

update와 / insert 합성 버젼.

문법은 위에서 배운 findorcreate와 별 다르지 않다.

const [city, created] = await City.upsert({
  cityName: "York",
  population: 20000,
});
console.log(created); // true or false
console.log(city); // City object

INSERT INTO Cities (cityName, population)
VALUES (?, ?)
ON DUPLICATE KEY
  UPDATE
    `cityName` = VALUES (`cityName`),
    `population` = VALUES (`population`);

---

삭제 (Delete)

로우 삭제는 destroy 메서드로 삭제한다.

User.destroy({
    where: { id: 2 },
});

User.destroy({
    where: { id: { [Op.in]: [1,3,5] } },
});

DELETE FROM users WHERE id = 2;

DELETE FROM users WHERE id in(1,3,5);

---

시퀄라이즈 관계 쿼리문

 

include

위의 사진처럼, 현재 User 모델은 Commenter 모델과 hasMany-belongsTo 관계가 맺어져 있으며, 만약 특정 사용자를 가져오면서 그 사람의 댓글까지 모두 가져오고 싶다면 include 속성을 사용하면 된다.

 

관계가 있는 모델을 include 배열에 넣어주면 된다.

배열인 이유는 다양한 모델과 관계가 있을 수 있기 때문이다.

댓글은 여러 개일 수 있으므로 (hasMany) user.Comments로 접근 가능하다.

const user = await User.findOne({
    include: [{ // join한다.
        model: Comment, // join할 테이블을 고른다.
    }]
});

console.log(user.Comments[0]); 
// 쿼리 결과인 user객체안에 Comments라는 키에 include한 comments테이블 쿼리들이 배열 값으로서 담겨 잇다.
// Comments 키의 이름은 User모델은 hasMany니까 복수형으로 자동으로 변환되서 생성된 것이다. (구분하기 편하게)
// => hasOne이면 단수형. M:N이면 항상 복수형.

select u.*, c.*
from users u
inner join comments c -- sequelize include는 기본동작은 inner join이다.
on u.id = c.commenter;

[ORM] 📚 Sequelize - left join 하는법

 

 

get모델명

위의 include로도 충분히 관계를 맺어 쿼리를 날릴수있지만 좀더 간편한 문법으로 관계쿼리를 얻을 수 있는데, 다음과 같이 댓글에 접근할 수도 있다.

문법이 너무 추상적이라 include보다 안 와닿을수 있겠지만 ORM문법을 간소화시키고 직관적인 메소드명을 사용할수 있는 장점이 있다.

const user = await User.findOne({});
const comments = await user.getComments(); 
// 따로 include할 필요없이 바로 get메소드를 쓰면 된다.
// 왜냐하면 associate로 모델을 정의한 순간 시퀄라이저가 관계가 맺어진 메소드를 알아서 만들어 주기 때문이다.

console.log(comments);

관계를 설정했다면, 시퀄라이즈는 다음과 같이

• getComment() / getComments( [ ] ) : 조회
• setComment() / setComments( [ ] ) : 수정 - 단 통째로 지우고 추가하는 식이라 조심
• addComment() / addComments( [ ] ) : 생성
• removeComment() / removeComments( [ ] ) : 삭제

메서드를 만들어 지원한다.

동사 뒤에 모델의 이름이 붙는 형식으로 생성된다.

s를 붙여도 되고 안붙여도 상관 없다.

다만, 보기 편하게 s를 붙인 메소드는 배열을 인자로 받는 것으로 규칙을 정해놓으면 좋다.

* 이 방법은, DB에 요청은 두번 보내지만, 하나의 쿼리만 가져오는 것에 유의 하자.
* 하나를 가져오는 순서는 당연히 오름차순이다.

 

조회는 위와 같이 하면 되지만,

수정, 생성, 삭제 때는 조금 다른 점이 있다.

// 생성
const user = await User.findOne({ 옵션 }); // 참조 관계에 부합한 유저를 선택하고
const comment = await Comment.create({ 옵션 }); // 데이터를 comment테이블에 넣는다.

await user.addComment(comment); // user의 외래키를 관계설정된 comment row에 추가/업뎃 한다
// 만일 user의 외래키를 위 Comment.create()과정에서 넣었다면 굳이 addComment() 쿼리를 안날려도 된다.
// FK가 null허용이고 insert과정에서 FK에 데이터를 안넣었을 경우 사용하는 것이다.

// 여러 개 생성
const user = await User.findOne({ 옵션 });
const comment1 = await Comment.create({ 옵션 });
const comment2 = await Comment.create({ 옵션 });

await user.addComment([comment1, comment2]);

insert into comments (필드) 
( select 필드 from user where 조건 )

 

관계 쿼리 메서드의 인수로 추가할 댓글 모델을 넣거나 댓글의 아이디를 넣으면 된다.

수정이나 삭제 또한 마찬가지이다.

 

 

as 별명

만일 동사 뒤의 모델 이름을 바꾸고 싶다면 관계 설정 시 as 옵션을 사용한다

// user.js

    static associate(db) {
        db.User.hasMany(db.Comment, { foreignKey: 'commenter', sourceKey: 'id', as: 'Answers' });
    }
};

위와 같이 관계를 설정했다면 댓글 객체도 user.Answers로 바뀌며, 쿼리 메서드들 또한 getAnswers() 등으로 변한다.

 

 

조인 속성

include나 관계 쿼리 메서드에도 where나 attributes 같은 옵션들을 사용할 수 있다.

아래는 id가 1인 댓글만 가져오고, 그 중에서도 id 컬럼만 가져오도록 하고 있다.

const user = await User.findOne({
    include: [{ // left outer join
        model: Comment, // join할 모델
        attributes: ['id'], // select해서 표시할 필드 지정
        where: { 
            id: 1, // on Comment.id = 1
        },
    }]
});

// 또는

const comments = await user.getComments({ // user가 comments를 get한다. (user와 comments는 관계되어 있다.)
    attributes: ['id'],  // select해서 표시할 필드 지정
    where: {
    	id: 1, // on Comment.id = 1
    },
});

select users.*, comments.id 
from users
inner join comments
on comments.commenter = users.id
where comments.id = 1
LIMIT 1;

---

이밖의 시퀄리아즈 쿼리문

모델사용 — Sequelize Of Node.js translate V5 5 documentation

 

쿼리 문자열 (literal)

시퀄라이즈에서 제공하는 메소드만으로는 쿼리가 부족한 경우가 있다.

literal() 은 쿼리 문자열을 추가해 주는 기능이다.

User.findAll({
  attributes: [
    ["name", "username"],
    [Sequelize.literal("age + 1"), "age"],
  ],
})

SELECT `name` AS `username`, age + 1 AS `age` FROM `users` AS `user`

보는바와 같이, sql문에서는 명령줄에 그대로 산술연산자를 써서 곧바로 계산된 값으로 쿼리를 날릴 수 있는데,

시퀄라이즈는 이러한 리터럴 산술연산을 지원하지 않아서 literal() 메소드를 통해 구현해야 한다.

---

SQL 쿼리 그대로 사용하기

시퀄라이즈의 쿼리를 사용하기 싫거나 헷갈린다면 직접 SQL문을 통해 쿼리할 수도 있다.

const [result, metadata] = await sequelize.query('SELECT * FROM comments');

---

참고문헌

Node.js 교과서 - 기본부터 프로젝트 실습까지 강의 - 조현영