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

Returning

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

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

// partial return
const deletedUserId = await db.delete(users)
  .where(eq(users.name, "Dan"))
  .returning({ deletedId: users.id });

// deletedUserId: { deletedId: number | null }[]

delete from "users" where "users"."name" = 'Dan' returning "id", "name";

delete from "users" where "users"."name" = 'Dan' returning "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"