PSQL event triggers with Node.js (Listen / Notify)

Image for post
Image for post
Puentes de San Jerónimo. By: Pablo Trinidad

The other day I needed to send data to Google Analytics when a new row was added on a table in the database. After approaching different solutions I finally wrote a Node.js (with Koa.js) bot that listens to notifications on the database and send it to GA.

First of all we need to have a database, in this case I’ll be using PSQL

After creating the database we need to create a table with some data

After that we need to send an event when a row is added. PSQL allows you to create event triggers.

We need to create a PSQL function first

Image for post
Image for post
created with https://carbon.now.sh

This function will send the new_testevent notification whenever it is executed

After that we create the event trigger

Image for post
Image for post
created with https://carbon.now.sh

Here we’re telling our database to execute our function (notify_testevent) whenever a row is added into the people table

At this point, PSQL is gonna send an event when a row is added, now we’re gonna listen the event on Node.js. I’m gonna skip the whole part of making a server.

After that, I’m gonna use pg to connect to PSQL, so we install it

created with https://carbon.now.sh

Let’s connect to the database

Image for post
Image for post
created with https://carbon.now.sh

Now that we have our client, let’s create the listener

Image for post
Image for post
created with https://carbon.now.sh

With that, Node is listening to the event, now we need to do some stuff with it

When ever the event is triggered, PSQL is gonna send a notification, so in order to do stuff with it let’s listen to it.

Image for post
Image for post
created with https://carbon.now.sh

That’s all, with that you can trigger and listen to events on PSQL.

I’d love to hear from you, you can reach me out on twitter: @edernegrete_ ❤️

Frontend Engineer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store