# How to Use DBX Schema Diff to Compare Database Structures: A Complete Guide

> Learn to use DBX schema diff to compare database structures. This guide covers comparing tables, columns, and functions to generate migration scripts efficiently.

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

---

**DBX schema diff compares database structures across connections using a three-layer architecture that generates migration scripts from detected differences in tables, columns, and functions.**

The `t8y2/dbx` repository provides a unified schema comparison toolkit that works across CLI, desktop UI, and programmatic interfaces. Whether you need to sync staging with production or validate migration safety, DBX exposes the same core diff engine through multiple entry points.

## Understanding the DBX Schema Diff Architecture

DBX implements schema comparison capabilities through three distinct layers that share a single source of truth.

### Backend API Layer

The core Node module in `packages/node-core` handles database driver communication through **schema-context.ts**. This layer exposes two critical methods: `listTables` for retrieving table metadata and `describeTable` for detailed column information.

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

### Diff Engine Layer

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 (source and target) along with optional details like columns and indexes.

The diff process follows this sequence:

1. **Comparison**: Generates `TableDiff` objects identifying discrepancies
2. **Conversion**: Transforms raw diffs into UI-friendly `SchemaDiffObject`s via `convertToSchemaDiffObjects`
3. **Classification**: Assigns operation types (`added`, `removed`, `modified`) using `getOperationType` and `getOperationLabel`

### Presentation and Deployment Layer

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

## How to Compare Schemas Using the DBX CLI

The CLI provides direct access to the same backend methods used by the desktop application.

### Listing Tables in a Schema

Use the `dbx schema list` command to retrieve all tables from a specific schema:

```bash

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

dbx schema list local --schema public --json

```

The CLI handler in [`packages/cli/src/cli.ts`](https://github.com/t8y2/dbx/blob/main/packages/cli/src/cli.ts) (line 130) forwards this request to `backend.listTables` and serializes the result:

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

```

### Describing Table Structure

For detailed column metadata, use the `describe` subcommand:

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

```

This invokes `backend.describeTable` (line 146 in [`cli.ts`](https://github.com/t8y2/dbx/blob/main/cli.ts)) and returns:

```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}
  ]
}

```

## Implementing Schema Diff in the Desktop UI

The desktop application invokes the Rust backend through Tauri commands and renders results using the diff engine.

### Preparing the Schema Comparison

Initiate a diff by calling `prepare_schema_diff` through the Tauri bridge:

```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 });
  return preparation;
}

```

This maps to [`apps/desktop/src/lib/tauri.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/tauri.ts) (line 914) and returns a `SchemaDiffPreparation` interface containing source/target tables, functions, and sequences.

### Converting and Grouping Diff Objects

Transform raw diffs into displayable objects:

```typescript
import { convertToSchemaDiffObjects, groupDiffObjects } from "@/lib/schemaDiff";

const objects = convertToSchemaDiffObjects(
  preparation.diffs, 
  preparation.functionDiffs ?? [], 
  preparation.sequences ?? []
);

const groups = groupDiffObjects(objects);

```

The `convertToSchemaDiffObjects` function enriches `TableDiff` data with human-readable labels, while `groupDiffObjects` organizes them for the tree view UI.

## Generating Migration Scripts

Convert selected differences into executable SQL using the deployment builder.

### Building Deploy SQL

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) aggregates changes into a single script:

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

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

```

For a selection including a new table and a modified column, 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 Server

The Model-Context-Protocol (MCP) server exposes diff capabilities to AI agents.

Configure the server in your MCP settings:

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

```

Request a diff programmatically:

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

```

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

## Summary

- **DBX schema diff** operates through three layers: the Node backend ([`packages/node-core/src/schema-context.ts`](https://github.com/t8y2/dbx/blob/main/packages/node-core/src/schema-context.ts)), the diff engine ([`apps/desktop/src/lib/schemaDiff.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/schemaDiff.ts)), and presentation utilities.
- **CLI commands** `dbx schema list` and `dbx schema describe` provide quick access to table metadata from the terminal.
- **Desktop integration** uses Tauri commands `prepare_schema_diff` and `generate_schema_sync_sql` defined in [`apps/desktop/src/lib/tauri.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/tauri.ts).
- **Diff types** include `added`, `removed`, and `modified`, mapped to labels via `getOperationType` and converted to UI objects via `convertToSchemaDiffObjects`.
- **Migration generation** happens through `buildDeploySqlForObjects`, which concatenates `syncSql` properties into deployment-ready scripts.
- **MCP support** enables AI agents to compare databases using the same backend methods as the CLI and UI.

## 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 contains the `TableDiff` generation, `convertToSchemaDiffObjects` conversion, `groupDiffObjects` organization, and `buildDeploySqlForObjects` SQL generation functions.

### How does DBX handle different database drivers?

DBX abstracts driver-specific queries through [`packages/node-core/src/schema-context.ts`](https://github.com/t8y2/dbx/blob/main/packages/node-core/src/schema-context.ts). This module provides `listTables` and `describeTable` methods that work with PostgreSQL, MySQL, and other supported backends, returning standardized `TableInfo` objects to the diff engine.

### 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 `dbx schema list` and `dbx schema describe` commands for terminal use. Additionally, the MCP server ([`packages/mcp-server/src/index.ts`](https://github.com/t8y2/dbx/blob/main/packages/mcp-server/src/index.ts)) exposes diff capabilities to AI agents, and you can programmatically call the HTTP endpoints defined in [`apps/desktop/src/lib/http.ts`](https://github.com/t8y2/dbx/blob/main/apps/desktop/src/lib/http.ts).

### What data structures does the DBX diff engine use?

The engine uses `SchemaDiffPreparation` as the primary interface containing source and target metadata. It generates `TableDiff` objects for raw changes, converts them to `SchemaDiffObject` for UI display, and assigns operation types (`added`, `removed`, `modified`) through the `getOperationType` function.