Drizzle | Upsert 查询
PostgreSQL
MySQL
SQLite
This guide assumes familiarity with:
PostgreSQL 和 SQLite
要在 PostgreSQL 和 SQLite 中实现上插入查询（跳到 MySQL），可以使用 .onConflictDoUpdate() 方法：
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 中一次查询上插入多行，您可以使用 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<Q, SQL>);
};
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 中使用多个目标实现上插入查询：
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 条件的上插入查询，可以使用 setWhere 属性在 onConflictDoUpdate 方法中：
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}` }, // 保持 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
要在 MySQL 中实现上插入查询，可以使用 .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 中一次查询上插入多行，您可以使用 sql 操作符 和 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 = <T extends MySqlTable, Q extends keyof T['_']['columns']>(
table: T,
columns: Q[],
) => {
const cls = getTableColumns(table);
return columns.reduce((acc, column) => {
acc[column] = sql`values(${cls[column]})`;
return acc;
}, {} as Record<Q, SQL>);
};
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}` }, // 保持 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;