# How SQL Export Architecture Is Structured in DrawDB: A Deep Dive Into the Dialect-Driven Engineering

> Explore DrawDB's SQL export architecture. Discover its three-layer plugin style, central dispatcher, and dialect-specific exporters for seamless DDL generation. Learn how DrawDB maps visual diagrams to SQL.

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

---

**DrawDB's SQL export system uses a three-layer, plugin-style architecture in `src/utils/exportSQL/` that maps visual database diagrams to dialect-specific DDL through a central dispatcher and dedicated exporter modules.**

The architecture separates concerns between entry-point routing, per-database syntax implementation, and migration generation. This design lets DrawDB support six major SQL dialects—SQLite, MySQL, PostgreSQL, MariaDB, MSSQL, and OracleSQL—while keeping each implementation isolated and testable.

## The Three-Layer Architecture

DrawDB's SQL export pipeline is organized into distinct layers that handle different responsibilities. Understanding this structure is essential for anyone extending the system or debugging export behavior.

### Layer 1: Entry Point and Dialect Selection

The `exportSQL(diagram)` function in [`src/utils/exportSQL/index.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/index.js) serves as the single entry point for all SQL generation. It reads the `database` property from the diagram object and routes to the appropriate exporter.

```javascript
// src/utils/exportSQL/index.js — simplified dispatcher logic
import { DB } from "../../data/constants.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.MSSQL:
      return toMSSQL(diagram);
    case DB.ORACLESQL:
      return toOracleSQL(diagram);
    case DB.MARIADB:
      return toMariaDB(diagram);
    default:
      return toGeneric(diagram);
  }
}

```

The `DB` enum is defined in [`src/data/constants.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/constants.js) and provides the canonical identifiers for all supported databases. This centralization prevents string-typos and makes the supported dialects discoverable.

### Layer 2: Dialect-Specific Exporters

Each supported database has its own module under `src/utils/exportSQL/` that encapsulates vendor-specific syntax rules. These modules handle:

- **Identifier quoting** (backticks for MySQL/MariaDB, double quotes for PostgreSQL, brackets for MSSQL)
- **Auto-increment syntax** (`AUTO_INCREMENT`, `SERIAL`, `IDENTITY`, `GENERATED AS IDENTITY`)
- **Type mappings** from DrawDB's abstract types to vendor-specific types
- **Engine clauses**, `IF NOT EXISTS` modifiers, and comment syntax

The six primary exporters and their distinctive characteristics:

- **[`sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/sqlite.js)** — Uses `AUTOINCREMENT` keyword, supports `IF NOT EXISTS`, minimal type system
- **[`mysql.js`](https://github.com/drawdb-io/drawdb/blob/main/mysql.js)** — Backtick identifiers, `AUTO_INCREMENT`, `UNSIGNED` modifier, `ENGINE=InnoDB`
- **[`postgres.js`](https://github.com/drawdb-io/drawdb/blob/main/postgres.js)** — Double-quote identifiers, `SERIAL`/`BIGSERIAL` types, `IF NOT EXISTS` on tables
- **[`mssql.js`](https://github.com/drawdb-io/drawdb/blob/main/mssql.js)** — Bracket identifiers `[like this]`, `IDENTITY(1,1)` for auto-increment, `GO` batch separators
- **[`oraclesql.js`](https://github.com/drawdb-io/drawdb/blob/main/oraclesql.js)** — `GENERATED ALWAYS AS IDENTITY` syntax, Oracle-specific type mappings
- **[`mariadb.js`](https://github.com/drawdb-io/drawdb/blob/main/mariadb.js)** — Reuses MySQL implementation with minor dialect adjustments

All exporters share helper functions like `q()` for identifier quoting and `parseType()` for abstract-to-concrete type conversion.

### Layer 3: Migration Generator

For schema evolution, [`src/utils/migrations/diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/migrations/diffToSQL.js) computes differences between diagram versions and generates incremental SQL. The `generateMigrationSQL(oldDiagram, newDiagram)` function:

1. Compares old and new diagram states to identify added, removed, and modified tables and fields
2. Applies the same `DB` enum-based dialect selection as `exportSQL`
3. Constructs `ALTER TABLE`, `DROP TABLE`, and `CREATE TABLE` statements with dialect-appropriate syntax
4. Adds batch separators like `GO` where required (MSSQL)

```javascript
// Example: Generating a migration between diagram versions
import { generateMigrationSQL } from "./src/utils/migrations/diffToSQL.js";

const migration = generateMigrationSQL(oldDiagram, newDiagram);
console.log(migration);
// Output:
// ALTER TABLE `users` ADD COLUMN `email` VARCHAR(255);
// DROP TABLE `legacy_table`;

```

## Complete Export Flow: From Diagram to SQL

