Build a Real-Time Chat App with PostgreSQL and Server-Sent Events

January 2026 · SSEReal-TimePostgreSQLChatNpgsqlRest

Real-time features like chat, notifications, and live updates are expected in modern applications. Traditional implementations require WebSocket servers, message brokers, pub/sub infrastructure, and hundreds of lines of code across multiple services.

What if you could build a fully functional, secure real-time chat with just a single SQL procedure and a few lines of TypeScript?

This tutorial demonstrates how NpgsqlRest's Server-Sent Events (SSE) support transforms PostgreSQL's RAISE statements into real-time events - enabling you to build secure chat applications in minutes, not days.

Source Code: github.com/NpgsqlRest/npgsqlrest-docs/examples/8_simple_chat_client

The Traditional Approach: Complex Infrastructure

Building real-time chat the traditional way requires:

1. WebSocket Server - Separate service to manage persistent connections
2. Message Broker - Redis Pub/Sub, RabbitMQ, or similar for message distribution
3. Connection Management - Track connected users, handle reconnections
4. Authentication Integration - Validate tokens on WebSocket handshake
5. Scaling Strategy - Sticky sessions or shared state for horizontal scaling
6. Frontend WebSocket Client - Handle connection lifecycle, reconnection logic
7. Backend API - REST endpoints for message history, user management
8. Database Integration - Separate persistence layer for messages

This creates a complex distributed system with multiple failure points, deployment complexity, and significant development time.

The NpgsqlRest Approach: PostgreSQL IS Your Real-Time Server

NpgsqlRest takes a radically different approach. Instead of adding infrastructure, it uses what you already have:

• PostgreSQL RAISE statements become SSE events - No message broker needed
• Cookie authentication works automatically - Same auth for REST and SSE
• Scoped event distribution - Control who receives events with annotations
• Single deployment - No separate WebSocket server
• Auto-generated TypeScript client - Including EventSource factory functions

The key insight: PostgreSQL already supports sending messages during query execution via RAISE. NpgsqlRest captures these messages and streams them to connected clients via Server-Sent Events.

How SSE Works in NpgsqlRest

    ┌─────────────────┐                    ┌─────────────────┐
    │   Alice's       │                    │    Bob's        │
    │   Browser       │                    │    Browser      │
    └────────┬────────┘                    └────────┬────────┘
             │                                      │
             │ EventSource("/api/send-message/info?TEST_CHANNEL")
             │◄─────────────────────────────────────┤
             │          SSE Connections             │
             ▼                                      ▼
    ┌─────────────────────────────────────────────────────────┐
    │                     NpgsqlRest                          │
    │                                                         │
    │   Maintains SSE connections for authorized clients      │
    │   Filters events by scope (authorize, matching, all)    │
    └────────────────────────┬────────────────────────────────┘
                             │
                             │ RAISE INFO '{"message": "Hello"}'
                             │
                             ▼
    ┌─────────────────────────────────────────────────────────┐
    │                    PostgreSQL                           │
    │                                                         │
    │   PROCEDURE send_message() executes RAISE INFO          │
    │   NpgsqlRest captures the notice and broadcasts         │
    └─────────────────────────────────────────────────────────┘

When a PostgreSQL function or procedure executes RAISE INFO, RAISE NOTICE, or RAISE WARNING, NpgsqlRest captures these messages and streams them to connected SSE clients based on the configured scope.

Building the Chat: Step by Step

Step 1: Schema Setup

-- Users table with secure password hashing
create table example_8.users (
    user_id int primary key generated always as identity,
    username text not null unique,
    password_hash text not null
);

-- Insert test users (alice/password123, bob/password456)
insert into example_8.users (username, password_hash) values
    ('alice', crypt('password123', gen_salt('bf'))),
    ('bob', crypt('password456', gen_salt('bf')));

-- Messages table for chat history
create table example_8.messages (
    message_id int primary key generated always as identity,
    user_id int not null references example_8.users(user_id),
    username text not null,
    message_text text not null,
    created_at timestamptz not null default now()
);

Step 2: Login Function

create function example_8.login(_username text, _password text)
returns table (scheme text, user_id int, user_name text)
language sql
security definer
as $$
    select 'cookies', u.user_id, u.username
    from example_8.users u
    where u.username = _username
      and u.password_hash = crypt(_password, u.password_hash);
