PSQL event triggers with Node.js (Listen / Notify)
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
This function will send the new_testevent notification whenever it is executed
After that we create the event trigger
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
Let’s connect to the database
Now that we have our client, let’s create the listener
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.
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_ ❤️
