Using simple database triggers for complex Django problems

Hey, I'm Wes Kindle, and in this talk I'm going to be talking about using simple database triggers for solving complex Django problems. The main takeaways from this talk, you're going to learn about triggers, which it's a functionality in databases like MySQL and Postgres. You're going to understand how they work. And you're also going to learn how to use them to reliably and succinctly solve many difficult Django problems, problems you may have already attempted to solve in application code. You're also going to learn how to integrate triggers really easily into your Django project using an open source library. And as a quick disclaimer about this talk, you know, before you spend time investing into watching it. Although MySQL does support triggers, I'm only going to be covering Postgres for this talk. Similarly, the library that I'm going to be talking about currently only supports Postgres, but there's a chance by the time that you watch this

that MySQL may even be supported. There's an open thread on this library that I'm going to be talking about with MySQL support. You can always reference this issue to see the progress on it. And even if you do use MySQL, the concepts from this talk still very much apply, and I hope you'll learn something. Before we talk about triggers and before we talk about their use cases and like all the problems that they solve, It's important to back up a little bit and talk about an example problem. Now, this problem doesn't involve triggers, but it is very good for illustrating what triggers are and what they solve. So many people when they make a new Django project, if if you want to have users register for your project, you need to make sure that you can't have duplicate users registered.

So the question is how do you prevent duplicate users from registering in the first place? Well typically what you do is that you query for the existing user first, you make sure and you see if maybe that email already exists in the database, and if it does. You don't allow the new user to be inserted. And although this is the common approach for people , there is a bit of a better answer for this, is that You query for the existing user first, but you also back it up by applying a uniqueness constraint to your database. So, what this ensures is that even if you do the check in your database, If there is a user that, like let's say someone else is registering at the exact same time, a uniqueness constraint ensures that it that a duplicate user doesn't just creep in the database because of some sort of race condition going on.

And the reason why this particular example is so important, and why I'm going to be referencing it a lot in this talk is because this is a very common problem that I like to refer to as a database-level problem. And what I mean when I say database level problem is I mean that you can only reliably solve this problem by using a database construct. And in this case, that's a uniqueness constraint. So if you don't use a uniqueness constraint in this problem, there is no way for you to solve it 100% reliably. You are always going to be open to a race condition or or from let's say someone just writing raw SQL and inserting a duplicate user. You need to back it up with a database construct. There's actually a lot of database level problems that people try to solve in the application and they don't even realize they're doing it.

But it's it's actually the same exact scenario as this uniqueness constraint problem that that I just talked about So one great example is trying to protect your models from being deleted or trying to protect your fields from being updated. So like trying to make an append only model. This is a great example of a database level problem that people oftentimes try to just solve in the application. So maybe you installed a library or maybe you made a base model that tried to prevent anybody from trying to delete it. This is a classic case of trying to solve a database level problem in the application layer because oftentimes what happens is that you might call a bulk operation on the model and it might bypass some of that protection that you tried to put in place. Or maybe someone just comes in with raw SQL and just

deletes all the rows when you otherwise wanted to make sure that those rows were never deleted. This same type of problem holds true for soft deleting models, for computing derived fields on a model, for tracking the history of a model. you know, tracking all the changes that happen and validating state transitions. The list actually goes on and on and on about many problems that are fundamentally a database level problem, meaning that you should Back them up with a database construct and in this case that database construct is called a trigger. And Similar to how you know that you should use a uniqueness constraint for the duplicate model problem, I want you to leave this talk knowing how to use triggers for a class of other very common problems and I want you to have a new tool and your tool belt.

Before we go over all the examples that you can use for triggers, let's just first of all define what a trigger is in the first place. So a trigger is a configurable routine that's in the database itself. It's not in your Python code. The trigger actually exists in the database. It's a function declared there. And it runs when certain events happen that that's all configurable. So for example, you can make this routine run after a specific row is updated, or after a column is changed, or before a row is deleted, or Before a column switches from one value to another, you can configure triggers to operate on a wide variety of conditions and events. And this is what a trigger actually looks like in Raw SQL.

