Postgres has in many ways become the go-to operational database for many a developer, in part because of the increasing support for JSON, and now JSONB (as of v9.4), providing capabilities as both a mature RDBMS and a noSQL JSON datastore, complete with indexing, and performance benchmarks that would certainly surprise some diehard mongoDB fans.
I recently implemented push notifications into a Postgres/NodeJS environment, and found myself wishing there was a more Node-like way of doing realtime pub/sub style messaging directly from the database. After a bit of research, I stumbled across pg_notify.
Publishing the event to a channel (pg_notify)
The native pg_notify function, used with a PLpgSQL trigger function, gives us the exact functionality we’re after, that is, publishing an event when a specific trigger is fired. As an example, we’ll notify our Node backend that a new order has been received.
First, we need to define the trigger function and it’s associated trigger…
In the above example, pg_notify accepts a channel (new_order), and a payload, (the new order which we are typecasting to JSON text).
Subscribing to the channel (NodeJS)
In the NodeJS backend, we’re going to configure the Postgres client to listen for the events, and then build an EventEmitter to handle our custom logic for responding to the events.
Basically here we have configured the Postgres client to listen for “notifications” that come through on the channel named “new_order” which we specified when we invoked pg_notify. We then emit an event to our EventEmitter and run our custom logic.
That’s all you need to create a basic pub/sub architecture using Postgres and NodeJS (using JSON).
Let me know via Twitter or in the comments if you have any questions or issues.