# How DrawDB Parses Existing SQL Database Schemas: A Deep Dive into the AST Pipeline

> Discover how DrawDB parses SQL database schemas. Learn about its AST pipeline, transforming SQL DDL into interactive diagram models via dialect-specific visitors.

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

---

**DrawDB converts raw SQL DDL statements into interactive diagram models using a three-step pipeline that transforms SQL into an abstract syntax tree (AST), then maps that AST to internal diagram objects using dialect-specific visitors.**

The open-source diagramming tool drawdb-io/drawdb ingests existing database schemas and renders them as editable entity-relationship diagrams. Understanding how it parses existing SQL database schemas reveals a robust architecture that normalizes diverse SQL dialects into a unified internal representation.

## The Three-Step SQL Parsing Pipeline

The conversion process follows a strict **SQL → AST → Diagram** transformation chain. This architecture isolates parsing concerns from diagram logic, allowing the application to support multiple database engines through a single internal model.

### Step 1: SQL to AST Conversion

The pipeline begins by feeding raw SQL text into third-party parsers. Depending on the target dialect, DrawDB leverages libraries like `@sqlite/sqlite-parser` for SQLite or `node-sql-parser` for MySQL. These engines emit a language-agnostic **abstract syntax tree** (AST) that describes tables, columns, constraints, and indexes as hierarchical node objects.

### Step 2: AST to Diagram Model Transformation

The core conversion logic resides in [`src/utils/importSQL/index.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/index.js). The central dispatcher function `importSQL(ast, toDb, diagramDb)` selects the appropriate dialect-specific visitor—`fromSQLite`, `fromMySQL`, or `fromPostgres`—based on the `toDb` argument. Each visitor walks the AST node-by-node, constructing plain JavaScript objects that match DrawDB’s diagram schema.

The resulting diagram object contains:

- **Tables** – Objects with `name`, `id`, `fields`, `indices`, `uniqueConstraints`, `comment`, and `color` properties.
- **Fields** – Each column receives a fresh `nanoid()` identifier, a normalized `type` (resolved via the `dbToTypes` lookup in [`src/data/datatypes.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/datatypes.js) and dialect-specific type-affinity maps), plus attributes for `size`, `default`, `notNull`, `primary`, `unique`, `increment`, `check`, and optional `values` for enums.
- **Relationships** – Foreign-key definitions become relationship objects containing `name`, `startTableId`, `endTableId`, `fields` (pairs of start/end field ids), `cardinality` (either **ONE_TO_ONE** or **MANY_TO_ONE** based on the source column’s uniqueness), and constraint rules for updates and deletes.
- **Indexes and Constraints** – Added to the owning table’s `indices` or `uniqueConstraints` arrays with generated identifiers.

### Step 3: Automatic Layout Arrangement

After assembling the diagram object, `arrangeTables(diagram)` (located in [`src/utils/arrangeTables.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/arrangeTables.js)) computes the initial spatial placement for tables on the canvas, ensuring the imported schema is immediately navigable without manual positioning.

## Dialect-Specific AST Visitors in `src/utils/importSQL`

Each supported database engine implements its own visitor to handle dialect-specific syntax while conforming to the same output contract.

### SQLite Schema Parsing ([`sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/sqlite.js))

The [`src/utils/importSQL/sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/sqlite.js) visitor handles SQLite-specific AST structures. It translates SQLite type affinities into standardized diagram types and manages `AUTOINCREMENT` modifiers. The visitor also extracts inline constraints and table-level foreign key definitions, converting them into relationship objects with proper cardinality detection.

### MySQL and PostgreSQL Support

The [`src/utils/importSQL/mysql.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/mysql.js) and [`src/utils/importSQL/postgres.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/postgres.js) visitors mirror the SQLite implementation but account for dialect differences. MySQL handling includes specific logic for `AUTO_INCREMENT` columns and MySQL-specific data type widths, while the PostgreSQL visitor manages that engine's extended type system and constraint syntax.

### Shared Utilities for SQL Reconstruction

The [`src/utils/importSQL/shared.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/shared.js) file provides helper functions like `buildSQLFromAST`, which converts sub-AST fragments (such as CHECK expressions) back into SQL strings. This ensures that complex constraints preserved during import can be accurately exported later without losing semantic meaning.

## Mapping AST Nodes to Diagram Objects

The visitors perform deep transformation of AST semantics into diagram primitives defined in [`src/data/constants.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/constants.js).

### Table Structure and Metadata

Each `CREATE TABLE` statement generates a table object with a stable `id` and optional metadata like comments. The visitors extract table-level constraints and partition them into appropriate arrays (`uniqueConstraints`, `indices`) for the diagram model.

### Field Definitions and Type Affinity

Type normalization occurs through the `dbToTypes` map in [`src/data/datatypes.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/datatypes.js). Each visitor implements **type affinity** logic (visible in the `affinity` mappings within the SQLite and MySQL visitors) to ensure that `VARCHAR(255)` in MySQL and `TEXT` in SQLite both resolve to appropriate internal type representations while preserving original size constraints.

