Postgres triggers on GraphQL mutations
TL;DR
Setup Postgres functions and triggers to perform backend operations like validation and inserting/updating other related data, whenever some inserts/updates happens on tables. Now when you use GraphQL mutations, Postgres triggers run, performing the required backend operations.
Introduction
There are cases where you would want run some custom backend function after an insert happens.
Some examples of this are:
- Perform some validation on the server-side. For e.g. in a banking application, validate that deposits cannot be than 0 or lesser.
- Insert some related data in a single transaction. For e.g. in a note taking application, create a revision whenever a note is updated.
What is a Postgres trigger ?
A trigger is associated with a table or view and is fired whenever an event occur. The ‘events’ are INSERT, DELETE, UPDATE or TRUNCATE.
Trigger will eventually call a function which will automatically be invoked when a specific event occurs.
Triggers can be invoked in the following scenarios:
- Before the operation.
- After the operation.
- Instead of the operation.
Validation on the server-side
Consider the following data model for a banking-like application:
customer
— id, name
account_savings
— account_no, customer_id, is_active, created_at
deposit_activity
— account_no, deposit_amount, deposit_time
withdraw_activity
— account_no, withdraw_amount, withdraw_time
So to demonstrate the first scenario, we will perform basic validation before an insert into deposit_activity
happens. We want to ensure that insert doesn’t happen, if the deposit_amount
is ≤ 0 or if the account is inactive.
Let’s create a trigger. You can copy-paste this in the “SQL” tab of Hasura console:
Now to to make deposits into an account, we can just use this insert mutation, and the trigger will make sure that the amount is valid (≥0) and the account is not an inactive account.
mutation deposit {
insert_deposit_activity(objects:[{
account_no: 1,deposit_amount: 1000 }]
) {
affected_rows
}
}
Making an insert where the account is inactive or amount < 0 will throw a Postgres exception:
mutation deposit {
insert_deposit_activity(objects:[{
account_no: 1, deposit_amount: 0 }]
) {
affected_rows
}
}
Results in:...
{
"error": {
"exec_status": "FatalError",
"hint": null,
"message": "Deposit amount must be greater than 0",
"status_code": "P0001",
"description": null
}
}
Insert related data
Consider the following data model:
user
— id, username
note
— id, user_id, title, data, created_at, updated_at
note_revision
— note_id, created_at, title, data
Whenever a user updates a note, we will insert into note_revision
before updating the note
table.
Create the following trigger:
Now let’s create a note:
mutation {
insert_note (objects: [{
user_id: 1, title: "my first note", data: "some long note data i have"}]
) {
affected_rows
}
}
Now when we update that note:
mutation {
update_note (where: {id: {_eq: 1}}, _set: {title: "My new note", data: "some other note data"}){
affected_rows
}
}
A new row in the note_revision
table will be created:
Conclusion
Postgres triggers can be used effectively to trigger operations/functions on the backend.