Building My Own Realtime Database Layer Inspired by Convex

I’d been using Convex on a side project and kept wondering how it actually keeps the UI in sync without polling. The short version turned out to be: the database changes, something reads the change, and a WebSocket pushes the new state to the client.

So I tried to build the smallest possible version of that on top of MySQL + Socket.IO + Next.js, mostly to convince myself I understood it.

The result is Realtime DB: MySQL changes hit the binary log, a Node process forwards them through Socket.IO, and a React hook on the other end keeps a table in sync without a single useEffect(fetch).

https://github.com/dev-kraken/realtime-db

This article explains the problem, the architecture, the data flow, and the lessons I learned while building it.

The Problem: Polling Is Inefficient

Most frontend applications fetch data like this:

useEffect(() => {
  fetch("/api/products");
}, []);

If you want fresh data, you usually add polling:

useEffect(() => {
  const interval = setInterval(() => {
    fetch("/api/products");
  }, 5000);

  return () => clearInterval(interval);
}, []);

This works, but it creates several problems:

The client keeps asking for updates even when nothing changed
The backend handles unnecessary requests
Cache invalidation becomes more complex
UI state can become inconsistent
Refetch logic spreads across components

I wanted something cleaner:

When the database changes, the UI updates automatically.

No polling.

No manual refresh.

No explicit refetch logic.

The Core Idea

The architecture is built around three simple ideas:

Listen to database changes
Detect which table changed
Push updated data to subscribed clients

Instead of making the frontend repeatedly ask:

Did something change?

The backend says:

Something changed. Here is the latest data.

This creates a more efficient and reactive data flow.

What Realtime DB Does

Realtime DB listens to MySQL binlog events and broadcasts updates to connected frontend clients over Socket.IO.

At a high level:

MySQL records data changes in the binary log
The server listens for INSERT, UPDATE, and DELETE events
Allowed tables are re-queried after changes
Updated rows are sent to connected clients
React components update automatically through a hook

The frontend API is simple:

const { data, loading, error } = useRealtimeTable("customers");

From the component’s perspective, it behaves like a normal data hook.

The difference is that the data stays live.

Architecture Overview

The system has four main parts:

MySQL binlog listener
Change processor
Socket.IO transport layer
React hook API

Together, these pieces create a small realtime layer on top of a traditional SQL database.

1. MySQL Binlog Listener

MySQL maintains a binary log that records database changes.

The binary log can include events for:

INSERT
UPDATE
DELETE
Table changes
Transaction activity

Realtime DB listens to the MySQL binlog and reacts when an allowlisted table changes.

This is the key part of the system.

Instead of modifying every query or adding custom triggers, the backend observes changes directly from the database log.

2. Change Processor

When a table change is detected, the system does not blindly broadcast raw binlog data.

Instead, it:

Checks whether the table is allowlisted
Re-queries the latest rows from MySQL
Prepares a full table snapshot
Broadcasts the updated data to connected clients

This keeps the client state simple and consistent.

The current implementation sends full table snapshots. That is simple and reliable for small to medium datasets.

In the future, this could evolve into row-level diffs or query-specific subscriptions.

3. Socket.IO Layer

For realtime transport, Realtime DB uses Socket.IO.

Socket.IO provides a persistent connection between the server and browser, allowing the server to push updates instantly.

When a table changes, the backend emits an event:

table:update

The payload looks like this:

{
  table: "customers",
  data: [...]
}

Connected clients receive the update and refresh their local state.

4. React Hook API

On the frontend, the realtime logic is wrapped in a React hook.

import { useRealtimeTable } from "@/src/hooks/useRealtimeTable";

type Customer = {
  id: number;
  name: string;
  email: string;
};

export function CustomersTable() {
  const { data, loading, error } = useRealtimeTable<Customer>("customers");

  if (loading) {
    return <p>Loading customers...</p>;
  }

  if (error) {
    return <p>Failed to load customers: {error}</p>;
  }

  return (
    <ul>
      {data.map((customer) => (
        <li key={customer.id}>
          {customer.name} — {customer.email}
        </li>
      ))}
    </ul>
  );
}

The hook handles:

Socket connection
Table subscription
Initial loading state
Realtime updates
Connection errors
Local state updates

The component only consumes data.

