Feature request: Add support for Data Change Notification Callbacks

[OldhamMade]

It would be great if exqlite supported Data Change Notification Callbacks, eg. set_update_hook, similar to mmzeeman/esqlite. esqlite is great, however it is very Erlang-focused and the original Elixir wrapper library for it has been abandoned. It would be very useful to be able to act on hooks within a project that is using ecto_sqlite3.

[warmwaffles]

I've contemplated adding that functionality. Although I am not sure how to make that jive with how ecto works.

I think on the adapter level, we could add it, but the main issue is how to handle it when the DbConnection pools the connections to the database.

Can you provide me a concrete use case that this would fit in to?

[ruslandoga]

@warmwaffles I wonder if it can be a separate connection behaviour like postgrex/replication_connection.ex

children = [
  App.DataChangeConnection, # something like Postgrex.ReplicationConnection with some user defined callbacks
  App.Repo, # ecto_sqlite3 repo
  # ...
]

Supervisor.start_link(children, ...)

Hm, by sqlite3_update_hook docs it appears it's limited to only the current connection changes. Then this approach won't work as App.DataChangeConnection won't receive any updates.

[warmwaffles]

I'm not entirely sure how this would function because the update hooks would only work on the currently connected db connection / exqlite connection.

Definitely an interesting use case that doesn't fit the current paradigm that DbConnection and Ecto have defined.

[OldhamMade]

Can you provide me a concrete use case that this would fit in to?

It would allow someone using a SQLite database to get close to mimicking the LISTEN/NOTIFY functionality from Postgres, which is my specific use-case: I'd like to act on table updates caused by DB triggers from within Elixir code.

Although I am not sure how to make that jive with how ecto works.

I'm not sure that it needs to. I think it is more akin to Postgrex.Notifications, a useful extra.

[warmwaffles]

I didn't realize Postgrex had that ability. I'll take a look at how they resolved the LISTEN / NOTIFY. We may be able to utilize a similar functionality.

[mdwaud]

+1 for interest in this capability