The end-to-end SQL export process in DrawDB follows this sequence:

1. **Diagram validation** — The diagram object must include a valid `database` property matching a `DB` enum value
2. **Dispatcher invocation** — UI or API calls `exportSQL(diagram)`
3. **Exporter selection** — [`index.js`](https://github.com/drawdb-io/drawdb/blob/main/index.js) routes to the specialized module based on `diagram.database`
4. **Schema traversal** — The chosen exporter iterates `diagram.tables`, rendering tables, columns, indexes, primary keys, foreign keys, and comments
5. **String assembly** — Helper functions apply quoting and type mapping; the complete DDL string is returned

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

const mysqlDDL = exportSQL(myDiagram);
// Returns:
// CREATE TABLE `users` (
//   `id` INT NOT NULL AUTO_INCREMENT,
//   `name` VARCHAR(255) NOT NULL,
//   PRIMARY KEY (`id`)
// ) ENGINE=InnoDB;

```

## Extending: Adding a New SQL Dialect

The plugin architecture makes adding dialects straightforward. The pattern requires:

1. Create [`src/utils/exportSQL/newdb.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/newdb.js) implementing the export function
2. Add `DB.NEWDB` to the enum in [`src/data/constants.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/constants.js)
3. Register the case in [`src/utils/exportSQL/index.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/index.js)

```javascript
// src/utils/exportSQL/duckdb.js (hypothetical example)
export function toDuckDB(diagram) {
  // Implement DuckDB-specific DDL generation
}

// src/utils/exportSQL/index.js — add case
case DB.DUCKDB:
  return toDuckDB(diagram);

```

This isolation ensures that dialect-specific bugs remain confined and testing stays targeted.

## Key Implementation Files

| File | Purpose |
|------|---------|
| [`src/utils/exportSQL/index.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/index.js) | Central dispatcher, entry point for all exports |
| [`src/utils/exportSQL/mysql.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/mysql.js) | MySQL DDL with backticks, `AUTO_INCREMENT`, `UNSIGNED` |
| [`src/utils/exportSQL/postgres.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/postgres.js) | PostgreSQL DDL with `SERIAL`, double quotes |
| [`src/utils/exportSQL/sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/sqlite.js) | SQLite DDL with `AUTOINCREMENT`, `IF NOT EXISTS` |
| [`src/utils/exportSQL/mssql.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/mssql.js) | MSSQL DDL with brackets, `IDENTITY`, `GO` |
| [`src/utils/exportSQL/oraclesql.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/oraclesql.js) | Oracle DDL with `GENERATED AS IDENTITY` |
| [`src/utils/exportSQL/mariadb.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/mariadb.js) | MariaDB variant of MySQL implementation |
| [`src/utils/exportSQL/generic.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/generic.js) | Fallback for minimal vendor-agnostic output |
| [`src/utils/migrations/diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/migrations/diffToSQL.js) | Migration script generation from diagram diffs |
| [`src/data/constants.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/constants.js) | `DB` enum defining supported database types |

## Summary

- **Three-layer architecture**: Dispatcher → Dialect exporter → Migration generator
- **Centralized routing** through `exportSQL()` in [`src/utils/exportSQL/index.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/index.js) using the `DB` enum
- **Isolated dialect modules** in `src/utils/exportSQL/` with dedicated syntax handling
- **Migration support** via `generateMigrationSQL()` in [`src/utils/migrations/diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/migrations/diffToSQL.js)
- **Extensible design**: New dialects require only a new file and a case registration

## Frequently Asked Questions

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

The `exportSQL(diagram)` function checks `diagram.database` against the `DB` enum from [`src/data/constants.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/constants.js) and routes to the matching exporter module. Each diagram stores its target database type, ensuring the correct DDL syntax is produced.

### What is the difference between `exportSQL` and `generateMigrationSQL`?

`exportSQL` in [`src/utils/exportSQL/index.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/index.js) generates complete DDL for an entire schema, while `generateMigrationSQL` in [`src/utils/migrations/diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/migrations/diffToSQL.js) compares two diagram versions and produces incremental `ALTER`, `DROP`, and `CREATE` statements. Both use the same `DB` enum for dialect selection.

### How would I add support for a new database like DuckDB?

Create a new exporter file at [`src/utils/exportSQL/duckdb.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/duckdb.js), add `DB.DUCKDB` to the constants enum, and register the case in [`src/utils/exportSQL/index.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/index.js). The modular structure keeps your implementation isolated and testable.

### Where are identifier quoting rules defined?

Each dialect exporter implements its own `q()` or equivalent helper function. MySQL uses backticks, PostgreSQL uses double quotes, MSSQL uses brackets, and SQLite uses double quotes—each handled within its respective module.