Socket.IO without a server: your event handlers are SQL functions

create function public.send_message(msg jsonb) returns jsonb
language plpgsql as $$
declare
  saved public.messages;
begin
  insert into public.messages (room, body) values (msg->>'room', msg->>'body')
  returning * into saved;
  perform snout_live.emit('room:' || saved.room, 'message', to_jsonb(saved), true);
  return jsonb_build_object('id', saved.id);
end $$;

select snout_live.on('send', 'public.send_message(jsonb)', '/');

import { io } from 'socket.io-client'

const socket = io('https://<ref>.api.snoutdata.com', {
  path: '/v1/live/socket.io/',
  query: { apikey: APP_KEY },
  auth: { token: session.access_token },
})

socket.on('message', (m) => render(m))
const { id } = await socket.emitWithAck('send', { room: '42', body: 'hello' })

That is a whole chat backend. The message is stored as the person who sent it, everyone else in the room hears it, and the sender gets the new row's id back as the acknowledgement. Nothing of yours is running between the browser and the database.

Why there was a server in the first place

Look at what a Socket.IO server usually does with an event: check who sent it, check they are allowed, write a row, tell a room. Three of those four are already the database's job, and the fourth is a message to whoever is listening. The server around them is a process to host, restart, scale and, once there is more than one, keep in step with the others.

Realtime handlers move that code next to the data it was always about. The checks become the same row-level security policies your data API already uses, so there is one place that decides who may do what, and nothing to keep in step with it.

What happens to an event

• It runs as the caller. The same role and token claims as a request to your data API, so snout_identity.user_id() is the person who sent it and your policies decide what it may read and write. Whether a role may call the function at all is the function's own grant.
• It is one transaction. What it sends with snout_live.emit() goes out only if it commits. A failed insert never announces a message that is not there.
• The return value is the ack. A function that raises comes back as { error: { code, message } }, with your own message: raise exception 'room is full' reaches the client as "room is full".
• A room is a Realtime topic. A handler puts a socket in one with snout_live.join(), as only the server can in Socket.IO. snout_live.signal() from a trigger or a cron job reaches it, and so does any channel of the same name, so Socket.IO clients and channel clients hear each other.
• Connecting is a handler too. A connection handler sees what the client put in auth and decides which rooms it belongs in, or refuses it with a sentence the client receives as its connect_error.

Not only for Socket.IO

The same handlers answer a Realtime channel. With @snoutdata/client 0.4.0:

const room = db.live.topic('room:42')
room.join()

const { data, error } = await room.call('send', { room: '42', body: 'hello' })

A message nobody has a handler for is relayed exactly as before, so turning this on changes nothing for a project that does not use it.

Moving a Socket.IO server over

Take one listener at a time. What it validates becomes a raise, what it writes becomes an insert that your policies check, what it emits becomes snout_live.emit(), and what it passed to ack becomes the return value. Emits from elsewhere in your backend, a REST endpoint telling a room something changed, become snout_live.signal() in the same SQL that made the change. The client changes by its URL and nothing else.

What does not become SQL is a process that holds a connection of its own open: a client logged in to a messaging network, a subscription to a broker, a game loop. On a paid plan that one runs as a Snout Container, your own image running all the time beside the project. Keep it small, with its state in your project's tables, and let it reach your users through snout_live.signal() like everything else.

What a live check found

Before calling this done we drove it end to end on a real paid project, through the same front door your app uses. It found two things our own tests had not, because they ran as a superuser:

• A project's owner on SnoutData Cloud is its own role, not postgres, and could not register a handler. Fixed the same day.
• That owner also could not write the table snout_live.signal() writes to, so a broadcast from your SQL editor, your own triggers or your cron jobs was refused, and only a warning said so. That one was older than handlers. Also fixed: if you tried it before and heard nothing, try again.

The tests now run as an ordinary owner, so neither can come back unnoticed.

Where it is today

Live on SnoutData Cloud for projects on the Plus and Pro plans, since handlers run inside your database. Long-polling and websocket both work, with the upgrade between them. Not supported yet: binary sent from a client (send base64, or a path in Storage; binary to a client works), several listeners for one event, and listing a room's sockets from the server.

Realtime is our own open-source server, Apache-2.0 at github.com/snoutdata/snout-realtime, and in the self-hosted stack, so you can read what runs your handlers or run it yourself.

Start with the Socket.IO guide, which has a full chat in SQL, and Realtime handlers for every rule.