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

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, 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, 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. 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.

List Tables in a Schema

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

# 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) forwards this request to backend.listTables and returns structured 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:

dbx schema describe local users --json

This command (line 146 in packages/cli/src/cli.ts) calls backend.describeTable and outputs detailed metadata:

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

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 (line 914) and ultimately executes the diff engine in 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

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

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

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

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

The MCP server (packages/mcp-server/src/index.ts) forwards the request to the same backend (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), diff engine (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 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) 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) 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 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 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.

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