Upsert 查询 - Drizzle ORM 中文文档

Drizzle | Upsert 查询

PostgreSQL

MySQL

SQLite

This guide assumes familiarity with:

开始使用 PostgreSQL, MySQL 和 SQLite
插入语句, onConflictDoUpdate 方法和 onDuplicateKeyUpdate 方法
复合主键
sql 操作符

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;

import { pgTable, serial, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  lastLogin: timestamp('last_login', { mode: 'date' }).notNull(),
});

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;

import { boolean, pgTable, serial, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  lastLogin: timestamp('last_login', { mode: 'date' }).notNull(),
  active: boolean('active').notNull().default(false),
});

这是如何在 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 { integer, numeric, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  stock: integer('stock').notNull(),
  price: numeric('price', { precision: 10, scale: 2 }).notNull(),
  lastUpdated: timestamp('last_updated').notNull().defaultNow(),
});

如果您想更新所有列，除了特定的一列，您可以像这样保留先前的值：

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

import { mysqlTable, serial, timestamp } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: serial('id').primaryKey(),
  lastLogin: timestamp('last_login', { mode: 'date' }).notNull(),
});

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 { boolean, mysqlTable, serial, timestamp } from 'drizzle-orm/mysql-core';

export const users = mysqlTable('users', {
  id: serial('id').primaryKey(),
  lastLogin: timestamp('last_login', { mode: 'date' }).notNull(),
  active: boolean('active').notNull().default(false),
});

如果您想更新所有列，除了特定的一列，您可以像这样保留先前的值：

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;