Now we're not going to be going over Raw SQL examples The the stuff I'm gonna show you today start it it ensures that you don't have to write raw SQL at all, but it's important to like understand what it looks like fr fr from the bare bones. So in Postgres, this is what it looks like when you ever create when you create a trigger on a table. The first important aspect of a trigger is when it should run. Should it run before or after an operation? And that operation can be an insert, an update, a truncate, or delete. And you have to specify what table that oper that uh trigger is going to run on. So the table obviously directly corresponds to a model in your Django code.

For each trigger, you can run it for every single row that is part of that operation, or you can run the trigger one time for a single SQL statement. Along with that, you can make sure that the trigger only runs when a specific condition holds true. So for example, making the trigger only run when a column of your model equals a certain value. And finally, after all this is configured, you actually tell it what function you want to execute. Again, this function is configured in the database itself using its own language. Some important properties to get it out of the way about row level triggers. So triggers that operate on every single row that's part of a SQL operation. Inside of a row level trigger you can access the old copy and the new copy of the row before or after the operation.

So Let's say that you have a trigger for a SQL update operation. You have a copy of the row before the update happens and a copy of the row after the update happens. Just this information itself provides an amazing amount of possibilities of what you can do in triggers, history tracking being one great example. Along with this, the row can be modified in place. So you can compute derived fields and do all sorts of things in a function in the database before the database operation is ever executed or persisted on the database. This is really powerful for a whole other host of problems. And you can even do things that like silently blocking operations from happening. So For example, in your Django code you could call dot delete on a model, but you can write a trigger that that actually just ignores the delete from ever happening.

And although you might ask yourself, like, why on earth would I ever want to do that? There are very good reasons you would want to use this functionality. And I'm going to be going over some of these examples in a little bit. So, I mean one of the big questions out of all of this is like, why don't more people use triggers in the Django projects? Or maybe for some people watching this talk, or like, why have I never heard about triggers in the first place? Well triggers they have to be migrated with models. Postgres specifically, the triggers are written in this in this language called PLPG SQL. It's difficult to write alongside the code. You have to like look up uh all the ways to to you know to write things uh in this dialect and along with that if you want to write a condition for your trigger it has to be written in raw SQL as well

So with all this in mind, triggers have just not really been all that accessible, even for the common simple use cases. And in my opinion, this is what has kind of prevented them from going a little bit more mainstream in people's projects. And that's why I'm happy to announce a library that makes it pretty straightforward to integrate triggers into your Django project. It's called Django PG Trigger. What this library allows you to do is it allows you to configure triggers alongside your Django models right in place with your model definition. The library automatically syncs trigger definitions after the migration. So if you update, delete, or you know, add a new trigger, it's gonna take care of that for you automatically out of the box. You don't have to worry about any raw SQL.

Uh and many common trigger operations are just provided out of the box. Uh even though you can write raw triggers and have them be installed you know, alongside your model, most of the time you're not going to have to do this for many common use cases because there's a lot of core classes that can be extended, it can be used. And um just briefly before we go in the tutorial of actually how to use PG Trigger, the way that it works is that you register triggers uh directly on your models. And the triggers have uh four core attributes. They have the operation, the database operation you care about, update, insert, delete. They also have the when, like when the trigger should fire. Should it fire before or after the operation? And similarly, you can specify the condition of the trigger.

So you can use Q and F objects to specify when this trigger should actually run. So for example, if you want to make a trigger that only runs Uh whenever the is active flag of your model is true, you can write a condition to make sure that the trigger only runs when that happens. There is a funk attribute for triggers. It allows you to write raw SQL if you wish. As mentioned though, Django PG Trigger comes with several base triggers so that you never have to write raw SQL. But it's there if you ever need to extend it or write your own raw SQL triggers. With this being said, we have a PG trigger tutorial that you can use and download. It is a complete Django project encapsulated in a Docker file. All you need is Docker to run it.

And we're going to go through a lot of core examples ranging from history tracking all the way to uh computing derived fields and versioning your models. So go go to this address uh and we're going to go through all these examples together. In order to do the setup step, we first need to clone the repo and do a Docker build. Now I am already in the repo here locally, so I just need to do my Docker Compose build step. And now the entire tutorial is going to be built and is ready to run. You also need to migrate the models locally to make sure that all your triggers are installed.

