SQL Insert

Drizzle ORM provides you the most SQL-like way to insert rows into the database tables.

Insert one row

Inserting data with Drizzle is extremely straightfoward and sql-like. See for yourself:

await db.insert(users).values({ name: 'Andrew' });

insert into "users" ("name") values ("Andrew");

If you need insert type for a particular table you can use typeof usersTable.$inferInsert syntax.

type NewUser = typeof users.$inferInsert;

const insertUser = async (user: NewUser) => {
  return db.insert(users).values(user);
}

const newUser: NewUser = { name: "Alef" };
await insertUser(newUser);

Insert returning

PostgreSQL

SQLite

MySQL

You can insert a row and get it back in PostgreSQL and SQLite like such:

await db.insert(users).values({ name: "Dan" }).returning();

// partial return
await db.insert(users).values({ name: "Partial Dan" }).returning({ insertedId: users.id });

Insert multiple rows

await db.insert(users).values([{ name: 'Andrew' }, { name: 'Dan' }]);

Upserts and conflicts

Drizzle ORM provides simple interfaces for handling upserts and conflicts.

On conflict do nothing

PostgreSQL

SQLite

MySQL

onConflictDoNothing will cancel the insert if there’s a conflict:

await db.insert(users)
  .values({ id: 1, name: 'John' })
  .onConflictDoNothing();

// explicitly specify conflict target
await db.insert(users)
  .values({ id: 1, name: 'John' })
  .onConflictDoNothing({ target: users.id });

On conflict do update

PostgreSQL

SQLite

MySQL

onConflictDoUpdate will update the row if there’s a conflict:

await db.insert(users)
  .values({ id: 1, name: 'Dan' })
  .onConflictDoUpdate({ target: users.id, set: { name: 'John' } });

`where` clauses

on conflict do update can have a where clause in two different places - as part of the conflict target (i.e. for partial indexes) or as part of the update clause:

insert into employees (employee_id, name)
values (123, 'John Doe')
on conflict (employee_id) where name <> 'John Doe'
do update set name = excluded.name

insert into employees (employee_id, name)
values (123, 'John Doe')
on conflict (employee_id) do update set name = excluded.name
where name <> 'John Doe';

To specify these conditions in Drizzle, you can use setWhere and targetWhere clauses:

await db.insert(employees)
  .values({ employeeId: 123, name: 'John Doe' })
  .onConflictDoUpdate({
    target: employees.employeeId,
    targetWhere: sql`name <> 'John Doe'`,
    set: { name: sql`excluded.name` }
  });

await db.insert(employees)
  .values({ employeeId: 123, name: 'John Doe' })
  .onConflictDoUpdate({
    target: employees.employeeId,
    set: { name: 'John Doe' },
    setWhere: sql`name <> 'John Doe'`
  });

Upsert with composite indexes, or composite primary keys for onConflictDoUpdate:

await db.insert(users)
  .values({ firstName: 'John', lastName: 'Doe' })
  .onConflictDoUpdate({
    target: [users.firstName, users.lastName],
    set: { firstName: 'John1' }
  });

On duplicate key update

PostgreSQL

SQLite

MySQL

MySQL supports ON DUPLICATE KEY UPDATE instead of ON CONFLICT clauses. 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.

Drizzle supports this through the onDuplicateKeyUpdate method:

// Note that MySQL automatically determines targets based on the primary key and unique indexes
await db.insert(users)
  .values({ id: 1, name: 'John' })
  .onDuplicateKeyUpdate({ set: { name: 'John' } });

While MySQL does not directly support doing nothing on conflict, you can perform a no-op by setting any column’s value to itself and achieve the same effect:

import { sql } from 'drizzle-orm';

await db.insert(users)
  .values({ id: 1, name: 'John' })
  .onDuplicateKeyUpdate({ set: { id: sql`id` } });

WITH INSERT clause

💡

Check how to use WITH statement with select, update, delete

Using the with clause can help you simplify complex queries by splitting them into smaller subqueries called common table expressions (CTEs):

const userCount = db.$with('user_count').as(
  db.select({ value: sql`count(*)`.as('value') }).from(users)
);

const result = await db.with(userCount)
  .insert(users)
  .values([
    { username: 'user1', admin: sql`((select * from ${userCount}) = 0)` }
  ])
  .returning({
    admin: users.admin
  });

with "user_count" as (select count(*) as "value" from "users") 
insert into "users" ("username", "admin") 
values ($1, ((select * from "user_count") = 0)) 
returning "admin"