DBMirror—WebEdition

A mirror for your databases, not a guess.

ROLE: Full-stack developer, solo project
BUILT FOR: Personal/portfolio project — a web rewrite of an earlier PyQt6 desktop tool
STACK: Next.js 15 (App Router), React 19, TypeScript, mssql (tedious driver), Tailwind CSS, Framer Motion, Zustand
PLATFORM: Web app

THE PROBLEM

Comparing two SQL Server databases — say, to confirm a migration went through cleanly, or to check whether staging matches production — is tedious to do by hand. You need to know which tables exist on both sides, which columns differ, which rows are missing, and which rows have drifted in value. Doing this with ad-hoc scripts means repeating the same INFORMATION_SCHEMA queries and primary-key lookups every time.

The project started life as a PyQt6 desktop app. That version worked, but it used untyped Python, a heuristic (id/code/name substring matching) to guess primary keys instead of actually looking them up, and committed credentials to a .env file — not something you'd want to hand someone else or demo without exposing a real database.

THE IDEA

Rebuild the same comparison engine as a stateless web service: type the connection in, once, per session, walk through a guided four-step flow (Connect → Databases → Tables → Compare), and watch results stream in per-table as they finish rather than waiting on one big blocking job.

Pair that with a demo mode backed by canned data, so the tool can be explored without a live SQL Server instance at all.

WHAT IT DOES

Step 1 — Connect

A connection form (server, username, password, encrypt/trust-certificate toggles) with an "Explore with demo data" escape hatch. Credentials are never persisted — they're collected per session and passed straight to mssql to open a pooled connection.

db-mirror.vercel.app

DB Mirror — Web Edition — Step 1 — Connect

Step 2 — Databases

Pick a source and a target database discovered on that connection. The architecture keeps this step dumb — it's pure selection, with the actual introspection (tables, columns, primary keys) happening once both sides are chosen.

db-mirror.vercel.app

DB Mirror — Web Edition — Step 2 — Databases

Step 3 — Tables

Pick what to diff from the tables that exist in both databases, with a "Common only" filter and a toggle for whether to compare row data or just schema. Tables over a configurable row threshold are flagged as sampled rather than fully diffed.

db-mirror.vercel.app

DB Mirror — Web Edition — Step 3 — Tables

Step 4 — Compare

Results stream in live over Server-Sent Events, one table at a time, instead of one big blocking response. The summary shows identical/different/skipped counts and total row diffs, with a per-table schema diff (added/changed columns) and a data diff below, exportable to HTML, CSV, or JSON.

db-mirror.vercel.app

DB Mirror — Web Edition — Step 4 — Compare

HOW IT WORKS

Browser (React + Zustand)
   │  SSE / fetch
   ▼
Next.js API routes (app/api/*)
   │  connect / databases / tables / compare / export / demo
   ▼
lib/db/connection.ts  →  pooled mssql (tedious) connections
   │
   ▼
lib/db/operations.ts  (introspection: tables, columns, PKs, rows)
lib/db/comparison.ts  (stateless schema + data diff engine)
   │
   ▼
Two SQL Server databases (read-only)

Real primary-key based row matching

operations.ts looks up actual primary keys from INFORMATION_SCHEMA.KEY_COLUMN_USAGE instead of guessing from column names. When a PK exists, rows are matched by key into source-only, target-only, and changed buckets. When there isn't one, comparison.ts falls back to a multiset diff over the normalized row.

A stateless comparison engine

comparison.ts intersects base tables on both sides, diffs INFORMATION_SCHEMA.COLUMNS for missing tables/columns and type mismatches, then diffs data per table — skipping volatile columns and normalizing values (dates to ISO, buffers to hex) so type-only mismatches like 1 vs 1.0 don't register as false positives. It takes table/column metadata and row data as plain inputs and returns a diff — nothing about SSE, HTTP, or React leaks into it, which is what lets it also be driven by the demo-data path.

Live streaming progress over SSE

/api/compare returns a Server-Sent Events stream, emitting each table's comparison result as soon as it's done rather than returning one big JSON blob at the end — a comparison across dozens of tables shows results incrementally instead of leaving the user staring at a spinner, without needing a job queue or websocket infrastructure.

Security notes

Identifiers are bracket-quoted/escaped and values use bound parameters. Only non-secret defaults are read from env; credentials are collected per session and never persisted.

CHALLENGES AND WHAT I LEARNED

Replacing a PK-guessing heuristic with a real catalog lookup

Moving from the desktop version's id/code/name substring matching to an actual INFORMATION_SCHEMA.KEY_COLUMN_USAGE lookup meant handling the no-PK case explicitly — the multiset fallback in comparison.ts — rather than assuming every table is well-keyed.

Normalizing values before diffing

Dates to ISO strings, buffers to hex — necessary to avoid noisy false positives between semantically-equal values, an easy thing to get wrong in a naive string comparison.

Blocking response to streamed state

Moving from a single blocking comparison call to an SSE-streamed, per-table response changes how the client has to manage state (a Zustand store accumulating streamed results) compared to a simple request/response fetch.