$$;

comment on function example_8.login(text, text) is '
HTTP POST
@login
@anonymous';

The login annotation creates a cookie-based session automatically.

Step 3: The Magic - Send Message with SSE

Here's the entire backend for real-time messaging:

create procedure example_8.send_message(
    _message_text text,
    _user_id text = null,
    _user_name text = null
)
language plpgsql
as $$
declare
    _message_id int;
    _created_at timestamptz;
begin
    -- Store the message
    insert into example_8.messages (user_id, username, message_text)
    values (_user_id::int, _user_name, _message_text)
    returning message_id, created_at into _message_id, _created_at;

    -- Broadcast to all connected authorized clients via SSE
    raise info '%', json_build_object(
        'message_id', _message_id,
        'user_id', _user_id::int,
        'username', _user_name,
        'message_text', _message_text,
        'created_at', _created_at
    );
end;
$$;

comment on procedure example_8.send_message(text, text, text) is '
HTTP POST
@authorize
@sse
@sse_scope authorize';

That's it. The entire real-time messaging backend is 25 lines of SQL.

Let's break down the annotations:

Annotation	Purpose
authorize	Only authenticated users can send messages
sse	Enable Server-Sent Events for this endpoint
sse_scope authorize	Only authenticated clients receive events

The RAISE INFO statement with JSON payload becomes the SSE event data. NpgsqlRest automatically:

• Captures the notice during procedure execution
• Serializes it as an SSE event
• Broadcasts to all connected clients matching the scope
• Handles connection management, reconnection, and cleanup

Step 4: Message History

create function example_8.get_messages()
returns table (
    message_id int,
    user_id int,
    username text,
    message_text text,
    created_at timestamptz
)
language sql
as $$
    select message_id, user_id, username, message_text, created_at
    from example_8.messages
    order by created_at asc;
$$;

comment on function example_8.get_messages() is '
HTTP GET
@authorize';

Understanding SSE Scopes

The sse_scope annotation controls who receives events:

sse_scope authorize

Only authenticated clients receive events. Perfect for private chat rooms.

comment on procedure private_broadcast() is '
@sse
@sse_scope authorize';

sse_scope authorize <roles/users>

Target specific roles or users:

-- Only admins receive these events
comment on procedure admin_notification() is '
@sse
@sse_scope authorize admin';

-- Specific users receive events
comment on procedure user_alert() is '
@sse
@sse_scope authorize alice, bob';

sse_scope matching

Clients with matching security context receive events:

comment on procedure team_update() is '
@sse
@sse_scope matching';

sse_scope all

Broadcast to everyone (use carefully):

comment on procedure public_announcement() is '
@sse
@sse_scope all';

Dynamic Scopes with RAISE HINT

Override scope per-event at runtime:

-- This event goes to admins only
raise notice 'Admin alert: server load high' using hint = 'authorize admin';

-- This event goes to everyone
raise notice 'System maintenance in 5 minutes' using hint = 'all';

-- This uses the default scope from annotation
raise notice 'Regular update...';

The Auto-Generated TypeScript Client

NpgsqlRest generates a complete TypeScript client including SSE support:

// Auto-generated EventSource factory
export const createSendMessageEventSource = (id: string = "") =>
    new EventSource(baseUrl + "/api/example-8/send-message/info?" + id);

// Auto-generated send function with SSE support
export async function sendMessage(
    request: ISendMessageRequest,
    onMessage?: (message: string) => void,
    id: string | undefined = undefined,
    closeAfterMs = 1000,
    awaitConnectionMs: number | undefined = 0
): Promise<{status: number, error: ... }> {
    const executionId = id ? id : window.crypto.randomUUID();
    let eventSource: EventSource;

    if (onMessage) {
        eventSource = createSendMessageEventSource(executionId);
        eventSource.onmessage = (event: MessageEvent) => {
            onMessage(event.data);
        };
        // ... connection handling
    }

    // ... fetch call with X-NpgsqlRest-ID header
}

The Frontend: Minimal Code Required

Using the generated client, the frontend is straightforward:

import { login, logout, sendMessage, createSendMessageEventSource, getMessages }
    from "./example8Api.ts";

const channelName = "TEST_CHANNEL";
let eventSource: EventSource | null = null;