### Foreign Keys and Cardinality Detection

When processing `FOREIGN KEY` constraints or inline references, the visitors analyze the source column’s uniqueness constraints to determine cardinality. If the source column holds a unique constraint, the relationship is marked as **ONE_TO_ONE**; otherwise, it defaults to **MANY_TO_ONE**. The `updateConstraint` and `deleteConstraint` properties (e.g., `CASCADE`, `SET NULL`) are extracted directly from the AST reference options.

## Practical Implementation Examples

The following examples demonstrate the complete parsing flow for SQLite and MySQL schemas.

```javascript
// Parse a SQLite schema string into a diagram
import { parse } from '@sqlite/sqlite-parser'
import { importSQL } from './utils/importSQL'
import { DB } from './data/constants'

const sql = `
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    author_id INTEGER,
    title TEXT,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
  );
`;

// 1️⃣ Convert SQL → AST
const ast = parse(sql)

// 2️⃣ Convert AST → DrawDB diagram
const diagram = importSQL(ast, DB.SQLITE, DB.GENERIC)

console.log(diagram.tables.map(t => t.name))       // ['users', 'posts']
console.log(diagram.relationships[0].cardinality) // 'MANY_TO_ONE'

```

```javascript
// Parse a MySQL schema
import { Parser } from 'node-sql-parser'
import { importSQL } from './utils/importSQL'
import { DB } from './data/constants'

const parser = new Parser()
const mysqlSql = `CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY, 
  name VARCHAR(100)
);`

const ast = parser.astify(mysqlSql)
const diagram = importSQL(ast, DB.MYSQL, DB.GENERIC)

```

Both implementations follow the identical **SQL → AST → `importSQL`** contract, producing standardized diagram objects regardless of source dialect.

## Summary

- DrawDB parses existing SQL database schemas using a three-stage pipeline that separates parsing from model generation.
- The `importSQL` function in [`src/utils/importSQL/index.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/index.js) dispatches to dialect-specific visitors (`fromSQLite`, `fromMySQL`, `fromPostgres`) that walk third-party ASTs.
- Visitors generate normalized diagram objects containing tables, fields with `nanoid()` identifiers, and relationships with calculated cardinality (**ONE_TO_ONE** or **MANY_TO_ONE**).
- Type normalization occurs through `dbToTypes` mappings in [`src/data/datatypes.js`](https://github.com/drawdb-io/drawdb/blob/main/src/data/datatypes.js) and dialect-specific affinity logic.
- The `arrangeTables` utility in [`src/utils/arrangeTables.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/arrangeTables.js) handles automatic spatial layout of imported schemas.

## Frequently Asked Questions

### What parser does DrawDB use for SQL schema importation?

DrawDB relies on external parser libraries rather than building its own. It uses `@sqlite/sqlite-parser` for SQLite schemas and `node-sql-parser` for MySQL and PostgreSQL. These libraries handle the lexical analysis and syntactic parsing, emitting standardized ASTs that DrawDB's internal visitors consume.

### How does DrawDB handle different SQL dialects when parsing schemas?

The `importSQL` function selects dialect-specific visitors based on the `toDb` parameter. Each visitor—located in [`src/utils/importSQL/sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/sqlite.js), [`mysql.js`](https://github.com/drawdb-io/drawdb/blob/main/mysql.js), or [`postgres.js`](https://github.com/drawdb-io/drawdb/blob/main/postgres.js)—understands its respective AST structure and type system, normalizing dialect-specific features (like MySQL's `AUTO_INCREMENT` versus SQLite's `AUTOINCREMENT`) into a generic diagram model.

### What determines the cardinality of relationships in the imported diagram?

Cardinality is determined by analyzing the uniqueness constraints on the source column of a foreign key. If the source column has a `UNIQUE` or `PRIMARY KEY` constraint (and is not nullable), the relationship is classified as **ONE_TO_ONE**; otherwise, it defaults to **MANY_TO_ONE**. This logic is implemented within the dialect visitors as they process `FOREIGN KEY` constraints.

### Can DrawDB preserve complex CHECK constraints during import?

Yes. The `buildSQLFromAST` helper in [`src/utils/importSQL/shared.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/shared.js) reconstructs SQL strings from AST sub-nodes. When visitors encounter CHECK constraints, they use this utility to store the original SQL expression in the field's `check` property, ensuring the constraint remains available for export even though it is not visually rendered in the diagram canvas.