How DrawDB Generates SQL for Oracle: A Deep Dive into the Export Engine

DrawDB generates Oracle SQL by traversing the internal diagram model in src/utils/exportSQL/oraclesql.js, converting tables, columns, constraints, and relationships into Oracle-compatible DDL statements with quoted identifiers and proper data type mappings.

DrawDB is an open-source database diagramming tool that exports visual schemas to executable SQL scripts. Understanding how DrawDB generates SQL for Oracle helps database architects validate their designs against Oracle-specific syntax requirements. The core translation logic resides in src/utils/exportSQL/oraclesql.js, which transforms the internal JSON diagram model into standard Oracle DDL.

The Oracle SQL Export Pipeline

Entry Point: The toOracleSQL Function

The export process begins in src/utils/exportSQL/oraclesql.js where the toOracleSQL function receives the diagram object. This function iterates over diagram.tables and diagram.references, building a complete SQL script through string concatenation. It processes each table sequentially, then handles foreign key relationships in a second pass to ensure all referenced tables exist before creating constraints.

Data Type Mapping and Metadata

DrawDB relies on src/data/datatypes.js to resolve generic types to Oracle-specific equivalents like VARCHAR2 and NUMBER. The dbToTypes map provides metadata properties including hasQuotes and hasCheck that determine how each column renders its default values and constraints. This abstraction allows the exporter to handle Oracle-specific formatting without hardcoding type logic in the export script.

Constructing Oracle DDL Statements

Table and Column Generation

For each table, DrawDB emits CREATE TABLE "<table_name>" with quoted identifiers. Columns include:

• Data type and size (e.g., VARCHAR2(100))
• NOT NULL constraints
• Identity columns using GENERATED ALWAYS AS IDENTITY
• Default values processed through the parseDefault helper
• Inline check constraints when hasCheck is true

Primary Keys and Unique Constraints

After column definitions, DrawDB appends PRIMARY KEY(…) for columns marked primary: true. Additional unique constraints generate via the uniqueConstraintClause helper from src/utils/exportSQL/shared.js, producing CONSTRAINT "<name>" UNIQUE ("col1", "col2") fragments that follow the column list.

Index Creation

Separate CREATE [UNIQUE] INDEX statements follow the table definition. Each index references quoted column names from the table's indices array, ensuring Oracle-compliant identifier casing.

Foreign Key Relationships

Post-table generation, DrawDB processes diagram.references to build ALTER TABLE statements. The getFkColumnNames helper maps column IDs to names, generating:

ALTER TABLE "<start>" ADD CONSTRAINT "<name>" 
FOREIGN KEY (…) REFERENCES "<end>" (…) 
ON UPDATE … ON DELETE …

Handling Default Values and Special Cases

The parseDefault Helper Function

Located in src/utils/exportSQL/shared.js, parseDefault determines whether to wrap defaults in single quotes based on the column's hasQuotes property and whether the value represents a function or keyword. This prevents string literals from being unquoted while allowing SQL functions like SYSDATE to pass through unquoted.

Identity Columns and Check Constraints

Oracle identity columns render as GENERATED ALWAYS AS IDENTITY when the field has increment: true. Check constraints appear inline when the data type metadata supports them, ensuring validation rules travel with the column definition rather than as separate ALTER statements.

Practical Example: Generating Oracle SQL from a Diagram

The following JavaScript demonstrates how DrawDB converts a diagram object into Oracle DDL:

// Example diagram fragment (simplified)
const diagram = {
  database: "ORACLESQL",
  tables: [
    {
      name: "EMPLOYEES",
      comment: "Employee records",
      fields: [
        { name: "ID", type: "INTEGER", notNull: true, increment: true, primary: true },
        { name: "NAME", type: "VARCHAR2", size: 100, notNull: true },
        { name: "SALARY", type: "NUMBER", size: 10, default: "0" },
      ],
      indices: [{ name: "EMP_NAME_IDX", unique: false, fields: ["NAME"] }],
      uniqueConstraints: [],
    },
  ],
  references: [],
};

// Generate Oracle SQL
import { toOracleSQL } from "./src/utils/exportSQL/oraclesql.js";
const oracleSQL = toOracleSQL(diagram);
console.log(oracleSQL);

Resulting Oracle SQL (pretty-printed):

/* Employee records */
CREATE TABLE "EMPLOYEES" (
	"ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
	"NAME" VARCHAR2(100) NOT NULL,
	"SALARY" NUMBER(10) DEFAULT 0
,	PRIMARY KEY("ID")
)
-- Employee records;

CREATE INDEX "EMP_NAME_IDX"
ON "EMPLOYEES" ("NAME");

Summary

• DrawDB generates Oracle SQL through toOracleSQL in src/utils/exportSQL/oraclesql.js
• The system uses src/data/datatypes.js for type metadata and src/utils/exportSQL/shared.js for shared helpers like parseDefault and getFkColumnNames
• Tables, indexes, and foreign keys use quoted identifiers per Oracle standards
• Default values are intelligently quoted via parseDefault based on the hasQuotes property
• Identity columns use Oracle's native GENERATED ALWAYS AS IDENTITY syntax

Frequently Asked Questions

What file handles the Oracle SQL export in DrawDB?

The main logic lives in src/utils/exportSQL/oraclesql.js, specifically the toOracleSQL function that orchestrates the entire conversion process from the internal diagram model to Oracle DDL.

How does DrawDB handle Oracle-specific data types like VARCHAR2?

DrawDB maps generic types to Oracle equivalents using the dbToTypes configuration in src/data/datatypes.js, which supplies Oracle-specific properties like hasQuotes and hasCheck that drive formatting decisions for each column.

Does DrawDB support reverse-engineering Oracle SQL back into diagrams?

Yes, the import functionality exists in src/utils/importSQL/oraclesql.js, enabling round-trip conversion from existing Oracle schemas back into editable diagram models using complementary parsing logic.

How are default values quoted in the generated Oracle SQL?

The parseDefault helper in src/utils/exportSQL/shared.js analyzes the hasQuotes metadata property from the data type definition to determine whether to wrap default values in single quotes, distinguishing between string literals and SQL functions.

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