Drizzle | Upsert 查询
PostgreSQL
MySQL
SQLite
本指南假定您熟悉
PostgreSQL 和 SQLite
要在 PostgreSQL 和 SQLite 中使用 Drizzle 实现 upsert 查询（跳至 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 中通过一个查询 upsert 多行，您可以使用 sql operator 和 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 中实现多目标的 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], // composite primary key
set: { quantity: sql`${inventory.quantity} + 100` }, // add 100 to the existing quantity
});insert into inventory ("warehouse_id", "product_id", "quantity") values (1, 1, 100)
on conflict ("warehouse_id","product_id") do update set quantity = quantity + 100;如果您想使用 where 子句为 update 语句实现 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: '[email protected]',
age: 29,
};
await db
.insert(users)
.values(data)
.onConflictDoUpdate({
target: users.id,
set: { ...data, email: sql`${users.email}` }, // leave email as it was
});insert into users ("id", "name", "email", "age") values (1, 'John', '[email protected]', 29)
on conflict ("id") do update set id = 1, name = 'John', email = email, age = 29;MySQL
要在 MySQL 中使用 Drizzle 实现 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 = <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: '[email protected]',
age: 29,
};
await db
.insert(users)
.values(data)
.onDuplicateKeyUpdate({
set: { ...data, email: sql`${users.email}` }, // leave email as it was
});insert into users (`id`, `name`, `email`, `age`) values (1, 'John', '[email protected]', 29)
on duplicate key update id = 1, name = 'John', email = email, age = 29;