When developing business systems, you’ll almost certainly run into the requirement “use logical (soft) deletes instead of physical deletes.” Writing WHERE deleted_at IS NULL in every query just isn’t practical.
This article organizes implementation patterns for transparent soft deletes using @SQLDelete + @SQLRestriction and @FilterDef, based on Spring Boot 3.x + Hibernate 6.4 + Lombok. We’ll also look at when to use the new @SoftDelete added in Hibernate 6.4, conflicts with unique constraints, restore operations, and recording the deleter — the practical gotchas you’ll hit in real-world projects.
Things to settle before choosing soft delete
Soft delete is an approach that simply flags a record as “deleted” while keeping the actual data. The benefits include recovery from accidental deletion, consistency with audit logs, and protection of related data.
On the other hand, tables tend to bloat, unique constraints become troublesome, and bad index design hurts performance. A practical approach is to use physical deletes for append-only tables like access logs, choosing the strategy per table.
For the column, deleted_at TIMESTAMP is preferable to deleted boolean. Being able to trace “when it was deleted” later is useful both for audits and for restoration. On PostgreSQL, choosing TIMESTAMP WITH TIME ZONE will help you avoid time zone-related troubles.
Which annotation to choose
Hibernate 6 has expanded the options. Let’s sort them out first.
@Wherewas deprecated in Hibernate 6.3. The successor is@SQLRestriction— the functionality is the same, only the annotation name changed.@SoftDeleteis a dedicated annotation introduced in Hibernate 6.4 that auto-generates behavior assuming a boolean flag. For new projects that can use a boolean column, it’s the shortest path. However, it can’t keep the deletion timestamp, so it’s not suitable when you have audit requirements.- For projects that want to “keep the deletion timestamp” or “match an existing table design,” the combination of
@SQLDelete + @SQLRestrictionis still the reliable choice.
If you’re starting fresh with @SoftDelete, you can write it like this:
@Entity
@SoftDelete(columnName = "deleted", strategy = SoftDeleteType.DELETED)
public class Article { /* omitted */ }Setting strategy to ACTIVE reverses the truth value, and you can customize the column name and values. If you want to use something other than boolean, you’re better off going with @SQLDelete + @SQLRestriction. This article assumes existing projects, so from here on we focus on @SQLDelete + @SQLRestriction.
Minimal entity example
The base User entity. It uses Lombok’s @Getter/@Setter.
@Entity
@Table(name = "users")
@Getter
@Setter
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String email;
@Column(nullable = false)
private String name;
@Column(name = "deleted_at")
private LocalDateTime deletedAt;
}The corresponding DDL (assuming PostgreSQL):
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
deleted_at TIMESTAMP WITH TIME ZONE NULL
);Making it transparent with @SQLDelete + @SQLRestriction
@SQLDelete (org.hibernate.annotations.SQLDelete) is an annotation that replaces the DELETE statement Hibernate would issue with an arbitrary SQL statement. @SQLRestriction (same package, formerly @Where) defines a WHERE clause that is always appended on SELECT.
@Entity
@Table(name = "users")
@SQLDelete(sql = "UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?")
@SQLRestriction("deleted_at IS NULL")
public class User {
// omitted
}The important thing here is the specification that Hibernate automatically binds only the identifier (and the version if @Version is present) to the ? in @SQLDelete. You can’t feed values of arbitrary properties like deletedAt or deletedBy into ?. In the SET clause, either write a literal (CURRENT_TIMESTAMP, etc.) directly, or switch to the @Modifying UPDATE pattern described later. If you use composite keys or optimistic locking, the bind order and count change, so define it after checking the actual SQL with spring.jpa.show-sql=true.
The execution log for userRepository.deleteById(1L) looks like this:
update users set deleted_at = current_timestamp where id = ?
select u.id, u.email, u.name from users u where u.deleted_at is null and u.id = ?@SQLRestriction applies to JPQL and Criteria API, but does not apply to native queries. This is an important property you can leverage when writing restore queries.
Use @FilterDef together if you want to see deleted records
Since @SQLRestriction is always on and can’t be turned off, you’ll hit trouble when you want to view a “deleted records list” on an admin screen. To switch it dynamically, combine it with @FilterDef.
@Entity
@Table(name = "users")
@SQLDelete(sql = "UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?")
@FilterDef(name = "activeOnly")
@Filter(name = "activeOnly", condition = "deleted_at IS NULL")
public class User { /* omitted */ }The scope at which a filter is enabled is per Hibernate Session (the EntityManager internally holds a Session). Typically, treat it as active within the same transaction, or for an entire request if OSIV is enabled.
@Service
@RequiredArgsConstructor
public class UserQueryService {
private final EntityManager em;
private final UserRepository userRepository;
public List<User> findActiveUsers() {
em.unwrap(Session.class)
.enableFilter("activeOnly");
return userRepository.findAll();
}
public List<User> findAllIncludingDeleted() {
em.unwrap(Session.class).disableFilter("activeOnly");
return userRepository.findAll();
}
}To use a parameterized @FilterDef, set the value with setParameter("name", value) on the return value of enableFilter. Note that @Filter, like @SQLRestriction, only targets JPQL/Criteria and does not apply to native queries. If you don’t have restore or admin-screen requirements, @SQLRestriction alone is sufficient.
Avoiding conflicts with unique constraints
The biggest pitfall with soft deletes is unique constraints. If email has a UNIQUE constraint, you can’t re-register with the same email address.
On PostgreSQL, a partial index is the most natural solution.
CREATE UNIQUE INDEX users_email_active_idx
ON users (email)
WHERE deleted_at IS NULL;On PostgreSQL 15 and later, you can also use NULLS NOT DISTINCT. With this, a composite UNIQUE on email + deleted_at treats NULLs as duplicates of each other, narrowing undeleted records to one.
MySQL doesn’t support partial indexes, but in version 8 you can achieve the equivalent with a generated column + UNIQUE.
ALTER TABLE users
ADD email_active VARCHAR(255)
GENERATED ALWAYS AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL,
ADD UNIQUE KEY uk_users_email_active (email_active);Since MySQL treats NULLs as “different” with respect to UNIQUE, setting email_active to NULL for deleted rows prevents them from being treated as duplicates.
Fetching and restoring deleted data
When you want to see deleted records too, native queries — which @SQLRestriction doesn’t affect — are the shortest path.
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM users WHERE id = :id", nativeQuery = true)
Optional<User> findByIdIncludingDeleted(@Param("id") Long id);
Optional<User> findByEmailAndDeletedAtIsNull(String email);
@Modifying
@Query(value = "UPDATE users SET deleted_at = NULL WHERE id = :id", nativeQuery = true)
int restore(@Param("id") Long id);
}Before restoring, you can prevent unique constraint violations by adding logic that checks with findByEmailAndDeletedAtIsNull whether an active user with the same email exists.
Considerations for related entities
If you also put @SQLRestriction on the target of a OneToMany association, deleted records will be automatically excluded when child collections are fetched from the parent entity. On the other hand, this means the value of collection.size() no longer matches the physical record count. For aggregations and history processing, it’s safer to re-fetch the count with a native query.
When combining CascadeType.REMOVE on the parent with @SQLDelete, the children need @SQLDelete too. If the child side remains physical-delete, things end up half-baked, so align the deletion strategy per entity. For how to set up associations, see JPA Entity Relationship Mapping, and for fetch-related topics, Spring Data JPA Performance Optimization is a good companion read.
Recording the deleter by combining with Auditing
As mentioned earlier, only the identifier is bound to ? in @SQLDelete, so if you want to record deleted_by (who deleted), you need a different approach. The straightforward way is to provide a @Modifying UPDATE method in the repository that takes the deleter as an argument.
public interface UserRepository extends JpaRepository<User, Long> {
@Modifying
@Query("UPDATE User u SET u.deletedAt = CURRENT_TIMESTAMP, u.deletedBy = :deletedBy WHERE u.id = :id")
int softDeleteById(@Param("id") Long id, @Param("deletedBy") String deletedBy);
}Get the deleter from AuditorAware. To be safe against cases where there’s no authentication info in the SecurityContext or where the user is anonymous, fixing a fallback like SYSTEM is safer.
@Service
@RequiredArgsConstructor
public class UserDeletionService {
private final UserRepository userRepository;
private final AuditorAware<String> auditorAware;
@Transactional
public void delete(Long id) {
String deletedBy = auditorAware.getCurrentAuditor()
.orElse("SYSTEM");
userRepository.softDeleteById(id, deletedBy);
}
}With this pattern, you can keep @SQLDelete on the entity while running soft deletes with deleter information through a service — a two-tier setup. For how to assemble AuditorAware and its relationship with @LastModifiedBy, see How to Automatically Manage Created and Modified Dates with Spring Data JPA Auditing.
Verifying soft deletes with @DataJpaTest
Since soft delete isn’t “it disappears when called” but rather “it gets hidden when called,” it’s worth verifying the behavior works as intended in tests. @DataJpaTest wraps each test in a transaction and rolls back at the end, so you can write tests without worrying about data contamination between them. Clearing the first-level cache with em.flush() and em.clear() before verifying lets you check behavior at the actual SQL level.
@DataJpaTest
class UserRepositorySoftDeleteTest {
@Autowired UserRepository userRepository;
@Autowired EntityManager em;
@Test
void deleteすると findByIdは空になる() {
User saved = userRepository.save(newUser("[email protected]"));
userRepository.deleteById(saved.getId());
em.flush();
em.clear();
assertThat(userRepository.findById(saved.getId())).isEmpty();
assertThat(userRepository.findByIdIncludingDeleted(saved.getId())).isPresent();
}
@Test
void restoreすると findByIdで再び取得できる() {
User saved = userRepository.save(newUser("[email protected]"));
userRepository.deleteById(saved.getId());
em.flush();
em.clear();
int updated = userRepository.restore(saved.getId());
em.flush();
em.clear();
assertThat(updated).isEqualTo(1);
assertThat(userRepository.findById(saved.getId())).isPresent();
}
}Summary
Choosing a soft delete approach isn’t that complex. For existing projects, make it transparent with @SQLDelete + @SQLRestriction, and add @FilterDef when you need restore or admin screens. If a boolean column is acceptable in a new project, @SoftDelete becomes a viable option too. For unique constraints, partial indexes (or NULLS NOT DISTINCT) are the first choice on PostgreSQL, and generated column + UNIQUE on MySQL. When you want to record the deleter, don’t rely on ? in @SQLDelete — the realistic approach is @Modifying UPDATE combined with AuditorAware. Align your deletion strategy at the initial design stage.
