마법 같은 `sql` 연산자 🪄

ORM 라이브러리를 사용하다 보면, 제공된 ORM 문법으로 특정 쿼리를 작성하기 어려운 경우가 있습니다. 이런 상황에서는 보통 raw 쿼리를 사용하게 되는데, 이는 쿼리를 문자열로 직접 작성하는 방식을 의미합니다. 하지만 raw 쿼리는 타입 안전성과 쿼리 파라미터화의 이점을 누리기 어렵다는 단점이 있습니다.

이 문제를 해결하기 위해 많은 라이브러리에서 sql 템플릿 개념을 도입했습니다. 이 템플릿을 사용하면 타입 안전성과 파라미터화된 쿼리를 더 쉽게 작성할 수 있어 코드의 안전성과 유연성을 높일 수 있습니다. 강력한 ORM 라이브러리인 Drizzle도 이 sql 템플릿을 지원합니다.

Drizzle의 sql 템플릿을 활용하면 쿼리를 더욱 세밀하게 구성할 수 있습니다. 라이브러리의 쿼리 빌더로 전체 쿼리를 작성하기 어려운 경우, Drizzle 쿼리의 특정 부분에만 sql 템플릿을 선택적으로 사용할 수 있습니다. 이 유연성 덕분에 sql 템플릿을 SELECT 문의 일부, WHERE 절, ORDER BY 절, HAVING 절, GROUP BY 절, 심지어 관계형 쿼리 빌더에서도 활용할 수 있습니다.

Drizzle의 sql 템플릿 기능을 활용하면 타입 안전성과 쿼리 파라미터화의 장점을 유지하면서도 원하는 쿼리 구조와 복잡성을 달성할 수 있습니다. 이를 통해 애플리케이션 내에서 더 견고하고 유지보수하기 쉬운 코드를 작성할 수 있습니다.

sql“ 템플릿

다른 ORM에서도 자주 볼 수 있는 일반적인 사용법 중 하나는, sql 쿼리를 그대로 사용하여 원시 쿼리를 작성하는 것입니다.

import { sql } from 'drizzle-orm' 

const id = 69;
await db.execute(sql`select * from ${usersTable} where ${usersTable.id} = ${id}`)

위 코드는 다음과 같은 쿼리를 생성합니다.

select * from "users" where "users"."id" = $1; --> [69]

sql 파라미터에 제공된 테이블과 컬럼은 자동으로 해당 SQL 구문에 매핑되며, 테이블 이름은 이스케이프 처리됩니다. 또한 컬럼 이름에는 이스케이프된 테이블 이름이 추가됩니다.

또한, ${id}와 같은 동적 파라미터는 $1 플레이스홀더로 매핑되고, 해당 값은 데이터베이스에 별도로 전달되는 값 배열로 이동합니다.

이 방식은 잠재적인 SQL Injection 취약점을 효과적으로 방지합니다.

`sql<T>`

sql<T>는 런타임 매핑을 수행하지 않습니다. sql<T>를 사용해 정의한 타입은 순전히 Drizzle을 위한 헬퍼 역할만 합니다. SQL 쿼리는 매우 다양하고 커스터마이징이 가능하기 때문에, 동적으로 정확한 타입을 결정하는 것은 불가능하다는 점을 이해하는 것이 중요합니다.

Drizzle에서 unknown이 아닌 특정 타입이 필요한 필드에 사용할 커스텀 타입을 정의할 수 있습니다.

이 기능은 부분 선택 쿼리에서 특히 유용하며, 선택된 필드에 대해 일관된 타입을 보장합니다:

// sql 타입이 정의되지 않은 경우
const response: { id: unknown }[] = await db.select({
    lowerName: sql`lower(${usersTable.id})`
}).from(usersTable);

// sql 타입이 정의된 경우
const response: { id: string }[] = await db.select({
    lowerName: sql<string>`lower(${usersTable.id})`
}).from(usersTable);

sql``.mapWith()

데이터베이스 드라이버에서 Drizzle로 전달되는 값을 런타임에 매핑해야 하는 경우 .mapWith()를 사용할 수 있습니다.

이 함수는 다양한 값을 받아들여 런타임에 응답을 매핑합니다.

mapWith 내부의 인터페이스가 Column에 의해 구현된 인터페이스와 동일하다면, 특정 컬럼 매핑 전략을 복제할 수 있습니다.

const usersTable = pgTable('users', {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
});

// 런타임에 이 값들은 `text` 컬럼이 Drizzle에서 매핑되는 방식과 동일하게 매핑됩니다.
sql`...`.mapWith(usersTable.name);

