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

> DrawDB generates diff SQL for schema migrations by comparing diagram snapshots and creating dialect-specific DDL for forward and rollback scripts. Learn how it works.

- Repository: [drawDB/drawdb](https://github.com/drawdb-io/drawdb)
- Tags: how-to-guide
- Published: 2026-08-11

---

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

```javascript
{ 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`](https://github.com/drawdb-io/drawdb/blob/main/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:

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

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

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

```sql
-- 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`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/dbml/diff.js), then generating SQL via `generateMigrationSQL` in [`src/utils/migrations/diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/diffToSQL.js) (lines 50-445) handles each entity type specifically.