Relational databases have,.... you already know right? So recently I found how to build realtime apps using PostgreSQL. And I can't wait to share it with you.

Making the client use WebSockets and update HTML has been covered before by so many tutorials around reactive programming, frontend frameworks, libraries like socket.io, and node.js server with Redis pubsub and alike.

I was very impressed when I first saw how Meteor JS was able to listen to MongoDB updates, and populate the update in realtime to the UI. Any app can do an update to a collection and the frontend can update. I guess this inspired the MongoDB team to implement change streams.

However now I finally found out how to build such realtime apps with the Postgres database. Here you go:

PG Features

The key lies in the combination of two features of Postgres. First Triggers: They observe a table and execute a function inside the database server. Second is Notifications: With them, you can get the event out of the server. If you know how to use these two features, you can skip the rest of this article.

Setup Triggers

The key to handle events from table updates you need to create a trigger. In PostgreSQL, the trigger requires a function. So we first create that.

CREATE OR REPLACE FUNCTION notify_table_update()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL  
  AS
$$
BEGIN
  IF TG_OP = 'INSERT' THEN
     PERFORM pg_notify(
        'update_' || TG_TABLE_NAME,
        '{"new":' || row_to_json(NEW)::text  || '}'     
     );
  END IF;

  IF TG_OP = 'UPDATE' THEN
     PERFORM pg_notify(
        'update_' || TG_TABLE_NAME,
        '{"new":' || row_to_json(NEW)::text  || ',"old":'  || row_to_json(NEW)::text || '}'
     );
  END IF;

  IF TG_OP = 'DELETE' THEN
     PERFORM pg_notify(
        'update_' || TG_TABLE_NAME,
        '{"old":'  || row_to_json(OLD)::text || '}'
     );
  END IF;
  RETURN null;
END;
$$;

The function you create is called notify_table_update and returns a trigger and has no arguments. Within the plpgsql code. The function uses pg_notify with some topic name and a data-string containing the updated entry and the same entry before the update. The topic I made a little generic, by using the TG_TABLE_NAME variable, so the function can directly be used for any table. TG_OP is a variable that is available in trigger functions. Returning NEW or null both is ok. There are other variables that can be used within a trigger function, you can find on this page documentation.

Creating the trigger is made just like an example from the official Postgres documentation.

CREATE TRIGGER users_notify_trigger
    AFTER UPDATE OR INSERT OR DELETE ON users
    FOR EACH ROW
    EXECUTE PROCEDURE notify_table_update();

To note here, and that was hard to find, even though nothing is passed into the function, the function still has access to NEW and OLD, the rows data from the table, and a few other variables. When using 'AFTER UPDATE', both old and new are available. For Insert, only NEW is there and when delete only OLD. Now, it is possible to use different triggers for each kind of update or use the plpgsql syntax for an if statement as shown in the function declaration above to further reduce the number of events in a controlled way, that is still enough for your app.

As JS developer, I like to present how to use these trigger to receive the event, using the postgres npm module:

sql.listen('update_user', payload => {
  const update = JSON.parse(payload);
});

So, now you have the event in nodejs where it is fully under your control. You can push it directly to the frontend via WebSocket, filter the events, push them through some other message system to be processed, clear some web cache or whatever else is needed. Depending on your app, I hope this article helped you learn how to build more interactive realtime apps with Postgres.

As these notifications are very cool and useful to build reactive apps with live updating UIs, these Postgres triggers are for sure not the last solution of its kind and not the one size fits all. When the nodejs server gets disconnected for a moment, these events would be lost. In MongoDB, you could pick them up later again. This limitation could be overcome, by not only sending the notification but also inserting it into a logging table. This can become very big, so scalability can become an issue.

Another issue is that there is quite some more development is needed to process the events and deliver them to the right client or reporting system.

What do you think of this approach? Did you already use triggers for notifications in PG? See you in the comments.

(The title photo is from unsplash.)