I’ve been integrating pg_webhooks into some project work lately. Conversely, I have not been using n8n much, but a vendor rep used it in a demo of some workflow automation capabilities for a platform one of my customers is using. I had dabbled with it about two years ago but haven’t done much with it since.
With that bit of housekeeping out of the way, let’s proceed with the example. Here, I use a self-hosted version of pg_webhooks connected to my local PostgreSQL server. It will be communicating with a paid, cloud-hosted version of n8n. We’ll start with setting up pg_webhooks.
1. pg_webhooks Setup
The easiest way to get going is to clone the pg_webhooks repository from GitHub. After that, we need to install its dependencies with
npm install (or use yarn if that’s your thing).
Next, we’ll create the
subscriptions table in the
public schema of our desired database. As of this writing, pg_webhooks requires the
subscriptions table to be in
public and it must also reside in the database from which notifications will be raised. So, if we plan to raise notifications from our
test database then the
subscriptions table must reside in the
public schema of the
The repo contains a SQL script that does all of the table setup for us. We simply run that script in our database using the PostgreSQL client of our choosing.
Now that the table is created, we’ll need to create a
.env file to hold our PostgreSQL connection information. This is suitable for development, but it is strongly recommended to use system-level environment variables in production. The repo contains a file called
"example.env". Simply copy that to a file named
".env" and edit it to reflect our information.
PGDATABASE variable should contain the name of the database where we created the
subscriptions table. This post won’t discuss
MAXFAILS so there’s no need to edit it.
With these tasks done, we should be able to run pg_webhooks by issuing the command
npm start. Assuming this was successful, we’ll move on to the next step, creating a webhook receiver in n8n and registering it with pg_webhooks.
2. Webhook Configuration
This step has three parts: 1) creating a webhook trigger in n8n, 2) registering its URL as a subscription in pg_webhooks, and 3) doing a test event to capture the payload schema in n8n for use downstream in our workspace.
This post is not an n8n tutorial, so it assumes that we have n8n running (either self-hosted or SaaS) and are able to access the workspace editor. If we are self-hosting, we may need to do some extra network configuration to set up tunneling or a subdomain. Again, this post assumes we have successfully done all of that.
Creating the Webhook Trigger
This workflow in n8n will be triggered by a webhook call. To set that up, we add a webhook trigger to the canvas. Use the “plus” icon to access the list of available nodes.
Use the search tool and, when we see the “Webhook” node available, choose it to add it to the editor canvas.
We can then double click the node in the editor to open its property sheet to complete this part of the setup.
We’ll see that n8n generates both test and production URLs for us. We’ll use the test URL for the rest of this post. In the property sheet, we need make sure the “HTTP Method” property is set to POST as pg_webhooks only supports the POST method. All other properties can stay the same. Next, we’ll need to click on the URL to copy it to the clipboard. Now we’ll register the webhook with pg_webhooks.
Register the Webhook
Assuming that we were previously able to successfully run the pg_webhooks and that it is currently running, we will use its
/subscriptions/add endpoint to register the n8n test URL. pg_webhooks does not provide a UI for this, so we’ll need to use something like curl or Postman. Here is a screenshot of Postman showing the proper URL and payload configurations.
The JSON payload contains the information needed to subscribe. The “channel” property is the name of the notifications channel in PostgreSQL and the callback is the test URL from n8n. When we send the payload via POST, we’ll receive a 200 response code and a JSON payload with a subscription ID that we can store as needed. In the future, pg_webhooks will send this ID with all payloads to help with validation of inbound calls.
At this point, the n8n test URL is registered with pg_webhooks. The final step is a test event so that n8n can retrieve the payload schema.
Firing a Test Event
Back in the webhook property page in the n8n editor, we will see a button labeled “Listen for Test Event”. We need to do this in order to capture a sample payload from the webhook for use in downstream n8n nodes. Click the button and n8n will listen for a webhook event.
Once we do this, and with pg_webhooks running, we’ll need to trigger an event from within your database. The easiest way to do this is to use the
pg_notify function from the PostgreSQL client of our choice. The name of the channel should match the name subscribed to in step 1. In this example, we used ‘dev’ so that should be the first function parameter. The second parameter is a sample payload. Here, we are using a simple string, but we will want a full example of our actual payload in order to properly configure downstream actions. Here is an example how to use the
select pg_notify('dev', 'this is a test');
Assuming all of the previous configuration steps have been done correctly, the test event will fire in n8n and we will see a sample of response.
With this sample payload, we can now configure downstream actions.
3. Doing Something
It’s time to actually do something! In this post, that something will be really simple: we’re going to write the timestamp of the event and the raw payload body to a Google Sheet. To do this, we’ll add a Google Sheets node in the same way we added the webhook trigger above. We’ll then double-click it to configure it in its property page.
We’ll configure our Google authentication as we see fit. For this example we are appending a row to a sheet (“Sheet1”) in an existing workbook (“n8n test”). The sheet has two columns,
message which are mapped manually here. Each is populated using an expression. The
date column gets the current date/time. The
message column gets the value of the “body” of the webhook response. We can only configure this because we fired the test event in the previous step.
Once this configuration is complete, we can close the property page and choose “Execute Workflow” in the n8n canvas.
This will cause the workflow to listen for events. Now we can issue the same test SQL statement we did in the previous step. If the connection is successful, then pg_webhooks will receive a JSON response from n8n indicating the workflow has started.
This particular workflow is simple and will execute quickly. If we go look at our Google Sheet, we will see a new row.
4. Wrapping Up
The preceding example shows the basics of using pg_webhooks with an IPaaS like n8n. n8n support numerous systems and operations, so we can design very robust workflows. Where we are simply writing data to a Sheet, we could instead write to a data warehouse such as BigQuery. Where we currently have a simple string for a payload, we could have a complex payload such as a GeoJSON feature collection. From there we can perform sophisticated calculations or transformations before send data to its destination. My personal preference is to keep such workflows as simple as possible but, regardless of the complexity of what you do inside n8n, it begins with a webhook trigger from pg_webhooks, so be sure to take care in how you design your payloads and your notification logic.