Online SQL-to-ER Diagram Tool: Features, Usage, and Implementation Details

The author shares a personal story about struggling with manual ER diagram drawing for a school database assignment and presents an online tool that can automatically convert SQL statements into professional ER diagrams.

The tool addresses the pain point of time‑consuming hand‑drawn ER diagrams by parsing SQL, recognizing table structures and relationships, and generating clear, editable diagrams, helping both beginners and experienced students complete assignments efficiently.

Key advantages:

Instant ER diagram generation from pasted SQL.

Accurate identification of tables, fields, constraints, and relationships.

Easy to understand and modify the resulting diagram.

Simplifies completion of database coursework.

Usage steps:

Copy your SQL into the input box.

Select the "SQL to ER" mode.

Click the generate button to obtain the diagram.

Example SQL used for demonstration:

-- 学生信息表
CREATE TABLE `students` (
    `id` INT AUTO_INCREMENT COMMENT '学生ID',
    `name` VARCHAR(50) NOT NULL COMMENT '姓名',
    `gender` ENUM ('男','女') NOT NULL COMMENT '性别',
    `birth_date` DATE COMMENT '出生日期',
    `email` VARCHAR(100) COMMENT '邮箱',
    `phone` VARCHAR(20) COMMENT '电话',
    `address` VARCHAR(255) COMMENT '家庭地址',
    `enrollment_date` DATE COMMENT '入学日期',
    `status` ENUM ('在校','毕业','退学') DEFAULT '在校' COMMENT '状态',
    PRIMARY KEY (`id`)
) COMMENT ='学生';

-- 课程信息表
CREATE TABLE `courses` (
    `id` INT AUTO_INCREMENT COMMENT '课程ID',
    `name` VARCHAR(100) NOT NULL COMMENT '课程名称',
    `description` TEXT COMMENT '课程描述',
    `credit` INT NOT NULL COMMENT '学分',
    `teacher` VARCHAR(50) COMMENT '授课教师',
    PRIMARY KEY (`id`)
) COMMENT ='课程';

-- 学生选课记录表
CREATE TABLE `student_courses` (
    `id` INT AUTO_INCREMENT COMMENT '选课记录ID',
    `student_id` INT NOT NULL COMMENT '学生ID',
    `course_id` INT NOT NULL COMMENT '课程ID',
    `enroll_date` DATE COMMENT '选课日期',
    `grade` DECIMAL(5,2) COMMENT '成绩',
    PRIMARY KEY (`id`),
    FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) COMMENT ='选课记录';

After entering the SQL, the tool instantly parses the statements and renders the corresponding ER diagram.

The implementation relies on a series of backend techniques:

String cleanSql = sql
        .replace("\r\n\t", " ")
        // Remove single‑line comments starting with "--"
        .replaceAll("--.*?(\r\n|\n)", "")
        // Remove single‑line comments starting with "#"
        .replaceAll("#.*?(\r\n|\n)", "")
        // Remove block comments "/*...*/"
        .replaceAll("/\*.*?\*/", "");

The cleaned SQL is then filtered for risky keywords and parsed using SQLUtils.parseStatements from the Druid library, producing an abstract syntax tree (AST) that the tool traverses to extract table names, comments, and column definitions.

Key Java snippets for extracting table and column information:

for (SQLStatement sqlStatement : sqlStatements) {
    if (sqlStatement instanceof MySqlCreateTableStatement) {
        MySqlCreateTableStatement createTableStatement = (MySqlCreateTableStatement) sqlStatement;
        String tableName = createTableStatement.getTableName().replaceAll("`", "");
        System.out.println("Table Name: " + tableName);
        for (SQLTableElement element : createTableStatement.getTableElementList()) {
            if (element instanceof SQLColumnDefinition) {
                SQLColumnDefinition columnDefinition = (SQLColumnDefinition) element;
                String columnName = columnDefinition.getColumnName().replaceAll("`", "");
                System.out.println("    Column Name: " + columnName);
            }
        }
    }
}

The tool’s architecture follows several classic design patterns:

Strategy Pattern : Defines ErConversionStrategy interface with implementations such as SqlErConversionStrategy and AiErConversionStrategy for different conversion methods.

Factory Pattern : ErConversionStrategyFactory registers and provides strategies based on a business type identifier.

Dependency Injection : Spring annotations (@Component, @Resource) inject required services and beans, decoupling components.

Singleton Pattern : The factory is a singleton Spring bean, ensuring a single point of strategy management.

Overall workflow:

User submits a request with SQL and a business type to ErController .

The controller obtains the appropriate strategy from ErConversionStrategyFactory .

The selected strategy parses the SQL and generates ER diagram data.

The result is wrapped in a BaseResponse and returned to the client.

In summary, the article provides a practical guide to using the online SQL‑to‑ER tool, demonstrates the underlying Java parsing logic, and explains the modular, pattern‑driven design that makes the system extensible and maintainable.