How DBX Performs Schema Diff and Comparison Between Different Database Connection Profiles

DBX compares database schemas by collecting metadata from both connection profiles, building a diff model in the SchemaDiffPreparationOptions struct, and running the prepare_schema_diff algorithm to generate executable SQL synchronization scripts.

The DBX open-source project (t8y2/dbx) provides a robust engine for schema diff and comparison between different database connection profiles within the dbx-core crate. This engine orchestrates metadata extraction from diverse database drivers, normalizes structural descriptors, and translates differences into executable SQL migration scripts. Whether comparing Postgres development and production instances or analyzing cross-vendor schemas, DBX abstracts connection-specific details into a unified diff model.

Metadata Collection from Connection Profiles

Driver-Specific Schema Introspection

DBX begins the comparison by requesting metadata from both source and target connections through specialized database drivers. Each driver (e.g., Oracle, Xugu) implements endpoints such as list_schemas, list_tables, list_objects, and get_columns to retrieve structural information. For example, the Xugu driver handles schema listing in [agents/drivers/xugu/main.go](https://github.com/t8y2/dbx/blob/main/agents/drivers/xugu/main.go#L752-L756) (lines 752-756).

The agents return structured data including:

• Table and view metadata (TableInfo)
• Detailed column, index, foreign key, and trigger definitions (TableSchemaDetail)
• Functions, sequences, rules, and ownership information
• The target DatabaseType identifier (defined in crates/dbx-core/src/models/connection.rs)

Building the Diff Model

The SchemaDiffPreparationOptions Struct

Once metadata is collected from both profiles, DBX packages all objects into the SchemaDiffPreparationOptions struct defined in [crates/dbx-core/src/schema_diff.rs](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/schema_diff.rs#L66-L98) (lines 66-98). This configuration struct contains:

• Source and target tables, details, functions, sequences, rules, and owners
• The target DatabaseType for SQL dialect handling
• Comparison flags: ignore_comments, cascade_delete, and compare_column_order

The Core Diff Algorithm

Entry Point and Object Comparison

The diff calculation begins at the prepare_schema_diff function ([schema_diff.rs](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/schema_diff.rs#L219-L225) lines 219-225). This function serves as the primary entry point and executes the comparison workflow:

1. Invokes diff_schema to categorize tables and views as added, removed, or modified
2. Generates individual sync SQL snippets for each table diff by calling generate_schema_sync_sql (see the processing loop at lines 225-238)

Granular Diff Helpers

For each modified table, DBX dispatches specialized comparison functions to produce detailed diff objects:

• diff_columns → ColumnDiff
• diff_indexes → IndexDiff
• diff_foreign_keys → ForeignKeyDiff
• diff_triggers, diff_functions, diff_sequences, diff_rules, diff_owners

These helpers capture precise structural changes such as column type modifications, index additions, or ownership transfers.

SQL Generation and Synchronization

After computing all differences, DBX constructs a unified migration script. The generate_schema_sync_sql function walks through every TableDiff and emits appropriate DDL statements including ALTER TABLE, CREATE INDEX, DROP FOREIGN KEY, and comment changes (core implementation at [schema_diff.rs](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/schema_diff.rs#L95-L150) lines 95-150).

The final output is a SchemaDiffPreparation struct containing:

• diffs: Table-level changes
• function_diffs, sequence_diffs, rule_diffs, owner_diffs: Optional object changes
• sync_sql: The complete, ready-to-run migration script

API Integration Examples

Rust API Direct Usage

You can invoke the diff engine directly from Rust code:

use dbx_core::schema_diff::{
    SchemaDiffPreparationOptions, prepare_schema_diff,
};
use dbx_core::models::connection::DatabaseType;

// Assume `src_tables`, `tgt_tables`, `src_details`, `tgt_details` were obtained
// from two different DBX agents.
let options = SchemaDiffPreparationOptions {
    source_tables: src_tables,
    target_tables: tgt_tables,
    source_details: src_details,
    target_details: tgt_details,
    source_functions: vec![],
    target_functions: vec![],
    source_sequences: vec![],
    target_sequences: vec![],
    source_rules: vec![],
    target_rules: vec![],
    source_owners: vec![],
    target_owners: vec![],
    database_type: DatabaseType::Postgres,
    target_schema: Some("public".into()),
    ignore_comments: false,
    cascade_delete: true,
    compare_column_order: true,
    ..Default::default()
};

let preparation = prepare_schema_diff(options);
println!("Diff SQL:\n{}", preparation.sync_sql);

CLI Interface

The DBX CLI provides a straightforward interface for comparing connection profiles:

# Export two connection profiles (source and target) as JSON files.

dbx schema-diff \
    --source-profile source.json \
    --target-profile target.json \
    --database-type postgres \
    --target-schema public \
    --output diff.sql

The CLI reads the profiles, queries the respective agents for schema metadata, constructs the SchemaDiffPreparationOptions, invokes prepare_schema_diff, and writes the generated SQL to diff.sql.

Tauri Frontend Integration

For desktop applications using the Tauri frontend, invoke the diff command from JavaScript:

import { invoke } from '@tauri-apps/api/tauri';

async function getDiff(options) {
  // `options` matches SchemaDiffPreparationOptions shape.
  const preparation = await invoke('prepare_schema_diff', { options });
  console.log(preparation.sync_sql); // Show diff script in UI.
}

The Tauri command prepare_schema_diff is defined in [src-tauri/src/commands/schema_diff.rs](https://github.com/t8y2/dbx/blob/main/src-tauri/src/commands/schema_diff.rs#L2-L5) (lines 2-5). Alternatively, web clients can access the same functionality via the HTTP endpoint /schema-diff/prepare exposed in [crates/dbx-web/src/routes/schema_diff.rs](https://github.com/t8y2/dbx/blob/main/crates/dbx-web/src/routes/schema_diff.rs#L17-L20) (lines 17-20).

Summary

• DBX performs schema diff by collecting metadata via database drivers (e.g., list_tables in agents/drivers/xugu/main.go) and normalizing it into TableInfo and TableSchemaDetail structures.
• The comparison logic resides in crates/dbx-core/src/schema_diff.rs, specifically the prepare_schema_diff function (lines 219-225) and its granular helpers like diff_columns and diff_indexes.
• Configuration is controlled through SchemaDiffPreparationOptions (lines 66-98), which supports flags for ignore_comments, cascade_delete, and compare_column_order.
• The engine generates executable SQL through generate_schema_sync_sql, producing ALTER TABLE, CREATE INDEX, and other DDL statements in a unified sync_sql output.
• Results are exposed via multiple interfaces: direct Rust API, CLI wrapper, Tauri desktop commands, and HTTP web endpoints.

Frequently Asked Questions

What file contains the core schema diff logic in DBX?

The core diff algorithm, data structures, and SQL generation reside in crates/dbx-core/src/schema_diff.rs. This file contains the prepare_schema_diff entry point (lines 219-225), the SchemaDiffPreparationOptions struct definition (lines 66-98), and the generate_schema_sync_sql implementation (lines 95-150).

How does DBX handle different database types during schema comparison?

DBX uses the DatabaseType enum defined in crates/dbx-core/src/models/connection.rs to drive database-specific SQL rendering. When generating synchronization scripts, the prepare_schema_diff function references this type to ensure the emitted DDL (e.g., ALTER TABLE syntax) matches the target database's dialect.

Can DBX compare schemas across different database vendors?

Yes. DBX abstracts vendor-specific details through its driver architecture. Agents for Oracle, Xugu, and other databases normalize schema metadata into standard structures (TableInfo, TableSchemaDetail) before comparison. The diff engine operates on these normalized structures, enabling cross-vendor schema analysis.

What options control the schema diff behavior?

The SchemaDiffPreparationOptions struct provides several control flags:

• ignore_comments: Excludes comment differences from the comparison
• cascade_delete: Enables cascading deletions during synchronization
• compare_column_order: Enforces strict column position matching between tables

These options are passed to prepare_schema_diff to customize the diff generation and SQL output.

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