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

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. 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. 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 SchemaDiffObjects via convertToSchemaDiffObjects
3. Classification: Assigns operation types (added, removed, modified) using getOperationType and getOperationLabel

Presentation and Deployment Layer

The groupDiffObjects function organizes SchemaDiffObjects 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:

# 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 (line 130) forwards this request to backend.listTables and serializes the result:

{
  "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:

dbx schema describe local users --json

This invokes backend.describeTable (line 146 in cli.ts) and returns:

{
  "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:

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 (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:

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

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:

-- 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:

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

Request a diff programmatically:

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

The MCP server in packages/mcp-server/src/index.ts forwards requests to 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), the diff engine (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.
• 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. 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. 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) provides dbx schema list and dbx schema describe commands for terminal use. Additionally, the MCP server (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.

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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}