또한 DriverValueDecoder 인터페이스에 대한 여러분만의 구현을 전달할 수도 있습니다:

sql``.mapWith({
    mapFromDriverValue: (value: any) => {
        const mappedValue = value;
        // 적용하려는 매핑
        return mappedValue;
    },
});
    
// 또는
sql``.mapWith(Number);

sql``.as<T>()

특정 상황에서는 커스텀 필드의 이름을 어떻게 지어야 할지 결정하기 어려울 때가 있습니다. 특히 복잡한 쿼리를 다룰 때, 선택할 필드에 대해 명시적으로 별칭(alias)을 지정해야 하는 경우가 종종 발생합니다. 이러한 상황을 해결하기 위해, .as('alias_name') 헬퍼를 도입했습니다. 이 기능을 사용하면 필드에 명확하고 의미 있는 이름을 부여할 수 있어, 쿼리를 더 직관적이고 읽기 쉽게 만들 수 있습니다.

sql`lower(usersTable.name)`.as('lower_name')

... "usersTable"."name" as lower_name ...

`sql.raw()`

입력값에서 파라미터화된 값을 생성하거나 테이블/컬럼을 이스케이프 처리된 값으로 매핑할 필요가 없는 경우가 있습니다. 대신, 쿼리를 있는 그대로 생성하고 싶을 때가 있죠. 이런 상황을 위해 sql.raw() 함수를 제공합니다.

sql.raw() 함수를 사용하면 추가적인 처리나 이스케이프 없이 쿼리 안에 원시 SQL 문을 포함할 수 있습니다. 이는 미리 구성된 SQL 문이 있거나 복잡하거나 동적인 SQL 코드를 직접 쿼리에 포함해야 할 때 유용합니다.

sql.raw(`select * from users where id = ${12}`);
// vs
sql`select * from users where id = ${12}`;

select * from users where id = 12;
--> vs
select * from users where id = $1; --> [12]

또한, sql.raw()를 sql 함수 내에서 사용할 수 있습니다. 이를 통해 메인 sql 템플릿 함수를 통해 이스케이프 처리되지 않은 원시 문자열을 쿼리에 포함할 수 있습니다.

sql 함수 내에서 sql.raw()를 사용하면, 템플릿 함수의 자동 이스케이프나 수정 없이 특정 SQL 코드나 표현식을 그대로 쿼리에 포함할 수 있습니다. 이는 특정 SQL 코드나 표현식이 템플릿 함수의 자동 처리에 의해 변경되지 않아야 할 때 특히 유용합니다.

sql`select * from ${usersTable} where id = ${12}`;
// vs
sql`select * from ${usersTable} where id = ${sql.raw(12)}`;

select * from "users" where id = $1; --> [12]
--> vs
select * from "users" where id = 12;

sql.fromList()

sql 템플릿은 SQL 조각을 생성합니다. 이 조각들은 데이터베이스나 Drizzle 쿼리에 SQL을 적용한 후 쿼리와 파라미터로 연결됩니다.

특정 상황에서는 커스텀 비즈니스 로직을 사용해 이러한 조각들을 배열로 모아야 할 수 있습니다. 그런 다음, 이를 하나의 SQL 문장으로 연결하여 데이터베이스나 쿼리에 전달할 수 있습니다. 이런 경우에 fromList 함수가 매우 유용합니다.

fromList 함수는 여러 SQL 조각을 하나의 SQL 문장으로 결합할 수 있게 해줍니다. 이를 통해 개별 SQL 부분을 특정 요구사항에 따라 모으고 연결한 후, 실행 가능한 통합 SQL 쿼리를 얻을 수 있습니다.

const sqlChunks: SQL[] = [];

sqlChunks.push(sql`select * from users`);

// 일부 로직

sqlChunks.push(sql` where `);

// 일부 로직

for (let i = 0; i < 5; i++) {
	sqlChunks.push(sql`id = ${i}`);

	if (i === 4) continue;
	sqlChunks.push(sql` or `);
}

const finalSql: SQL = sql.fromList(sqlChunks)

select * from users where id = $1 or id = $2 or id = $3 or id = $4 or id = $5; --> [0, 1, 2, 3, 4]

sql.join()

sql.join 함수는 fromList 헬퍼와 비슷한 역할을 합니다. 하지만 SQL 청크 사이의 공간을 처리하거나, SQL 청크를 연결할 때 커스텀 구분자를 지정하는 데 있어 더 많은 유연성을 제공합니다.

sql.join을 사용하면 지정된 구분자로 SQL 청크를 연결할 수 있습니다. 이 구분자는 청크 사이에 삽입하고 싶은 문자열이나 문자일 수 있습니다.

