[Feature Request] Soft Delete

[joeky888]

Use case

• Sometimes we can't always perform hard deletions in production.
• This is what most ORMs do

The Ideal way

• Respect the deleted_at column (pub deleted_at: Option<DateTimeWithTimeZone>)
• All deleted_at checkings should run behind the scenes instead manually adding AND deleted_at IS NULL
• deleted_at columns should be indexed for performance reasons

Delete

let res: DeleteResult = fruit.delete(db).await?; // ID = 10

Should generate the SQL below

-- Soft Delete - If the column deleted_at exists
UPDATE `fruit` SET `deleted_at`="2020-10-29 10:23" WHERE id = 10

-- Hard Delete - If the column deleted_at doesn't exist
DELETE FROM `fruit` WHERE `fruit`.`id` = 10'

Select

let cheese: Option<cake::Model> = Cake::find_by_id(1).one(db).await?;

Should generate the SQL below

-- If the column deleted_at exists
SELECT * FROM cake WHERE id = 1 AND deleted_at IS NULL

-- If the column deleted_at doesn't exist
SELECT * FROM cake WHERE id = 1

Golang GORM

PS: I am a Go developer most of the time.

GORM, the popular ORM for Go, has defined a default struct called gorm.Model.

// gorm.Model definition
type Model struct {
  ID        uint           `gorm:"primaryKey"`
  CreatedAt time.Time
  UpdatedAt time.Time
  DeletedAt gorm.DeletedAt `gorm:"index"`
}

And any struct with gorm.Model in it will automatically become soft-delete aware.

type User struct {
  gorm.Model
  Name       string
  Phone      string
}

If one really needs to perform hard deletion, here's how in GORM:

db.Delete(&order)
// UPDATE orders SET deleted_at="2020-10-29 10:23" WHERE id = 10;

db.Unscoped().Delete(&order)
// DELETE FROM orders WHERE id=10;

[tyt2y3]

Thank you so much for the proposal. And welcome to the Rust community!

Rust libraries tend to be less opinionated or at least offer choices. I am keen to bring this feature into SeaORM.

I also wonder, what is your stance on this? Are you confident in implementing this? If so, we can work together to bring this into reality.

Some rough thoughts off my head, I think we can add an attribute to the DeriveEntityModel macro to have a soft_delete annotation. The model then have to include a deleted_at attribute (or the user can specify a different attribute via another annotation).

Then, the Entity would impl a trait to allow us to automatically include the deleted_at IS NULL on find and alter the behavior of delete.

Let me know your thoughts!

[joeky888]

what is your stance on this?

This must be done for ORMs in my opinion. Currently, there is no rust ORM supports soft delete.

Are you confident in implementing this?

Still learning rust, can't really answer this. (At least not this month.)

Let me know your thoughts!

Like #185 , not only deleted_at , ORM should also respect created_at and updated_at. I am thinking of something like this:

pub struct Model {
    #[sea_orm(primary_key)]
    pub id: u32,
    #[sea_orm(created_at = "created_at")]
    pub created_at: DateTimeWithTimeZone,
    #[sea_orm(updated_at = "updated_at")]
    pub updated_at: DateTimeWithTimeZone,
    #[sea_orm(deleted_at = "deleted_at")]
    pub deleted_at: Option<DateTimeWithTimeZone>,
}

[Ka1wa]

I'd be willing to help out on this feature request. I haven't fully dug into the code yet and would require some pointers probably.

[billy1624]

We can just implement soft delete for now and leave the created_at & updated_at for another PR.

Got a rough idea below on the implementation plan

1. Add deleted_at macro attribute.

   pub struct Model {
       // ...
       #[sea_orm(deleted_at)]
       pub deleted_at: Option<DateTimeWithTimeZone>,
   }

2. Store whether soft delete is enabled and its column name in ActiveModelTrait. Define the extra metadata inside derive macro given that the deleted_at macro attribute is tagged on some fields on Model struct.

3. Update existing CRUD logics & conditions

   • Read: return records with deleted_at = NULL
   • Insert: insert NULL
   • Update: update records with deleted_at = NULL
   • Delete: soft delete records

4. Unit testing it!

[Ka1wa]

Agree on doing the pull requests seperately, I'll read up on the macros tonight and if I have any more questions I will follow up!

[tyt2y3]

I think 1) would be the most difficult if not familiar with derive macro already.

Let us know if you need help on that. Feel free to reach out on Discord.

[Ka1wa]

So I've been reading a bit and so far came up with this: https://github.com/Ka1wa/sea-orm/commit/95c66e499d7d1b6de7189c6f2685c4217536f0af

Not sure about line 269~270 from src/entity/column.rs.

I figured the next logical step would be to work on the ActiveModelTrait, I checked out the DeriveEntityModel macro and ended up at derive_active_model -> derives::expand_derive_active_model which has the fields iterator which I assume would contain the deleted_at column.

How would I check if this column exists and where would I store that metadata to be used in the query logic?

[billy1624]

Wow, Thanks!!

I think we can store deleted_at info inside ActiveModelTrait::delete_at() function. And leave the src/entity/column.rs untouched.

//  src/entity/active_model.rs
pub trait ActiveModelTrait {
    // Return `None` if soft delete is not enabled
    fn deleted_at() -> Option<<Self::Entity as EntityTrait>::Column> { None }
}

Then we can implement the new deleted_at function inside proc macros

