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

> Discover how DrawDB transforms ERD diagrams into SQL DDL. Explore its JSON schema traversal and modular renderers for generating precise CREATE TABLE and ALTER TABLE statements. Learn the technical details now.

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

---

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

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

```sql
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`](https://github.com/drawdb-io/drawdb/blob/main/src/components/ExportModal.jsx), which presents a dialect selector dropdown. When the user triggers an export, the component calls:

```javascript
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`](https://github.com/drawdb-io/drawdb/blob/main/src/data/schemas.js) as JSON objects with tables and relations arrays
- **Modular Renderers**: Dialect-specific logic lives in [`src/utils/exportSQL/toMySQL.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/toMySQL.js), [`toPostgres.js`](https://github.com/drawdb-io/drawdb/blob/main/toPostgres.js), and [`toSQLite.js`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/toMySQL.js), [`toPostgres.js`](https://github.com/drawdb-io/drawdb/blob/main/toPostgres.js), or [`toSQLite.js`](https://github.com/drawdb-io/drawdb/blob/main/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.