MySQL Database Schema Reader

Introduction

The MySQL Schema Reader is a specialized tool for reading MySQL database table structure information. It can automatically scan all tables in a database and extract complete structural information, including column definitions, indexes, constraints, foreign key relationships, and more, returning data in a structured array format for subsequent processing and analysis.

Core Features

🔍 Complete Table Structure Analysis

• Read all table column information (name, type, length, default value, etc.)
• Identify primary keys, foreign keys, and unique keys
• Retrieve index information and constraint details
• Support reading view and trigger information

🎯 Flexible Filtering Options

• Specify specific tables to read
• Exclude unwanted system or temporary tables
• Optionally include or exclude views
• Read index and constraint information on demand

⚡ High-Performance Design

• Built-in connection pool management
• Batch query optimization
• Support for concurrent reading
• Intelligent caching mechanism

Configuration Parameters

Database Connection Configuration

Parameter	Type	Default	Description
host	string	'localhost'	MySQL server address
port	number	3306	MySQL server port
user	string	'root'	Database username
password	string	''	Database password
database	string	''	Target database name

All connection fields have defaults (host=localhost, port=3306, user=root, password="", database="") and can be overridden via inputs.config. The operator merges inputs.config over these defaults, so none of these fields are strictly required.

Read Options Configuration

note

These read options and connection-pool parameters are handled by the worker implementation and are not part of the operator's registry configuration schema (which only defines host, port, user, password, database). Behavior may vary depending on the worker.

Parameter	Type	Default	Description
includeTables	array	[]	List of specific table names to read; empty array means read all tables
excludeTables	array	[]	List of table names to exclude
includeViews	boolean	false	Whether to include database views
includeIndexes	boolean	true	Whether to read index information
includeConstraints	boolean	true	Whether to read constraint information
includeTriggersInfo	boolean	false	Whether to read trigger information

Connection Pool Configuration

Parameter	Type	Default	Description
connectionLimit	number	10	Maximum number of connections in the pool
acquireTimeout	number	60000	Timeout for acquiring a connection (milliseconds)
timeout	number	60000	Query timeout (milliseconds)

Usage

Basic Usage

The simplest usage — read all table structures from a specified database:

{
  "id": "read-schema",
  "type": "mysql-schema",
  "config": {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "your_password",
    "database": "your_database"
  }
}

Read Specific Tables

Read only specific tables:

{
  "id": "read-user-tables",
  "type": "mysql-schema",
  "config": {
    "host": "localhost",
    "user": "root",
    "password": "your_password",
    "database": "your_database",
    "includeTables": ["users", "user_profiles", "user_permissions"]
  }
}

Exclude System Tables

Exclude log and temporary tables:

{
  "id": "read-business-tables",
  "type": "mysql-schema",
  "config": {
    "host": "localhost",
    "user": "root",
    "password": "your_password",
    "database": "your_database",
    "excludeTables": ["logs", "temp_data", "cache_table"]
  }
}

Complete Configuration Example

A complete configuration with all available options:

{
  "id": "comprehensive-schema-read",
  "type": "mysql-schema",
  "config": {
    "host": "192.168.1.100",
    "port": 3306,
    "user": "schema_reader",
    "password": "secure_password",
    "database": "production_db",
    "includeTables": ["users", "orders", "products", "categories"],
    "excludeTables": ["temp_logs", "session_data"],
    "includeViews": true,
    "includeIndexes": true,
    "includeConstraints": true,
    "includeTriggersInfo": false,
    "connectionLimit": 5,
    "acquireTimeout": 30000,
    "timeout": 30000
  }
}

Output Format

Output key

MySQL returns the tables array under the key schema. In workflows, bind it as {{node.schema}} (for example {{read-schema.schema}}). Note that the PostgreSQL Schema Reader uses tables instead — be sure to bind the correct key for each operator.

Success Response Structure

{
  "success": true,
  "database": "your_database",
  "host": "localhost",
  "schema": [
    {
      "tableName": "users",
      "tableType": "BASE TABLE",
      "engine": "InnoDB",
      "tableComment": "User information table",
      "columns": [
        {
          "columnName": "id",
          "dataType": "int",
          "columnType": "int(11)",
          "isNullable": false,
          "defaultValue": null,
          "isAutoIncrement": true,
          "comment": "User ID",
          "maxLength": null,
          "precision": 10,
          "scale": 0,
          "position": 1
        },
        {
          "columnName": "username",
          "dataType": "varchar",
          "columnType": "varchar(50)",
          "isNullable": false,
          "defaultValue": null,
          "isAutoIncrement": false,
          "comment": "Username",
          "maxLength": 50,
          "precision": null,
          "scale": null,
          "position": 2
        }
      ],
      "primaryKeys": ["id"],
      "foreignKeys": [
        {
          "constraintName": "FK_user_role",
          "columnName": "role_id",
          "referencedTable": "roles",
          "referencedColumn": "id"
        }
      ],
      "indexes": [
        {
          "indexName": "PRIMARY",
          "columns": [
            {
              "columnName": "id",
              "position": 1
            }
          ],
          "isUnique": true,
          "indexType": "BTREE"
        },
        {
          "indexName": "idx_username",
          "columns": [
            {
              "columnName": "username",
              "position": 1
            }
          ],
          "isUnique": true,
          "indexType": "BTREE"
        }
      ],
      "constraints": [
        {
          "constraintName": "PRIMARY",
          "constraintType": "PRIMARY KEY"
        }
      ],
      "triggers": []
    }
  ],
  "metadata": {
    "totalTables": 1,
    "readAt": "2024-01-15T10:30:00.000Z",
    "executionTime": 1250,
    "config": {
      "includeViews": false,
      "includeIndexes": true,
      "includeConstraints": true,
      "includeTriggersInfo": false
    }
  }
}

