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

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) 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 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) 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. 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. 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. Its signature is:

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

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

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	Public API entry point, orchestrates import pipeline
src/utils/importSQL/shared.js	Core buildSQLFromAST normalization logic
src/utils/importSQL/mysql.js	MySQL/MariaDB parser integration
src/utils/importSQL/postgres.js	PostgreSQL parser integration
src/utils/importSQL/sqlite.js	SQLite custom lexer integration
src/utils/arrangeTables.js	Auto-layout algorithm for initial diagram positioning
data/datatypes.js	Cross-database type mapping definitions
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 provides consistent intermediate representation across all dialects
• Canonical type system: 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, 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.

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.

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