Drizzle | Upsert 쿼리
PostgreSQL
MySQL
SQLite
This guide assumes familiarity with:
PostgreSQL과 SQLite
Drizzle를 사용해 PostgreSQL과 SQLite에서 Upsert 쿼리를 구현하려면 .onConflictDoUpdate() 메서드를 사용할 수 있습니다. (MySQL은 여기로 건너뛰세요.)
import { users } from './schema';
const db = drizzle(...);
await db
.insert(users)
.values({ id: 1, name: 'John' })
.onConflictDoUpdate({
target: users.id,
set: { name: 'Super John' },
});insert into users ("id", "name") values (1, 'John')
on conflict ("id") do update set name = 'Super John';PostgreSQL과 SQLite에서 여러 행을 한 번에 Upsert하려면 sql 연산자와 excluded 키워드를 사용할 수 있습니다. excluded는 충돌로 인해 삽입되지 않은 행을 참조하는 특별한 키워드입니다.
다음은 그 방법입니다:
index.ts
schema.ts
import { sql } from 'drizzle-orm';
import { users } from './schema';
const values = [
{
id: 1,
lastLogin: new Date(),
},
{
id: 2,
lastLogin: new Date(Date.now() + 1000 * 60 * 60),
},
{
id: 3,
lastLogin: new Date(Date.now() + 1000 * 60 * 120),
},
];
await db
.insert(users)
.values(values)
.onConflictDoUpdate({
target: users.id,
set: { lastLogin: sql.raw(`excluded.${users.lastLogin.name}`) },
});insert into users ("id", "last_login")
values
(1, '2024-03-15T22:29:06.679Z'),
(2, '2024-03-15T23:29:06.679Z'),
(3, '2024-03-16T00:29:06.679Z')
on conflict ("id") do update set last_login = excluded.last_login;Drizzle는 간단하고 유연한 API를 제공하여 커스텀 솔루션을 쉽게 만들 수 있습니다. 다음은 PostgreSQL과 SQLite에서 충돌 시 특정 컬럼을 업데이트하는 커스텀 함수를 만드는 방법입니다:
index.ts
schema.ts
import { SQL, getTableColumns, sql } from 'drizzle-orm';
import { PgTable } from 'drizzle-orm/pg-core';
import { SQLiteTable } from 'drizzle-orm/sqlite-core';
import { users } from './schema';
const buildConflictUpdateColumns = <
T extends PgTable | SQLiteTable,
Q extends keyof T['_']['columns']
>(
table: T,
columns: Q[],
) => {
const cls = getTableColumns(table);
return columns.reduce((acc, column) => {
const colName = cls[column].name;
acc[column] = sql.raw(`excluded.${colName}`);
return acc;
}, {} as Record);
};
const values = [
{
id: 1,
lastLogin: new Date(),
active: true,
},
{
id: 2,
lastLogin: new Date(Date.now() + 1000 * 60 * 60),
active: true,
},
{
id: 3,
lastLogin: new Date(Date.now() + 1000 * 60 * 120),
active: true,
},
];
await db
.insert(users)
.values(values)
.onConflictDoUpdate({
target: users.id,
set: buildConflictUpdateColumns(users, ['lastLogin', 'active']),
});insert into users ("id", "last_login", "active")
values
(1, '2024-03-16T15:44:41.141Z', true),
(2, '2024-03-16T16:44:41.141Z', true),
(3, '2024-03-16T17:44:41.141Z', true)
on conflict ("id") do update set last_login = excluded.last_login, active = excluded.active;PostgreSQL과 SQLite에서 여러 타겟을 가진 Upsert 쿼리를 구현하는 방법은 다음과 같습니다:
import { sql } from 'drizzle-orm';
import { inventory } from './schema';
await db
.insert(inventory)
.values({ warehouseId: 1, productId: 1, quantity: 100 })
.onConflictDoUpdate({
target: [inventory.warehouseId, inventory.productId], // 복합 기본 키
set: { quantity: sql`${inventory.quantity} + 100` }, // 기존 수량에 100 추가
});insert into inventory ("warehouse_id", "product_id", "quantity") values (1, 1, 100)
on conflict ("warehouse_id","product_id") do update set quantity = quantity + 100;update 문에 where 절을 추가해 Upsert 쿼리를 구현하려면 onConflictDoUpdate 메서드의 setWhere 속성을 사용할 수 있습니다:
index.ts
schema.ts
import { or, sql } from 'drizzle-orm';
import { products } from './schema';
const data = {
id: 1,
title: 'Phone',
price: '999.99',
stock: 10,
lastUpdated: new Date(),
};
const excludedPrice = sql.raw(`excluded.${products.price.name}`);
const excludedStock = sql.raw(`excluded.${products.stock.name}`);
await db
.insert(products)
.values(data)
.onConflictDoUpdate({
target: products.id,
set: {
price: excludedPrice,
stock: excludedStock,
lastUpdated: sql.raw(`excluded.${products.lastUpdated.name}`)
},
setWhere: or(
sql`${products.stock} != ${excludedStock}`,
sql`${products.price} != ${excludedPrice}`
),
});insert into products ("id", "title", "stock", "price", "last_updated")
values (1, 'Phone', 10, '999.99', '2024-04-29T21:56:55.563Z')
on conflict ("id") do update
set stock = excluded.stock, price = excluded.price, last_updated = excluded.last_updated
where (stock != excluded.stock or price != excluded.price);특정 컬럼을 제외하고 모든 컬럼을 업데이트하려면 다음과 같이 이전 값을 유지할 수 있습니다:
import { sql } from 'drizzle-orm';
import { users } from './schema';
const data = {
id: 1,
name: 'John',
email: 'john@email.com',
age: 29,
};
await db
.insert(users)
.values(data)
.onConflictDoUpdate({
target: users.id,
set: { ...data, email: sql`${users.email}` }, // 이메일은 그대로 유지
});insert into users ("id", "name", "email", "age") values (1, 'John', 'john@email.com', 29)
on conflict ("id") do update set id = 1, name = 'John', email = email, age = 29;MySQL
Drizzle를 사용하여 MySQL에서 upsert 쿼리를 구현하려면 .onDuplicateKeyUpdate() 메서드를 사용할 수 있습니다. MySQL은 기본 키와 고유 인덱스를 기반으로 충돌 대상을 자동으로 결정하고, 고유 인덱스 충돌이 발생하면 해당 행을 업데이트합니다.
다음은 이를 구현하는 방법입니다:
await db
.insert(users)
.values({ id: 1, name: 'John' })
.onDuplicateKeyUpdate({ set: { name: 'Super John' } });insert into users (`id`, `first_name`) values (1, 'John')
on duplicate key update first_name = 'Super John';MySQL에서 하나의 쿼리로 여러 행을 upsert하려면 sql operator와 values() 함수를 사용할 수 있습니다. values() 함수는 고유 키 충돌이 발생하지 않았을 경우 삽입될 컬럼의 값을 참조합니다.
index.ts
schema.ts
import { sql } from 'drizzle-orm';
import { users } from './schema';
const values = [
{
id: 1,
lastLogin: new Date(),
},
{
id: 2,
lastLogin: new Date(Date.now() + 1000 * 60 * 60),
},
{
id: 3,
lastLogin: new Date(Date.now() + 1000 * 60 * 120),
},
];
await db
.insert(users)
.values(values)
.onDuplicateKeyUpdate({
set: {
lastLogin: sql`values(${users.lastLogin})`,
},
});insert into users (`id`, `last_login`)
values
(1, '2024-03-15 23:08:27.025'),
(2, '2024-03-15 00:08:27.025'),
(3, '2024-03-15 01:08:27.025')
on duplicate key update last_login = values(last_login);Drizzle는 간단하고 유연한 API를 제공하여 커스텀 솔루션을 쉽게 만들 수 있습니다. 다음은 MySQL에서 충돌이 발생할 때 특정 컬럼을 업데이트하는 커스텀 함수를 만드는 방법입니다:
index.ts
schema.ts
import { SQL, getTableColumns, sql } from 'drizzle-orm';
import { MySqlTable } from 'drizzle-orm/mysql-core';
import { users } from './schema';
const buildConflictUpdateColumns = (
table: T,
columns: Q[],
) => {
const cls = getTableColumns(table);
return columns.reduce((acc, column) => {
acc[column] = sql`values(${cls[column]})`;
return acc;
}, {} as Record);
};
const values = [
{
id: 1,
lastLogin: new Date(),
active: true,
},
{
id: 2,
lastLogin: new Date(Date.now() + 1000 * 60 * 60),
active: true,
},
{
id: 3,
lastLogin: new Date(Date.now() + 1000 * 60 * 120),
active: true,
},
];
await db
.insert(users)
.values(values)
.onDuplicateKeyUpdate({
set: buildConflictUpdateColumns(users, ['lastLogin', 'active']),
});insert into users (`id`, `last_login`, `active`)
values
(1, '2024-03-16 15:23:28.013', true),
(2, '2024-03-16 16:23:28.013', true),
(3, '2024-03-16 17:23:28.013', true)
on duplicate key update last_login = values(last_login), active = values(active);특정 컬럼을 제외한 모든 컬럼을 업데이트하려면 이전 값을 유지할 수 있습니다:
import { sql } from 'drizzle-orm';
import { users } from './schema';
const data = {
id: 1,
name: 'John',
email: 'john@email.com',
age: 29,
};
await db
.insert(users)
.values(data)
.onDuplicateKeyUpdate({
set: { ...data, email: sql`${users.email}` }, // 이메일은 이전 값 유지
});insert into users (`id`, `name`, `email`, `age`) values (1, 'John', 'john@email.com', 29)
on duplicate key update id = 1, name = 'John', email = email, age = 29;