typeorm: SoftDelete in relationships cascade does not work

Immediately after posting I discovered that if I include the relations in my original query then the cascade deletes will work.

For example, the following did not soft delete the children:

const parent = await repo.findOneOrFail({ id })               
await repo.softRemove(parent)         

Then by adding the relations it did:

const parent = await repo.findOneOrFail({ id }, {
    relations: ['children']
}) 
repo.softRemove(parent)

My entities are as follows:

// Parent
  @OneToMany(
    (type) => Child, 
    (joint) => child.parent,
    { cascade: true }
  )
  public children: Child[];

// Child
  @ManyToOne(
    (type) => Parent, 
    (parent) => parent.children
  )
  public parent!: Parent;

Also to note, the only way to get the cascade to work properly is to use softRemove and pass the entire entity in with all children. e.g. repo.softRemove(parent) where parent contains all children. I suppose this makes sense, otherwise the softRemove method would have to perform additional queries for all children and nested children, but the behavior isn’t very intuitive or helpful seeing as one could simply query the children separately and pass them to repo.softRemove().

To complement @manymikes answer, I was able to solve this issue put a cascade: true in my OneToOne decorator with the onDelete: "CASCADE" option.

@OneToOne(() => User, { onDelete: "CASCADE", cascade: true })

@johncmunson You need to add the relations key to your findOne options just as @mikehroth did in his example.

const darkRoastCategory = await this.categoryRepository.findOne({ 
    name: 'Dark Roast' ,
    relations: ['coffees']
})
await this.categoryRepository.softRemove(darkRoastCategory)

Please let me know if this solves your issue

Ok, in your example it works if I change something on parent. In this case, I don’t want to change anything except deletedAt which is done by softRemove().

It’s a part of a feature that receive an event when a guild leave, find if settings for this guild exist, if yes softRemove it and softremove all guild’s members settings too.

Because softRemove() change something on the parent (the deletedAt column), I was thinking this will propagate the fact that parent is softRemoved and childrens need to be softRemoved too.

@woobottle I don’t think it makes sense logically to put both cascade and onDelete to your parent entity. They are 2 different things.

• The cascade option allows you to save changes applied to children/relation objects whenever you do something on that field within the parent.
  • E.g. if you set cascade: true on a OneToMany relation (parent->children) such as above, then doing parent.children = [new array of children entities] and saving that parent, means the children get updated/inserted/removed automatically (based on what you changed & also what you allow via the cascade option)
• The onDelete (and other onSomething options) should be set on the children (the Many side of OneToMany) and dictates what happens with the children when something happens to the parent
  • E.g. setting onDelete: 'CASCADE' on the child entity would delete those related children if the parent gets deleted.

I’m not really sure how (or why) onDelete: 'CASCADE' would work on the parent side of the relation. What would be expected? If you delete any of the children the parent gets deleted too? (and then the rest of children as well?)

first. set cascade and delete in entity second. find with relations and soft remove this it will work i will add my code

// users.entity

@OneToMany(() => Comment, (comment) => comment.user, {
    cascade: true,
    onDelete: 'CASCADE',
  })
  comments: Comment[];

  @OneToMany(() => UsersPress, (usersPress) => usersPress.user, {
    cascade: true,
    onDelete: 'CASCADE',
  })
  user_presses: UsersPress[];

// soft remove part

const user = await this.usersRepository.findOne({
      where: { id },
      relations: [
        'comments',
        'user_presses',
        'likes',
        'notices',
        'dislikes',
        'usersShareParagraphs',
      ],
    });
    await this.usersRepository.softRemove(user);

first. set cascade and delete in entity second. find with relations and soft remove this it will work i will add my code

// users.entity

@OneToMany(() => Comment, (comment) => comment.user, {
    cascade: true,
    onDelete: 'CASCADE',
  })
  comments: Comment[];

  @OneToMany(() => UsersPress, (usersPress) => usersPress.user, {
    cascade: true,
    onDelete: 'CASCADE',
  })
  user_presses: UsersPress[];

// soft remove part

const user = await this.usersRepository.findOne({
      where: { id },
      relations: [
        'comments',
        'user_presses',
        'likes',
        'notices',
        'dislikes',
        'usersShareParagraphs',
      ],
    });
    await this.usersRepository.softRemove(user);

This works but why do we need to find with relations and then do the softRemove? Could it be possible to soft delete records without finding them first? Maybe @pleerock can help here!

Thanks in advance.

Thank you @Alkadian. I have not had a chance to try out @mikehroth’s solution. But, assuming it does work, it’s still problematic because the parent entity is required to have too much knowledge of it’s children. Think about how the relationship works in normal SQL…

CREATE TABLE coffee (
    ...
    category_id integer REFERENCES category ON DELETE CASCADE,
    ...
);

It’s the child that gets to dictate what happens when the parent is deleted, and not the other way around.

With some effort, I was able to come up with my own soft delete solution that preserves the proper parent/child semantics. Basically, it involved creating a base repository that all other entities inherit from. The base repo contains a softDelete method and a restore method, each of which emit an event when they are called. This concept was combined with a custom @OnSoftDelete decorator that registers an event listener.

