How DrawDB Generates Diff SQL for Schema Migrations: A Complete Technical Guide

DrawDB generates diff SQL for schema migrations by computing a structural diff between diagram snapshots and transforming those changes into dialect-specific DDL statements, producing both forward (up) and rollback (down) scripts.

DrawDB is an open-source database diagram designer that automates schema migration generation through a sophisticated two-stage pipeline. When you modify a database diagram in the visual editor, DrawDB generates diff SQL for schema migrations by comparing DBML (Database Markup Language) representations and converting detected changes into executable ALTER TABLE, CREATE INDEX, and foreign key commands. This process supports multiple database engines including PostgreSQL, MySQL, and others through a dialect-aware generation engine.

Stage 1: Computing the Structural Diagram Diff

The migration process begins in src/utils/dbml/diff.js with the diffDiagram function. This utility accepts two arguments—the previous diagram state (from) and the current diagram state (to)—and produces a plain JavaScript object representing every structural change.

The function walks the DBML representation of both diagrams, comparing tables, fields, relationships, indices, and enums. For each difference found, it adds an entry to the diff object where keys encode the path to the changed element (for example, tables#users#fields#email) and values contain the from and to states. This structured diff serves as the input for SQL generation.

Stage 2: Translating Diffs to Executable SQL

The core migration engine resides in src/utils/migrations/diffToSQL.js. The generateMigrationSQL function (line 41) receives three parameters: the diff object, a database target constant (such as DB.POSTGRES or DB.MYSQL), and the original diagram objects. It returns an object with up and down properties containing the forward and rollback SQL scripts.

Dialect-Aware Identifier Quoting

To ensure SQL safety across different databases, the getQuote helper (lines 12-16) returns the appropriate identifier quotation marks for the target DBMS:

• Backticks for MySQL (`)
• Square brackets for SQL Server ([])
• Double quotes for PostgreSQL and others (")

Column and Table Definition Builders

The generator uses specialized builders to construct DDL fragments. The columnDefinition function (lines 31-68) assembles column specifications including data types, nullability constraints, default values, and comments. For new tables, toTable (lines 71-87) constructs complete CREATE TABLE statements, handling primary key clauses, unique constraints, table inheritance, and initial indexes.

Relationship Resolution

Before generating foreign key statements, resolveRel (lines 89-110) maps DBML relationship definitions to concrete table and column names. This resolution step ensures that ALTER TABLE ... ADD CONSTRAINT statements reference the correct fields when establishing foreign keys with specific ON UPDATE and ON DELETE actions.

The Core Generation Loop

The heart of the engine is the iteration loop at lines 50-445: for (const [path, change] of Object.entries(diff)). This loop processes each diff entry and dispatches to specific handlers based on the entity type:

• Tables: Handles CREATE TABLE and DROP TABLE operations, plus column-level changes including additions, removals, renames, and property modifications (type changes, nullability toggles, default value updates, unique constraints, and auto-increment settings).
• Indices: Generates CREATE INDEX and DROP INDEX statements, managing index renames and field composition changes.
• Unique Constraints: Emits ALTER TABLE ... ADD CONSTRAINT ... UNIQUE or corresponding drop statements.
• Relationships: Produces ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY with proper reference clauses and cascade actions, or drops them for rollbacks.
• Types and Enums: Invokes toTypeDefinition and toEnumDefinition (lines 221-260) to create or drop user-defined types and enumerated types.

Up and Down Migration Separation

Throughout the generation process, the engine maintains two arrays: up for forward migrations and down for rollbacks. Each handler pushes the appropriate DDL statement into the respective array. At line 447, the function returns:

{ up: up.join("\n"), down: down.join("\n") }

This structure allows database administrators to execute the up script to apply changes and the down script to revert them if necessary.

UI Integration

The migration interface connects these utilities in src/components/EditorHeader/SideSheet/Migration.jsx. When a user opens the migration panel, the component calls diffDiagram to compare the saved diagram against the current working state (line 11), then passes the result to generateMigrationSQL with the selected database engine. The resulting SQL renders in the UI at line 73, allowing users to review, copy, or execute the generated scripts.

Practical Implementation Examples

The following example demonstrates how to programmatically generate migration scripts using DrawDB's internal utilities:

import { diffDiagram } from "./utils/dbml/diff";
import { generateMigrationSQL } from "./utils/migrations/diffToSQL";
import { DB } from "./data/constants";

const oldDiagram = /* diagram snapshot before change */;
const newDiagram = /* diagram snapshot after change */;

// 1️⃣ Compute the diff
const diff = diffDiagram(oldDiagram, newDiagram);

// 2️⃣ Generate the SQL
const { up, down } = generateMigrationSQL(diff, DB.POSTGRES, {
  from: oldDiagram,
  to: newDiagram,
});

console.log("=== UP Migration ===\n", up);
console.log("=== DOWN Migration ===\n", down);

When the diff detects a new column addition, the generator produces dialect-specific output:

-- Up
ALTER TABLE "users" ADD COLUMN "age" INT NOT NULL;

-- Down
ALTER TABLE "users" DROP COLUMN "age";

For table renames, the engine generates reversible statements:

-- Up
ALTER TABLE "old_name" RENAME TO "new_name";

-- Down
ALTER TABLE "new_name" RENAME TO "old_name";

Foreign key creation includes complete constraint definitions:

-- Up
ALTER TABLE "orders" ADD CONSTRAINT "fk_customer"
  FOREIGN KEY ("customer_id") REFERENCES "customers" ("id")
  ON UPDATE NO ACTION ON DELETE CASCADE;

-- Down
ALTER TABLE "orders" DROP CONSTRAINT "fk_customer";

Summary

• DrawDB uses a two-stage pipeline: first computing a structural diff via diffDiagram in src/utils/dbml/diff.js, then generating SQL via generateMigrationSQL in src/utils/migrations/diffToSQL.js.
• The DBML representation serves as the intermediate format for detecting changes between diagram versions.
• Dialect-aware generation ensures proper identifier quoting and syntax for PostgreSQL, MySQL, SQL Server, and other supported databases.
• The engine produces bidirectional migrations, collecting statements into separate up and down arrays for forward and rollback operations.
• All schema objects are supported including tables, columns, indices, unique constraints, foreign keys, and user-defined enums.

Frequently Asked Questions

What file handles the diff calculation in DrawDB?

The diff calculation logic resides in src/utils/dbml/diff.js. This file exports the diffDiagram function, which compares two DBML diagram objects and returns a structured diff object containing all detected changes between the old and new states.

How does DrawDB handle different SQL dialects when generating migrations?

DrawDB handles dialects through the DB constants (such as DB.POSTGRES and DB.MYSQL) passed to generateMigrationSQL. The engine uses the getQuote function to apply correct identifier quoting and switches between dialect-specific syntax rules for column definitions, constraint naming, and ALTER TABLE operations based on the target parameter.

Can DrawDB generate rollback scripts for schema changes?

Yes. The generateMigrationSQL function maintains separate up and down arrays throughout the generation loop. The up array collects forward-migration DDL statements while the down array collects corresponding rollback statements. Both are returned as joined strings, providing complete revert capability for every supported schema change.

What types of schema changes does DrawDB support in its diff SQL generation?

DrawDB supports comprehensive schema changes including table creation and deletion, column additions/removals/renames, data type modifications, nullability and default value changes, index creation and removal, unique constraint management, foreign key relationships with cascade actions, and user-defined type/enumeration changes. The core loop in diffToSQL.js (lines 50-445) handles each entity type specifically.

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