이 기능은 SQL 청크를 특정 형식으로 포맷하거나 구분해야 할 때 특히 유용합니다. 커스텀 구분자를 지정함으로써 최종 SQL 쿼리에서 원하는 구조와 형식을 얻을 수 있습니다.

const sqlChunks: SQL[] = [];

sqlChunks.push(sql`select * from users`);

// 일부 로직

sqlChunks.push(sql`where`);

// 일부 로직

for (let i = 0; i < 5; i++) {
    sqlChunks.push(sql`id = ${i}`);

    if (i === 4) continue;
    sqlChunks.push(sql`or`);
}

const finalSql: SQL = sql.join(sqlChunks, sql.raw(' '));

select * from users where id = $1 or id = $2 or id = $3 or id = $4 or id = $5; --> [0, 1, 2, 3, 4]

sql.append()

이미 sql 템플릿을 사용해 SQL을 생성했다면, append 함수를 사용해 새로운 SQL 청크를 직접 추가함으로써 fromList와 동일한 동작을 구현할 수 있습니다.

append 함수를 사용하면 기존 SQL 문자열에 동적으로 추가 SQL 청크를 덧붙일 수 있습니다. 이를 통해 여러 청크를 하나로 합쳐 최종 SQL 쿼리를 만들 수 있습니다. 이 방법은 커스텀 로직이나 비즈니스 규칙을 적용해 청크를 통합할 때 유용합니다.

const finalSql = sql`select * from users`;

// 로직 처리

finalSql.append(sql` where `);

// 로직 처리

for (let i = 0; i < 5; i++) {
    finalSql.append(sql`id = ${i}`);

    if (i === 4) continue;
    finalSql.append(sql` or `);
}

select * from users where id = $1 or id = $2 or id = $3 or id = $4 or id = $5; --> [0, 1, 2, 3, 4]

sql.empty()

sql.empty()를 사용하면 빈 SQL 객체로 시작한 후 필요에 따라 SQL 조각을 동적으로 추가할 수 있습니다. 이를 통해 SQL 쿼리를 점진적으로 구성하고, 각 조각의 내용을 결정하기 위해 커스텀 로직이나 조건을 적용할 수 있습니다.

sql.empty()로 SQL 객체를 초기화한 후에는 파라미터화, 합성, 이스케이핑과 같은 SQL 템플릿의 모든 기능을 활용할 수 있습니다. 이를 통해 특정 요구사항에 맞게 SQL 쿼리를 유연하고 제어된 방식으로 구성할 수 있습니다.

const finalSql = sql.empty();

// 일부 로직

finalSql.append(sql`select * from users`);

// 일부 로직

finalSql.append(sql` where `);

// 일부 로직

for (let i = 0; i < 5; i++) {
    finalSql.append(sql`id = ${i}`);

    if (i === 4) continue;
    finalSql.append(sql` or `);
}

select * from users where id = $1 or id = $2 or id = $3 or id = $4 or id = $5; --> [0, 1, 2, 3, 4]

SQL을 문자열과 파라미터로 변환하기

이전 예제들에서 여러분은 TypeScript에서 SQL 템플릿 구문과 생성된 SQL 출력을 사용하는 것을 확인했습니다.

SQL 템플릿에서 생성된 쿼리 문자열과 해당 파라미터를 얻으려면, 쿼리를 생성할 데이터베이스 방언(dialect)을 지정해야 합니다. 각 데이터베이스는 파라미터화와 이스케이프에 대해 서로 다른 구문을 사용하기 때문에 적절한 방언을 선택하는 것이 중요합니다.

방언을 선택한 후, 해당 구현체의 기능을 사용하여 SQL 템플릿을 원하는 쿼리 문자열과 파라미터 형식으로 변환할 수 있습니다. 이렇게 하면 작업 중인 특정 데이터베이스 시스템과의 호환성이 보장됩니다.

PostgreSQL

MySQL

SQLite

import { PgDialect } from 'drizzle-orm/pg-core';

const pgDialect = new PgDialect();
pgDialect.sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`);

select * from "users" where "users"."id" = $1; --> [ 12 ]

import { MySqlDialect } from 'drizzle-orm/mysql-core';

const mysqlDialect = new MySqlDialect();
mysqlDialect.sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`);

select * from `users` where `users`.`id` = ?; --> [ 12 ]

import { SQLiteSyncDialect } from 'drizzle-orm/sqlite-core';

const sqliteDialect = new SQLiteSyncDialect();
sqliteDialect.sqlToQuery(sql`select * from ${usersTable} where ${usersTable.id} = ${12}`);

