How to Use MySQL Database with WebSocket

The first time I built a “real-time” feature backed by MySQL, I made the classic beginner mistake: I had the client poll a WebSocket server every second, which just ran a fresh SELECT against MySQL every second, for every connected client. It worked fine with five users in a demo and fell over completely under real load. Getting this right taught me a lot about how MySQL and WebSocket architectures actually need to fit together, and that’s what I want to walk through here — from the fundamentals up through the patterns I actually use in production.

Why This Combination Is Tricky

WebSockets give you a persistent, bidirectional connection so a server can push data to clients the instant something changes — no polling required. MySQL, on the other hand, has no native “push” mechanism to notify your application layer when a row changes. That mismatch is the core challenge: you need something that bridges MySQL’s inherently pull-based nature with a WebSocket server’s push-based nature.

flowchart LR
    Client1[WebSocket Client 1] <--> WSServer[WebSocket Server]
    Client2[WebSocket Client 2] <--> WSServer
    Client3[WebSocket Client 3] <--> WSServer
    WSServer <--> Bridge[Change Detection / Bridge Layer]
    Bridge <--> MySQL[(MySQL Database)]

The “Bridge” layer is where the real engineering decisions happen, and there are a few different valid approaches depending on your requirements.

Approach 1: Application-Level Events (Simplest, Most Common)

In most real-world systems I’ve built, I don’t ask MySQL to notify me of changes at all. Instead, the application code that performs the write is the same code that knows to push a WebSocket update — MySQL stays a simple, dumb data store, and the “real-time” logic lives entirely in the application layer.

sequenceDiagram
    participant Client as WebSocket Client
    participant App as Application Server
    participant MySQL as MySQL Database
    participant WS as WebSocket Server / Pub-Sub

    Client->>App: HTTP request to update data
    App->>MySQL: UPDATE orders SET status='shipped' WHERE order_id=1001
    MySQL-->>App: Success
    App->>WS: Publish event: order_1001_updated
    WS->>Client: Push update to subscribed clients

A minimal Node.js example using ws and a MySQL client:

const WebSocket = require('ws');
const mysql = require('mysql2/promise');

const wss = new WebSocket.Server({ port: 8080 });
const subscriptions = new Map(); // orderId -> Set of ws clients

wss.on('connection', (ws) => {
  ws.on('message', (msg) => {
    const { action, orderId } = JSON.parse(msg);
    if (action === 'subscribe') {
      if (!subscriptions.has(orderId)) subscriptions.set(orderId, new Set());
      subscriptions.get(orderId).add(ws);
    }
  });

  ws.on('close', () => {
    for (const clients of subscriptions.values()) clients.delete(ws);
  });
});

async function updateOrderStatus(orderId, newStatus) {
  const pool = await mysql.createPool({ host: 'localhost', user: 'app_user', database: 'ecommerce_db' });
  await pool.execute('UPDATE orders SET order_status = ? WHERE order_id = ?', [newStatus, orderId]);

  const clients = subscriptions.get(orderId);
  if (clients) {
    const payload = JSON.stringify({ orderId, status: newStatus });
    for (const client of clients) client.send(payload);
  }
}

I favor this approach for the majority of use cases — order status updates, chat-adjacent features, live dashboards driven by user actions — because it’s simple, has no extra infrastructure, and the “real-time” logic is exactly as reliable as your application code.

The limitation: this only works if every write to MySQL goes through this application layer. If another service, a batch job, or a direct database change modifies the data, WebSocket clients won’t hear about it.

Approach 2: Change Data Capture (CDC) via Binary Log

When multiple systems can write to MySQL, or you need guaranteed notification of every change regardless of source, I go back to the binary log — the same mechanism I use for replication and ETL (see my other articles on those topics). Tools like Debezium stream row-level change events out of MySQL’s binlog into a message broker (usually Kafka), and a WebSocket server subscribes to that stream to push updates to clients.

flowchart LR
    A[Any Writer: App, Batch Job, Admin Tool] --> B[(MySQL)]
    B --> C[Binary Log]
    C --> D[Debezium Connector]
    D --> E[Kafka Topic]
    E --> F[WebSocket Bridge Service]
    F --> G[Connected WebSocket Clients]

This decouples “who wrote the data” from “who needs to know it changed” completely — any write, from any source, eventually surfaces as a WebSocket event, because it’s reading directly off the actual stream of committed changes rather than trusting application code to remember to publish an event.

I reach for this approach specifically when:

• Multiple independent services or tools can write to the same tables.
• I need guaranteed delivery and ordering of change events (Kafka gives durability that in-process pub/sub doesn’t).
• I want to decouple the WebSocket layer entirely from the write path for scalability reasons.

Approach 3: MySQL Triggers + Notification Table (Lightweight Middle Ground)

For a smaller-scale system where I don’t want the operational overhead of Kafka/Debezium but still want the database itself to be the source of truth for “something changed,” I’ve used triggers combined with a polling or notification table:

CREATE TABLE change_events (
    event_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL,
    row_id BIGINT UNSIGNED NOT NULL,
    event_type ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    processed BOOLEAN NOT NULL DEFAULT FALSE
) ENGINE=InnoDB;

DELIMITER //
CREATE TRIGGER trg_orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO change_events (table_name, row_id, event_type)
    VALUES ('orders', NEW.order_id, 'UPDATE');
END //
DELIMITER ;

A lightweight background worker polls change_events on a short interval (say, every 200–500ms), pushes new rows out over WebSocket, and marks them processed:

setInterval(async () => {
  const [rows] = await pool.execute(
    'SELECT * FROM change_events WHERE processed = FALSE ORDER BY event_id ASC LIMIT 100'
  );
  for (const row of rows) {
    broadcastToSubscribers(row.table_name, row.row_id, row.event_type);
    await pool.execute('UPDATE change_events SET processed = TRUE WHERE event_id = ?', [row.event_id]);
  }
}, 300);

I’m honest about this approach’s trade-off: it’s not truly push-based at the database level (it’s short-interval polling under the hood), and the change_events table needs regular cleanup/archiving so it doesn’t grow unbounded. But it avoids running a full CDC/Kafka stack, which is a meaningful simplicity win for smaller systems.

Managing MySQL Connections in a WebSocket Server

This is where I’ve seen the most production incidents, so I want to be specific about it. WebSocket servers hold long-lived connections per client — potentially thousands of them — but that does not mean you should hold a dedicated MySQL connection per WebSocket client. That exhausts max_connections almost immediately.

// WRONG: opens a new MySQL connection per WebSocket client
wss.on('connection', async (ws) => {
  const conn = await mysql.createConnection({ host: 'localhost', ... }); // do NOT do this
});

// RIGHT: a shared connection pool used by all WebSocket handling logic
const pool = mysql.createPool({
  host: 'localhost',
  user: 'app_user',
  database: 'ecommerce_db',
  waitForConnections: true,
  connectionLimit: 20,
  queueLimit: 0
});

wss.on('connection', (ws) => {
  ws.on('message', async (msg) => {
    const [rows] = await pool.query('SELECT * FROM orders WHERE order_id = ?', [orderId]);
    ws.send(JSON.stringify(rows[0]));
  });
});

I size connectionLimit based on realistic concurrent query load, not concurrent WebSocket connections — those are two very different numbers. A server handling 10,000 WebSocket connections might genuinely only need 20–50 MySQL connections in the pool if most of those connections are idle most of the time, which they usually are for real-time push scenarios.

-- On the MySQL side, I always check this matches expectations across all app instances combined
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

Scaling WebSocket + MySQL Horizontally

Once I need more than one WebSocket server instance (for horizontal scaling or high availability), a new problem appears: a change published by application instance A needs to reach WebSocket clients connected to instance B. I solve this with a pub/sub layer between instances — Redis Pub/Sub is what I reach for most often given its simplicity:

flowchart TB
    subgraph Instance A
    WA[WebSocket Server A] --> ClientsA[Clients connected to A]
    end
    subgraph Instance B
    WB[WebSocket Server B] --> ClientsB[Clients connected to B]
    end
    App[Application Write] --> MySQL[(MySQL)]
    App --> Redis[Redis Pub/Sub Channel]
    Redis --> WA
    Redis --> WB

const redisSub = redis.createClient();
redisSub.subscribe('order_updates');
redisSub.on('message', (channel, message) => {
  broadcastToLocalSubscribers(JSON.parse(message));
});

async function updateOrderStatus(orderId, newStatus) {
  await pool.execute('UPDATE orders SET order_status = ? WHERE order_id = ?', [newStatus, orderId]);
  await redisPub.publish('order_updates', JSON.stringify({ orderId, status: newStatus }));
}

This way, MySQL remains purely the durable source of truth, while Redis handles fan-out across WebSocket server instances — each doing what it’s actually good at.

Handling Race Conditions Between Writes and Broadcasts

I always make sure the WebSocket broadcast happens after the MySQL write is confirmed committed, never before or in a way that could race ahead of it:

// Correct order: commit first, broadcast second
await connection.beginTransaction();
try {
  await connection.execute('UPDATE inventory SET quantity = quantity - 1 WHERE sku = ?', [sku]);
  await connection.commit();
  await redisPub.publish('inventory_updates', JSON.stringify({ sku, delta: -1 }));
} catch (err) {
  await connection.rollback();
}

If the broadcast happens before commit and the transaction later rolls back, clients receive phantom updates for data that never actually persisted — a subtle bug that’s genuinely painful to track down later.

Performance Considerations

• I never let WebSocket message handlers block on slow, unindexed MySQL queries — every query triggered by a WebSocket event goes through the same query optimization discipline as any other production query (proper indexes, verified with EXPLAIN).
• For high-frequency updates (e.g., live counters, stock tickers), I batch/throttle broadcasts rather than pushing a WebSocket message on every single row change — clients rarely need updates faster than every 100–250ms for human-perceptible real-time feel, and this dramatically reduces both MySQL query load and network traffic.
• I use read replicas for any WebSocket-triggered read-heavy queries (e.g., “send me the full current state on reconnect”) to keep that load off the primary.

