Building My Own Realtime Database Layer (Inspired by Convex)

Modern applications are no longer request-response driven. Users expect dashboards to update instantly, chats to sync automatically, and admin panels to reflect database changes in real time — without refreshing the page.

While exploring real-time systems like Convex, I became curious about how such systems actually work under the hood. Instead of just reading about it, I decided to build my own lightweight real-time data layer on top of MySQL and Next.js.

The result is Realtime DB — a simple but powerful architecture that listens to database changes and pushes updates to the frontend instantly.

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

This article explains the architecture, technical decisions, and how the system works internally.

The Problem: Polling Is Inefficient

Traditionally, frontend apps fetch data like this:

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

If you want updates, you:

• Add polling (setInterval)
• Implement cache invalidation
• Manually refetch after mutations

This creates:

• Unnecessary load
• Complex client logic
• Inconsistent UI states

I wanted something cleaner:

When the database changes → the UI updates automatically.

No polling. No manual refetching.

The Core Idea

The architecture is built around three principles:

Listen to database changes

Detect what changed

Push updates to subscribed clients

Instead of making the frontend ask repeatedly:

“Did something change?”

The backend says:

“Something changed. Here’s the update.”

Architecture Overview

The system has four main parts:

1. MySQL Binary Log (Binlog Listener)

MySQL maintains a binary log that records every:

• INSERT
• UPDATE
• DELETE

By subscribing to the binlog, the server can detect changes the moment they happen — without modifying existing queries or adding triggers.

This is the key that makes everything reactive.

2. Change Processor

When a table change is detected:

• The system checks if the table is allow-listed
• It re-queries the latest rows
• It prepares the updated dataset for broadcast

This ensures:

• Clients always receive fresh, consistent data
• No partial or stale state is transmitted

3. WebSocket Layer

For real-time transport, I used WebSockets (via Socket.IO).

Why WebSockets?

Because unlike HTTP:

• The connection stays open
• The server can push data instantly
• No polling required

When a table changes:

• All subscribed clients receive an event
• Their local state updates automatically

4. React Hook API

On the frontend, I designed a simple hook:

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

This abstracts away:

• WebSocket management
• Subscription handling
• Reconnection logic
• State management

From the component’s perspective, it behaves like a normal data hook — but it’s fully reactive.

How the Data Flow Works (Step by Step)

A record in MySQL changes.

The binlog listener detects the event.

The backend determines which table changed.

The system fetches the updated rows.

WebSocket broadcasts to all subscribers.

React hook updates local state.

UI re-renders instantly.

No polling.
No manual refresh.
No explicit refetch logic.

Why This Approach Works

1. It Leverages Existing Infrastructure

Instead of replacing the database, this approach enhances it.

You keep:

• MySQL
• SQL queries
• Your existing schema

You just add a reactive layer on top.

2. It’s Event-Driven

The system reacts to actual database events, not timers.

This makes it:

• Efficient
• Scalable
• Clean in architecture

3. It Keeps the Frontend Simple

From the UI perspective, nothing special is required.

Just:

const { data } = useRealtimeTable("customers")

And the UI always stays in sync.

Deployment Considerations

Because WebSockets require persistent connections:

• It cannot run on serverless platforms that don’t support long-lived connections.
• It needs a Node.js environment (e.g., VPS, Docker, etc.).

This is an architectural trade-off — but necessary for real-time push communication.

Security Considerations

Security was an important design decision.

The system:

• Only allows explicitly registered tables
• Validates subscriptions server-side
• Never exposes raw database access to the client

The client only receives broadcasted data — nothing more.

What I Learned Building This

Building this system clarified how modern real-time backends work internally:

• Real-time is fundamentally event propagation.
• The database is the source of truth.
• Reactivity can be built on top of traditional SQL systems.
• Clean abstractions make complex systems feel simple.

It also reinforced that “real-time” doesn’t require a completely new database — it requires listening intelligently to changes.

Future Improvements

Some ideas for evolution:

• Row-level diff broadcasting instead of full table re-fetch
• Fine-grained subscriptions (filtered queries)
• Horizontal scaling via Redis pub/sub
• Optimistic UI updates
• Multi-tenant support

Final Thoughts

This project started as curiosity.

I wanted to understand how real-time platforms work — not just use them.

So I built my own reactive data layer on top of MySQL.

It may not be perfect.
It may not be the final architecture.
But it works — and more importantly, I understand every part of it.

That’s the real win.