Community project showcase: an Ably-Postgres connector to stream DB changes to millions of clients in realtime

Introduction

Built by our long-time community expert Apoorv Vardhan, the Ably-Postgres connector can listen to changes in a Postgres table and publish realtime messages on Ably channels whenever a change occurs.

Dive into the project on Ably Labs GitHub and try it out yourself.

The connector enables building database-driven realtime applications where long-term storage and update triggers from previously published messages are essential. One such example is an editable chat app, which we’ll talk about later in this article.

How does the connector work?

Apoorv explains that the connector accepts a configuration file where you input the connection details for your database and the tables you want to listen to for data changes. It also accepts an Ably API key to be able to instantiate and publish messages to your Ably app following any changes to the specified tables.

Using the config file, the connector creates a special table called the ablycontroltable in your database. This table is used to maintain the Ably channel mapping for various changes to the tables in your database.

Next, the connector creates a procedure to listen to changes on the specified tables using the pg_notify function. This notify function then publishes the change data capture(CDC) payload on the relevant Ably channel, as specified in the config.

Here’s an example config you’d need to provide to the connector:

{
    "dbConfig": {
        "host": "db",
        "port": 5432,
        "user": "postgres",
        "password": "postgres",
        "database": "mydb"
    },
    "connector": [
        {
            "tablename": "chat",
            "ablychannelname": "ably-chat-added",
            "operation": "INSERT"
        },
        {
            "tablename": "chat",
            "ablychannelname": "ably-chat-updated",
            "operation": "UPDATE"
        },
        {
            "tablename": "chat",
            "ablychannelname": "ably-chat-deleted",
            "operation": "DELETE"
        }
    ],
    "ably": {
        "apiKey": "API_KEY"
    }
}

You have the option of supplying this config either via a JSON file like above or via environment variables.

What can you do with this connector?

If you want to follow a database-driven approach to your app architecture, much like with Firebase or Supabase, this connector easily enables it.

As you may know, Ably doesn’t provide long-term storage by default. It is a publish/subscribe messaging infrastructure which can scale to arbitrary levels. Ably messages are transient. The most common way people implement long-term storage is by publishing all the data from a channel to a database, at the same time as other clients (essentially, the database itself behaves like a client). Database queries are made via a pre-configured Webhook endpoint set up on Ably’s Integrations dashboard. The Ably-Airtable starter kit shows an example of such an architecture.

While this approach is very useful and straightforward, it does suffer from the fact that any updates on previously stored data do not get propagated to interested parties.

Consider an example of a chat application where you can edit previously published messages. Ably messages are immutable by nature. You publish an update, it gets fanned out to all the subscribers. But how do you implement editable functionality on such messages? This is exactly the kind of problem the Ably-Postgres connector is designed to solve.

Seeing the Ably-Postgres connector in action

Let’s look at the architecture for a fully serverless and scalable chat app demo where you can edit messages.

One of the obvious architectural patterns when it comes to building chat apps with a service like Ably is to publish messages on a channel and make sure all the participants are subscribed to that channel so they can receive updates. Let’s slightly change this by separating the outgoing and incoming chat channels. This gives us a chance to add additional components between the publishers and subscribers.

In our case, we’ll add the database as an additional component between the publishers and subscribers. We’ll make use of various tech services to get the messages from Ably into the database and then from the database back to Ably, so all the front-end clients can chat with each other in realtime.

Let’s understand this architecture with a diagram:

1. When a client sends a new chat message or edits an existing message, this update is delivered via the ‘outgoing-chat’ Ably channel.
2. As a result, Ably will trigger a pre-configured Lambda function and publish the payload of the chat message to it.
3. The Lambda function will check if the trigger is due to a new message or an edited message. Depending on this, it’ll either `INSERT` a new row into the Postgres DB with the chat message data or `UPDATE` an existing row with the edited message and flag that row as edited.
4. While this happens, the Ably-Postgres connector is already watching the Postgres DB for any changes, so when there’s either an ‘UPDATE’ or an ‘INSERT’, the connector will publish the data to Ably on the ‘incoming-chat’ Ably channel.
5. Given that our chat clients are already subscribed to ‘incoming-chat’, they’ll receive this update in realtime.
   

In this architecture, the PostgresDB is hosted on AWS RDS, and the Ably-Postgres Connector is containerized and hosted on AWS Fargate. Putting them all together in the same infrastructural setup gives an easy way for these non-Ably components to communicate with each other, and also scale automatically as needed. But of course any other options should work well too.

This demo is hosted at https://serverless-scalable-chat.netlify.app/. Feel free to check out the source code and dive into the README to learn more about the specifics. Stay tuned for a future post where we’ll take a look at this demo in much more detail.

In a previous article about decoupling realtime communications with storage in Firebase, I wrote about a similar architecture, only this time, it’s with PostgresDB, using its innate listen/notify feature.

Conclusion

The Ably-Postgres connector opens up a whole new set of opportunities and app architectures we can make use of to build robust realtime features. It gives us a way to combine long-term storage with the ability to trigger realtime updates on previously stored data.

The connector is currently available to be used directly via GitHub and NPM. It is a fully open-sourced project and we’ll continue to update it with new features and improvements. Check it out and feel free to contribute.