How to implement soft delete in TypeORM? Including soft delete configuration, operations, and best practices

Soft delete is a data deletion strategy that doesn't actually delete records from the database but marks them as "deleted" to hide them. TypeORM provides built-in soft delete support, making it simple and powerful to implement soft deletion.

Soft Delete Basic Concepts

What is Soft Delete

Soft delete is a data retention strategy where when deleting records:

• Records are not actually deleted from the database
• Records are only marked as "deleted"
• Queries by default don't include deleted records
• Deleted records can be recovered at any time

Soft Delete vs Hard Delete

Feature	Soft Delete	Hard Delete
Data Retention	Retained in database	Deleted from database
Recoverability	Can be recovered	Cannot be recovered
Storage Space	Occupies storage space	Frees storage space
Query Performance	Needs to filter deleted records	Better query performance
Audit Trail	Preserves deletion history	Cannot track deletion history

TypeORM Soft Delete Implementation

Basic Soft Delete Configuration

typescript
import { 
  Entity, 
  PrimaryGeneratedColumn, 
  Column, 
  DeleteDateColumn 
} from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  // Soft delete column: When record is soft deleted, this column is set to current time
  @DeleteDateColumn()
  deletedAt: Date | null;
}

Using Soft Delete

typescript
import { DataSource } from 'typeorm';

const dataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User],
  synchronize: true,
});

// Soft delete user
await dataSource.manager.softRemove(user);

// Or use Repository
await userRepository.softRemove(user);

// Recover deleted user
await dataSource.manager.recover(user);

// Or use Repository
await userRepository.recover(user);

// Hard delete user (actually delete)
await dataSource.manager.remove(user);

// Or use Repository
await userRepository.remove(user);

Soft Delete Operations

Soft Deleting Records

typescript
// Method 1: Use softRemove
const user = await userRepository.findOne({ where: { id: 1 } });
await userRepository.softRemove(user);

// Method 2: Use softDelete
await userRepository.softDelete(1);

// Method 3: Use QueryBuilder
await userRepository
  .createQueryBuilder()
  .softDelete()
  .where('id = :id', { id: 1 })
  .execute();

// Batch soft delete
await userRepository.softDelete([1, 2, 3]);

Recovering Deleted Records

typescript
// Method 1: Use recover
const user = await userRepository.findOne({ 
  where: { id: 1 },
  withDeleted: true // Include deleted records
});
await userRepository.recover(user);

// Method 2: Use restore
await userRepository.restore(1);

// Method 3: Use QueryBuilder
await userRepository
  .createQueryBuilder()
  .restore()
  .where('id = :id', { id: 1 })
  .execute();

// Batch recover
await userRepository.restore([1, 2, 3]);

Querying Deleted Records

typescript
// Query all records (including deleted)
const allUsers = await userRepository.find({
  withDeleted: true
});

// Query only deleted records
const deletedUsers = await userRepository.find({
  withDeleted: true,
  where: {
    deletedAt: Not(IsNull())
  }
});

// Use QueryBuilder to query deleted records
const deletedUsers = await userRepository
  .createQueryBuilder('user')
  .withDeleted()
  .where('user.deletedAt IS NOT NULL')
  .getMany();

Soft Delete and Relationships

Cascade Soft Delete

typescript
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @DeleteDateColumn()
  deletedAt: Date | null;

  @OneToMany(() => Post, post => post.author, {
    cascade: true, // Cascade operations
  })
  posts: Post[];
}

@Entity()
export class Post {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @DeleteDateColumn()
  deletedAt: Date | null;

  @ManyToOne(() => User, user => user.posts)
  author: User;
}

// Cascade soft delete all posts when soft deleting user
const user = await userRepository.findOne({ 
  where: { id: 1 },
  relations: ['posts']
});
await userRepository.softRemove(user);

Handling Soft Delete When Querying Related Data

typescript
// Query user and their non-deleted posts
const users = await userRepository.find({
  relations: ['posts'],
  where: {
    deletedAt: IsNull() // Only query non-deleted users
  }
});