select * from "users" where "users"."id" = ?; --> [ 12 ]

`sql` select

SQL 기능은 부분 선택 쿼리에서도 사용할 수 있습니다. 부분 선택 쿼리를 사용하면 전체 행을 가져오는 대신 테이블에서 특정 필드나 컬럼만 조회할 수 있습니다.

부분 선택 쿼리에 대한 더 자세한 정보는 **Core API 문서**를 참고하세요.

테이블에서 다양한 커스텀 필드 선택하기

여기서는 sql<T>, sql``.mapWith(), **sql``.as<T>()**의 사용 예제를 확인할 수 있습니다.

import { sql } from 'drizzle-orm'
import { usersTable } from 'schema'

await db.select({
    id: usersTable.id,
    lowerName: sql`lower(${usersTable.name})`,
    aliasedName: sql`lower(${usersTable.name})`.as('aliased_column'),
    count: sql`count(*)`.mapWith(Number) 
}).from(usersTable)

select `id`, lower(`name`), lower(`name`) as `aliased_column`, count(*) from `users`;

`sql`을 where 절에서 사용하기

Drizzle는 SQL 템플릿 내에서 사용할 수 있는 다양한 표현식을 제공합니다. 하지만 데이터베이스는 확장 기능 등을 통해 더 많은 표현식을 지원하는 경우가 많습니다.

Drizzle에서 기본적으로 지원하지 않는 표현식을 사용하려면, sql 함수를 직접 사용하여 SQL 템플릿을 작성할 수 있습니다. 이를 통해 SQL의 모든 기능을 활용하고, 특정 데이터베이스 시스템에서 제공하는 표현식이나 기능을 자유롭게 사용할 수 있습니다.

sql 템플릿을 사용하면 Drizzle에서 미리 정의된 표현식에 제한되지 않고, 복잡한 쿼리를 작성하거나 데이터베이스 시스템이 지원하는 모든 표현식을 활용할 수 있습니다.

id로 필터링하기 (SQL 사용)

import { sql } from 'drizzle-orm'
import { usersTable } from 'schema'

const id = 77

await db.select()
        .from(usersTable)
        .where(sql`${usersTable.id} = ${id}`)

select * from "users" where "users"."id" = $1; --> [ 77 ]

고급 전체 텍스트 검색 where 절

import { sql } from 'drizzle-orm'
import { usersTable } from 'schema'

const searchParam = "Ale"

await db.select()
        .from(usersTable)
        .where(sql`to_tsvector('simple', ${usersTable.name}) @@ to_tsquery('simple', ${searchParam})`)

select * from "users" where to_tsvector('simple', "users"."name") @@ to_tsquery('simple', '$1'); --> [ "Ale" ]

`sql`을 orderBy에서 사용하기

Drizzle에서 제공하지 않는 특정 정렬 기능이 필요하지만, 완전히 raw SQL을 사용하고 싶지 않을 때 sql 템플릿을 ORDER BY 절에서 사용할 수 있습니다.

import { sql } from 'drizzle-orm'
import { usersTable } from 'schema'

await db.select()
  .from(usersTable)
  .orderBy(sql`${usersTable.id} desc nulls first`)

위 코드는 다음과 같은 SQL 쿼리로 변환됩니다.

select * from "users" order by "users"."id" desc nulls first;

이 방법을 사용하면 Drizzle의 타입 안전성을 유지하면서도 복잡한 정렬 로직을 구현할 수 있습니다.

HAVING과 GROUP BY에서 `sql` 사용하기

Drizzle에서 제공하지 않는 특정 기능을 사용해야 하지만, 완전히 raw SQL로 전환하고 싶지 않을 때 sql 템플릿을 HAVING 및 GROUP BY 절에서 사용할 수 있습니다.

import { sql } from 'drizzle-orm'
import { usersTable } from 'schema'

await db.select({ 
    projectId: usersTable.projectId,
    count: sql`count(${usersTable.id})`.mapWith(Number)
}).from(usersTable)
    .groupBy(sql`${usersTable.projectId}`)
    .having(sql`count(${usersTable.id}) > 300`)

위 코드는 다음과 같은 SQL 쿼리로 변환됩니다.

select "project_id", count("users"."id") from users group by "users"."project_id" having count("users"."id") > 300;

이 예제에서는 sql 템플릿을 사용하여 GROUP BY와 HAVING 절에 필요한 조건을 직접 지정했습니다. 이를 통해 Drizzle의 기능을 확장하면서도 raw SQL을 최소화할 수 있습니다.

마법 같은 sql 연산자 🪄