Delete Queries

Delete data from your database using Drizzle ORM’s type-safe delete query builder with filtering and returning capabilities.

Drizzle ORM provides a type-safe API for deleting records from your database tables.

Basic Delete

Delete rows with a where clause:

import { db } from './db';
import { users } from './schema';
import { eq } from 'drizzle-orm';

const result = await db.delete(users)
  .where(eq(users.id, 1));

Always include a where() clause unless you intentionally want to delete all rows in the table.

Delete with Multiple Conditions

Combine conditions to delete specific rows:

import { and, eq, lt } from 'drizzle-orm';

// Delete inactive users created before a certain date
await db.delete(users)
  .where(and(
    eq(users.active, false),
    lt(users.createdAt, new Date('2020-01-01'))
  ));

Delete with OR Conditions

Delete rows matching any of several conditions:

import { or, eq } from 'drizzle-orm';

// Delete users with specific roles
await db.delete(users)
  .where(or(
    eq(users.role, 'guest'),
    eq(users.role, 'trial')
  ));

Delete with Returning

Get the deleted row(s) back from the database:

Return All Columns

const [deletedUser] = await db.delete(users)
  .where(eq(users.id, 1))
  .returning();

console.log('Deleted:', deletedUser);

.returning() is supported in PostgreSQL, SQLite, and MySQL 8.0+. It’s not available in older MySQL versions.

Delete by ID

Common pattern for deleting by primary key:

// Delete single user by id
await db.delete(users)
  .where(eq(users.id, 1));

// Delete and return
const [deleted] = await db.delete(users)
  .where(eq(users.id, 1))
  .returning();

Delete Multiple Rows

Delete rows using array of values:

import { inArray } from 'drizzle-orm';

// Delete multiple users by their IDs
await db.delete(users)
  .where(inArray(users.id, [1, 2, 3, 4, 5]));

// Delete users with specific emails
const emails = ['[e-mail]', '[e-mail]'];
await db.delete(users)
  .where(inArray(users.email, emails));

Delete All Rows

Delete every row in a table (use with extreme caution):

// Delete all users - BE VERY CAREFUL!
await db.delete(users);

Omitting the where() clause deletes ALL rows in the table. This operation cannot be undone. Always double-check before running such queries.

Delete with Pattern Matching

Delete based on pattern matching:

import { like, ilike } from 'drizzle-orm';

// Delete users with temporary email addresses
await db.delete(users)
  .where(like(users.email, '%+temp@%'));

// Case-insensitive pattern match
await db.delete(posts)
  .where(ilike(posts.title, '%draft%'));

Delete with Null Checks

Delete based on null values:

import { isNull, isNotNull } from 'drizzle-orm';

// Delete users without email verification
await db.delete(users)
  .where(isNull(users.emailVerifiedAt));

// Delete only rows with specific data
await db.delete(sessions)
  .where(isNotNull(sessions.expiresAt));

Delete with Comparison Operators

Delete using various comparison operators:

Greater Than / Less Than

import { gt, lt, lte } from 'drizzle-orm';

// Delete old sessions
await db.delete(sessions)
  .where(lt(sessions.expiresAt, new Date()));

// Delete high-price items
await db.delete(products)
  .where(gt(products.price, 1000));

Delete with Subqueries

Delete using subquery conditions:

import { sql } from 'drizzle-orm';

// Delete users who have no posts
await db.delete(users)
  .where(sql`NOT EXISTS (
    SELECT 1 FROM ${posts} 
    WHERE ${posts.userId} = ${users.id}
  )`);

// Delete inactive accounts
await db.delete(users)
  .where(sql`${users.id} IN (
    SELECT user_id FROM inactive_accounts
  )`);

Soft Delete Pattern

Instead of deleting, mark records as deleted:

// Don't actually delete - just mark as deleted
await db.update(users)
  .set({ 
    deletedAt: new Date(),
    active: false,
  })
  .where(eq(users.id, 1));

// Query only non-deleted records
const activeUsers = await db.select()
  .from(users)
  .where(isNull(users.deletedAt));

Soft deletes preserve data and maintain referential integrity while allowing easy recovery.

Delete in Transactions

Delete within a transaction for consistency:

await db.transaction(async (tx) => {
  // Delete user's posts first
  await tx.delete(posts)
    .where(eq(posts.userId, 1));

  // Then delete the user
  await tx.delete(users)
    .where(eq(users.id, 1));

  // If any operation fails, all are rolled back
});

Cascade Deletes

Handle related data when deleting:

Manual Cascade

// Manually delete related records
await db.transaction(async (tx) => {
  const userId = 1;
  
  // Delete user's comments
  await tx.delete(comments)
    .where(eq(comments.userId, userId));
  
  // Delete user's posts
  await tx.delete(posts)
    .where(eq(posts.userId, userId));
  
  // Finally delete the user
  await tx.delete(users)
    .where(eq(users.id, userId));
});

Conditional Delete Logic

Build dynamic delete queries:

const filters = {
  inactive: true,
  oldDate: new Date('2020-01-01'),
};

const conditions = [];
if (filters.inactive) {
  conditions.push(eq(users.active, false));
}
if (filters.oldDate) {
  conditions.push(lt(users.createdAt, filters.oldDate));
}

if (conditions.length > 0) {
  await db.delete(users)
    .where(and(...conditions));
}

Delete with Limits (MySQL)

Limit the number of rows deleted:

// MySQL only: delete limited number of rows
await db.delete(users)
  .where(eq(users.active, false))
  .limit(100);

LIMIT in DELETE is MySQL-specific. PostgreSQL and SQLite don’t support this directly.

Type Safety

Drizzle ensures type safety for deletes:

await db.delete(users)
  .where(eq(users.id, 1));
  // TypeScript ensures column names are valid
  // and types match

Common Delete Patterns

1. Archive Before Delete

   await db.transaction(async (tx) => {
     // Copy to archive
     const [user] = await tx.select()
       .from(users)
       .where(eq(users.id, 1));
     
     await tx.insert(archivedUsers).values(user);
     
     // Then delete
     await tx.delete(users).where(eq(users.id, 1));
   });
   
2. Delete Expired Records

   await db.delete(sessions)
     .where(lt(sessions.expiresAt, new Date()));
   
3. Cleanup Orphaned Records

   await db.delete(posts)
     .where(sql`NOT EXISTS (
       SELECT 1 FROM ${users} 
       WHERE ${users.id} = ${posts.userId}
     )`);
   
4. Delete Duplicates

   await db.delete(users)
     .where(sql`id NOT IN (
       SELECT MIN(id) 
       FROM users 
       GROUP BY email
     )`);
   

Safety Checklist

Always test delete queries in development first

Use transactions when deleting related records

Consider soft deletes for important data

Backup data before running bulk deletes

Use WHERE clauses to avoid accidental mass deletion

Performance Tips

Index Where Columns

Ensure columns in WHERE clauses are indexed for faster deletes

Batch Deletes

Use inArray() to delete multiple rows efficiently

Disable Triggers Temporarily

For bulk deletes, consider temporarily disabling triggers

Vacuum After Large Deletes

Run VACUUM (PostgreSQL) or OPTIMIZE (MySQL) after deleting many rows

Next Steps

Update Queries

Learn about updating data

Select Queries

Query your data

Transactions

Ensure data consistency with transactions