마법의 `sql` 연산자 🪄

ORM 라이브러리를 사용할 때, 제공된 ORM 문법으로 특정 쿼리를 작성하기 어려운 경우가 있을 수 있습니다. 이러한 상황에서는 raw 쿼리를 사용할 수 있는데, 이는 쿼리를 raw 문자열로 구성하는 방식입니다. 하지만 raw 쿼리는 타입 안전성과 쿼리 파라미터화의 이점을 누리지 못하는 경우가 많습니다.

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

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

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

sql“ 템플릿

다른 ORM에서도 흔히 볼 수 있는 가장 일반적인 사용법 중 하나는 raw 쿼리를 위해 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이 아닌 특정 타입을 요구하는 곳에서 사용할 수 있습니다.

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

// without sql<T> type defined
const response: { lowerName: unknown }[] = await db.select({
    lowerName: sql`lower(${usersTable.id})`
}).from(usersTable);

// with sql<T> type defined
const response: { lowerName: 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(),
});

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

DriverValueDecoder 인터페이스에 대한 자체 구현을 전달할 수도 있습니다:

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

// 또는
sql``.mapWith(Number);

sql``.as<T>()

경우에 따라 사용하려는 커스텀 필드의 이름을 어떻게 지정할지 결정하기 어려울 수 있습니다. 선택될 필드에 대해 별칭을 명시적으로 지정해야 하는 상황이 발생할 수 있습니다. 이는 복잡한 쿼리를 다룰 때 특히 유용할 수 있습니다.

이러한 시나리오를 해결하기 위해 별칭을 명시적으로 정의할 수 있는 유용한 .as('alias_name') 헬퍼를 도입했습니다. 이 기능을 활용하면 필드에 명확하고 의미 있는 이름을 제공하여 쿼리를 더 직관적이고 읽기 쉽게 만들 수 있습니다.

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

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

`sql.raw()`

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

sql.raw() 함수를 사용하면 추가적인 처리나 이스케이프 없이 쿼리 내에 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 함수 내에서 sql.raw()를 활용하여 메인 sql 템플릿 함수를 통해 이스케이프하지 않고 모든 raw 문자열을 포함할 수 있습니다.

sql 함수 내에서 sql.raw()를 사용하면 이스케이프되지 않은 raw 문자열을 쿼리에 직접 통합할 수 있습니다. 이는 템플릿 함수의 자동 이스케이프나 수정의 영향을 받지 않아야 하는 특정 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을 데이터베이스나 쿼리에 적용한 후 쿼리와 params로 연결될 SQL 부분의 배열입니다.

특정 시나리오에서는 커스텀 비즈니스 로직을 사용하여 이러한 청크를 배열로 집계한 다음, 데이터베이스나 쿼리에 전달할 수 있는 단일 SQL 문으로 연결해야 할 수 있습니다. 이러한 경우 fromList 함수가 매우 유용할 수 있습니다.

fromList 함수를 사용하면 여러 SQL 청크를 단일 SQL 문으로 결합할 수 있습니다. 특정 요구사항에 따라 개별 SQL 부분을 집계하고 연결한 다음 실행 가능한 통합된 SQL 쿼리를 얻을 수 있습니다.

const sqlChunks: SQL[] = [];

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

// some logic

sqlChunks.push(sql` where `);

// some logic

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`);

// some logic

sqlChunks.push(sql`where`);

// some logic

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`;

// some logic

finalSql.append(sql` where `);

// some logic

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();

// some logic

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

// some logic

finalSql.append(sql` where `);

// some logic

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를 지정해야 합니다. 데이터베이스마다 파라미터화 및 이스케이프를 위한 문법이 다르기 때문에 적절한 dialect를 선택하는 것이 중요합니다.

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 ]

select 절에서 `sql` 사용하기

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

부분 select 쿼리에 대한 자세한 정보는 **Core API docs**에서 확인할 수 있습니다.

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

여기서 sql<T>, sql``.mapWith(), sql``.as<T>() 사용법을 확인할 수 있습니다.

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

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

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

where 절에서 `sql` 사용하기

실제로 Drizzle은 sql 템플릿 내에서 사용할 수 있는 다양한 표현식을 제공합니다. 하지만 데이터베이스는 종종 확장 기능이나 다른 방법을 통해 제공되는 것을 포함하여 더 광범위한 표현식을 사용할 수 있는 것이 사실입니다.

유연성을 보장하고 Drizzle에서 기본적으로 지원하지 않는 표현식을 사용할 수 있도록 sql 함수를 사용하여 SQL 템플릿을 직접 작성할 수 있는 자유가 있습니다. 이를 통해 SQL의 모든 기능을 활용하고 대상 데이터베이스에 특정한 표현식이나 기능을 통합할 수 있습니다.

sql 템플릿을 사용하면 Drizzle의 미리 정의된 표현식에만 제한되지 않습니다. 대신 복잡한 쿼리를 표현하고 기본 데이터베이스 시스템이 제공하는 지원되는 모든 표현식을 통합할 수 있습니다.

sql을 사용한 id로 필터링

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" ]

orderBy 절에서 `sql` 사용하기

Drizzle에서 사용할 수 없는 정렬을 위한 특정 기능이 필요하지만 raw SQL에 의존하고 싶지 않을 때 ORDER BY 절에서 sql 템플릿을 실제로 사용할 수 있습니다.

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

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

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

having 및 groupBy 절에서 `sql` 사용하기

Drizzle에서 사용할 수 없는 정렬을 위한 특정 기능이 필요하지만 raw SQL에 의존하고 싶지 않을 때 HAVING 및 GROUP BY 절에서 sql 템플릿을 실제로 사용할 수 있습니다.

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

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

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

마법의 sql 연산자 🪄

sql“ 템플릿

sql<T>