The Visual Data Diff: comparing Prod and Staging side by side

When you work with several database environments, one question comes up often: does the data in staging match what you expect in production? Has a record been modified? Has a row disappeared after a migration? To answer these questions without writing ad hoc scripts, QoreDB offers a built-in tool for visually comparing data, the Visual Data Diff.

The idea is simple: take two data sets, align them row by row, and highlight what has changed. The principle is the same as a Git diff, but applied to the results of SQL or NoSQL queries.

Two sources, three modes

The diff in QoreDB is based on a panel split into two sides: a left source and a right source. Each side can be fed in three different ways. The "table" mode lets you directly select a table in an active connection. The "query" mode lets you write a free-form query and run its result. The "snapshot" mode lets you load a state previously saved via QoreDB's snapshot system.

This flexibility matters. Comparing two tables with the same name on two different servers is the most common case, but it's not the only one. You can also compare the result of a filtered query with a snapshot taken the day before, or two different queries on the same database to test a hypothesis. Each side of the diff is independent: connection, namespace, source mode, everything can be configured separately.

Primary-key alignment

For a diff to be useful, you have to be able to match rows to each other. If you simply compare by position (row 1 against row 1), the slightest difference in sorting throws off the whole result. QoreDB solves this problem by using key columns to identify each row uniquely.

By default, the tool tries to automatically detect the primary key of the compared tables. It queries the schema on both sides via the describeTable command, retrieves the columns marked as PK, and computes their intersection. If the two tables share the same primary key, it's used directly. Otherwise, the user can manually select the columns that serve as the alignment key from among the columns common to both results.

This design choice reflects a reality: not all tables have an explicit primary key, and some comparisons involve query results that have no PK at all. Rather than refusing the diff in these cases, QoreDB allows comparing on any combination of columns, including all of them if necessary.

Four statuses, one color code

Each row of the result receives one status among four possibilities. "unchanged" means the row exists on both sides with identical values. "added" means it exists only in the right source. "removed" means it exists only in the left source. "modified" means the row exists on both sides but at least one cell has changed value.

The display uses a consistent color code: green for additions, red for removals, orange for modifications, and gray for unchanged rows. For modified cells, the old value is shown struck through above the new one. This presentation lets you spot immediately what has changed without having to read every cell.

A statistics bar summarizes the diff in figures: number of rows added, removed, modified and unchanged. Each counter is clickable and acts as a quick filter. You can also hide unchanged rows to focus solely on the differences, which is useful when comparing two large data sets that differ on only a few rows.

Virtualization and performance

The diff can produce results with thousands of rows. To keep the interface smooth, the results grid uses virtualization via TanStack Virtual. Only the rows visible on screen are rendered in the DOM, with an overscan of a few rows to guarantee smooth scrolling. Each row has a fixed height of 36 pixels, which lets the virtualizer compute positions without dynamic measurement.

The diff computation itself is done on the frontend in TypeScript. The algorithm builds two maps indexed by row key, then iterates over the left map to find matches in the right map. The rows remaining in the right map are marked as added. Value comparison handles edge cases: null, nested JSON objects, mixed types. The final sort places removals first, then modifications, unchanged rows, and finally additions.

Concrete use cases

The most frequent scenario is post-migration verification. After running a migration script on a staging environment, you compare the migrated table with its production equivalent to make sure only the expected changes were applied. The unchanged rows confirm that the rest of the data is intact.

Another common case is debugging inconsistent data. When a bug is reported and you suspect a divergence between two environments, the diff lets you pinpoint exactly which rows and columns are affected without writing an EXCEPT or a LEFT JOIN.

The snapshot mode adds a temporal dimension. By taking a snapshot before a critical operation, you can then compare the current state with the saved state to measure exactly the impact of the changes. This approach is especially useful for data-cleanup operations or bulk imports.

Exporting the results

The result of a diff can be exported to CSV or JSON. The CSV export adds a _status column in first position to identify the type of each row. For modified rows, the affected cells show the old and new values separated by an arrow. The JSON export is more structured: each modified row contains an object with old and new fields for the columns that changed. These exports let you archive a diff or share it with a colleague without them needing to open QoreDB.

The Visual Data Diff is a verification tool, not a synchronization tool. It doesn't generate a migration script and doesn't modify any data. Its role is to make visible what has changed between two states, quickly and unambiguously. For a developer who manages several environments day to day, it's a direct shortcut between "I wonder what changed" and the answer.