How drawDB's Deep Diff Algorithm Detects Schema Changes

The deep diff algorithm in drawDB compares two DBML diagram objects by recursively diffing tables, fields, and relationships to produce a structured change report.

drawDB uses a custom deep diff implementation to detect schema modifications between diagram versions. Located in src/utils/dbml/diff.js, this algorithm powers migration generation and change visualization throughout the application. Understanding how it works helps developers extend drawDB or integrate its diffing logic into their own database tooling.

Core Diff Architecture

The algorithm follows a three-layer recursive structure. Each layer transforms complex objects into comparable maps, then classifies items as added, removed, or updated.

Table-Level Diffing

The diffTables function (lines 84-106) handles the first layer of comparison. It builds name-keyed maps for both diagram states, then categorizes each table:

• Added: Present in "after" but not "before"
• Removed: Present in "before" but not "after"
• Existing: Present in both, triggering field-level diffing

This map-based approach provides O(n) complexity for table detection rather than nested iteration.

// Conceptual flow from src/utils/dbml/diff.js
function diffTables(beforeTables, afterTables) {
  const beforeMap = Object.fromEntries(beforeTables.map(t => [t.name, t]));
  const afterMap = Object.fromEntries(afterTables.map(t => [t.name, t]));
  
  // Classification logic yields added/removed/updated arrays
}

Field-Level Comparison

For tables that exist in both states, diffFields (lines 58-78) performs granular column analysis. The function compares field properties across eight specific attributes:

Property	Comparison Behavior
type	Data type changes (e.g., VARCHAR → TEXT)
enum	Enumeration value modifications
default	Default value additions or changes
note	Documentation/description updates
unique	Constraint toggles
increment	Auto-increment flag changes
nullability	Nullable ↔ NOT NULL transitions
pk	Primary key designation

A field is marked updated only when at least one property differs. This prevents false positives from object reference changes or property ordering differences.

// Example: Detecting a modified column
const fieldDiff = diffFields(oldTable.fields, newTable.fields);
// Returns: { added: [], removed: [], updated: [{ name: 'email', changes: ['unique'] }] }

Relationship Diffing

The diffRelationships function (lines 116-148) handles foreign key changes. Relationships are matched using a composite key combining:

• Source table name
• Target table name
• Source field name(s)
• Target field name(s)

This composite matching prevents collisions when multiple relationships exist between the same tables.

The Top-Level diffDiagram Function

All three diff layers converge in diffDiagram, which returns a standardized structure:

{
  tables: {
    added: [...],      // New table definitions
    removed: [...],   // Dropped tables
    updated: [...]    // Tables with field changes
  },
  relationships: {
    added: [...],
    removed: [...],
    updated: [...]
  }
}

This plain object format enables downstream consumption by:

• Migration generators (src/utils/migrations/diffToSQL.js)
• UI highlighting (change indicators in the canvas)
• Export utilities (JSON patch formats)

Deterministic Output Guarantees

The algorithm enforces consistent diff ordering through alphabetic sorting at each comparison layer. This determinism ensures that:

• Generated migrations are reproducible
• UI change lists render predictably
• Diff objects can be safely serialized and hashed

Missing properties are treated as explicit changes rather than ignored, preventing silent drift when metadata is partially defined.

Practical Usage Examples

Generating a Schema Diff

import { diffDiagram } from '@/utils/dbml/diff';

const schemaDiff = diffDiagram(previousDiagram, currentDiagram);

// Inspect table additions
console.log('New tables:', schemaDiff.tables.added.map(t => t.name));

Converting Diff to SQL

import { diffToSQL } from '@/utils/migrations/diffToSQL';

const statements = diffToSQL(schemaDiff);
// Produces: ALTER TABLE ... ADD COLUMN ..., DROP COLUMN ..., etc.

Applying Diff to UI State

// Within a React component or state manager
diagram.applyDiff(schemaDiff);
// Updates visual indicators: green (added), red (removed), yellow (modified)

Summary

• Map-based comparison at tables, fields, and relationships layers provides linear time complexity
• Eight property checks per field ensure granular change detection without false positives
• Composite key matching for relationships handles complex multi-column foreign keys
• Deterministic alphabetic sorting guarantees reproducible output across runs
• Plain object return structure enables flexible downstream consumption by migrations, UI, and exports

Frequently Asked Questions

How does drawDB handle renamed tables or columns?

The deep diff algorithm treats renames as remove + add operations rather than updates. This conservative approach ensures data safety—migrations explicitly drop the old structure and create the new one, requiring user confirmation for destructive changes.

What happens when field properties are undefined in one version?

Undefined values are compared as explicit states. If a field has default: undefined in the before state and default: 'none' in the after state, this registers as an update. This prevents silent acceptance of missing metadata that could affect database behavior.

Can the diff algorithm detect index changes?

The current implementation in src/utils/dbml/diff.js focuses on tables, fields, and relationships. Index modifications are handled separately in the DBML parsing layer—indexes are treated as table properties and would appear in field-level updates when their definitions change.

Where does drawDB use the diff output in the application?

The primary consumer is src/utils/migrations/diffToSQL.js, which transforms diff objects into executable ALTER TABLE statements. The editor UI (src/pages/Editor.jsx and src/hooks/useDiagram.js) also uses diff results to highlight modified elements with color-coded borders and change badges.

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