How DrawDB Converts ERD Diagrams to SQL DDL: A Technical Deep Dive

DrawDB converts ERD diagrams to SQL DDL by traversing a JSON schema representation of tables and relationships, then generating dialect-specific CREATE TABLE statements and ALTER TABLE constraints through modular renderers located in src/utils/exportSQL/.

DrawDB is an open-source database entity-relationship diagram designer that transforms visual schema designs into executable SQL statements. The conversion engine lives in the drawdb-io/drawdb repository and handles multiple database dialects including MySQL, PostgreSQL, and SQLite. Understanding this conversion pipeline reveals how the application bridges visual modeling with production-ready database code.

Schema Extraction and Internal Representation

The conversion process begins with DrawDB’s internal schema object defined in src/data/schemas.js. This JavaScript object stores the complete ERD state including an array of table definitions and their relationships.

Each table entry contains a structured columns array where every column specifies:

• name – the column identifier
• type – data type (e.g., INT, VARCHAR, TEXT)
• notNull – nullability constraint
• unique – uniqueness flag
• primary – primary key designation
• default – default value expressions
• unsigned – signedness (for numeric types)

The schema also maintains a relations array that maps foreign keys between tables, storing source and target column references necessary for constraint generation.

Dialect-Specific SQL Generation

The exportSQL module in src/utils/exportSQL/index.js serves as the entry point for DDL generation. It accepts the schema object and a dialect string (e.g., 'mysql', 'postgres', 'sqlite'), then delegates to specialized renderer files.

MySQL Rendering (toMySQL.js)

The MySQL renderer generates statements with backtick quoting and MySQL-specific syntax. It handles:

• Auto-increment keywords: AUTO_INCREMENT for primary keys
• Unsigned integers: Appends UNSIGNED to numeric types when specified
• Default constraints: Wraps string defaults in single quotes

PostgreSQL Rendering (toPostgres.js)

The PostgreSQL renderer emits standard SQL with PostgreSQL extensions:

• Serial types: Converts integer primary keys to SERIAL or uses GENERATED ALWAYS AS IDENTITY
• Array support: Handles PostgreSQL array notation for column types
• Double-quote identifiers: Preserves case-sensitive table and column names

SQLite Rendering (toSQLite.js)

The SQLite renderer produces lightweight DDL compatible with SQLite’s type system:

• Autoincrement: Uses SQLite-specific AUTOINCREMENT keyword only for integer primary keys
• Type affinity: Maps standard SQL types to SQLite storage classes
• Foreign key support: Enables PRAGMA foreign_keys = ON context where required

Column-Level SQL Construction

Each dialect renderer implements a fieldToSQL helper function that transforms column metadata into SQL fragments. This function concatenates:

1. The column name with proper identifier quoting
2. The data type specification
3. Constraints: NOT NULL, UNIQUE, DEFAULT values, and PRIMARY KEY
4. Dialect flags: UNSIGNED (MySQL), AUTO_INCREMENT variants, or SERIAL (PostgreSQL)

For example, a column defined as id: INT, primary: true, notNull: true renders as:

• MySQL: `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT
• PostgreSQL: "id" SERIAL NOT NULL PRIMARY KEY
• SQLite: `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT

Table Assembly and Foreign Key Constraints

After generating column definitions, the export engine assembles complete CREATE TABLE blocks by iterating through schema.tables. Each table generates:

CREATE TABLE table_name (
  column_definitions,
  table_constraints
);

Foreign key relationships require a two-phase approach. Since referential integrity constraints often reference tables defined later in the script, the engine:

1. First emits all CREATE TABLE statements without foreign keys
2. Then iterates through schema.relations to generate ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY statements

This ensures the DDL script executes sequentially without dependency errors. For a relation linking posts.user_id to users.id, the generator produces:

ALTER TABLE `posts` 
ADD CONSTRAINT `fk_posts_user_id` 
FOREIGN KEY (`user_id`) 
REFERENCES `users` (`id`);

UI Integration and Export Workflow

The export functionality surfaces in src/components/ExportModal.jsx, which presents a dialect selector dropdown. When the user triggers an export, the component calls:

import { exportSQL } from '@/utils/exportSQL';

function handleExport(dialect) {
  // diagram object contains tables and relations arrays
  const ddl = exportSQL(diagram, dialect);
  downloadFile(`${diagram.name}.sql`, ddl);
}

The exportSQL function signature accepts (schema, dialect) and returns a concatenated string containing all DDL statements separated by newlines. This string passes back to the UI for clipboard copying or file download.

Summary

• Internal Schema: DrawDB stores ERD data in src/data/schemas.js as JSON objects with tables and relations arrays
• Modular Renderers: Dialect-specific logic lives in src/utils/exportSQL/toMySQL.js, toPostgres.js, and toSQLite.js
• Column Generation: The fieldToSQL helper constructs column definitions with appropriate type modifiers and constraints
• Two-Pass Constraints: Foreign keys generate via separate ALTER TABLE statements to avoid circular dependencies
• Entry Point: The exportSQL function in src/utils/exportSQL/index.js orchestrates the conversion based on user-selected database type

Frequently Asked Questions

How does DrawDB handle different SQL dialects during export?

DrawDB delegates to specialized renderer modules based on the dialect parameter passed to exportSQL(). Each renderer—toMySQL.js, toPostgres.js, or toSQLite.js—implements dialect-specific syntax for data types, quoting, and auto-increment keywords while sharing the same schema traversal logic.

Can I customize the generated SQL output in DrawDB?

The open-source codebase allows modification of the renderer files in src/utils/exportSQL/. You can edit fieldToSQL implementations or table assembly loops to add custom constraints, change naming conventions, or insert database-specific pragmas before the standard output returns to the UI.

Why are foreign keys created with ALTER TABLE instead of inline constraints?

DrawDB uses a two-phase generation strategy to prevent reference errors during script execution. By emitting CREATE TABLE statements first, then ALTER TABLE ... ADD CONSTRAINT statements afterward, the DDL remains valid regardless of the order in which tables appear in the diagram or script.

What database systems does DrawDB currently support for SQL export?

According to the source code in src/utils/exportSQL/, DrawDB supports MySQL, PostgreSQL, and SQLite generation natively. The modular architecture allows additional dialects by creating new renderer files following the established pattern of exporting a default function that transforms schema objects into DDL strings.

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