// Use QueryBuilder to query
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post', 'post.deletedAt IS NULL')
  .where('user.deletedAt IS NULL')
  .getMany();

Advanced Soft Delete Usage

Custom Soft Delete Column

typescript
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  // Use custom column name and type
  @DeleteDateColumn({ 
    name: 'deleted_at',
    type: 'timestamp',
    nullable: true 
  })
  deletedAt: Date | null;

  // Or use boolean flag
  @Column({ 
    name: 'is_deleted', 
    type: 'boolean', 
    default: false 
  })
  isDeleted: boolean;

  @Column({ 
    name: 'deleted_by', 
    type: 'int', 
    nullable: true 
  })
  deletedBy: number | null;
}

Soft Delete Audit

typescript
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @DeleteDateColumn()
  deletedAt: Date | null;

  @Column({ nullable: true })
  deletedBy: number | null; // Deletor ID

  @Column({ type: 'text', nullable: true })
  deleteReason: string | null; // Deletion reason
}

// Record audit information when soft deleting
async function softDeleteWithAudit(userId: number, deletedBy: number, reason: string) {
  const user = await userRepository.findOne({ where: { id: userId } });
  
  if (!user) {
    throw new Error('User not found');
  }

  user.deletedAt = new Date();
  user.deletedBy = deletedBy;
  user.deleteReason = reason;

  await userRepository.save(user);
}

Soft Delete Subscriber

typescript
import { EntitySubscriberInterface, EventSubscriber, SoftRemoveEvent } from 'typeorm';

@EventSubscriber()
export class UserSubscriber implements EntitySubscriberInterface<User> {
  listenTo() {
    return User;
  }

  async beforeSoftRemove(event: SoftRemoveEvent<User>) {
    console.log('Before soft remove user:', event.entity);
    
    // Check if soft delete is allowed
    if (event.entity.hasActiveOrders()) {
      throw new Error('Cannot delete user with active orders');
    }
  }

  async afterSoftRemove(event: SoftRemoveEvent<User>) {
    console.log('After soft remove user:', event.entity);
    
    // Record audit log
    await this.logAudit('SOFT_DELETE', event.entity);
    
    // Send notification
    await this.sendNotification(event.entity);
  }

  async beforeRecover(event: any) {
    console.log('Before recover user:', event.entity);
    
    // Check if recovery is allowed
    if (event.entity.emailConflict()) {
      throw new Error('Cannot recover user due to email conflict');
    }
  }

  async afterRecover(event: any) {
    console.log('After recover user:', event.entity);
    
    // Record recovery log
    await this.logAudit('RECOVER', event.entity);
  }

  private async logAudit(action: string, user: User) {
    // Logic to record audit log
    console.log(`Audit log: ${action} user ${user.id}`);
  }

  private async sendNotification(user: User) {
    // Logic to send notification
    console.log(`Sending notification for user ${user.id}`);
  }
}

Soft Delete and QueryBuilder

Using Soft Delete in QueryBuilder

typescript
// Query non-deleted records (default behavior)
const users = await userRepository
  .createQueryBuilder('user')
  .getMany();

// Query all records (including deleted)
const allUsers = await userRepository
  .createQueryBuilder('user')
  .withDeleted()
  .getMany();

// Query only deleted records
const deletedUsers = await userRepository
  .createQueryBuilder('user')
  .withDeleted()
  .where('user.deletedAt IS NOT NULL')
  .getMany();

// Soft delete records
await userRepository
  .createQueryBuilder('user')
  .softDelete()
  .where('user.id = :id', { id: 1 })
  .execute();

// Recover records
await userRepository
  .createQueryBuilder('user')
  .restore()
  .where('user.id = :id', { id: 1 })
  .execute();

Handling Soft Delete in Complex Queries

typescript
// Query user and their non-deleted posts
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post', 'post.deletedAt IS NULL')
  .where('user.deletedAt IS NULL')
  .getMany();