Field Descriptions

Table Info

• tableName: Table name
• tableType: Table type (BASE TABLE, VIEW, etc.)
• engine: Storage engine (InnoDB, MyISAM, etc.)
• tableComment: Table comment

Column Info

• columnName: Column name
• dataType: Data type (int, varchar, etc.)
• columnType: Full column type definition
• isNullable: Whether NULL values are allowed
• defaultValue: Default value
• isAutoIncrement: Whether auto-increment is enabled
• comment: Column comment
• maxLength: Maximum length (for string types)
• precision: Numeric precision
• scale: Numeric scale
• position: Column position in the table

Index Info

• indexName: Index name
• columns: Columns included in the index
• isUnique: Whether it is a unique index
• indexType: Index type (BTREE, HASH, etc.)

Foreign Key Info

• constraintName: Constraint name
• columnName: Foreign key column name
• referencedTable: Referenced table
• referencedColumn: Referenced column

Error Response Structure

{
  "success": false,
  "error": "Failed to connect to database: Access denied for user 'root'@'localhost'",
  "database": "your_database",
  "host": "localhost",
  "readAt": "2024-01-15T10:30:00.000Z",
  "executionTime": 1000
}

Use Cases

1. Database Documentation Generation

Automatically generate database documentation and data dictionaries:

{
  "workflow": [
    {
      "id": "read-schema",
      "type": "mysql-schema",
      "config": {
        "host": "localhost",
        "user": "root",
        "password": "password",
        "database": "production_db",
        "includeIndexes": true,
        "includeConstraints": true
      }
    },
    {
      "id": "generate-docs",
      "type": "js-executor",
      "inputs": {
        "schema": "{{read-schema.schema}}",
        "code": `
          let markdown = '# Database Structure Documentation\\n\\n';
          inputs.schema.forEach(table => {
            markdown += \`## \${table.tableName} - \${table.tableComment || 'No comment'}\\n\\n\`;
            markdown += '| Column | Type | Required | Default | Comment |\\n';
            markdown += '|--------|------|----------|---------|---------|\\n';
            table.columns.forEach(col => {
              markdown += \`| \${col.columnName} | \${col.columnType} | \${col.isNullable ? 'No' : 'Yes'} | \${col.defaultValue || '-'} | \${col.comment || '-'} |\\n\`;
            });
            markdown += '\\n';
          });
          return { markdown };
        `
      }
    }
  ]
}

2. Database Migration and Synchronization

Compare database structure differences across environments:

{
  "workflow": [
    {
      "id": "read-prod-schema",
      "type": "mysql-schema",
      "config": {
        "host": "prod-db.example.com",
        "user": "readonly",
        "password": "{{prod_password}}",
        "database": "production"
      }
    },
    {
      "id": "read-dev-schema",
      "type": "mysql-schema",
      "config": {
        "host": "dev-db.example.com",
        "user": "dev",
        "password": "{{dev_password}}",
        "database": "development"
      }
    },
    {
      "id": "compare-schemas",
      "type": "js-executor",
      "inputs": {
        "prodSchema": "{{read-prod-schema.schema}}",
        "devSchema": "{{read-dev-schema.schema}}",
        "code": `
          const prodTables = new Set(inputs.prodSchema.map(t => t.tableName));
          const devTables = new Set(inputs.devSchema.map(t => t.tableName));
          
          return {
            missingInDev: [...prodTables].filter(t => !devTables.has(t)),
            missingInProd: [...devTables].filter(t => !prodTables.has(t)),
            commonTables: [...prodTables].filter(t => devTables.has(t))
          };
        `
      }
    }
  ]
}

3. Code Auto-Generation

Generate model classes or API code based on database structure:

{
  "workflow": [
    {
      "id": "read-schema",
      "type": "mysql-schema",
      "config": {
        "host": "localhost",
        "user": "root",
        "password": "password",
        "database": "app_db",
        "includeTables": ["users", "orders", "products"]
      }
    },
    {
      "id": "generate-models",
      "type": "js-executor",
      "inputs": {
        "schema": "{{read-schema.schema}}",
        "code": `
          let models = {};
          inputs.schema.forEach(table => {
            const className = table.tableName.charAt(0).toUpperCase() + table.tableName.slice(1);
            let classCode = \`class \${className} {\\n\`;
            
            table.columns.forEach(col => {
              const jsType = col.dataType.includes('int') ? 'number' : 
                           col.dataType.includes('varchar') ? 'string' : 'any';
              classCode += \`  \${col.columnName}: \${jsType}; // \${col.comment || ''}\\n\`;
            });
            
            classCode += '}\\n';
            models[table.tableName] = classCode;
          });
          
          return { models };
        `
      }
    }
  ]
}

