DEV Community

Hasura for Hasura

Posted on • Originally published at blog.hasura.io on

3

Postgres triggers on GraphQL mutations

Postgres trigger on a GraphQL mutation in action

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:

-- Create the function
CREATE FUNCTION insert_deposit()
RETURNS trigger AS $BODY$
DECLARE active_account BOOLEAN;
BEGIN
IF NEW."deposit_amount" <= 0 THEN
RAISE EXCEPTION 'Deposit amount must be greater than 0';
END IF;
SELECT a.is_active INTO active_account FROM "account_savings" a WHERE a.account_no = NEW."account_no";
IF active_account != TRUE THEN
RAISE EXCEPTION 'Account must be active';
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
--- Create the trigger with the above function
CREATE TRIGGER insert_deposit BEFORE INSERT OR UPDATE ON "deposit_activity" FOR EACH ROW EXECUTE PROCEDURE insert_deposit();

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  
    }
}
Enter fullscreen mode Exit fullscreen mode

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
  }
}
Enter fullscreen mode Exit fullscreen mode

Results in:...

{
  "error": { 
    "exec_status": "FatalError",
    "hint": null,
    "message": "Deposit amount must be greater than 0",
    "status_code": "P0001",
    "description": null
  }
}
Enter fullscreen mode Exit fullscreen mode

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:

-- the function to call when the trigger is invoked
CREATE FUNCTION trigger_on_note_revision()
RETURNS TRIGGER
LANGUAGE PLPGSQL AS $BODY$
BEGIN
-- Create revision only if node's subject or body columns have changed
IF OLD.title <> NEW.title OR OLD."data" <> NEW."data" THEN
INSERT INTO note_revision (note_id, created_at, title, "data")
VALUES (OLD.id, OLD.updated_at, OLD.title, OLD."data");
NEW.updated_at = now();
END IF;
-- Return the NEW record so that update can carry on as usual
RETURN NEW;
END; $BODY$;
-- create the trigger
CREATE TRIGGER trigger_note_revision
BEFORE UPDATE
ON note
FOR EACH ROW
EXECUTE PROCEDURE trigger_on_note_revision();

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
  }
}
Enter fullscreen mode Exit fullscreen mode

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
    }
}
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)