Now that that's all the way, we can go into the f the first and Probably the most common use case for using Django PG Trigger, and that's for protecting certain model operations. Specifically for this example, we are going to talk about how to protect model deletions. So if you want to make sure that your model can never ever be deleted, this is the trigger that you want to use. In order to do this, you uh first you you have a model and let's say it's the cannot delete model as as we have. Here shown. When this model is defined, you can use the PG Trigger Register decorator to register the protection trigger. This is part of the core PG Trigger library.

As you can see in this example, we've named this trigger Protect Deletes. You have to name every single trigger and PG trigger because it uses this name in order to uniquely identify the trigger. For this protection trigger, you can see the second attribute here is we have the operation that we want to protect. In this case, we want to protect deletes. So then now now that we have this trigger defined, we're going to run some example code here directly inside of a Django shell. We're going to instantiate our cannot delete model and now we're going to try to delete it So when we run this code, we're going to see that a error is raised. And this is a data, this is an internal database error that is

that is raised From an error that happens in the database, it is propagated all the way up to your application. And now you can see that that deletions are no longer allowed via Django PG Trigger. Going more into our protection example, you can use the protect trigger in order to make a model that is append only. So in this case, if we want to make an append-only model We need to protect both updates and delete operations. So as you can see here in our example code on the left, we have made a protection trigger, we've named it append only. And we've registered it for the update or the delete operation.

So when we protect both of these operations at the same time, we have an append-only model. So this model , as you can see in the example on the right here in my terminal, we've made an append-only model here. And uh if you try to save anything, it results in an internal error and we should see that we cannot update it. Similarly, if we try to delete the model, we should see that we can no longer delete it. And just like that, you have a model that is now an append-only model with no code, no additional code outside of the trigger definition. And you you have you have the certainty that if you

if you try to do any sort of bulk operations or anything like that, you are gonna get the same error. For the next example, we're going to continue to use the Protect trigger, but we're going to use it to make read-only fields. So this is where we get into something a little bit more advanced with Django PG Trigger, and that this is where we get into conditions. So in order to make a field read-only, we need to first of all ensure that we are protecting the update operation But we need to make a condition on the trigger that ensures that the updates are only protected when the field that you care about is updated. So as you can see from the example code on the right, we have a model here called read-only

field. And we want to make sure that our created at field is a read-only field. The end field can be updated to whatever we want it to be, but we want to make sure that when this model is created, that created at field can never be updated. So in this example, our condition, we we use the Q object here provided by Django PG Trigger, and we reference the old row. And we we ensure that in this condition, this trigger will only execute when the old rows created at attribute is distinct from the new rows created at. So we use a combination of Q and F objects, similar to how Django lets you use Q and F objects, to reference fields on the old and the new row.

And you can use this syntax uh in in any in any site uh sort of condition where you can reference the old row and the new row, you can reference all the fields on it. And this DF operator is for doing a distinct from. This helps ensure that this trigger only fires whenever it's distinct from the other field. So Given this model definition, we can now instantiate a read-only model. We can set the integer field to zero here, and as you can see, we can we can update the integer field just fine. We can save it without. However, if we now try to override this created at field, which should never be updated, we will now get an internal error because the trigger protects that field from being updated.

Now we're gonna run this and you can see everything works and we receive our error whenever we try to update that field. Now um if if we want to extend this uh example to multiple fields, uh there's an example in the in the PG trigger tutorial. where you can OR together combinations of fields to protect in your condition. So go check out the tutorial and you'll see an example of how to do read-only fields for multiple fields. For our next example, we have a very common pattern that I see a lot and that I've used a lot in many of my Django projects. It's the soft delete pattern. So when I say soft delete, I mean

Deleting a model, but instead of actually destroying it in the database, we we set an isactive flag to false. This is what is known as a soft delete pattern And uh this is a great example of where you can use triggers. So previously in this uh in this talk I mentioned That a trigger can block an operation from happening. So the soft delete trigger is set up so that it blocks whatever operation is configured. In this case it's the delete operation The trigger blocks that operation from happening, and then instead of performing the delete operation, it actually performs an update operation. So it sets the field that you care about. to the value that it you want it to be set to whenever you do a delete.

