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

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

The 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 and 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 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.

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

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

// 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 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 and dialect-specific affinity logic.
• The arrangeTables utility in 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, mysql.js, or 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 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.

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