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

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 handles database-agnostic schema migrations through a clean separation: first a structural diff is calculated, then generateMigrationSQL in 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 (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 (lines 31-69 in 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:

// 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:

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.

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 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 provides consistent handling of defaults, escaping, and comments

Practical Usage Examples

Generating a Complete Migration

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

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	Core migration generation with generateMigrationSQL, columnDefinition, toTable, resolveRel
src/utils/exportSQL/shared.js	Shared utilities: escapeQuotes, parseDefault, exportFieldComment, uniqueConstraintClause
src/data/constants.js	DB enum defining supported database engines
src/data/databases.js	Capability flags per database (types, enums, inheritance)
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 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 and capability flags in 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.

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