마법의 sql 연산자 🪄

ORM 라이브러리로 작업할 때, 제공되는 ORM 구문을 사용하여 특정 쿼리를 작성하기 어려운 경우가 있을 수 있습니다. 이러한 상황에서는 원시 문자열로 쿼리를 구성하는 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 인젝션 취약점을 효과적으로 방지합니다.

sql<T>

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

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

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

// sql<T> 타입이 정의된 경우
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 부분의 배열인 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 템플릿에서 생성된 쿼리 문자열과 해당 매개변수를 얻으려면, 쿼리를 생성하려는 데이터베이스 방언을 지정해야 합니다. 데이터베이스마다 매개변수화 및 이스케이프에 대한 구문이 다르므로 적절한 방언을 선택하는 것이 중요합니다.

방언을 선택한 후에는 해당 구현의 기능을 활용하여 SQL 템플릿을 원하는 쿼리 문자열과 매개변수 형식으로 변환할 수 있습니다. 이를 통해 작업 중인 특정 데이터베이스 시스템과의 호환성이 보장됩니다.

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 문서**에서 확인할 수 있습니다.

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

여기서 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;