4. Database Health Check

Check database design standards and best practices:

{
  "workflow": [
    {
      "id": "read-schema",
      "type": "mysql-schema",
      "config": {
        "host": "localhost",
        "user": "root",
        "password": "password",
        "database": "app_db",
        "includeIndexes": true
      }
    },
    {
      "id": "health-check",
      "type": "js-executor",
      "inputs": {
        "schema": "{{read-schema.schema}}",
        "code": `
          let issues = [];
          
          inputs.schema.forEach(table => {
            // Check for primary key
            if (table.primaryKeys.length === 0) {
              issues.push(\`Table \${table.tableName} has no primary key\`);
            }
            
            // Check for table comment
            if (!table.tableComment) {
              issues.push(\`Table \${table.tableName} has no comment\`);
            }
            
            // Check column comments
            table.columns.forEach(col => {
              if (!col.comment && col.columnName !== 'id') {
                issues.push(\`Column \${col.columnName} in table \${table.tableName} has no comment\`);
              }
            });
            
            // Check indexes
            const nonPrimaryIndexes = table.indexes.filter(idx => idx.indexName !== 'PRIMARY');
            if (nonPrimaryIndexes.length === 0 && table.columns.length > 3) {
              issues.push(\`Table \${table.tableName} may need additional indexes\`);
            }
          });
          
          return { issues, totalIssues: issues.length };
        `
      }
    }
  ]
}

Security Configuration

Database User Permissions

For security purposes, it is recommended to create a dedicated read-only user:

-- Create a dedicated schema reader user
CREATE USER 'schema_reader'@'%' IDENTIFIED BY 'secure_password';

-- Grant necessary permissions
GRANT SELECT ON information_schema.* TO 'schema_reader'@'%';
GRANT SHOW VIEW ON your_database.* TO 'schema_reader'@'%';

-- Apply permission changes
FLUSH PRIVILEGES;

Connection Security

SSL connections are recommended for production environments:

{
  "config": {
    "host": "secure-db.example.com",
    "port": 3306,
    "user": "schema_reader",
    "password": "{{DB_PASSWORD}}",
    "database": "production",
    "ssl": {
      "rejectUnauthorized": true
    }
  }
}

Performance Optimization

1. Connection Pool Configuration

Adjust connection pool parameters based on concurrency requirements:

{
  "config": {
    "connectionLimit": 5,
    "acquireTimeout": 30000,
    "timeout": 30000
  }
}

2. Selective Reading

Read only the necessary information to improve performance:

{
  "config": {
    "includeTables": ["users", "orders"],
    "includeViews": false,
    "includeTriggersInfo": false
  }
}

3. Batch Processing

For large databases, consider reading in batches:

{
  "workflow": [
    {
      "id": "read-user-tables",
      "type": "mysql-schema",
      "config": {
        "includeTables": ["users", "user_profiles", "user_sessions"]
      }
    },
    {
      "id": "read-order-tables",
      "type": "mysql-schema",
      "config": {
        "includeTables": ["orders", "order_items", "payments"]
      }
    }
  ]
}

Troubleshooting

Common Errors and Solutions

Error Message	Possible Cause	Solution
Access denied for user	Incorrect username or password	Verify database credentials
Unknown database	Database does not exist	Confirm the database name is correct
Connection timeout	Network connectivity issue	Check network connection and firewall settings
Table doesn't exist	Specified table does not exist	Verify table names in includeTables
Too many connections	Connection limit exceeded	Reduce connectionLimit or increase MySQL max_connections

Debugging Tips

1. Test Connection:

   mysql -h localhost -u root -p your_database

2. Check Permissions:

   SHOW GRANTS FOR 'your_user'@'%';

Best Practices

1. Environment Variable Management

Use environment variables to store sensitive information:

{
  "config": {
    "host": "{{DB_HOST}}",
    "user": "{{DB_USER}}",
    "password": "{{DB_PASSWORD}}",
    "database": "{{DB_NAME}}"
  }
}

2. Error Handling

Add error handling in workflows:

{
  "workflow": [
    {
      "id": "read-schema",
      "type": "mysql-schema",
      "config": {
        "host": "localhost",
        "user": "root",
        "password": "password",
        "database": "app_db"
      },
      "onError": {
        "continue": true,
        "defaultValue": {
          "success": false,
          "schema": [],
          "error": "Failed to read schema"
        }
      }
    }
  ]
}

3. Result Caching

For large databases, consider caching results:

{
  "workflow": [
    {
      "id": "read-schema",
      "type": "mysql-schema",
      "config": {
        "host": "localhost",
        "user": "root",
        "password": "password",
        "database": "app_db"
      },
      "cache": {
        "ttl": 3600,
        "key": "mysql-schema-{{database}}"
      }
    }
  ]
}

Related Resources

• MySQL Information Schema Documentation
• Creating Custom Operators
• Workflow Engine Overview