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

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, a single function that inspects diagram.database and routes to the appropriate engine-specific exporter.

// 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	toMySQL
PostgreSQL	postgres.js	toPostgres
SQLite	sqlite.js	toSQLite
MariaDB	mariadb.js	toMariaDB
SQL Server	mssql.js	toMSSQL
Oracle	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 maps increment: true to SERIAL, while toMySQL uses AUTO_INCREMENT. jsonToSQLite (and the dedicated 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)

The [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:

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

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

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

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 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 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) routes to the correct engine based on diagram.database
• Engine-specific exporters (mysql.js, postgres.js, sqlite.js, etc.) handle dialect-specific DDL generation
• Shared utilities (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 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 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 ensure generated SQL is syntactically valid without executing untrusted input as code.

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