[ENHANCEMENT] Add before_create, after_create, at_update, on_delete hooks for tables(schemas).

[coder3112]

Issue

Currently when a schema is made, there is no way to run a function at time of creation. Although custom crud functions can be made, it would be better if some kind of functions ran at time of creation, deletion and update of the schemas through a pre_defined meta class.

Minimal Use Case

class User(Table, tablename='my_users'):
    username = Varchar(length=100, unique=True)
    password = Secret(length=255)
    first_name = Varchar(null=True)
    last_name = Varchar(null=True)
    email = Varchar(length=255, unique=True)
    active = Boolean(default=False)
    admin = Boolean(default=False)

class Profile(Table, tablename='foo'):
   uuid = UUID()
   user = ForeignKey(references=User)
   ......

Here, I would want that a function foo_func and foo_func2 be executed when it is deleted, updated or created.

Proposed solution

I can use a function like so:

def foo_func():
   do_something

def foo_func2():
   do_something_else

def foo_func3():
   do_something_interesting

class User(Table, tablename='my_users'):
    username = Varchar(length=100, unique=True)
    password = Secret(length=255)
    first_name = Varchar(null=True)
    last_name = Varchar(null=True)
    email = Varchar(length=255, unique=True)
    active = Boolean(default=False)
    admin = Boolean(default=False)
    
    class Config:
         pre_create = ['foo_func', 'foo_func2']
         post_create = []
         on_update = ['foo_func2']
         on_delete = ['foo_func3']

Now whenever I run INSERT, UPDATE or DELETE, these functions will run accordingly.

PS - I can start working on a PR if you think this should be done

[dantownsend]

@coder3112 It's a nice idea. What kind of things would you run in the functions though?

[coder3112]

These would fundamentally do the same things as pre_save & post_save signals in django.

Example:

class User(Table, tablename='my_users'):
    username = Varchar(length=100, unique=True)
    password = Secret(length=255)
    first_name = Varchar(null=True)
    last_name = Varchar(null=True)
    email = Varchar(length=255, unique=True)
    active = Boolean(default=False)
    admin = Boolean(default=False) 
    
   class Config:
      on_update = ['func']

class Profile(Table, tablename='foo'):
   uuid = UUID()
   user = ForeignKey(references=User)
   active = Boolean(default=True)

def func():
  # Update the value of Profile.active if it has changed.
      

Now if I update User.active to be false, then Profile.active should automatically be false

[dantownsend]

@coder3112 OK, I can see this being useful.

I guess that by having these functions, it's cleaner than overriding the save method for a Table.

I can imagine people putting some validation logic in there.

The limitation with the Django implementation is that it doesn't trigger for bulk updates. The same would have to be true here.

One thing to be aware of is Piccolo doesn't currently use a Meta class as a child of a Table, like in other ORMs. It uses a fairly modern Python feature where you can pass arguments into a class, using __init_subclass__. So the easiest implementation would be:

class User(Table, tablename='my_users', pre_save=[some_function]):
    username = Varchar(length=100, unique=True)
    password = Secret(length=255)
    first_name = Varchar(null=True)
    last_name = Varchar(null=True)
    email = Varchar(length=255, unique=True)
    active = Boolean(default=False)
    admin = Boolean(default=False) 

[coder3112]

Ok, so I can start working on a PR now. I guess it would change the run and run_sync functions.

[dantownsend]

@coder3112 Yeah, sounds good - thanks.

[davidolrik]

@coder3112 You should take a look at what has been implemented in Ormar - They have a very nice concept called "signals" and you can even create your own.

Being able to change fields "pre_update", like dates or passwords would be some of my use cases, and "post_delete" for scheduling cleanup jobs.

[aminalaee]

@davidolrik Maybe it's better to turn this into a discussion.
Although this is very handy, it is sometimes considered an anti-pattern, even django signals can get complicated and it's not obvious how some changes are happening to the model.

I think one workaround to is to implement the signals inside the model itself so it's more contained within the model class, and they I think that can help with better type hints.

class Band(Table):
    name = Varchar(length=100)

    # special classmethods for signals
    @classmethod
    def before_create(cls): ...

    @classmethod
    def before_delete(cls): ...

These methods would be know to piccolo and will be treated as signals.

[devsarvesh92]

Any idea by when this would be implemented?

[dantownsend]

@devsarvesh92 It's still a work in progress. As @aminalaee mentions, it can sometimes be an anti-pattern and be abused. On the other hand, this topic has come up a few times, so there's a desire for it.

For a client project I did this (overriding the save method):

class MyTable(Table):
    def save(self, columns=None):
        instance = self
        save_ = super().save

        class Save:
            async def run(self, *args, **kwargs):
                # do custom logic before saving
                # if instance.some_value ....

                await save_(columns=columns).run(*args, **kwargs)

                # do custom logic after saving
                # if instance.some_value ....

            def run_sync(self, *args, **kwargs):
                return run_sync(self.run(*args, **kwargs))

            def __await__(self):
                return self.run().__await__()

        return Save()

It looks a bit complex, but that's because Piccolo lets you run a query async or sync.

We have hooks in Piccolo Admin which is OK if you just want it for Piccolo Admin.

It wouldn't be that hard for us to let the user specify pre_save and post_save methods on a Table, and if present we call them.

[devsarvesh92]

Thanks @dantownsend 👍

[abelayalew]

is this feature out yet?

[hoosnick]

any updates? @dantownsend @sinisaos

[sinisaos]

@hoosnick There has been no progress on this issue since PR #83 was closed. Since I don't know what the API should be (add these methods to the table meta or something else), I haven't looked into it. Feel free to make a PR if you want. As a workaround, you can override the methods (as @dantownsend showed in previous comments) or you can override all methods this.You can also override methods in BaseTable and then use that table in all other tables (inherit from BaseTable).

[abelayalew]

yes the one example @dantownsend really works well i've done it like this. here's my basic implementations

class BaseTableMixin(Table):
    created_at = Timestamptz()
    updated_at = Timestamptz(default=None, null=True)

    @classmethod
    async def pre_save(cls, instance):
        ...

    @classmethod
    async def post_save(cls, instance):
        ...

    def save(self, columns=None):
        instance = self

        class Save:
            @classmethod
            async def run(cls, *args, **kwargs):
                if not isinstance(self.id, QueryString):
                    instance.updated_at = dtm.datetime.now(dtm.timezone.utc)

                await instance.pre_save(instance)
                await super(BaseTableMixin, instance).save(columns=columns).run(
                    *args, **kwargs
                )
                await instance.post_save(instance)

            @classmethod
            def run_sync(cls, *args, **kwargs):
                return run_sync(cls.run(*args, **kwargs))

            def __await__(self):
                return self.run().__await__()

        return Save()