# Schema Diff and Comparison Between Databases Using DBX: A Technical Guide

> Easily perform schema diff and comparison between databases with DBX. Visualize changes and generate migration scripts using this powerful tool's CLI, UI, or MCP server.

- Repository: [skyler/dbx](https://github.com/t8y2/dbx)
- Tags: how-to-guide
- Published: 2026-07-02

---

**DBX provides a unified schema diff engine that compares database structures across connections, visualizes changes as added, removed, or modified objects, and generates deployable SQL migration scripts through its CLI, Desktop UI, or MCP server.**

DBX (t8y2/dbx) is an open-source database management toolkit that unifies schema comparison workflows across multiple interfaces. The **schema diff and comparison between databases using DBX** operates through a three-layer architecture—backend API, diff engine, and presentation layer—ensuring consistent results whether you are using the command line, desktop application, or programmatic APIs.

## Architecture of the DBX Schema Diff Engine

DBX implements schema diff capabilities through three distinct layers that share a single source of truth via the `SchemaDiffPreparation` interface.

### Backend API and Driver Abstraction

The core Node module (`packages/node-core`) communicates directly with database drivers and exposes two critical functions: `listTables` and `describeTable`. These functions are implemented in [`packages/node-core/src/schema-context.ts`](https://github.com/t8y2/dbx/blob/main/packages/node-core/src/schema-context.ts) and retrieve metadata from PostgreSQL, MySQL, and other supported backends.

The desktop application accesses these capabilities through a thin HTTP wrapper defined in [`apps/desktop/src/lib/http.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/http.ts). This module exposes two endpoints: `/api/schema-diff/prepare` for fetching schema metadata and `/api/schema-diff/generate-sync-sql` for producing migration scripts.

### Core Diff Logic in the UI Library

All diff computation resides in [`apps/desktop/src/lib/schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/schemaDiff.ts). The engine receives two sets of `TableInfo` objects (representing source and target schemas) along with optional details including columns, indexes, and constraints.

The process follows these steps:

1. **Comparison**: The engine builds a list of `TableDiff` objects by comparing source and target metadata.
2. **Conversion**: The `convertToSchemaDiffObjects` function transforms raw diffs into UI-friendly `SchemaDiffObject` instances.
3. **Classification**: Each diff item receives a `type` property—`added`, `removed`, or `modified`—mapped to human-readable labels via `getOperationType` and `getOperationLabel`.

### Presentation and Deployment

The `groupDiffObjects` function organizes `SchemaDiffObject` instances by operation type (modify/create/delete) and by object kind (tables, views, functions) for the side-panel UI. When you confirm a deployment, the `buildDeploySqlForObjects` function iterates through selected top-level objects and concatenates the pre-generated `syncSql` (or raw DDL) into a single executable migration script.

## How to Compare Database Schemas with DBX

DBX exposes schema comparison capabilities through three primary interfaces: CLI commands, Desktop UI interactions, and MCP server protocols.

### CLI Schema Inspection

The command-line interface provides direct access to the same backend functions used by the GUI. All CLI handlers are implemented in [`packages/cli/src/cli.ts`](https://github.com/t8y2/dbx/blob/main/packages/cli/src/cli.ts).

List all tables in a specific schema:

```bash
dbx schema list local --schema public --json

```

This command forwards the request to `backend.listTables` and returns structured JSON:

```json
{
  "connection": "local",
  "schema": "public",
  "tables": [
    {"name":"users","type":"BASE TABLE"},
    {"name":"orders","type":"BASE TABLE"}
  ]
}

```

Describe a specific table's structure:

```bash
dbx schema describe local users --json

```

The output includes column definitions, nullability constraints, and primary key status:

```json
{
  "connection":"local",
  "schema":"public",
  "table":"users",
  "columns":[
    {"name":"id","data_type":"uuid","is_nullable":false,"is_primary_key":true},
    {"name":"email","data_type":"text","is_nullable":false}
  ]
}

```

### Desktop UI Workflow

The desktop application invokes the Rust backend through Tauri bridges defined in [`apps/desktop/src/lib/tauri.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/tauri.ts). The `invoke("prepare_schema_diff")` function (line 914) initiates the comparison process:

```typescript
import { invoke } from "@/lib/tauri";

async function runSchemaDiff(sourceConn: string, targetConn: string, schema?: string) {
  const options = { source: sourceConn, target: targetConn, schema };
  // Calls the Rust side which builds SchemaDiffPreparation
  const preparation = await invoke("prepare_schema_diff", { options });

  // Convert raw diffs → UI objects
  const objects = convertToSchemaDiffObjects(
    preparation.diffs, 
    preparation.functionDiffs ?? [], 
    preparation.sourceTables,
    preparation.targetTables
  );

  // Group for display in tree view
  const groups = groupDiffObjects(objects);
  return { objects, groups };
}

```

### MCP Server Integration

For programmatic access in AI workflows, the MCP server ([`packages/mcp-server/src/index.ts`](https://github.com/t8y2/dbx/blob/main/packages/mcp-server/src/index.ts)) exposes the diff engine via the Model-Context-Protocol. After configuring the server:

```json
{
  "mcpServers": {
    "dbx": {
      "command": "npx",
      "args": ["-y", "@dbx-app/mcp-server"]
    }
  }
}

```

Agents can request diffs using the `dbx_schema_diff` method:

```json
{
  "method": "dbx_schema_diff",
  "params": {
    "source_connection": "prod",
    "target_connection": "staging",
    "schema": "public"
  }
}

```

The MCP server forwards requests to [`schema-context.ts`](https://github.com/t8y2/dbx/blob/main/schema-context.ts) and returns JSON diff structures identical to CLI output.

## Generating Synchronization SQL

Once you have identified schema differences, DBX generates executable migration scripts through the `buildDeploySqlForObjects` function in [`apps/desktop/src/lib/schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/schemaDiff.ts) (lines 30-70).

```typescript
import { buildDeploySqlForObjects } from "@/lib/schemaDiff";

function getDeploySql(selectedObjects: SchemaDiffObject[]) {
  // Produces a single SQL script that applies all selected changes
  return buildDeploySqlForObjects(selectedObjects);
}

```

If you select a table creation and column modification, the resulting script resembles:

```sql
-- Create table: orders
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  amount NUMERIC NOT NULL,
  created_at TIMESTAMP NOT NULL
);

-- Modify column: users.email
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

```

The function walks selected top-level objects and concatenates their pre-computed `syncSql` properties or generates raw DDL when necessary.

## Summary

- **Three-layer architecture**: The **backend API** ([`packages/node-core/src/schema-context.ts`](https://github.com/t8y2/dbx/blob/main/packages/node-core/src/schema-context.ts)) retrieves metadata, the **diff engine** ([`apps/desktop/src/lib/schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/schemaDiff.ts)) computes differences, and the **presentation layer** organizes results for UI or CLI output.
- **Unified interface**: The `SchemaDiffPreparation` interface ensures consistency across CLI commands (`dbx schema list/describe`), Desktop UI (Tauri invocations), and MCP server implementations.
- **Change classification**: DBX categorizes every difference as `added`, `removed`, or `modified`, mapping these to human-readable operation labels.
- **SQL generation**: The `buildDeploySqlForObjects` function assembles selected changes into executable migration scripts containing CREATE, ALTER, and DROP statements.

## Frequently Asked Questions

### What file contains the core diff logic in DBX?

The core diff logic resides in [`apps/desktop/src/lib/schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/schemaDiff.ts). This file implements the `TableDiff` generation, `SchemaDiffObject` conversion, `groupDiffObjects` organization, and `buildDeploySqlForObjects` SQL assembly. It receives metadata from the backend via `SchemaDiffPreparation` interfaces and outputs UI-ready structures.

### How does DBX categorize schema changes?

DBX assigns every detected difference a `type` property with one of three values: `added` for new objects, `removed` for deleted objects, or `modified` for altered objects. The `getOperationType` and `getOperationLabel` functions in [`schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/schemaDiff.ts) map these technical codes to human-readable descriptions like "CREATE TABLE" or "ALTER COLUMN".

### Can DBX generate SQL migration scripts from schema comparisons?

Yes. The `buildDeploySqlForObjects` function walks selected `SchemaDiffObject` instances and concatenates their `syncSql` properties (or generates raw DDL) into a single executable script. This works for all object types including tables, views, functions, and indexes, producing standard SQL that can run directly against the target database.

### Is it possible to integrate DBX schema diff into automated workflows?

Yes. Through the MCP server implementation in [`packages/mcp-server/src/index.ts`](https://github.com/t8y2/dbx/blob/main/packages/mcp-server/src/index.ts), DBX exposes the `dbx_schema_diff` method that AI agents and CI/CD pipelines can invoke programmatically. The CLI also supports JSON output (`--json` flag) for integration with shell scripts and automation tools, both accessing the same underlying logic in [`packages/node-core/src/schema-context.ts`](https://github.com/t8y2/dbx/blob/main/packages/node-core/src/schema-context.ts).