# How to Compare Database Schemas Using DBX Schema Diff: A Complete Guide

> Compare database schemas with DBX Schema Diff. Analyze differences across PostgreSQL, MySQL, and more. Generate SQL migration scripts easily.

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

---

**DBX Schema Diff compares database schemas across PostgreSQL, MySQL, and other supported drivers by retrieving metadata via the Node core backend, processing differences through the diff engine in [`schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/schemaDiff.ts), and generating deployable SQL migration scripts.**

DBX is an open-source database management toolkit that unifies schema comparison across CLI, desktop UI, and programmatic interfaces. The `t8y2/dbx` repository implements a three-layer architecture that enables you to compare database schemas using DBX Schema Diff through a consistent API, whether you are working in a terminal, a visual diff tree, or an automated MCP agent.

## Understanding the DBX Schema Diff Architecture

The schema diff capability spans three distinct layers that share a single source of truth through the `SchemaDiffPreparation` interface.

### Backend API Layer

The core Node module in `packages/node-core` communicates directly with database drivers and exposes `listTables` and `describeTable` methods. The desktop application accesses these capabilities through a thin HTTP wrapper located in [`apps/desktop/src/lib/http.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/http.ts), which exposes two critical endpoints: `/api/schema-diff/prepare` and `/api/schema-diff/generate-sync-sql`. This abstraction allows the UI to remain agnostic of the specific database driver while retrieving complete metadata including tables, columns, indexes, functions, and sequences.

### Diff Engine Core

All comparison logic 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 the source and target schemas, along with optional details such as columns and indexes. It constructs a list of `TableDiff` objects, then expands them into UI-friendly `SchemaDiffObject` instances via `convertToSchemaDiffObjects`. Each diff item carries a `type` property—`added`, `removed`, or `modified`—which maps to human-readable operation labels through `getOperationType` and `getOperationLabel`.

### Presentation and Deployment Layer

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

## Comparing Schemas via the CLI

The DBX CLI provides direct access to the backend comparison engine through the `dbx schema` command group, implemented in [`packages/cli/src/cli.ts`](https://github.com/t8y2/dbx/blob/main/packages/cli/src/cli.ts).

### List Tables in a Schema

To retrieve all tables from a specific schema for comparison baseline:

```bash

# List all tables in the "public" schema of a PostgreSQL connection called "local"

dbx schema list local --schema public --json

```

The CLI handler (line 130 in [`packages/cli/src/cli.ts`](https://github.com/t8y2/dbx/blob/main/packages/cli/src/cli.ts)) forwards this 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 Table Structure

To inspect column-level details for precise comparison:

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

```

This command (line 146 in [`packages/cli/src/cli.ts`](https://github.com/t8y2/dbx/blob/main/packages/cli/src/cli.ts)) calls `backend.describeTable` and outputs detailed metadata:

```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},
    {"name":"created_at","data_type":"timestamp","is_nullable":false}
  ]
}

```

## Using the Desktop UI for Visual Diff

The desktop application invokes the same backend through Tauri (`tauri.invoke`) and renders the diff tree using the schema diff library.

### Initiating a Schema Comparison

To programmatically trigger a diff from the UI layer:

```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 ?? [], 
    // ... additional parameters
  );

  // Group for display in the side panel
  const groups = groupDiffObjects(objects);
  return { objects, groups };
}

```

This call maps to [`apps/desktop/src/lib/tauri.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/tauri.ts) (line 914) and ultimately executes the diff engine in [`apps/desktop/src/lib/schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/schemaDiff.ts), which processes the `TableDiff` array into categorized `SchemaDiffObject` instances.

## Generating Migration Scripts

Once you select changes for deployment, DBX assembles the final SQL script using the deployment builder.

### Building the Deploy SQL

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

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

```

Located in [`apps/desktop/src/lib/schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/schemaDiff.ts) (lines 30-70), this function concatenates the pre-computed `syncSql` for each object. If you select a table creation and a column modification, the output 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);

```

## Programmatic Access via MCP

DBX exposes the same diff engine to AI agents through the Model-Context-Protocol (MCP) server.

### Configuring the MCP Server

Add the following to your MCP configuration:

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

```

After starting the server (`npx @dbx-app/mcp-server`), an agent can request a diff:

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

```

The MCP server ([`packages/mcp-server/src/index.ts`](https://github.com/t8y2/dbx/blob/main/packages/mcp-server/src/index.ts)) forwards the request to the same backend ([`packages/node-core/src/schema-context.ts`](https://github.com/t8y2/dbx/blob/main/packages/node-core/src/schema-context.ts)) and returns a JSON diff identical to the CLI output.

## Summary

- **DBX Schema Diff** operates through a three-layer architecture: backend driver abstraction ([`schema-context.ts`](https://github.com/t8y2/dbx/blob/main/schema-context.ts)), diff engine ([`schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/schemaDiff.ts)), and presentation layer (`groupDiffObjects`).
- **CLI commands** `dbx schema list` and `dbx schema describe` provide JSON output for table-level and column-level comparisons.
- **Desktop UI** uses Tauri invocations (`prepare_schema_diff`) and converts raw diffs to `SchemaDiffObject` instances for visual comparison.
- **SQL generation** via `buildDeploySqlForObjects` produces executable migration scripts from selected diff objects.
- **MCP integration** allows AI agents to compare schemas remotely using the same backend API.

## Frequently Asked Questions

### How does DBX determine what has changed between two schemas?

The diff engine in [`apps/desktop/src/lib/schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/schemaDiff.ts) compares two sets of `TableInfo` objects and categorizes differences into three types: `added` (present in source, missing in target), `removed` (present in target, missing in source), and `modified` (structural differences in columns, indexes, or constraints). These are mapped to human-readable labels via `getOperationType` and `getOperationLabel`.

### Can I use DBX Schema Diff without the desktop application?

Yes. The CLI ([`packages/cli/src/cli.ts`](https://github.com/t8y2/dbx/blob/main/packages/cli/src/cli.ts)) provides full access to the comparison engine through `dbx schema list` and `dbx schema describe` commands. Additionally, the MCP server ([`packages/mcp-server/src/index.ts`](https://github.com/t8y2/dbx/blob/main/packages/mcp-server/src/index.ts)) exposes the same functionality for programmatic use in automated workflows or AI agents.

### Where does the actual SQL generation happen?

SQL generation occurs in [`apps/desktop/src/lib/schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/schemaDiff.ts) within the `buildDeploySqlForObjects` function. This utility walks the selected `SchemaDiffObject` instances and concatenates either pre-generated `syncSql` provided by the backend or raw DDL statements into a single migration script ready for execution.

### What database drivers are supported by the schema diff backend?

The backend abstraction in [`packages/node-core/src/schema-context.ts`](https://github.com/t8y2/dbx/blob/main/packages/node-core/src/schema-context.ts) supports multiple drivers including PostgreSQL and MySQL. The driver-agnostic design allows DBX to retrieve metadata (tables, columns, indexes, functions, sequences) uniformly across different database systems, enabling cross-platform schema comparisons.