// sea-orm-macros/src/derives/active_model.rs
impl sea_orm::ActiveModelTrait for ActiveModel {
    type Entity = Entity;

    fn deleted_at() -> Option<<Self::Entity as EntityTrait>::Column> { ... }
}

[tqwewe]

I like the idea of adding it to ActiveModelTrait. But I'm thinking if there's a way to make this more generic rather than just for deleted_at.

I'm not sure if it's possible, but perhaps ActiveModelTrait could have a function instead like:

fn before_execute(&mut self, &mut query);

So it could be added with:

impl ActiveModelTrait {
    fn before_execute(&mut self, &mut query) {
        if query.is_select() {
            query.filter(Column::DeletedAt.not_null());
        } else if query.is_delete() {
            // ...
        }
    }
}

And SeaORM could export a helper function: fn soft_delete<C: ColumnTrait>(col: C, &mut query):

impl ActiveModelTrait {
    fn before_execute(&mut self, &mut query) {
        sea_orm::before_execute::soft_delete(Column::DeletedAt, query);
    }
}

[Ka1wa]

Actually quite like that idea, I've been reading up on the syn crate because looking at the macros code I'm honestly a bit out of my depth on that topic. I will probably be able to take another look over the weekend.

[billy1624]

Hey @Ka1wa, feel free to ask questions if you need help

[tqwewe]

Actually quite like that idea, I've been reading up on the syn crate because looking at the macros code I'm honestly a bit out of my depth on that topic. I will probably be able to take another look over the weekend.

I have got into syn over the past few weeks. It's not as intimidating as it seems. If you want to ask any questions you can feel free to ping me in the Discord and I'd be happy to help out with anything I can :)

[billy1624]

Now I'm think how do we handle joins? Perhaps we should include deleted_at IS NULL when joining table with soft delete enabled.

E.g.

SELECT `filling`.`id`, `filling`.`name`, `filling`.`vendor_id` FROM `filling`
INNER JOIN `cake_filling` ON `cake_filling`.`filling_id` = `filling`.`id` AND `cake_filling`.`deleted_at` IS NULL
INNER JOIN `cake` ON `cake`.`id` = `cake_filling`.`cake_id` AND `cake`.`deleted_at` IS NULL
WHERE `filling`.`deleted_at` IS NULL

[billy1624]

Hey everyone, the soft delete PR is ready for comments and review

• https://github.com/SeaQL/sea-orm/pull/790

[crypto-hacker]

maybe you can implement it like this:

trait QrySoftDel<E: EntityTrait> {
    async fn qry_all<C: ConnectionTrait>(self, db: &C) -> Result<Vec<E::Model>, DbErr>;
}

trait UptSoftDel<E: EntityTrait> {
    async fn upt_all<C: ConnectionTrait>(self, db: &C) -> Result<UpdateResult, DbErr>;
}

trait DelSoftDel<E: EntityTrait> {
    async fn del_all<C: ConnectionTrait>(self, db: &C) -> Result<UpdateResult, DbErr>;
}

impl<E: EntityTrait> QrySoftDel<E> for Select<E> {
    async fn qry_all<C: ConnectionTrait>(self, db: &C) -> Result<Vec<E::Model>, DbErr> {
        self.filter(Expr::cust("deleted_at is null")).all(db).await
    }
}

impl<E: EntityTrait> UptSoftDel<E> for UpdateMany<E> {
    async fn upt_all<C: ConnectionTrait>(self, db: &C) -> Result<UpdateResult, DbErr> {
        self.filter(Expr::cust("deleted_at is null")).exec(db).await
    }
}

impl<E: EntityTrait> DelSoftDel<E> for UpdateMany<E> {
    async fn del_all<C: ConnectionTrait>(self, db: &C) -> Result<UpdateResult, DbErr> {
        self.exec(db).await
    }
}

trait BaseRepo<T: EntityTrait> {
    fn field(&self) -> impl ColumnTrait;

    fn qry(&self) -> Select<T> {
        T::find()
    }

    fn upt(&self) -> UpdateMany<T> {
        T::update_many()
    }

    fn del(&self) -> UpdateMany<T> {
        T::update_many().col_expr(self.field(), Expr::cust("now()"))
    }
}

pub struct DocRepoImpl<'a> {
    conn: Connection<'a>,
}

impl<'a> BaseRepo<doc::Entity> for DocRepoImpl<'a> {
    fn field(&self) -> impl ColumnTrait {
        doc::Column::DeletedAt
    }
}

impl<'a> DocRepoImpl<'a> {
    pub fn new() -> Self {
        let conn = Connection::DatabaseConnection(get_db());
        Self { conn }
    }

    pub async fn query(&self) {
        let sql = self.qry().qry_all(&self.conn).await;
        println!("{:?}", sql);
    }

    pub async fn update(&self) {
        let sql = self.upt().col_expr(doc::Column::Type, Expr::cust("222222")).upt_all(&self.conn).await;
        println!("{:?}", sql);
    }

    pub async fn delete(&self) {
        let sql = self.del().filter(doc::Column::Id.eq(1)).del_all(&self.conn).await;
        println!("{:?}", sql);
    }
}

[bismitpanda]

Any updates on this? I am using soft delete in my project but currently implementing it by creating a trait which wraps around existing methods (similar to the one above, but with a few differences). I would love to have a soft delete feature at this point.