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

> Learn how DBX performs schema diff and comparison between database connection profiles. DBX collects metadata, builds a diff model, and generates SQL synchronization scripts.

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

---

**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)](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`](https://github.com/t8y2/dbx/blob/main/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)](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/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/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:

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

```bash

# 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`](https://github.com/t8y2/dbx/blob/main/diff.sql).

### Tauri Frontend Integration

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

```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)](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)](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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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.