EctoWatch - Get updates from PostgreSQL in your Elixir app

cheerfulstoic · July 4, 2024, 9:03am

I’ve created a library that allows you to get notified about PostgeSQL inserts, updates, and deletes as Phoenix PubSub messages in your Elixir app. You might find this useful, for example, when updating caches or sending notifications about record updates.

See the README for more details and examples:

c4710n · July 4, 2024, 12:13pm

Thank you for providing a more general and complete solution for pg_notify. The DX is good. Really like it.

akramic · July 7, 2024, 11:19am

This is really useful - well done and thanks for making this available to the community.

apoorv-2204 · December 29, 2024, 7:44am

Can we use this in production?

cheerfulstoic · December 30, 2024, 2:38pm

As far as I know it’s been working stably for people. It has a pretty thorough test suite. So overall, it should be fine to use in production.

The method of database notifications that ecto_watch uses should scale quite a ways, but if you have a really large amount of activity or if you have big bursts, you should understand it’s performance characteristics (as with anything).

I have one PR that I hope to be merging soon to protect people from database trigger/function names being too long. Before that is merged, I’d suggest checking out this issue and using the label option if you experience a similar issue on startup.

apoorv-2204 · December 30, 2024, 2:40pm

is it possible to have brief overview of it works as diagram if otp used?

cheerfulstoic · December 30, 2024, 3:14pm

Not sure that I understand, but the structure that is used is pretty simple. If you add EctoWatch into your application, a Supervisor is created. For every EctoWatch watcher that you add, a GenServer is created which listens for messages from the database.

When the Supervisor is started, it runs these queries for each watcher:

CREATE OR REPLACE FUNCTION ...
DROP TRIGGER IF EXISTS ...
CREATE TRIGGER ...

The trigger calls the function which sends a notification to your application/the watcher GenServer.

And then each GenServer does a Phoenix.PubSub.broadcast for each message from the database so that you can get application updates (as described in the README)

If that’s not clear I’m happy to answer other questions.

Bodhert · March 25, 2025, 11:26pm

Great library! thanks for creating this!
I did find and issues (there was no clear way) trying to test. I did create an issue on GitHub to clarify how I managed to test it with the core logic, or if maybe I am missing another easy way to test it.

github.com/cheerfulstoic/ecto_watch

Testing facilities

opened 11:23PM - 25 Mar 25 UTC

Bodhert

First of all great library I really loved! I was able to use it right away afte…r installation, I did find issues testing it, did not find a comment or post that guide me through it. I am more than happy to add the documentation or help in a pr to enhance this. basically I learned that to test this the transaction must be committed to the db in test so you will probably need to use `Ecto.Adapters.SQL.Sandbox.checkout(Your-repo, sandbox: false)` [info](http://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.Sandbox.html#mode/2) that implies that you will need to manually clean the db. with that you can assert that your code gets notified. example ```elixir defmodule Notifier do use GenServer alias ProjectX.Workers.ImageWorker require Logger def get_state do GenServer.call(__MODULE__, :get_state) end def start_link(_opts) do GenServer.start_link(__MODULE__, %{}, name: __MODULE__) end def init(_opts) do :ok = EctoWatch.subscribe(:notification) {:ok, %{counter: 0}} end def handle_info({:notification, %{id: id}}, state) do new_state = Map.update(map, key, 1, &(&1 + 1)) {:noreply, new_state} end def handle_call(:get_state, _from, state) do {:reply, state, state} end end ``` in the tests ```elixir defmodule ProjectX.Media.ImageNotifierTest do use ExUnit.Case setup do Ecto.Adapters.SQL.Sandbox.checkout(ProjectX.Repo, sandbox: false) cleanup() end def cleanup do # clean the db before testing end test "it should be notified when the image description is updated" do :ok = EctoWatch.subscribe(:notification) record = record_fixture(%{}) Records.update_record(record, %{key: "some_value"}) record_id = record.id assert_receive {:record_updated, %{id: ^record_id}}, 2000 state = Notifier.get_state() assert Map.get(state, :counter) == 1 # get called once end end ```

cheerfulstoic · March 26, 2025, 2:45pm

Thanks very much! I left a comment there and perhaps that’s the best place to continue the discussion

StandardMikeBenza · March 26, 2025, 9:04pm

Do you have any advice for using this in conjunction with Ecto.Adapters.SQL.Sandbox in tests? I’d like to build a test that triggers a SQL change and verify that a side-effect event happens. When trying to start EctoWatch, Ecto.Adapters.SQL.Sandbox complains. I haven’t dug too deep into it yet – I just wanted to see if you have any advice before I go in.

cheerfulstoic · March 27, 2025, 1:33pm

Yeah, that’s actually the thing that @Bodhert was suggesting in his issue that he linked to. Basically his approach is to disable the sandbox for tests which require verifying your ecto_watch configuration/setup. He was suggesting that we could have a guide in the docs to discuss it.