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

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 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.

// 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 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 — Uses AUTOINCREMENT keyword, supports IF NOT EXISTS, minimal type system
• mysql.js — Backtick identifiers, AUTO_INCREMENT, UNSIGNED modifier, ENGINE=InnoDB
• postgres.js — Double-quote identifiers, SERIAL/BIGSERIAL types, IF NOT EXISTS on tables
• mssql.js — Bracket identifiers [like this], IDENTITY(1,1) for auto-increment, GO batch separators
• oraclesql.js — GENERATED ALWAYS AS IDENTITY syntax, Oracle-specific type mappings
• 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 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)

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

// 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 implementing the export function
2. Add DB.NEWDB to the enum in src/data/constants.js
3. Register the case in src/utils/exportSQL/index.js

// 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	Central dispatcher, entry point for all exports
src/utils/exportSQL/mysql.js	MySQL DDL with backticks, AUTO_INCREMENT, UNSIGNED
src/utils/exportSQL/postgres.js	PostgreSQL DDL with SERIAL, double quotes
src/utils/exportSQL/sqlite.js	SQLite DDL with AUTOINCREMENT, IF NOT EXISTS
src/utils/exportSQL/mssql.js	MSSQL DDL with brackets, IDENTITY, GO
src/utils/exportSQL/oraclesql.js	Oracle DDL with GENERATED AS IDENTITY
src/utils/exportSQL/mariadb.js	MariaDB variant of MySQL implementation
src/utils/exportSQL/generic.js	Fallback for minimal vendor-agnostic output
src/utils/migrations/diffToSQL.js	Migration script generation from diagram diffs
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 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
• 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 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 generates complete DDL for an entire schema, while generateMigrationSQL in 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, add DB.DUCKDB to the constants enum, and register the case in 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.

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