How the Data Flow Works

Here is the complete flow:

A row changes in MySQL
The binlog listener detects the event
The backend identifies the changed table
The table name is validated against the allowlist
The backend fetches the latest rows
Socket.IO broadcasts the update
The React hook receives the payload
Local state updates
The UI re-renders automatically

No polling is needed.

Example Data Flow

MySQL
  ↓
Binlog listener
  ↓
Change processor
  ↓
MySQL table re-query
  ↓
Socket.IO broadcast
  ↓
React hook
  ↓
UI update

This is the main idea behind the project.

The database remains the source of truth, and the UI reacts to changes as they happen.

Project Structure

The project uses a clean layered structure.

realtime-db/
├── server.ts
├── src/
│   ├── realtime/
│   │   ├── domain/
│   │   │   ├── types.ts
│   │   │   └── channels.ts
│   │   ├── application/
│   │   │   ├── ports.ts
│   │   │   ├── broadcast-table.ts
│   │   │   └── binlog-service.ts
│   │   ├── infrastructure/
│   │   │   ├── mysql-pool.ts
│   │   │   ├── socket-io-transport.ts
│   │   │   └── mysql-events-trigger.ts
│   │   ├── env.ts
│   │   └── index.ts
│   ├── lib/
│   │   └── socket-client.ts
│   └── hooks/
│       └── useRealtimeTable.ts
├── app/
│   ├── api/
│   │   ├── health/route.ts
│   │   └── tables/[table]/route.ts
│   ├── layout.tsx
│   └── page.tsx
├── docker-compose.yml
├── Dockerfile
└── .env.example

The architecture separates concerns clearly:

Layer	Responsibility
Domain	Types and event names
Application	Use cases and ports
Infrastructure	MySQL, Socket.IO, and binlog implementations
Adapters	Custom server, API routes, socket handlers, and React hook

This makes the system easier to reason about and extend.

Quick Start

Clone the repository:

git clone https://github.com/dev-kraken/realtime-db.git
cd realtime-db

Copy the environment file:

cp .env.example .env

Start the project with Docker:

docker compose up --build

The app runs at:

http://localhost:3000

The Docker setup includes MySQL with binlog enabled and an initial customers table.

Local Development Without Docker

If you already have MySQL running locally, copy the environment file:

cp .env.example .env

Then update your MySQL settings:

MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASSWORD=root
MYSQL_DATABASE=testdb
NEXT_PUBLIC_SOCKET_URL=http://localhost:3000
SOCKET_CORS_ORIGIN=http://localhost:3000

Install dependencies:

bun install

Start development:

bun dev

Your MySQL server must have binary logging enabled.

Example MySQL config:

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW

Environment Variables

Realtime DB uses the following environment variables:

Variable	Where	Description
`MYSQL_HOST`	Server	MySQL host. Defaults to `127.0.0.1`
`MYSQL_PORT`	Server	MySQL port. Defaults to `3306`
`MYSQL_USER`	Server	MySQL username
`MYSQL_PASSWORD`	Server	MySQL password
`MYSQL_DATABASE`	Server	MySQL database name
`NEXT_PUBLIC_SOCKET_URL`	Client	Optional Socket.IO URL. Defaults to same origin
`HOSTNAME`	Server	Optional hostname for logging
`SOCKET_CORS_ORIGIN`	Server	Socket.IO CORS origin. Set this in production

In production, avoid using SOCKET_CORS_ORIGIN=*.

Set it to your actual frontend origin.

API Reference

Realtime DB exposes a small HTTP and Socket.IO API.

HTTP Endpoints

Method	Path	Description
`GET`	`/api/health`	Returns health status and database status
`GET`	`/api/tables/:table`	Returns rows for an allowlisted table

The table must exist in the server-side allowlist.

Socket.IO Events

Event	Direction	Payload	Description
`table:subscribe`	Client → Server	`{ table: string }`	Subscribes to one table
`table:update`	Server → Client	`{ table: string; data: unknown[] }`	Sends updated table data

React Hook

import { useRealtimeTable } from "@/src/hooks/useRealtimeTable";

const { data, loading, error } = useRealtimeTable<YourRowType>("customers");

Returned values:

Value	Description
`data`	Array of rows that updates in real time
`loading`	`true` until the first update or timeout
`error`	Connection or subscription error