That way, the child entities can decorate their foreign keys if they want to opt-in to soft deletes.

export class Coffee {
  ...
  @OnSoftDelete(
    references: Category,
    strategy: 'CASCADE', // 'SET_NULL' is also a valid option
  )
  @Column()
  categoryId: number;
  ...
}

Unfortunately, I wasn’t able to utilize TypeORM’s built-in event system for this feature, and had to roll my own events and listeners. This is because the TypeORM event system has some very questionable design decisions baked in and is very inconsistent.

Without cascade, your change about the related objects will not be saved.

Working as expected

@Entity('User')
export class User implements IUser {
	
	@DeleteDateColumn()
	delete_date: string;

	@BeforeSoftRemove()
	updateInactive(){
		console.debug('BeforeSoftRemove for user');
	}
}

@Entity('Shelter')
export class Shelter implements IShelter{
	@OneToMany(() => Pet, (pet) => pet.shelter, {
		cascade: true
	})
	pets: Pet[];

	@OneToOne(() => User, { cascade: true})
	@JoinColumn()
	user: User;

	@DeleteDateColumn()
	delete_date: string;

	@BeforeSoftRemove()
	updateInactive(){
		console.debug('BeforeSoftRemove for shelter');
	}
}

@Entity('Pet')
export class Pet implements IPet{
	@DeleteDateColumn()
	delete_date: string;

	@ManyToOne(() => Shelter, (shelter) => shelter.pets, {
		onDelete: 'CASCADE',
		orphanedRowAction: 'delete', 
		nullable: false
	})
	shelter: Shelter;
}

softdelete

const qbSelect = this.repository.createQueryBuilder('shelter')
	.select()
	.leftJoinAndSelect('shelter.pets', 'pets')
	.leftJoinAndSelect('shelter.user', 'user')
	.where({ id: id});

const entity = await qbSelect.getOneOrFail();

console.debug(entity);

const result = await this.repository.softRemove(entity);

console.debug(result);

Log

Shelter {
  id: '6491151a-2738-48e6-9d92-9b5ec16572b6',
  userId: 'fd0163e0-4091-4475-8b0b-258339d73b68',
  inactive: true,
  delete_date: null,
  pets: [
    Pet {
      id: '0893f3c9-cca7-48e8-9aab-aa3aef827802',
      name: 'pet1',
      age: 3,
      age_unit: 'y',
      size_variety: 'xs',
      type: 'cat',
      adopted: false,
      photo: null,
      create_date: 2023-04-27T21:28:15.453Z,
      update_date: 2023-04-28T15:36:49.442Z,
      delete_date: null,
      shelterId: '6491151a-2738-48e6-9d92-9b5ec16572b6'
    },
    Pet {
      id: 'e75c332f-cd21-483d-b834-8a0838aa60ff',
      name: 'pet2',
      age: 2,
      age_unit: 'y',
      size_variety: 'm',
      type: 'dog',
      adopted: false,
      photo: null,
      create_date: 2023-04-28T01:40:13.228Z,
      update_date: 2023-04-28T15:36:49.442Z,
      delete_date: null,
      shelterId: '6491151a-2738-48e6-9d92-9b5ec16572b6'
    },
    Pet {
      id: '2cb45194-ef7e-4d7e-91aa-42c4da03b7af',
      name: 'pet3',
      age: 3,
      age_unit: 'y',
      size_variety: 'xl',
      type: 'dog',
      adopted: false,
      photo: null,
      create_date: 2023-04-28T15:38:09.118Z,
      update_date: 2023-04-28T15:38:09.118Z,
      delete_date: null,
      shelterId: '6491151a-2738-48e6-9d92-9b5ec16572b6'
    }
  ],
  user: User {
    id: 'fd0163e0-4091-4475-8b0b-258339d73b68',
    role: 'shelter',
    status: null,
    email: 'abrigo1@mail.com',
    password: '$2b$10$RU/bRVDUu7voypHRK4ppzOZavshLvdaDrOj1NlDPGOY1KPDFFlgNG',
    name: 'abrigo1',
    phone: '11888888888',
    city: null,
    state: null,
    delete_date: null
  }
}

