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

Read Options Configuration

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

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