SQL Delete

You can delete all rows in the table:

await db.delete(users);

And you can delete with filters and conditions:

await db.delete(users).where(eq(users.name, 'Dan'));

Limit

PostgreSQL

MySQL

SQLite

SingleStore

MSSQL

CockroachDB

Use .limit() to add limit clause to the query - for example:

await db.delete(users).where(eq(users.name, 'Dan')).limit(2);

delete from "users" where "users"."name" = $1 limit $2;

Order By

Use .orderBy() to add order by clause to the query, sorting the results by the specified fields:

import { asc, desc } from 'drizzle-orm';

await db.delete(users).where(eq(users.name, 'Dan')).orderBy(users.name);
await db.delete(users).where(eq(users.name, 'Dan')).orderBy(desc(users.name));

// order by multiple fields
await db.delete(users).where(eq(users.name, 'Dan')).orderBy(users.name, users.name2);
await db.delete(users).where(eq(users.name, 'Dan')).orderBy(asc(users.name), desc(users.name2));

delete from "users" where "users"."name" = $1 order by "name";
delete from "users" where "users"."name" = $1 order by "name" desc;

delete from "users" where "users"."name" = $1 order by "name", "name2";
delete from "users" where "users"."name" = $1 order by "name" asc, "name2" desc;

Returning

PostgreSQL

SQLite

MySQL

SingleStore

MSSQL

CockroachDB

You can delete a row and get it back in PostgreSQL and SQLite:

const deletedUser = await db.delete(users)
  .where(eq(users.name, 'Dan'))
  .returning();

// partial return
const deletedUserIds: { deletedId: number }[] = await db.delete(users)
  .where(eq(users.name, 'Dan'))
  .returning({ deletedId: users.id });

Output

MSSQL

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

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

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

WITH DELETE clause

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

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

const averageAmount = db.$with('average_amount').as(
  db.select({ value: sql`avg(${orders.amount})`.as('value') }).from(orders)
);

const result = await db
	.with(averageAmount)
	.delete(orders)
	.where(gt(orders.amount, sql`(select * from ${averageAmount})`))
	.returning({
		id: orders.id
	});

with "average_amount" as (select avg("amount") as "value" from "orders") 
delete from "orders" 
where "orders"."amount" > (select * from "average_amount") 
returning "id"