build reactive realtime apps with POSTGRES

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.)

Comments

[Yasen Velichkov]

Hello Tobias,
it's an interesting topic but there are some issues with the code. A trigger function returning NULL cancels the operation that triggered it. Your code only works because you call the function as a procedure in the trigger definition which ignores the return value of the function. (Here there is a misconception if you read old PostgreSQL docs v10 and earlier, when these were indeed called as procedures - just switch between the versions: postgresql.org/docs/13/plpgsql-tri...)

Just for clearing the concepts I would be glad if the function returns the NEW value unless TG_OP = 'DELETE' when only the OLD value is accessible. Then the trigger definition should call it as a function because calling it as a procedure is an undocumented behavior as of PostgreSQL 11 and newer.

[Tobias Nickel]

Ohh, yes, my tests where made using a version 9.

Thanks, I will see to do an update. 👍

[dc]

Interesting piece. I was reading up on supabase choice of techstack and they mention using the replication stream over NOTIFY

github.com/supabase/realtime#cool-...

kind of how the meteorJS people did realtime on mongo way back, using the changelog stream, before you could even tail capped collections (which is how i used to do it but it only works for collections where you can throw away the data. which many people would say is the best way to use mongo anyway lol)

you're in china? so i guess you can't use firebase. supabase probably has a window before the GFW drawbridge smashes down, but it will be slow anyway. When I was there I tried something using pusher and, tho not banned it super sucked.