// Connect to SSE when user logs in
function connectEventSource() {
    eventSource = createSendMessageEventSource(channelName);

    eventSource.onmessage = (event: MessageEvent) => {
        const msg = JSON.parse(event.data);
        appendMessage(msg);  // Display in UI
    };
}

// Send a message - it will be broadcast to all connected clients
async function sendChatMessage() {
    const messageText = messageInput.value.trim();
    if (!messageText) return;

    messageInput.value = "";

    await sendMessage(
        { messageText },
        undefined,      // Skip local onMessage (we're already connected)
        channelName     // Channel identifier
    );
}

// Disconnect when logging out
function disconnectEventSource() {
    if (eventSource) {
        eventSource.close();
        eventSource = null;
    }
}

Code Comparison: Traditional vs NpgsqlRest

Traditional Real-Time Chat Architecture

Backend (Node.js + Socket.IO + Redis):

// server.js - WebSocket server
const io = require('socket.io')(server);
const redis = require('redis');
const pub = redis.createClient();
const sub = redis.createClient();

// Authentication middleware
io.use(async (socket, next) => {
    const token = socket.handshake.auth.token;
    try {
        const user = await verifyToken(token);
        socket.user = user;
        next();
    } catch (err) {
        next(new Error('Authentication failed'));
    }
});

// Connection handling
io.on('connection', (socket) => {
    const userId = socket.user.id;

    // Join user's room
    socket.join(`user:${userId}`);

    // Handle chat messages
    socket.on('chat:message', async (data) => {
        // Save to database
        const message = await db.messages.create({
            userId: socket.user.id,
            username: socket.user.username,
            text: data.text,
            createdAt: new Date()
        });

        // Broadcast via Redis pub/sub
        pub.publish('chat:messages', JSON.stringify(message));
    });

    // Handle disconnection
    socket.on('disconnect', () => {
        console.log(`User ${userId} disconnected`);
    });
});

// Redis subscription for horizontal scaling
sub.subscribe('chat:messages');
sub.on('message', (channel, message) => {
    const msg = JSON.parse(message);
    io.emit('chat:message', msg);
});

Plus you need:

• Redis server running
• Session store configuration
• CORS configuration
• Reconnection logic
• Heartbeat/ping-pong
• Room management
• Error handling

Frontend (Socket.IO client):

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

const socket = io('http://localhost:3000', {
    auth: { token: getAuthToken() },
    reconnection: true,
    reconnectionAttempts: 5,
    reconnectionDelay: 1000
});

socket.on('connect', () => {
    console.log('Connected');
    loadMessageHistory();
});

socket.on('chat:message', (msg) => {
    appendMessage(msg);
});

socket.on('disconnect', () => {
    showDisconnected();
});

socket.on('connect_error', (err) => {
    handleConnectionError(err);
});

function sendMessage(text) {
    socket.emit('chat:message', { text });
}

NpgsqlRest Approach

Backend (SQL only):

create procedure example_8.send_message(
    _message_text text,
    _user_id text = null,
    _user_name text = null
)
language plpgsql
as $$
declare
    _message_id int;
    _created_at timestamptz;
begin
    insert into example_8.messages (user_id, username, message_text)
    values (_user_id::int, _user_name, _message_text)
    returning message_id, created_at into _message_id, _created_at;

    raise info '%', json_build_object(
        'message_id', _message_id,
        'user_id', _user_id::int,
        'username', _user_name,
        'message_text', _message_text,
        'created_at', _created_at
    );
end;
$$;

comment on procedure example_8.send_message(text, text, text) is '
HTTP POST
@authorize
@sse
@sse_scope authorize';

Frontend (using generated client):

import { sendMessage, createSendMessageEventSource } from "./example8Api.ts";

const eventSource = createSendMessageEventSource(channelName);

eventSource.onmessage = (event) => {
    appendMessage(JSON.parse(event.data));
};

async function send(text: string) {
    await sendMessage({ messageText: text }, undefined, channelName);
}

The Numbers

Component	Traditional	NpgsqlRest
Backend code	100-200 lines	25 lines (SQL)
Frontend code	50-80 lines	20 lines
Infrastructure	WebSocket server + Redis	None (PostgreSQL only)
Dependencies	socket.io, redis, jwt, etc.	None additional
Services to deploy	3+ (API, WebSocket, Redis)	1 (NpgsqlRest)
TypeScript types	Manual	Auto-generated
Auth integration	Custom middleware	Built-in (cookies)
Horizontal scaling	Redis pub/sub required	Works out of the box
Time to implement	1-3 days	30 minutes