Adding a New Realtime Table

To add a new table, first allowlist it on the server.

In src/realtime/infrastructure/mysql-pool.ts, update ALLOWED_TABLES:

const ALLOWED_TABLES = ["customers", "products"] as const;

Then create the table in MySQL.

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  price DECIMAL(10, 2)
);

Now use the hook in a client component:

const { data } = useRealtimeTable<Product>("products");

The binlog listener automatically detects changes and broadcasts updates for the allowlisted table.

Why This Approach Works

This architecture works because it enhances the existing database instead of replacing it.

You keep:

MySQL
SQL queries
Your existing schema
Your existing backend logic

Then you add a reactive layer on top.

This makes it easier to understand than adopting a completely new database model.

Why Not Just Use Polling?

Polling is easy to implement, but it becomes inefficient as your app grows.

With polling:

Every client sends repeated requests
Most requests return unchanged data
Backend load increases with connected users
UI freshness depends on interval timing
Developers must manually coordinate refetching

With the binlog and WebSocket approach:

Updates happen only when data changes
The server pushes fresh data immediately
Client logic stays simple
UI state stays closer to the database state

Why Not Use Database Triggers?

Database triggers can work, but they add logic inside the database itself.

This project avoids triggers because binlog listening is less invasive.

With binlog-based change detection:

Existing queries do not need to change
Database schema can stay simple
Application logic remains in the app layer
Changes can be observed centrally

Triggers may still be useful in some systems, but they were not necessary for this experiment.

Deployment Considerations

Realtime DB uses a custom Next.js server and long-lived Socket.IO connections.

Because of that, it is not a good fit for platforms that only support serverless request-response execution.

For deployment, use a platform that supports a persistent Node or Bun process.

Good options include:

Docker
VPS
Railway
Fly.io
Render
A self-managed server

The project is not designed for standard Vercel serverless deployment because WebSockets and a custom server require a long-running process.

Security Considerations

Security is an important part of the design.

Realtime DB includes a few important safeguards:

Only allowlisted tables can be read
Table names are validated server-side
Clients cannot run raw SQL
Clients only receive broadcasted table data
Database credentials stay on the server
Socket CORS can be restricted in production

The most important rule is:

Never let the client choose arbitrary database tables or queries.

Always validate subscriptions on the server.

Production Hardening Checklist

Before using this pattern in production, consider adding:

Authentication for socket connections
Per-user authorization
Row-level access control
Tenant-aware filtering
Rate limiting
Structured logging
Redis pub/sub for horizontal scaling
Backpressure handling
Payload size limits
Schema validation for socket payloads
Production-safe CORS settings
Monitoring and alerting

The current project is a learning-focused implementation, but these additions would be important for real production workloads.

What I Learned

Building this project clarified how modern realtime systems work internally.

The main lessons were:

Realtime is event propagation
The database should remain the source of truth
WebSockets are useful when the server needs to push updates
React hooks can hide complex subscription logic
Allowlists are essential for safe database access
Re-querying full tables is simple, but not always scalable
Clean architecture makes realtime systems easier to maintain

The most valuable part was not just getting it working.

It was understanding every moving piece.

Future Improvements

There are many ways this project could evolve.

Potential improvements include:

Row-level diff broadcasting instead of full table snapshots
Query-level subscriptions
Filtered subscriptions
Optimistic UI updates
Redis pub/sub for horizontal scaling
Multi-tenant support
Authenticated socket sessions
Role-based access control
Debounced or batched broadcasts
Support for more databases
Better reconnection recovery
Change history and replay support

The current version proves the concept. These improvements would make it more production-ready.

Final thoughts

This was a “open the box” project, not a Convex replacement. Convex, Supabase, and Firebase have years of work in conflict resolution, auth, scaling, and offline sync that this 600-line proof of concept obviously doesn’t.

What I got out of it was a model in my head: tail the database, broadcast diffs, let the client subscribe. Once that clicked, the existing realtime products felt less like magic and more like a stack of solved problems.

If you want to play with the code, the repo is dev-kraken/realtime-db.

Type-safe environment configuration for Next.js — the env layer I use on this kind of custom Next.js server.
What is OpenClaw? — another “let me see how this thing actually works” exploration.