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:

1. Perform some validation on the server-side. For e.g. in a banking application, validate that deposits cannot be than 0 or lesser.
2. 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.