OneSea ERP Data MCP Server

A Model Context Protocol (MCP) server for OneSea ERP data MySQL database integration with comprehensive querying capabilities.

Features

• Database Connection: Secure SSL connection to OneSea ERP data MySQL RDS instance
• Query Execution: Execute custom SQL queries with safety limits
• Schema Exploration: List databases, tables, and describe table structures
• Performance Analysis: Query execution plans with EXPLAIN
• Resource Access: Browse databases and connection info through MCP resources
• Prompt Templates: Built-in prompts for SQL query generation and database exploration
• TypeScript: Full TypeScript support with proper type definitions
• Logging: Comprehensive logging with Winston
• Error Handling: Robust error handling and connection management

Installation

Global Installation

npm install -g onesea-erp-data_mcp_server

Local Installation

npm install onesea-erp-data_mcp_server

Using npx (Recommended)

npx onesea-erp-data_mcp_server

Configuration

Environment Variables

Create a .env file in your project root:

DB_HOST=synergy-mysql.cbewyuuwoz0p.ap-south-1.rds.amazonaws.com
DB_PORT=3306
DB_USER=safenet
DB_PASSWORD=your_password_here
DB_NAME=optional_default_database
LOG_LEVEL=info
MAX_QUERY_RESULTS=1000
QUERY_TIMEOUT=30000

MCP Client Configuration

Add to your MCP client configuration (e.g., Siya):

{
  "mcpServers": {
    "onesea-erp-data": {
      "command": "npx",
      "args": ["-y", "@siya-onesea/onesea-erp-data_mcp_server@latest"],
      "description": "OneSea ERP data MySQL database integration",
      "capabilities": [
        "Execute custom SQL queries",
        "List databases and tables",
        "Describe table structures",
        "Query performance analysis",
        "Database schema exploration"
      ],
      "env": {},
      "category": "database"
    }
  }
}

Available Tools

execute_query

Execute SQL queries on the OneSea ERP data MySQL database.

Parameters:

• query (required): SQL query to execute
• database (optional): Database name to use
• limit (optional): Maximum rows to return (default: 1000)

Example:

{
  "query": "SELECT * FROM users WHERE active = 1",
  "database": "onesea_prod",
  "limit": 50
}

list_databases

List all databases available on the MySQL server.

Example Response:

{
  "databases": ["onesea_prod", "onesea_staging", "information_schema"],
  "count": 3
}

list_tables

List all tables in a specific database.

Parameters:

• database (required): Database name
• pattern (optional): SQL LIKE pattern to filter tables

Example:

{
  "database": "onesea_prod",
  "pattern": "user_%"
}

describe_table

Get the structure and column information of a specific table.

Parameters:

• database (required): Database name
• table (required): Table name

Example:

{
  "database": "onesea_prod",
  "table": "users"
}

table_info

Get detailed information about tables including row counts and sizes.

Parameters:

• database (required): Database name
• table (optional): Specific table name

database_stats

Get statistics and information about a specific database.

Parameters:

• database (required): Database name

explain_query

Get the execution plan for a SQL query using EXPLAIN.

Parameters:

• query (required): SQL query to explain
• database (required): Database name

Resources

onesea-erp://databases

List of all available databases with metadata.

onesea-erp://connection-info

Current database connection details and configuration.

Prompts

sql_query_helper

Interactive prompt to help generate SQL queries.

Arguments:

• database: Target database name
• table: Target table name (optional)
• operation: Type of operation (SELECT, INSERT, UPDATE, DELETE)
• description: Description of what you want to achieve

database_exploration

Guide for systematically exploring the database structure.

Arguments:

• database: Database to explore (optional)

Usage Examples

Basic Query Execution

# Using MCP client
mcp call execute_query '{"query": "SHOW TABLES", "database": "onesea_prod"}'

Database Exploration

# List all databases
mcp call list_databases '{}'

# List tables in a database
mcp call list_tables '{"database": "onesea_prod"}'

# Describe a table structure
mcp call describe_table '{"database": "onesea_prod", "table": "vessels"}'

Performance Analysis

# Explain a query
mcp call explain_query '{
  "query": "SELECT * FROM vessels WHERE status = \"active\"",
  "database": "onesea_prod"
}'

Security Features

• SSL Connection: Secure connection to RDS instance
• Query Limits: Automatic LIMIT clause addition to prevent large result sets
• Input Validation: SQL injection protection through parameterized queries
• Connection Pooling: Efficient connection management
• Error Sanitization: Sensitive information filtered from error messages

Development

Building from Source

git clone https://github.com/syia-ai/mcp-onesea-erp-data.git
cd mcp-onesea-erp-data
npm install
npm run build

Running in Development Mode

npm run dev

Running Tests

npm test

Troubleshooting

Connection Issues

1. Verify database credentials in .env file
2. Check network connectivity to RDS instance
3. Ensure SSL certificates are properly configured
4. Verify user permissions on MySQL server

Query Timeouts

• Increase QUERY_TIMEOUT environment variable
• Optimize queries for better performance
• Use LIMIT clauses for large result sets

Memory Issues

• Reduce MAX_QUERY_RESULTS limit
• Use pagination for large datasets
• Monitor query complexity

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests for new functionality
5. Submit a pull request

License

MIT License - see LICENSE file for details.

Support

For issues and questions:

• GitHub Issues: https://github.com/syia-ai/mcp-onesea-erp-data/issues
• Documentation: https://docs.syia.ai/mcp/onesea-erp-data