postgresql2websocket

How does it work?

Execute postgresql2websocket.py and connect to ws://localhost:8080/channel where channel is the channel name of the selected database (see PostgreSQL documentation - NOTIFY).

How to configure it?

Copy postgresql2websocket.conf.example as postgresql2websocket.conf and change the values according to your needs.

What technologies are you using?

• PostgreSQL
• Python (3.5.3 or greater)
• aiohttp + asyncpg
• Poetry for dependency management

What can I do with it?

You can create a trigger on a table in order to get notifications over a websocket in real time. For example, this function send a notification using the postgresql2websocket channel containing the action that has been performed and a JSON representation of the row.

CREATE OR REPLACE FUNCTION table_update_notify() RETURNS trigger AS $$
DECLARE
  rec RECORD;
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    rec = NEW;
  ELSE
    rec = OLD;
  END IF;
  -- postgresql2websocket is the default channel name
  PERFORM pg_notify('postgresql2websocket', json_build_object('table', TG_TABLE_NAME, 'type', TG_OP, 'row', row_to_json(rec))::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Then you can trigger this function when something happens on mytable:

CREATE TRIGGER mytable_notify_update AFTER UPDATE ON mytable FOR EACH ROW EXECUTE PROCEDURE table_update_notify();
CREATE TRIGGER mytable_notify_insert AFTER INSERT ON mytable FOR EACH ROW EXECUTE PROCEDURE table_update_notify();
CREATE TRIGGER mytable_notify_delete AFTER DELETE ON mytable FOR EACH ROW EXECUTE PROCEDURE table_update_notify();

Bidirectional communication

The websocket channel can be used to execute remote functions too:

For example:

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Executing ws.send('select add(1, 2);') will return a JSON response containing {"add": 3}.

How to use it?

Execute postgresql2websocket.py, open examples/console.html and execute some operations on the table.

Limitations

The "payload" string to be communicated along with the notification. This must be specified as a simple string literal. In the default configuration it must be shorter than 8000 bytes. (If binary data or large amounts of information need to be communicated, it's best to put it in a database table and send the key of the record.)

Source: https://www.postgresql.org/docs/current/static/sql-notify.html