Security Considerations

• I authenticate WebSocket connections (token-based, validated on connection handshake) before allowing any subscription to data — a WebSocket endpoint is a live, persistent attack surface and deserves the same rigor as any REST API.
• I authorize subscriptions per-resource — a client should only be able to subscribe to updates for orders/data they’re actually permitted to see, checked against MySQL just as a REST endpoint would.
• I use a tightly-scoped MySQL user for the WebSocket bridge service, with only the specific privileges it needs.
• For CDC-based approaches, I make sure the Kafka/Debezium layer itself is secured (authentication, encryption in transit) since it now carries a full stream of database changes.

wss.on('connection', (ws, req) => {
  const token = getTokenFromRequest(req);
  const user = verifyJWT(token);
  if (!user) {
    ws.close(4001, 'Unauthorized');
    return;
  }
  ws.userId = user.id;
});

Troubleshooting Common Issues

Best Practices I Follow

• Use a shared MySQL connection pool sized for real query concurrency, never a connection-per-WebSocket-client model.
• Choose the bridge mechanism deliberately: application-level events for simplicity, CDC/binlog streaming when multiple writers need to be captured reliably.
• Always broadcast only after a confirmed MySQL commit, never before.
• Use Redis (or an equivalent) for cross-instance fan-out once you scale beyond a single WebSocket server.
• Authenticate and authorize WebSocket subscriptions with the same rigor as REST endpoints.
• Throttle high-frequency updates rather than broadcasting on every single row change.

Interview Questions

1. Why is it a bad idea to open a dedicated MySQL connection per WebSocket client?
2. What’s the difference between application-level event publishing and CDC-based change detection for driving WebSocket updates?
3. How would you ensure WebSocket clients on different server instances all receive the same real-time update?
4. Why must a WebSocket broadcast happen only after a MySQL transaction commit, not before?
5. When would you choose Debezium/binlog-based CDC over simple application-level events for a real-time feature?
6. How would you secure a WebSocket endpoint that streams live database changes to clients?
7. How would you handle throttling for a feature that needs to broadcast very high-frequency data changes (e.g., live inventory counts)?

FAQs

Do I need Kafka and Debezium for every real-time MySQL + WebSocket feature? No — for most applications where writes go through a single application layer, publishing events directly from that application code after a successful write is simpler and perfectly reliable. I reach for CDC/Kafka specifically when multiple independent writers need to be captured or when I need guaranteed, ordered delivery semantics.

Can MySQL push notifications directly to my application without polling or binlog streaming? Not natively — MySQL has no built-in pub/sub or LISTEN/NOTIFY mechanism (unlike PostgreSQL’s LISTEN/NOTIFY). The binary log is effectively MySQL’s closest equivalent, which is why CDC tools built on top of it exist.

How many MySQL connections do I actually need for a WebSocket server handling thousands of clients? Far fewer than the client count — size the pool based on how many concurrent database queries your application logic actually performs, not the number of open WebSocket connections, since most WebSocket connections are idle most of the time from the database’s perspective.

What’s the simplest way to add real-time updates to an existing MySQL-backed app? Start with application-level event publishing: after any write that matters, publish an event (in-process for a single server, or via Redis Pub/Sub if you have multiple instances) and push it to relevant WebSocket subscribers. Only add CDC/Kafka complexity once you have a concrete need for it.

Summary and Key Takeaways

Combining MySQL with WebSocket comes down to bridging a pull-based database with a push-based communication layer, and choosing the right bridge for your actual requirements — application-level events for simplicity, CDC via the binary log when you need to reliably capture every writer, or a lightweight trigger-and-poll pattern as a middle ground. Whichever approach you choose, the operational fundamentals stay the same: pool your MySQL connections sensibly, broadcast only after commits, secure your WebSocket layer as rigorously as any API, and plan for horizontal scaling with a proper fan-out mechanism from day one if you expect to need it.

Key takeaways:

• MySQL has no native push mechanism — you need a deliberate bridge layer (app-level events, CDC, or trigger+poll).
• Use a shared, appropriately-sized MySQL connection pool, never one connection per WebSocket client.
• Broadcast updates only after a confirmed database commit to avoid phantom updates.
• Use Redis Pub/Sub (or equivalent) for fan-out once you run multiple WebSocket server instances.
• Secure and authorize WebSocket subscriptions with the same rigor as any other data access path.

References

• MySQL 8.0 Reference Manual — The Binary Log: https://dev.mysql.com/doc/refman/8.0/en/binary-log.html
• MySQL 8.0 Reference Manual — Triggers: https://dev.mysql.com/doc/refman/8.0/en/triggers.html
• MySQL 8.0 Reference Manual — Connection Management: https://dev.mysql.com/doc/refman/8.0/en/connection-management.html
• Debezium Documentation — MySQL Connector: https://debezium.io/documentation/reference/stable/connectors/mysql.html
• MySQL 8.0 Reference Manual — max_connections: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connections