# How DrawDB Parses and Imports SQL DDL: A Deep Dive into the Architecture

> Explore the DrawDB architecture for parsing and importing SQL DDL. Discover how our three-stage pipeline transforms DDL into interactive database diagrams.

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

---

**DrawDB uses a three-stage pipeline—parsing, AST normalization, and model conversion—to transform raw SQL DDL into interactive database diagrams.**

This article explores the complete architecture for parsing and importing SQL DDL in drawdb-io/drawdb, from the initial parser selection to the final diagram model generation. Understanding this system reveals how the tool achieves cross-database compatibility while maintaining clean separation of concerns.

---

## Overview of the SQL DDL Import Pipeline

The import architecture in DrawDB follows a **language-agnostic design pattern**. Rather than building monolithic parsers for each database dialect, the codebase isolates database-specific logic into thin wrapper modules while sharing common transformation logic across all supported engines.

The pipeline consists of three distinct stages:

1. **Parsing** – Database-specific parsers convert raw DDL strings into abstract syntax trees (ASTs)
2. **Normalization** – The shared `buildSQLFromAST` function walks each AST and produces a common intermediate representation
3. **Model Conversion** – The `importSQL` function transforms the normalized schema into DrawDB's internal diagram format

This separation allows new database dialects to be added with minimal code changes.

---

## Stage 1: SQL Parsing by Database Dialect

Each supported database engine has dedicated parser integration in `src/utils/importSQL/`. The tool relies on established parsing libraries rather than custom grammars, reducing maintenance burden and improving reliability.

### MySQL and MariaDB: node-sql-parser