And this is how we can uh perform a soft delete operation. On the code on the left, you can see that there is a soft elete model. There's an isActive flag. By default, it's set to true. And we have a soft elite trigger defined on this. And we're targeting that isActive field. And whenever the model is deleted, instead of deleting the model, we're just going to set that value equal to false. And on the right here, we have some code where we instantiate this soft delete model Is active is equal to true by default. We maintain a reference to the ID because we're going to need it to fetch the model again. So we delete the model here and Django

clears out the model fields and everything, but the database doesn't actually go through with the delete. It actually sets the isactive field to false underneath the hood. So you'll notice whenever we ref we can refetch the model and we can print the is active field and now the is active field is going to be false. So again, this is a soft elite pattern that is now completely configured in the database. You don't have to worry about ever overriding all your models with the base model. You don't have to worry about doing bulk operations. Whatever operations you do on that model, if it results in them being deleted, It will now set the isactive field to false and it won't delete the model.

Another Example that you know that's commonly used, and uh this this is an example where we're going to get into using some raw SQL for a trigger is is versioning a model. So let's consider the problem of a model where we want to update a version field every time anything in that model changes. So in this example we we have a versioned model to the right. It has a version field. It's an integer field. It defaults to zero. And let's also put a character field inside of that model that we're going to update. And anytime anything on this model is changed, of course the char field is the only thing that could be changed. We're going to increment this version. And if any redundant updates happen, we're not going to enter

increment the version at all. So um In the example to the right, the second trigger that is defined is called the versioned trigger, and it executes before an update happens. So you can see we've overridden the when field of the trigger here And uh we we've also set the operation to be update. So before an update happens, we are going to we're going to execute this function. This function sets the new Rose version equal to the new Rose version plus one, and it returns the new row to Postgres so that it can be used in the update operation. So every Postgres trigger has access to the new row and to the old row and and you can, as I mentioned earlier, you can update attributes of the new row in place, and then Postgres

will use those attributes. Whenever it actually performs the operation. So here again, we're incrementing the version and we're also putting a condition in place too to make sure that we don't increment the version on redundant updates. So we have a condition in place that ensures that we only fire this trigger when all of the fields of the old row are distinct from the new row. As an additional protection in this versioned model, we're going to make sure that we can't edit the version either. So as you can see, we have another trigger. defined where we protect the version updates. It's a protection trigger, very similar to one that we've gone over before. And voila, we have a version model now. So in this example code here on the right , I have a

versioned model that I'm creating. I'm setting the character field here to hello. I'm going to print off the initial version, it should be zero Then we're going to set the character field to high. The version should be updated to version one. If we do a redundant update and we save you know no changes at all, the version should still be one. And then at the end here we're going to update the character field to something new and the version should be two. As a sanity check, we're going to try to update the version ourselves and we should see that you can no longer update it. And when we run this, we should see that the version starts off at zero, goes to one, it's it's still at one after a redundant update, and then after the final update, it's

it's at two And as our sanity check shows, we cannot update the version when we try to. Another interesting problem that you can solve with Django PG Trigger is creating official interfaces. So imagine that you're at an organization and you've written this method for creating a creating a model, and inside of this method it does a lot of custom logic, and you want to be sure that any engineer in your organization always uses this method to create this particular model. You want to be sure, for example, that they don't call dot create on the model and that they instead use the official interface for creating it. You can set up an official interface like this by first of all, in the code example to the left, creating a model where you protect all of the inserts for the model.

So As we show here, that there's an official interface model. We've protected all the inserts on it. And on this official interface model, we've made a custom manager and we have an official creation method on it. And we want to be sure that this is the only method that can be used to create this particular object. So we have our protection trigger, we've protected all the inserts. And then what we've done on this official method is we've decorated it with the pgtrigger. ignore decorator. And what this decorator does is that it ignores the particular trigger that you give it, it ignores it for the entire thread of execution, and it does it dynamically. So The great thing about this is that you can create official interfaces with this decorator, and you can also

instrument other parts of your code where you might need to ignore a trigger for a particular thread of execution. For those of the for those that are familiar with triggers, you might already know that triggers can only be be disabled globally. But PG Trigger comes with a built-in mechanism where you can ignore a trigger dynamically for a single thread of execution in your code without disabling it globally. So going into an example on the right, in the terminal, we have this official interface model. We first count the number of objects, you know, as a sanity check. And we try to create it. So you'll see here that you know you cannot use dot create. That is not the official interface.

