How to use the postgres-adapter in Socket.io in node.js

[uhakdt]

My current setup is as such: I have connected to my postgres database and i also have the websocket connected fine to the frontend. But I am not sure how to use the postgres-adapter as its suggested in the documentation: They say that you make a pool instead of a Client - thats fine for the db - then you say you use the io.adapter(createAdapter(pool));

The main functionality I want to get at is - when a change occurs on the postgresdb - then a notification gets send out just as I have currently - but then it should be connected inside the websocket for the websocket to send a message with the data to all connected clients. Has anyone implemented this?

client.connect(err => {
  if (err) {
    console.log("Error in connecting database: ", err);
  } else {
    console.log("Database connected");
    client.on('notification', (msg) => {
      console.log(msg.payload);
      console.log(msg)
    });
    const query = client.query("LISTEN update_notification");
  }
})

const webSocketSetup = (server) => {
  var io = new Server(server, {
    cors: {
      origin: [
        'http://localhost:3000',
      ]
    }
  });
  
  io.on('connection', (socket) => {
    console.log('a user connected');
    socket.on('disconnect', () => {
      console.log('user disconnected');
    });

    socket.on('my message', (msg) => {
      "" I Want the postgres Notification to be setup here. ""
    });

  });
}

[darrachequesne]

The PostgreSQL adapter acts as a relay between several Socket.IO servers (so that a packet sent with io.emit() gets broadcasted to all connected clients).

Reference: https://socket.io/docs/v4/postgres-adapter/

If I understand your use case correctly, you should be able to use a room:

socket.on('my message', (msg) => {
  socket.join('room for notification listeners');
});

And then:

client.on('notification', (msg) => {
  io.to('room for notification listeners').emit('new notification', msg.payload);
});

Or if you want to broadcast to all clients directly:

client.on('notification', (msg) => {
  io.emit('new notification', msg.payload);
});

[uhakdt]

Thank you very much for that - that solved my problem!

[rohitbrleaf]

can we connect with sequelize

[darrachequesne]

@rohitbrleaf unfortunately, I don't think sequelize gives access to the underlying pg pool.

See: https://stackoverflow.com/questions/39457757/share-a-database-connection-pool-between-sequelize-and-pg

[izzuddinraffar]

Hello @darrachequesne , I'm using Knex.js as my query builder. Can I utilize the postgres-adapter with Knex.js instead of the pg library? If it's possible, could you please provide an example of how to set up the connection and use it?

[darrachequesne]

@izzuddinraffar unfortunately, Knex.js provides its own connection pooling manager, so I don't think it is possible to use it with this adapter.

Reference: https://github.com/knex/knex/blob/master/lib/dialects/postgres/index.js