Drizzle | Upsert Query
PostgreSQL
MySQL
SQLite
This guide assumes familiarity with:
- Get started with PostgreSQL, MySQL and SQLite
- Insert statement, onConflictDoUpdate method and onDuplicateKeyUpdate method
- Composite primary key
- sql operator
PostgreSQL and SQLite
To implement an upsert query in PostgreSQL and SQLite (skip to MySQL) with Drizzle you can use .onConflictDoUpdate() method:
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';
To upsert multiple rows in one query in PostgreSQL and SQLite you can use sql operator and excluded keyword. excluded is a special reference that refer to the row that was proposed for insertion, but wasn’t inserted because of the conflict.
This is how you can do it:
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 has simple and flexible API, which lets you easily create custom solutions. This is how you do custom function for updating specific columns in multiple rows due to the conflict in PostgreSQL and 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),
});
This is how you can implement an upsert query with multiple targets in PostgreSQL and 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], // 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;
If you want to implement upsert query with where clause for update statement, you can use setWhere property in onConflictDoUpdate method:
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(),
});
If you want to update all columns except of specific one, you can leave the previous value like this:
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
To implement an upsert query in MySQL with Drizzle you can use .onDuplicateKeyUpdate() method. MySQL will automatically determine the conflict target based on the primary key and unique indexes, and will update the row if any unique index conflicts.
This is how you can do it:
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';
To upsert multiple rows in one query in MySQL you can use sql operator and values() function. values() function refers to the value of column that would be inserted if duplicate-key conflict hadn’t occurred.
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 has simple and flexible API, which lets you easily create custom solutions. This is how you do custom function for updating specific columns in multiple rows due to the conflict in 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),
});
If you want to update all columns except of specific one, you can leave the previous value like this:
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;
