PostgreSQL’s NOTIFY/LISTEN method for subscribing to events from a database is a subject I return to periodically. I’ve touched on it in one form or another over several years. My latest run at it involves building a Node Express application that will allow external systems to subscribe to webhooks that are fired by NOTIFY statements from PostgreSQL.
I was prompted to do this by a number of factors. First, I have stepped back into the consulting world and find myself doing much more coding and technical work than I had been doing in my previous role. Some of my current work has involved building and optimizing data workflows and have been using database triggers in key parts of it. In my previous role, webhooks figured prominently into data integration tasks among various SaaS platforms. Finally, I simply wanted start a new coding project.
The resulting work can be found here: https://github.com/geobabbler/pg_webhooks. In its current form, it provides a simple interface for subscribing to a channel. Channel names in Postgres are arbitrary, so you can subscribe to anything, even if no events are ever generated. That’s something I’ll probably look at tightening up soon.
Why webhooks? They are a one-way communication mechanism that allows one system to to send a message via HTTP to another systems. This differs from web sockets, which enable bi-directional communication. Basically, the source systems broadcasts to anyone who cares that something happened. What that means or what action to take is up to the listener.
To subscribe, send a simple POST request with a JSON payload as shown below. You tell it the name of the channel you want to subscribe to and provide a callback URL for resulting webhook calls. The app only supports POST for outbound URLs, so your callback will need to use POST. Because a listener could choose to take action based on a callback, I don’t plan to support GET, which should be idempotent.
The application uses a PostgreSQL table to store subscribers. That table enforces a unique constraint on the combination of channel and listener host (not the full URL). This means each host can listen to a channel once. It is a simple approach to minimize attempts to use the app as a denial-of-service vector for either the source or the listener.
I’ve got a list of updates that will be coming soon, primarily addressing security, performance, and stability. For example, the application assumes its subscribers table will be stored in the public schema and that the source database is running on the default port. Both of these will be configurable in short order. Also, it expects the NOTIFY payload to be JSON but does nothing to ensure that. I’ll wrap the payload in an update that will fix that assumption. There will be others so what the project’s issues list.
The app is open-source under a BSD 3-clause license, so feel free to kick the tires.
