build reactive realtime apps with POSTGRES
Tags: node, postgres, javascript, tutorial
Published: 2020-08-27 10:52:29

---
title: build reactive realtime apps with POSTGRES
published: true
description: with this knowledge, you will be able to build reactive apps with sql backend.
tags: 
  - nodejs
  - postgres
  - javascript
  - tutorial
canonical_url: http://tnickel.de/2020/08/23/2020-08-reactive-apps-with-postgresql/
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/njic3jw4mjd54gkoshei.jpg
---

Relational databases have,.... you already know right? So recently [I](http://tnickel.de/) 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](https://github.com/tobiasnickel) 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](https://docs.mongodb.com/manual/changeStreams/). 

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.
```sql
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](https://www.postgresql.org/docs/12/plpgsql-trigger.html).

Creating the trigger is made just like an example from the official Postgres [documentation](https://www.postgresql.org/docs/9.1/sql-createtrigger.html).
```sql
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](http://npmjs.com/package/postgres) npm module:

```js
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](https://unsplash.com/s/photos/elephant).)