// Count non-deleted and deleted users
const [activeCount, deletedCount] = await Promise.all([
  userRepository.count({ where: { deletedAt: IsNull() } }),
  userRepository.count({ 
    withDeleted: true,
    where: { deletedAt: Not(IsNull()) }
  })
]);

console.log(`Active users: ${activeCount}, Deleted users: ${deletedCount}`);

Soft Delete Best Practices

1. Use Soft Delete Appropriately

typescript
// ✅ Good: Use soft delete for important data
@Entity()
export class User {
  @DeleteDateColumn()
  deletedAt: Date | null;
}

@Entity()
export class Order {
  @DeleteDateColumn()
  deletedAt: Date | null;
}

// ✅ Good: Use hard delete for temporary data
@Entity()
export class TempFile {
  // Don't use soft delete, hard delete directly
}

// ❌ Bad: Use soft delete for all data
// This causes database bloat and query performance degradation

2. Regularly Clean Up Deleted Records

typescript
// Regularly clean up deleted records older than a certain period
async function cleanupOldDeletedRecords() {
  const retentionDays = 90; // Retain for 90 days
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - retentionDays);

  // Actually delete records older than retention period
  await userRepository
    .createQueryBuilder('user')
    .withDeleted()
    .where('user.deletedAt < :cutoffDate', { cutoffDate })
    .delete()
    .execute();
}

// Execute regularly using scheduled tasks
// setInterval(cleanupOldDeletedRecords, 24 * 60 * 60 * 1000); // Execute daily

3. Handle Unique Constraint Conflicts

typescript
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @DeleteDateColumn()
  deletedAt: Date | null;
}

// After soft deleting user, may need to recover
async function recoverUser(userId: number) {
  const user = await userRepository.findOne({ 
    where: { id: userId },
    withDeleted: true 
  });

  if (!user) {
    throw new Error('User not found');
  }

  // Check if email is already in use
  const existingUser = await userRepository.findOne({ 
    where: { email: user.email } 
  });

  if (existingUser) {
    throw new Error('Email already in use');
  }

  await userRepository.recover(user);
}

4. Soft Delete and Foreign Key Constraints

typescript
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @DeleteDateColumn()
  deletedAt: Date | null;

  @OneToMany(() => Post, post => post.author)
  posts: Post[];
}

@Entity()
export class Post {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @DeleteDateColumn()
  deletedAt: Date | null;

  @ManyToOne(() => User, user => user.posts)
  @JoinColumn({ onDelete: 'SET NULL' }) // Set to NULL on soft delete
  author: User | null;
}

5. Soft Delete and Indexes

typescript
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @DeleteDateColumn()
  @Index() // Add index for soft delete column to improve query performance
  deletedAt: Date | null;
}

// Or use composite index
@Entity()
@Index(['deletedAt', 'createdAt']) // Composite index
export class User {
  @DeleteDateColumn()
  deletedAt: Date | null;

  @CreateDateColumn()
  createdAt: Date;
}

Soft Delete Performance Optimization

1. Use Indexes to Optimize Queries

typescript
@Entity()
export class User {
  @DeleteDateColumn()
  @Index('IDX_USER_DELETED_AT') // Add index for soft delete column
  deletedAt: Date | null;
}

// Querying non-deleted users will use index
const users = await userRepository.find({
  where: { deletedAt: IsNull() }
});

2. Avoid Overusing withDeleted

typescript
// ❌ Bad: Always query all records
const users = await userRepository.find({ withDeleted: true });

// ✅ Good: Only query deleted records when needed
const users = await userRepository.find();
const deletedUsers = await userRepository.find({ 
  withDeleted: true,
  where: { deletedAt: Not(IsNull()) }
});

3. Use QueryBuilder to Optimize Complex Queries

typescript
// ✅ Good: Use QueryBuilder to optimize queries
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.deletedAt IS NULL')
  .orderBy('user.createdAt', 'DESC')
  .limit(10)
  .getMany();

TypeORM's soft delete functionality provides powerful data management capabilities. Proper use of soft delete can protect important data, provide audit trails, and maintain application flexibility. However, attention must be paid to performance impact and regular cleanup strategies.