# How DrawDB Generates Migration SQL for Schema Modifications: A Deep Dive into the Diff-to-SQL Pipeline

> Learn how DrawDB generates migration SQL by diffing diagram states and converting the changes into executable SQL for your database. Understand the diff-to-SQL pipeline.

- Repository: [drawDB/drawdb](https://github.com/drawdb-io/drawdb)
- Tags: deep-dive
- Published: 2026-08-14

---

**DrawDB creates migration scripts by computing a diff between two diagram states and transforming that diff into executable SQL for your chosen database engine.**

The open-source diagram editor [drawdb-io/drawdb](https://github.com/drawdb-io/drawdb) handles database-agnostic schema migrations through a clean separation: first a structural diff is calculated, then `generateMigrationSQL` in [`src/utils/migrations/diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/migrations/diffToSQL.js) walks that diff to produce reversible "up" and "down" scripts. This article explains the complete pipeline, from quote handling to final output.

## The Core Pipeline: From Diff to Executable SQL

The migration generation process centers on `generateMigrationSQL`, which receives a diff object, target database identifier, and diagram state objects, then returns `{ up, down }` containing the full migration script pair.

### Step 1: Database-Specific Quote Handling

Every identifier in the generated SQL must respect the target database's quoting rules. The `getQuote` helper selects the appropriate style based on the `DB` enum value:

- `"` for PostgreSQL
- `` ` `` for MySQL/MariaDB
- `[]` for SQL Server (MSSQL)

This branching occurs early in [`diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/diffToSQL.js) (lines 12-16) and propagates through all subsequent SQL construction.

### Step 2: Building Column Definitions with `columnDefinition`

Before assembling table or alteration statements, the generator needs complete column specifications. The `columnDefinition` function constructs these from field objects, incorporating:

- Data type and size (e.g., `VARCHAR(255)`, `DECIMAL(10,2)`)
- Nullability constraints
- Default values (parsed via `parseDefault`)
- Comments (via `exportFieldComment`)
- Unique constraints (via `uniqueConstraintClause`)

This function reuses shared utilities from [`src/utils/exportSQL/shared.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/shared.js) (lines 31-69 in [`diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/diffToSQL.js)), keeping the codebase DRY while supporting database-specific syntax variations.

### Step 3: Table Creation with `toTable`

New tables are assembled by `toTable`, which builds complete `CREATE TABLE` statements including:

- All column definitions
- Primary key clauses
- Unique constraints
- Inheritance specifications (PostgreSQL only)
- Initial indexes

The function applies the appropriate quoting function throughout (lines 71-88).

### Step 4: Relationship Resolution via `resolveRel`

Foreign key generation requires mapping abstract relationships to concrete column pairs. The `resolveRel` helper extracts source and target table names along with their respective field names from relationship objects (lines 89-106). This mapping feeds into `ALTER TABLE … ADD CONSTRAINT` and `DROP CONSTRAINT` generation.

## Diff Traversal and SQL Generation

The heart of `generateMigrationSQL` iterates over diff entries and dispatches to specialized handlers based on the changed element type.

### Path Parsing and Switch Dispatch

Each diff key encodes what changed using a path notation:

```js
// Examples of diff paths
"tables#name=users"                          // entire table added/removed
"tables#fields[name=age,type=INTEGER]#type"  // column type changed
"relationships#name=fk_orders_users"         // foreign key modified
"indices#name=idx_email"                     // index changed

```

The function walks these paths with:

```js
for (const [path, change] of Object.entries(diff)) {
  // switch statement selects handler based on element type
}

```

Lines 41-46 implement this dispatch, with separate branches for tables, fields, relationships, indices, types, and enums.

### Table-Level Change Handling

For structural modifications, the generator produces comprehensive `ALTER TABLE` statements:

| Change Type | Up Statement | Down Statement |
|-------------|-----------|----------------|
| Add column | `ADD COLUMN` | `DROP COLUMN` |
| Remove column | `DROP COLUMN` | `ADD COLUMN` (with full definition) |
| Type change | `ALTER COLUMN … TYPE` | Reverse type change |
| Default change | `SET DEFAULT` / `DROP DEFAULT` | Restore previous default |
| Nullability | `SET NOT NULL` / `DROP NOT NULL` | Toggle opposite |
| Comment | `COMMENT ON COLUMN` | Restore previous comment or drop |

Lines 61-260 handle these cases, with special logic for primary key modifications, unique constraints, check constraints, auto-increment flags, and array type annotations.

### Index and Constraint Management

Index operations (lines 362-459) account for database-specific behaviors:

- **SQLite**: Uses `IF NOT EXISTS` guards
- **MSSQL**: Employs `EXEC sp_rename` for index renames
- **PostgreSQL/MySQL**: Standard `CREATE INDEX` / `DROP INDEX` syntax

