Create event triggers for PostgresDB

Postgres Triggers

SO what are Postgres triggers and why are they so great. To be honest I also didn't know about them until I wrote this application. And realising just how powerful they are made me rethink my view on web-development.

Basically Postgres triggers are functions that are triggered when an event(CREATE, READ, UPDATE, DELETE) happens. You can do powerful joins and interpolations using these triggers. They are completely on the backend and you can use them to set restrictions on the data.

So let's say you want to embed to_id field automatically into every message. to_id is the id of the user the message is addressed to. Since this is not available on the frontend, we will do it from the backend.

Go to the SQL tab in your Hasura console:

And add the following code:

CREATE FUNCTION create_to_id()
    RETURNS trigger AS $BODY$
    BEGIN
    SELECT id INTO NEW.to_id FROM users WHERE email = NEW.to_email;
    RETURN NEW;
    END;
    $BODY$ LANGUAGE plpgsql;

CREATE TRIGGER insert_id BEFORE INSERT OR UPDATE ON messages FOR EACH ROW EXECUTE PROCEDURE create_to_id();

We are telling SQL to get the id from email and insert that into the new record before inserting the record. This way we get the to id information in the table for using later.