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

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

List all tables in a specific schema:

dbx schema list local --schema public --json

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

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

Describe a specific table's structure:

dbx schema describe local users --json

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

{
  "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. The invoke("prepare_schema_diff") function (line 914) initiates the comparison process:

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) exposes the diff engine via the Model-Context-Protocol. After configuring the server:

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

Agents can request diffs using the dbx_schema_diff method:

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

The MCP server forwards requests to 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 (lines 30-70).

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:

-- 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) retrieves metadata, the diff engine (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. 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 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, 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.

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