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

> Learn how DrawDB generates Oracle SQL by converting your diagram model into compatible DDL statements. Explore the export engine at src/utils/exportSQL/oraclesql.js.

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

---

**DrawDB generates Oracle SQL by traversing the internal diagram model in [`src/utils/exportSQL/oraclesql.js`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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:

```sql
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`](https://github.com/drawdb-io/drawdb/blob/main/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:

```javascript
// 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):**

```sql
/* 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`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/oraclesql.js)
- The system uses [`src/data/datatypes.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/datatypes.js) for type metadata and [`src/utils/exportSQL/shared.js`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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`](https://github.com/drawdb-io/drawdb/blob/main/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.