DEV Community

loading...
Cover image for Yggdrasil and PostgreSQL Notifications

Yggdrasil and PostgreSQL Notifications

alexdesousa profile image Alex de Sousa Originally published at thebroken.link Updated on ・2 min read

One thing I really like about PostgreSQL is its notifications via pg_notify. This feature is very useful when trying to get real-time notifications for certain changes in a databases.

PostgreSQL notifications

Creating notifications in PostgreSQL is very easy e.g. let's say we have a table for books:

-- User table creation
CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL UNIQUE
);
Enter fullscreen mode Exit fullscreen mode

and we want JSON notifications in the channel new_books every time a new book is created in our database e.g:

{
  "id": 1,
  "title": "Animal Farm"
}
Enter fullscreen mode Exit fullscreen mode

The trigger could be implemented as follows:

-- Trigger function creation
CREATE OR REPLACE FUNCTION trigger_new_book()
  RETURNS TRIGGER AS $$
  DECLARE
    payload JSON;
  BEGIN
    payload := json_build_object(
      'id', NEW.id,
      'title', NEW.title
    );

    PERFORM pg_notify('new_books', payload::TEXT);
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

-- Sets the trigger function in 'books' table
CREATE TRIGGER books_notify_new_book
  BEFORE INSERT ON books
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_new_book();
Enter fullscreen mode Exit fullscreen mode

Then, the following query would trigger our JSON message in the channel new_books:

INSERT INTO books (title) VALUES ('Animal Farm');
Enter fullscreen mode Exit fullscreen mode

The Problem

Though subscribing to our database notifications can be done easily with Postgrex library, handling the connections to the database is a bit of a hassle. We need to ensure:

  • Connection multiplexing: avoiding over consuming database resources.
  • Fault-tolerant connections: supporting re-connections in case of failure or disconnection.
  • Re-connection back-off time: avoiding overloading the database on multiple re-connections.

problem

The Solution

Yggdrasil for PostgreSQL is an adapter that supports all the features mentioned above while maintaining Yggdrasil's simple API e.g:

For our example, we could subscribe to the database messages by doing the following:

iex> Yggdrasil.subscribe(name: "new_books", adapter: :postgres, transformer: :json)
iex> flush()
{:Y_CONNECTED, %Yggdrasil.Channel{...}}
Enter fullscreen mode Exit fullscreen mode

Running the following query:

INSERT INTO books (title) VALUES ('1984');
Enter fullscreen mode Exit fullscreen mode

We will get the following message in IEx:

iex> flush()
{:Y_EVENT, %Yggdrasil.Channel{...}, %{"id" => 2, "title" => "1984"}}
Enter fullscreen mode Exit fullscreen mode

Note: Yggdrasil comes with built-in message transformers. We've used
:json transformer for this example in order to get a map from the JSON
data.

Additionally, our subscriber could also be an Yggdrasil process e.g:

defmodule Books.Subscriber do
  use Yggdrasil

  def start_link(options \\ []) do
    channel = [
      name: "new_books",
      adapter: :postgres,
      transformer: :json
    ]

    Yggdrasil.start_link(__MODULE__, [channel], options)
  end

  @impl true
  def handle_event(_channel, %{"id" => id, "title" => title}, _state) do
    ... handle event ...
    {:ok, nil}
  end
end
Enter fullscreen mode Exit fullscreen mode

It's also possbible to use Yggdrasil.publish/2 with PostgreSQL:

iex> message = %{"id" => 3, "title" => "A Brave New World"}
iex> Yggdrasil.publish([name: "new_books", adapter: :postgres, transformer: :json], message)
Enter fullscreen mode Exit fullscreen mode

Too easy!

Conclusion

Yggdrasil for PostgreSQL simplifies subscriptions to PostgreSQL notifications and let's you focus in what really matters: messages.

Cover image by Nasa

Discussion (0)

pic
Editor guide