Estimated savings: 85-90% less code, single deployment, zero additional infrastructure.

When to Use SSE vs WebSockets

SSE (Server-Sent Events) is ideal for:

• Server-to-client streaming - Chat messages, notifications, live updates
• Simple integration - Uses standard HTTP, works through proxies
• Auto-reconnection - Built into the EventSource API
• Cookie auth works seamlessly - Cookies are sent automatically with EventSource connections

WebSockets are better for:

• Bidirectional high-frequency - Gaming, collaborative editing
• Binary data - File transfers, video streaming
• Custom protocols - When you need full control

For most real-time features (chat, notifications, dashboards), SSE is simpler and sufficient.

Why Not PostgreSQL LISTEN/NOTIFY?

A common question: why use RAISE INFO instead of PostgreSQL's built-in LISTEN/NOTIFY for real-time events?

LISTEN/NOTIFY has a critical scalability problem. When you execute NOTIFY during a transaction, PostgreSQL acquires a global lock on the entire database during the commit phase. This serializes all commits across your system.

As documented by Recall.ai, this creates severe issues under high concurrency:

• Global mutex contention - All transactions queue behind the notification lock
• Throughput collapse - Query throughput drops dramatically under load
• Paradoxical behavior - CPU, disk I/O, and network actually decrease during high load because processes are waiting for the lock
• Hundreds of blocked processes - Sessions pile up waiting for the global lock

Their load testing showed that removing NOTIFY allowed full CPU utilization and rapid recovery from load spikes, while NOTIFY caused the database to grind to a halt.

How NpgsqlRest Avoids This Problem

NpgsqlRest's SSE implementation uses RAISE INFO/NOTICE/WARNING instead of NOTIFY:

Aspect	LISTEN/NOTIFY	RAISE + SSE
Locking	Global database lock on commit	No additional locking
Scalability	Serializes all commits	Scales with connections
Delivery	Requires dedicated listener connection	HTTP streaming (standard)
Persistence	Fire-and-forget (can lose messages)	Immediate streaming
Connection model	Long-lived DB connections	Standard HTTP connections
Client implementation	Custom pg_notify client	Standard EventSource API

RAISE statements are connection-local - they emit notices to the current connection's notice handler without any global coordination. NpgsqlRest captures these notices during query execution and streams them to SSE clients. No locks, no contention, no scalability ceiling.

This is why NpgsqlRest chose RAISE over NOTIFY for real-time events - it's architecturally sound for high-concurrency systems.

Advanced: Multiple Channels

Use the execution ID to create separate channels:

// Different channels for different purposes
const generalChat = createSendMessageEventSource("general");
const teamChat = createSendMessageEventSource("team-123");
const notifications = createSendMessageEventSource("notifications");

// Send to specific channel
await sendMessage({ messageText: "Hello team!" }, undefined, "team-123");

Messages with matching IDs in the X-NpgsqlRest-ID header are routed to EventSource connections with matching query parameters.

Conclusion

NpgsqlRest's SSE support transforms PostgreSQL's notice system into a powerful real-time messaging infrastructure. By using RAISE INFO to emit events and annotations to control distribution, you can build secure real-time features with minimal code and zero additional infrastructure.

The key benefits:

• No WebSocket server - SSE uses standard HTTP
• No message broker - PostgreSQL handles everything
• Secure by default - Same cookie auth as your REST API
• Scoped distribution - Control exactly who receives events
• Auto-generated client - Including EventSource factories

For chat, notifications, live dashboards, and most real-time use cases, this approach eliminates complexity while maintaining security and scalability.

Source Code: View the complete example on GitHub

More Blog Posts:
Custom Types & Multiset · Performance & High Availability · Benchmark 2025 · End-to-End Type Checking · Database-Level Security · Multiple Auth Schemes & RBAC · PostgreSQL BI Server · Secure Image Uploads · CSV & Excel Ingestion · External API Calls · Reverse Proxy & AI Service · Zero to CRUD API · NpgsqlRest vs PostgREST vs Supabase

Get Started:
Quick Start Guide · SSE Annotation · SSE Scope · SSE Level · Code Generation