# DrawDB SQL Export System Architecture: A Deep Dive into Multi-Dialect DDL Generation

> Explore the three-layer architecture behind DrawDB's SQL export system. Learn how it generates multi-dialect DDL from JSON diagram definitions for efficient database schema creation.

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

---

**DrawDB's SQL export system uses a three-layer architecture—dispatcher, engine-specific exporters, and shared utilities—to transform JSON diagram definitions into database-specific CREATE TABLE statements.**

This article examines how the open-source database diagramming tool converts visual schemas into executable SQL for MySQL, PostgreSQL, SQLite, and other engines. The architecture separates dialect-specific syntax from common logic, making it straightforward to add support for new database systems.

## The Three-Layer Export Pipeline

DrawDB stores every diagram as a JSON object containing **tables**, **fields**, **relationships**, **indexes**, and optional **comments**. The SQL export subsystem in `src/utils/exportSQL/` transforms this structure into valid DDL through a clean pipeline: **Diagram → Dispatcher → Engine Exporter → Shared Helpers → Final SQL string**.

### Layer 1: The Dispatcher (`exportSQL`)

The entry point is [`exportSQL`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/index.js), a single function that inspects `diagram.database` and routes to the appropriate engine-specific exporter.

```javascript
// Simplified dispatcher logic from exportSQL/index.js
export function exportSQL(diagram) {
  switch (diagram.database) {
    case DB.MYSQL:      return toMySQL(diagram);
    case DB.POSTGRES:   return toPostgres(diagram);
    case DB.SQLITE:     return toSQLite(diagram);
    case DB.MARIADB:    return toMariaDB(diagram);
    case DB.MSSQL:      return toMSSQL(diagram);
    case DB.ORACLE:     return toOracle(diagram);
    default:            throw new Error(`Unsupported database: ${diagram.database}`);
  }
}

```

This **centralized routing** keeps engine selection in one location. Adding support for a new database requires only a new case and a new exporter file.

### Layer 2: Engine-Specific Exporters

Each database engine has its own module that builds dialect-specific DDL:

| Engine | File | Key Function |
|--------|------|--------------|
| MySQL | [`mysql.js`](https://github.com/drawdb-io/drawdb/blob/main/mysql.js) | `toMySQL` |
| PostgreSQL | [`postgres.js`](https://github.com/drawdb-io/drawdb/blob/main/postgres.js) | `toPostgres` |
| SQLite | [`sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/sqlite.js) | `toSQLite` |
| MariaDB | [`mariadb.js`](https://github.com/drawdb-io/drawdb/blob/main/mariadb.js) | `toMariaDB` |
| SQL Server | [`mssql.js`](https://github.com/drawdb-io/drawdb/blob/main/mssql.js) | `toMSSQL` |
| Oracle | [`oraclesql.js`](https://github.com/drawdb-io/drawdb/blob/main/oraclesql.js) | `toOracle` |

All exporters follow a **consistent pattern**:

1. **Iterate over `diagram.tables`** — generate column definitions, primary keys, unique constraints, and indexes
2. **Iterate over `diagram.references`** — produce `ALTER TABLE … ADD FOREIGN KEY` statements (or inline FKs for SQLite)
3. **Apply database-specific type mappings** — convert abstract types like `INT`, `ENUM`, `JSON` to dialect-specific syntax

For example, [`toPostgres`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/postgres.js) maps `increment: true` to `SERIAL`, while [`toMySQL`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/mysql.js) uses `AUTO_INCREMENT`. [`jsonToSQLite`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/generic.js#L70-L104) (and the dedicated [`sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/sqlite.js)) embeds foreign keys directly in CREATE TABLE statements since SQLite's `PRAGMA foreign_keys` requires this approach.

### Layer 3: Shared Utilities ([`shared.js`](https://github.com/drawdb-io/drawdb/blob/main/shared.js))

The [[`exportSQL/shared.js`](https://github.com/drawdb-io/drawdb/blob/main/exportSQL/shared.js)](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/shared.js) module provides **database-agnostic helpers** that all exporters consume:

- **`getFkColumnNames`** — extracts source and target column names from relationship definitions
- **`parseDefault`** — determines if a default value is a function, keyword, or requires quoting
- **`escapeQuotes`** — safely escapes single quotes in comments or default values
- **`exportFieldComment`** — formats multiline comments for the target dialect
- **`uniqueConstraintClause`** — assembles `UNIQUE` constraint syntax

These utilities **prevent code duplication** and ensure consistent handling of edge cases across all engines.

## Type Mapping and Dialect Quirks

Each exporter handles **abstract field types** through specialized conversion logic. Consider how `INT` with `increment: true` transforms:

```javascript
// PostgreSQL: SERIAL is a pseudo-type creating a sequence
`"${field.name}" SERIAL PRIMARY KEY`

// MySQL: explicit AUTO_INCREMENT attribute
`${field.name} INT AUTO_INCREMENT PRIMARY KEY`

// SQLite: INTEGER PRIMARY KEY implies auto-increment behavior
`${field.name} INTEGER PRIMARY KEY`

```

The **type mapping functions** (often `getTypeString` or inline logic) centralize these conversions, making the codebase maintainable when database vendors update their syntax.

## Foreign Key Resolution Strategies

DrawDB handles relationships through **`getFkColumnNames`** in [`shared.js`](https://github.com/drawdb-io/drawdb/blob/main/shared.js), which resolves the source and target columns for any reference. Exporters then choose their strategy:

- **MySQL, PostgreSQL, MariaDB, Oracle**: Generate separate `ALTER TABLE … ADD FOREIGN KEY` statements after all tables exist
- **SQLite**: Embed foreign key clauses directly in `CREATE TABLE` because SQLite's `ALTER TABLE` has limited FK support

This **dual-path approach** respects database capabilities without complicating the diagram data model.

## Safe Default and Comment Handling

Two critical utilities protect against syntax errors and injection-like issues:

**`parseDefault`** in [`shared.js`](https://github.com/drawdb-io/drawdb/blob/main/shared.js):
- Recognizes unquoted keywords: `CURRENT_TIMESTAMP`, `NOW()`, `UUID()`
- Wraps string literals in appropriate quotes
- Preserves numeric values unquoted

**`escapeQuotes`** and **`exportFieldComment`**:
- Escape nested quotes to prevent string termination issues
- Format multiline comments using dialect-specific syntax (PostgreSQL's `COMMENT ON`, MySQL's `COMMENT=` table attribute)

## Practical Example: Exporting a Diagram

Here's a complete workflow from diagram JSON to MySQL output:

```javascript
import { exportSQL } from "./src/utils/exportSQL/index.js";

const diagram = {
  database: "MYSQL",
  tables: [
    {
      name: "orders",
      fields: [
        { name: "id", type: "INT", primary: true, notNull: true, increment: true },
        { name: "user_id", type: "INT", notNull: true },
        { name: "total", type: "DECIMAL", size: "10,2", notNull: true },
        { name: "status", type: "ENUM", values: ["pending", "shipped", "delivered"], default: "pending" }
      ],
      indexes: [
        { name: "idx_user", fields: ["user_id"] }
      ]
    },
    {
      name: "users",
      fields: [
        { name: "id", type: "INT", primary: true, notNull: true, increment: true },
        { name: "email", type: "VARCHAR", size: "255", notNull: true, unique: true }
      ]
    }
  ],
  references: [
    {
      startTableId: 0,    // orders
      endTableId: 1,      // users
      startFieldId: 1,    // orders.user_id
      endFieldId: 0       // users.id
    }
  ]
};

const sql = exportSQL(diagram);
console.log(sql);

```

Generated MySQL output (formatted for clarity):

```sql
CREATE TABLE IF NOT EXISTS `users` (
	`id` INT AUTO_INCREMENT NOT NULL,
	`email` VARCHAR(255) NOT NULL UNIQUE,
	PRIMARY KEY(`id`)
);

CREATE TABLE IF NOT EXISTS `orders` (
	`id` INT AUTO_INCREMENT NOT NULL,
	`user_id` INT NOT NULL,
	`total` DECIMAL(10,2) NOT NULL,
	`status` ENUM('pending', 'shipped', 'delivered') DEFAULT 'pending',
	PRIMARY KEY(`id`),
	KEY `idx_user` (`user_id`)
);

ALTER TABLE `orders` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

```

Changing `database: "POSTGRES"` in the same diagram produces PostgreSQL-specific DDL with `SERIAL`, `"quoted_identifiers"`, and `COMMENT ON` statements—without modifying any other code.

## Extensibility: Adding a New Database Engine

The architecture makes engine addition straightforward:

1. Create [`src/utils/exportSQL/newengine.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/newengine.js) exporting a `toNewEngine` function
2. Add the engine to the `DB` enum and `exportSQL` dispatcher switch case
3. Implement type mappings and any dialect-specific constraint handling
4. Reuse [`shared.js`](https://github.com/drawdb-io/drawdb/blob/main/shared.js) utilities for FK resolution, defaults, and comments

The **modular file organization** isolates dialect complexity, while the **shared utility layer** prevents fragmentation of common logic.

## Summary

- **DrawDB's SQL export system** uses a three-layer pipeline: dispatcher → engine exporter → shared utilities
- The **dispatcher** (`exportSQL` in [`index.js`](https://github.com/drawdb-io/drawdb/blob/main/index.js)) routes to the correct engine based on `diagram.database`
- **Engine-specific exporters** ([`mysql.js`](https://github.com/drawdb-io/drawdb/blob/main/mysql.js), [`postgres.js`](https://github.com/drawdb-io/drawdb/blob/main/postgres.js), [`sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/sqlite.js), etc.) handle dialect-specific DDL generation
- **Shared utilities** ([`shared.js`](https://github.com/drawdb-io/drawdb/blob/main/shared.js)) provide reusable FK resolution, default parsing, quote escaping, and comment formatting
- **Type mapping** is localized per engine, allowing precise control over database-specific syntax
- The **modular architecture** enables adding new database support with minimal changes to existing code

## Frequently Asked Questions

### How does DrawDB decide which SQL dialect to generate?

The `exportSQL` function in [`src/utils/exportSQL/index.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/index.js) reads `diagram.database`—a value from the `DB` enum such as `DB.MYSQL` or `DB.POSTGRES`—and uses a switch statement to invoke the corresponding engine-specific exporter. This centralizes dialect selection and makes the routing logic transparent.

### Can I export the same diagram to multiple database formats?

Yes. Since the diagram is a pure JSON object describing tables, fields, and relationships without database-specific syntax, you can pass the same object to `exportSQL` with different `database` values. The dispatcher will produce MySQL, PostgreSQL, SQLite, or other dialect outputs from identical source data.

### Where is foreign key logic handled in DrawDB's export system?

Foreign key resolution uses `getFkColumnNames` from [`src/utils/exportSQL/shared.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/shared.js) to extract column names from relationship definitions. Each engine then formats FK constraints appropriately—SQLite uses inline `REFERENCES` clauses in `CREATE TABLE`, while other engines generate separate `ALTER TABLE … ADD FOREIGN KEY` statements after table creation.

### What prevents SQL injection when exporting default values or comments?

The `parseDefault` function distinguishes between SQL keywords/functions (left unquoted) and string literals (properly quoted). `escapeQuotes` sanitizes single quotes in any text content. These utilities in [`shared.js`](https://github.com/drawdb-io/drawdb/blob/main/shared.js) ensure generated SQL is syntactically valid without executing untrusted input as code.