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

This is how you upsert with onConflictDoUpdate, you can specify set and where clauses.

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

Upsert with where clause for onConflictDoUpdate:

await db.insert(users)
  .values({ id: 1, name: 'John' })
  .onConflictDoUpdate({
    target: users.id,
    set: { name: 'John1' },
    where: sql`${users.createdAt} > '2023-01-01'::date`,
  });

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