EctoWatch - Get updates from PostgreSQL in your Elixir app

cheerfulstoic

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:

https://github.com/cheerfulstoic/ecto_watch

10 comments

#library #ecto_watch

19 1228 10

2025-03-27 13:33:03 UTC

Showing Posts 1 to 10

Show Best Posts
Show All (oldest first)
Show All (newest first)

c4710n

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

Post #1

akramic

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

Post #2

apoorv-2204

Can we use this in production?

Post #3

cheerfulstoic

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.

Post #4

apoorv-2204

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

Post #5

cheerfulstoic

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.

Post #6

Bodhert

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.
https://github.com/cheerfulstoic/ecto_watch/issues/43

Post #7

cheerfulstoic

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

Post #8

StandardMikeBenza

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.

Post #9

cheerfulstoic

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.

Post #10

— All posts loaded —