The MySQL importer ([`src/utils/importSQL/mysql.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/mysql.js)) wraps the **node-sql-parser** library. This parser handles `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, and constraint definitions across MySQL 5.7+ and MariaDB 10.x syntax.

### PostgreSQL: pgsql-ast-parser

PostgreSQL support lives in [`src/utils/importSQL/postgres.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/postgres.js) and uses **pgsql-ast-parser**. This library captures PostgreSQL-specific features including:

- Array types and composite types
- `SERIAL` and `BIGSERIAL` pseudo-types
- `ON DELETE` and `ON UPDATE` referential actions
- Partial indexes and expression-based constraints

### SQLite: Custom Lexer

SQLite parsing ([`src/utils/importSQL/sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/sqlite.js)) employs a **custom lexer** due to SQLite's simpler grammar and deviations from standard SQL. This lightweight approach avoids heavy dependencies for a dialect with relatively limited DDL surface area.

---

## Stage 2: AST to Common Intermediate Representation

The heart of the normalization stage resides in [`src/utils/importSQL/shared.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/shared.js). The `buildSQLFromAST` function performs **database-agnostic AST traversal**, extracting schema elements regardless of which parser produced the tree.

### Type Mapping Through data/datatypes.js

During normalization, native database types are mapped to DrawDB's canonical type system via [`data/datatypes.js`](https://github.com/drawdb-io/drawdb/blob/main/data/datatypes.js). Examples include:

| Native Type | DrawDB Canonical |
|-------------|------------------|
| `VARCHAR(n)` | `string` |
| `INT`, `INTEGER`, `BIGINT` | `number` |
| `DATETIME`, `TIMESTAMP` | `datetime` |
| `BOOLEAN`, `BOOL` | `boolean` |
| `TEXT`, `CLOB` | `text` |

This mapping ensures visual consistency across diagrams imported from different database engines.

### Relationship and Cardinality Resolution

The normalization stage invokes `utils/getRelationshipFields` to identify **foreign key relationships** and determine cardinality:

- **One-to-many**: Single-column foreign key with unique constraint on referenced column
- **Many-to-many**: Implicit through junction tables (detected via naming conventions and composite keys)

Primary keys, unique constraints, and index definitions are also extracted and attached to the intermediate schema object.

---

## Stage 3: Model Conversion and Diagram Generation

The public API for DDL import is the `importSQL` function exported from [`src/utils/importSQL/index.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/index.js). Its signature is:

```javascript
importSQL(ast, toDb, diagramDb)

```

### Parameters

| Parameter | Purpose |
|-----------|---------|
| `ast` | The parsed abstract syntax tree from the database-specific parser |
| `toDb` | Target database identifier (e.g., `DB.MYSQL`, `DB.POSTGRES`) |
| `diagramDb` | The generic diagram database constant |

### Internal Flow

The function delegates to **database-specific helpers** (`fromMySQL`, `fromPostgres`, `fromSQLite`) which each:

1. Invoke `buildSQLFromAST` to produce the normalized schema
2. Apply any dialect-specific post-processing
3. Call `arrangeTables` from [`src/utils/arrangeTables.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/arrangeTables.js) to compute initial table positions

The output conforms to DrawDB's internal schema defined in `data/db`, ready for canvas rendering.

---

## Practical Implementation Examples

### Importing a MySQL Schema

```javascript
import { importSQL } from "./utils/importSQL";
import { parse } from "node-sql-parser";

const sql = `
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE
);

CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  title VARCHAR(200),
  body TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
`;

const ast = parse(sql);
const diagram = importSQL(ast, DB.MYSQL, DB.GENERIC);

// diagram.tables and diagram.relationships now populated
canvas.loadDiagram(diagram);

```

### Importing a PostgreSQL Schema

```javascript
import { importSQL } from "./utils/importSQL";
import { parse as pgParse } from "pgsql-ast-parser";

const pgDDL = `
CREATE TABLE departments (
  dept_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  emp_id SERIAL PRIMARY KEY,
  dept_id INTEGER REFERENCES departments(dept_id) ON DELETE SET NULL,
  full_name VARCHAR(200),
  salary NUMERIC(10,2),
  hire_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_emp_name ON employees(full_name);
`;

const pgAST = pgParse(pgDDL);
const diagram = importSQL(pgAST, DB.POSTGRES, DB.GENERIC);

```

---

## Key Architectural Files

| File Path | Responsibility |
|-----------|--------------|
| [`src/utils/importSQL/index.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/index.js) | Public API entry point, orchestrates import pipeline |
| [`src/utils/importSQL/shared.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/shared.js) | Core `buildSQLFromAST` normalization logic |
| [`src/utils/importSQL/mysql.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/mysql.js) | MySQL/MariaDB parser integration |
| [`src/utils/importSQL/postgres.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/postgres.js) | PostgreSQL parser integration |
| [`src/utils/importSQL/sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/sqlite.js) | SQLite custom lexer integration |
| [`src/utils/arrangeTables.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/arrangeTables.js) | Auto-layout algorithm for initial diagram positioning |
| [`data/datatypes.js`](https://github.com/drawdb-io/drawdb/blob/main/data/datatypes.js) | Cross-database type mapping definitions |
| [`data/db.js`](https://github.com/drawdb-io/drawdb/blob/main/data/db.js) | Internal diagram schema constants |

---

## Summary

- **Three-stage pipeline**: Parsing → AST normalization → model conversion enables clean separation between database-specific and generic logic
- **Parser library selection**: Proven libraries (`node-sql-parser`, `pgsql-ast-parser`) minimize custom grammar maintenance
- **Shared normalization**: `buildSQLFromAST` in [`src/utils/importSQL/shared.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/shared.js) provides consistent intermediate representation across all dialects
- **Canonical type system**: [`data/datatypes.js`](https://github.com/drawdb-io/drawdb/blob/main/data/datatypes.js) ensures visual uniformity regardless of source database
- **Extensible architecture**: Adding new SQL dialects requires only a thin parser wrapper compatible with the shared builder interface

---

## Frequently Asked Questions

### How does DrawDB handle database-specific SQL extensions?

DrawDB delegates to specialized parser libraries that understand dialect extensions. MySQL's `AUTO_INCREMENT`, PostgreSQL's `SERIAL` pseudo-types, and SQLite's `WITHOUT ROWID` tables are all captured by their respective parsers and normalized during the AST-to-intermediate conversion.

### What happens when a SQL type has no DrawDB equivalent?

According to [`data/datatypes.js`](https://github.com/drawdb-io/drawdb/blob/main/data/datatypes.js), unmapped types fall back to a generic `unknown` category. The diagram renders these with default styling, and users can manually adjust the type in the visual editor after import.

### Can the import pipeline be used server-side or only in the browser?

The architecture is designed for browser execution—all dependencies (`node-sql-parser`, `pgsql-ast-parser`) are Browserify-compatible JavaScript modules. Server-side usage would require Node.js compatibility verification for the specific parser versions locked in [`package.json`](https://github.com/drawdb-io/drawdb/blob/main/package.json).

### How does DrawDB preserve referential integrity constraints in the diagram?

Foreign key relationships are extracted during `buildSQLFromAST` via `utils/getRelationshipFields`. The function analyzes column constraints and index definitions to determine cardinality, then stores relationship metadata in the diagram model's `relationships` array with source/target table references.