NOTIFY/LISTEN

[daviestar]

Is there a way to access the notification event from node-postgres client via slonik?

I'd like to send messages via WebSocket using this event and pg_notify, similar to this article:

https://medium.com/@simon.white/postgres-publish-subscribe-with-nodejs-996a7e45f88

Thanks for an awesome Postgres client!

[daviestar]

After some more reading on this subject, I discovered

https://github.com/GraphQLCollege/graphql-postgres-subscriptions

which uses a neat little package called pg-ipc under the hood:

https://github.com/emilbayes/pg-ipc

Also, an interesting discussion around the convention of using AsyncIterator for this kind of thing:

apollographql/graphql-subscriptions#116

My ideal feature request would be for slonik to expose an AsyncIterator-style api, and an addEventListener style api (similar to pg-ipc) for maximum flexibility, while still hooking into the same interceptor pattern as other queries.

This could be an interceptor and live in its own package(s), but AFAICS a change would still need to be made to this package to pass the 'raw' pg client as a parameter to an interceptor.

I'd be happy to work on a PR for this if you like the sound of it.

[paulovieira]

This module might also be of interest, in case you haven't heard about it:

https://github.com/andywer/pg-listen

[gajus]

I think that this is conceptually quite different from what the rest of the library is used (persistent subscriptions VS isolated transactions). I don't see anything wrong with pg-listen being used in addition to Slonik, without merging the former into the latter.

Please use 👍 on the OPs issue if you would like this feature. I am keeping an eye on the most upvoted issues. I will re-open the issue if there is meaningful demand.

[anilsambasivan]

I am interested in slonik if supporting this feature, dont want another library for only to listen to database events. Would you be supporting this feature any soon? or is it already available now? @gajus

This is an awesome library, thanks for creating something like this @gajus

[wasd171]

@gajus look like this feature request got quite a few upvotes :) I would second that it would be great to have such support in slonik! Source code of pg-listen is relatively small and a comparable implementation should not bloat slonik too much (also I would expect that some functionality like reconnects is implemented in slonik already). Having a single library to interact with PostgreSQL would be a breeze

[gajus]

Open to exploring it, but since I don't have active use case, it isn't something that I plan to own.

[wasd171]

Unfortunately I am not too confident with slonik's internals to contribute but perhaps these findings would help someone:

The way pg-listen works is the following:

1. Opens and holds 1 database connection
2. For every .notifications.on(channel, callback) sends a LISTEN ${channel}; query over this connection
3. Hooks into the client.on('notification', callback) and re-emits these messages to subscribers
4. ^ on error / disconnect repeats the routine

Since slonik is (at least so far) based on pg it is possible to replicate this logic to some extent:

1. Open a connection that would never resolve (you might need to create a dedicated pool with 1 connection and connectionTimeout: 'DISABLE_TIMEOUT')

pool.connect(async connection => {
  await connection.query(sql.unsafe`LISTEN ${channel}`)
  // same for all other channels that you want to subscribe to

  await new Promise(resolve => {}) // never resolves but you might want to add some EventEmitter to resolve at some point
})

2. Modify these lines

const onNotification = (notification) => {
	console.log({notification});
}

client.on('error', onError);
client.on('notice', onNotice);
client.on('notification', onNotification);

return {
	connect: async () => {
		await client.connect();
	},
	end: async () => {
		await client.end();
		client.removeListener('error', onError);
		client.removeListener('notice', onNotice);
		client.removeListener('notification', onNotification);
	},

3. Run the code and execute NOTIFY ${channel}, ${payload};
4. See the message in your console :)

Initially I thought that it would be possible to use pool.stream to stream the LISTEN changes, but unfortunately it does not work like that. I guess this feature would require new API instead of patching the existing one, something like that

pool.listen(channel: string, callback: (notification: Notification) => void): Promise<void> // AsyncIterator would also be an option
pool.unlisten(channel: string): Promise<void>
pool.unlistenAll(): Promise<void> // or pool.unlisten('*')

Since NOTIFY can be executed with pool.query() already it does not require new API