Foreign key alterations use the relationship resolution from `resolveRel` to ensure column references remain accurate through renames and deletions.

### Custom Types and Enums

When the target database supports advanced type systems (`databases[database].hasTypes` or `hasEnums`), the generator emits:

- `CREATE TYPE` / `DROP TYPE` for composite types
- `ALTER TYPE … RENAME` for type renames
- `COMMENT ON TYPE` for documentation

These branches (lines 376-456) are conditionally executed based on capability flags defined in [`src/data/databases.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/databases.js).

## Database-Specific Adaptations

The migration system maintains engine-agnostic core logic while adapting to individual database quirks through:

1. **Capability checks** — [`src/data/databases.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/databases.js) flags (types, enums, inheritance support)
2. **Syntax branches** — Conditional string templates for MSSQL rename procedures, SQLite `IF NOT EXISTS`, etc.
3. **Shared utility reuse** — [`src/utils/exportSQL/shared.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/shared.js) provides consistent handling of defaults, escaping, and comments

## Practical Usage Examples

### Generating a Complete Migration

```js
import { generateMigrationSQL } from "./src/utils/migrations/diffToSQL";
import { DB } from "./src/data/constants";

// Diff object from diagram comparison
const diff = {
  "tables#name=users": { 
    from: null, 
    to: { name: "users", fields: [...] } 
  },
  "tables#fields[name=age,type=INTEGER]#type": { 
    from: "INTEGER", 
    to: "BIGINT" 
  },
};

const result = generateMigrationSQL(diff, DB.POSTGRES, {
  from: oldDiagram,
  to: newDiagram,
});

// result.up contains apply statements
// result.down contains revert statements

```

### Creating Column Definitions Directly

```js
import { columnDefinition } from "./src/utils/migrations/diffToSQL";

const field = {
  name: "price",
  type: "DECIMAL",
  size: "10,2",
  notNull: true,
  default: "0",
  comment: "product price",
};

const sql = columnDefinition(field, DB.MYSQL);
// `price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'product price'`

```

## Key Files in the Migration System

| File | Purpose |
|------|---------|
| [`src/utils/migrations/diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/migrations/diffToSQL.js) | Core migration generation with `generateMigrationSQL`, `columnDefinition`, `toTable`, `resolveRel` |
| [`src/utils/exportSQL/shared.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/shared.js) | Shared utilities: `escapeQuotes`, `parseDefault`, `exportFieldComment`, `uniqueConstraintClause` |
| [`src/data/constants.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/constants.js) | `DB` enum defining supported database engines |
| [`src/data/databases.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/databases.js) | Capability flags per database (types, enums, inheritance) |
| [`src/utils/utils.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/utils.js) | `getRelationshipFields` for foreign key resolution |

## Summary

- DrawDB generates migration SQL through a **two-phase pipeline**: diff computation followed by SQL generation
- The `generateMigrationSQL` function in [`src/utils/migrations/diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/migrations/diffToSQL.js) orchestrates the transformation, producing reversible up/down scripts
- **Database-agnostic design** uses capability flags and quote selection to adapt to PostgreSQL, MySQL, MSSQL, SQLite, and others
- **Comprehensive change coverage** includes tables, columns, constraints, indexes, relationships, and custom types
- All generated migrations are **fully reversible**, with down statements that precisely undo the up changes

## Frequently Asked Questions

### How does DrawDB handle database-specific SQL syntax differences?

DrawDB uses a combination of the `DB` enum from [`src/data/constants.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/constants.js) and capability flags in [`src/data/databases.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/databases.js) to branch logic appropriately. The `getQuote` function selects identifier quoting styles per engine, while conditional blocks handle syntax like `EXEC sp_rename` for MSSQL or `IF NOT EXISTS` for SQLite. This keeps the core diff traversal generic while surfacing engine quirks at the statement construction level.

### Can DrawDB migrations be run automatically or only exported as scripts?

The analysis focuses on SQL script generation rather than execution. The `generateMigrationSQL` function returns `{ up, down }` string objects that can be exported, reviewed, and executed through your preferred migration runner. The generated SQL follows standard conventions compatible with tools like Flyway, Liquibase, or raw `psql`/`mysql` clients.

### What happens when a column type change isn't directly reversible?

When type alterations lack perfect inverse mappings (e.g., `VARCHAR` → `TEXT`), the down migration records the original type from the `from` field in the diff. Since diffs preserve complete before/after states, DrawDB can reconstruct the previous column definition precisely, even for complex changes involving defaults, constraints, and comments.

### How does DrawDB detect what changed between diagram versions?

While this article covers SQL generation from diffs, the diff itself is computed by a separate algorithm that compares diagram objects. The resulting diff uses path notation like `"tables#fields[name=age,type=INTEGER]#type"` to locate specific elements and `{ from, to }` pairs to describe changes. `generateMigrationSQL` consumes this standardized diff format without concern for how it was produced.