BeforeSoftRemove for shelter
BeforeSoftRemove for user
Shelter {
  id: '6491151a-2738-48e6-9d92-9b5ec16572b6',
  userId: 'fd0163e0-4091-4475-8b0b-258339d73b68',
  inactive: true,
  delete_date: 2023-04-28T21:56:02.948Z,
  pets: [
    Pet {
      id: '0893f3c9-cca7-48e8-9aab-aa3aef827802',
      name: 'pet1',
      age: 3,
      age_unit: 'y',
      size_variety: 'xs',
      type: 'cat',
      adopted: false,
      photo: null,
      create_date: 2023-04-27T21:28:15.453Z,
      update_date: 2023-04-28T21:56:02.948Z,
      delete_date: 2023-04-28T21:56:02.948Z,
      shelterId: '6491151a-2738-48e6-9d92-9b5ec16572b6'
    },
    Pet {
      id: 'e75c332f-cd21-483d-b834-8a0838aa60ff',
      name: 'pet2',
      age: 2,
      age_unit: 'y',
      size_variety: 'm',
      type: 'dog',
      adopted: false,
      photo: null,
      create_date: 2023-04-28T01:40:13.228Z,
      update_date: 2023-04-28T21:56:02.948Z,
      delete_date: 2023-04-28T21:56:02.948Z,
      shelterId: '6491151a-2738-48e6-9d92-9b5ec16572b6'
    },
    Pet {
      id: '2cb45194-ef7e-4d7e-91aa-42c4da03b7af',
      name: 'pet3',
      age: 3,
      age_unit: 'y',
      size_variety: 'xl',
      type: 'dog',
      adopted: false,
      photo: null,
      create_date: 2023-04-28T15:38:09.118Z,
      update_date: 2023-04-28T21:56:02.948Z,
      delete_date: 2023-04-28T21:56:02.948Z,
      shelterId: '6491151a-2738-48e6-9d92-9b5ec16572b6'
    }
  ],
  user: User {
    id: 'fd0163e0-4091-4475-8b0b-258339d73b68',
    role: 'shelter',
    status: null,
    email: 'abrigo1@mail.com',
    password: '$2b$10$RU/bRVDUu7voypHRK4ppzOZavshLvdaDrOj1NlDPGOY1KPDFFlgNG',
    name: 'abrigo1',
    phone: '11888888888',
    city: null,
    state: null,
    delete_date: 2023-04-28T21:56:02.948Z
  }
}

}

Thank you @Alkadian. I have not had a chance to try out @mikehroth’s solution. But, assuming it does work, it’s still problematic because the parent entity is required to have too much knowledge of it’s children. Think about how the relationship works in normal SQL…

CREATE TABLE coffee (
    ...
    category_id integer REFERENCES category ON DELETE CASCADE,
    ...
);

It’s the child that gets to dictate what happens when the parent is deleted, and not the other way around.

With some effort, I was able to come up with my own soft delete solution that preserves the proper parent/child semantics. Basically, it involved creating a base repository that all other entities inherit from. The base repo contains a softDelete method and a restore method, each of which emit an event when they are called. This concept was combined with a custom @OnSoftDelete decorator that registers an event listener.

That way, the child entities can decorate their foreign keys if they want to opt-in to soft deletes.

export class Coffee {
  ...
  @OnSoftDelete(
    references: Category,
    strategy: 'CASCADE', // 'SET_NULL' is also a valid option
  )
  @Column()
  categoryId: number;
  ...
}

Unfortunately, I wasn’t able to utilize TypeORM’s built-in event system for this feature, and had to roll my own events and listeners. This is because the TypeORM event system has some very questionable design decisions baked in and is very inconsistent.

@johncmunson could you provide a sample code for the listeners and event emitters?

Have same issue with postgres too

first. set cascade and delete in entity second. find with relations and soft remove this it will work i will add my code

// users.entity

@OneToMany(() => Comment, (comment) => comment.user, {
    cascade: true,
    onDelete: 'CASCADE',
  })
  comments: Comment[];

  @OneToMany(() => UsersPress, (usersPress) => usersPress.user, {
    cascade: true,
    onDelete: 'CASCADE',
  })
  user_presses: UsersPress[];

// soft remove part

const user = await this.usersRepository.findOne({
      where: { id },
      relations: [
        'comments',
        'user_presses',
        'likes',
        'notices',
        'dislikes',
        'usersShareParagraphs',
      ],
    });
    await this.usersRepository.softRemove(user);

It is not working in my case: (node:29864) UnhandledPromiseRejectionWarning: Error: Cannot query across one-to-many for property ITEMS)

And setting cascade on both entities throws an error too. Can be only set on the parent

For cascade: true, when we soft delete the parent, we also soft delete the child. Is there a way we can soft delete the parent and cascade to hard delete its children?

Given the following two entities, Category and Coffee…

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

  @Column()
  name: string;

  @DeleteDateColumn()
  deletedDate: Date;

  @OneToMany(
    () => Coffee, coffee => coffee.category,
    {cascade: ['soft-remove']} // I also tried {cascade: true}
  )
  coffees: Coffee[];
}

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

  @Column()
  name: string;

  @Column()
  brand: string;

  @DeleteDateColumn()
  deletedDate: Date;

  @ManyToOne(
    () => Category, category => category.coffees, {onDelete: 'CASCADE'}
  )
  category: Category;
}

And given the following code…

const darkRoastCategory = await this.categoryRepository.findOne({ name: 'Dark Roast' })
await this.categoryRepository.softRemove(darkRoastCategory)

I would expect for the deletedDate field to be updated for the ‘Dark Roast’ Category, and for all of the child Coffee entities that have a Category of “Dark Roast”.

However, this is not what happens. The Category entity is the only thing that gets soft deleted.

Therefore, what the heck is the point of {cascade: ['soft-remove']}??

Would love it if someone were able to point out something I’m doing wrong in my example, but as it stands… it definitely seems like the soft delete functionality in TypeORM is not yet fully baked, especially cascading soft deletes.