An error happens. As a sanity check, we verify no objects were actually created. And then here you can see that we can use our official interface to create it. We print off the object. We also verify that an object was actually created. Everything holds true here in our example and you now have an official interface for creating this particular object. Another core problem that can be solved by Django PG Trigger is ensuring that a status field, for example, can only transition among approved transitions. So for example, looking at the code on the left, we have this FSM model, stands for finite state machine, and we have this status field on it with these three different possible states.

And we have a trigger that is provided by Django PG Trigger called the FSM trigger. And what it does is it allows you to target a particular field. And it allows you to provide a list of transitions that are valid for that field. In this case, we have listed three different transitions that are valid for that status. On my terminal to the right, you can see that you know we're making this FSM object, we're setting the the trend the status field to this published uh field to start out. Since we can go to the inactive state from published , you can see that we can successfully save it. If we try to go from inactive to published, however, since this is an invalid transition, it will result in an error.

When we run this code, we should see that an invalid transition has happened. And just like that, you have now successfully ensured that this status build can only change among approved transitions. One final example, and one of the most powerful examples of Django PG Trigger, is using it to track model history and changes. And because this problem is so big, Django PG Trigger has a sister project called Django PG History And this particular library is set up for the specific case of tracking model history. Django PG History is is a project in itself and it has all these built-in features for snapshotting and for tracking particular

model events and it even has example code of how to integrate it into the Django admin. So I encourage you to go check out this particular project briefly though. We have this model here on our left. It's called the tracked model. We have an infilled and a char fill on it. And we have decorated three different PG history trackers on it. One of which completely snapshots the model on every insert and update, and then two other trackers that track creation events and tracks when the integer field goes below zero. So we've set up these different uh trackers and in in the example here on the right we we've created our tracked object, we've we've saved it, we've made a lot of different changes.

And um one great thing about PG history is that we can attach context. To operations as well. So we're attaching this key called miscellaneous, we're giving it this value called context and We're also tracking all these different events. We're setting the integer field to a low value. Since it's now below zero, it's going to track a low int event. And this example is fairly involved, so I'm just going to briefly skim through it. Again, I encourage you to go to the PG Trigger tutorial for an in-depth example. But after we've performed all these operations , we can now see these tracked events that have happened. So we can filter them based on the snapshot events. We can see a complete list of the before and the after. of

all these fields, Django PG history actually creates a new model underneath the scenes and attracts the structural changes to the before and the after. tracks the diffs and all this great stuff. So again, I had to rush through this particular example because PG history is so involved. but I really encourage you to go check out this library. It can track diffs, snapshots, all kinds of really great things. And you can have structured changes alongside your model. Out of the box, it's very reliable, and it's impossible to bypass the history tracking. Alright, so that's it for the tutorial and that's really it for this talk. So Key takeaways for this is that triggers

are a very powerful database construct, and you can use Django PG Trigger to idiomatically integrate them into your Django project. Given some of the examples we've gone over today, I encourage everybody to give triggers a try in their projects to see what kind of problems they can solve. And once you get more acquainted with what triggers can solve, you know, I encourage you to maybe try to make some other trigger classes that Might, for example, compute derived properties on your models and do complex things that that would otherwise be challenging to do in the application. At the end of the day, if you want your database to always reflect certain things about the world, such as You know, ensuring that models are never deleted, triggers can be a very important tool to add to your arsenal in order to ensure that you make a reliable and robust application.

Well, that's a wrap for my talk. Again, I hope that you learned something about triggers and how you can use it for your Django project. My name is Wes Kindle. Here's my contact information. I've been doing Django for a while now. You know, you're always welcome to just hit me up if you have any random thoughts. I'm the founder of this open source organization called Opus 10. You can check out our GitHub. We have a lot of other Postgres packages on there. Uh a lot of cool stuff. And as a special thanks, uh I did a lot of this work in collaboration with Three people from uh a previous company I was at called Jive, San Bobby and Tomos. Really appreciate their help with this and uh Huge thanks to the to the Django Khan uh organizers for putting all this together.

So thank you