Collect ideas with ShardingSphere and MCP

Hello Community,

Currently, LLMs are experiencing a surge in popularity.
Apache ShardingSphere as a database middleware, can be considered as the entry point for large models to access databases in the AI era.
The way databases are accessed is also shifting from human/application access to AI access.

MCP has become the standard for connecting LLM.
Regarding how to develop new features of Apache ShardingSphere and LLM through MCP, any ideas are welcome to be discussed here.

Some ideas:

Manage metadata: provide chat to Analyze data relationship feature with all databases managed by ShardingSphere
Manage data: provide chat to SQL feature with all databases managed by ShardingSphere
Manage query result report: provide chat to report feature with all databases managed by ShardingSphere
Manage features: chat to sharding, encrypt, mask etc...

similar to mysql-mcp-server that supports sharding tables .
When we use natural language to access sharding table data, can work correctly

Would using MCP for operations that require real-time responses (like executing SQL statements) have a noticeable impact on performance?
Also, maybe there could be a feature in ShardingSphere-Proxy where developers submit database config files or configuration prompts, and then the proxy service uses MCP to generate DistSQL — either sending it back to the client or applying it directly within the proxy itself.

mcp用于需要即时响应的操作（如sql语句执行等），会不会比较影响性能？
或许能有个关于shardingsphere-proxy的功能。大概是开发人员将数据库的配置文件或者配置描述prompt提交，proxy服务调用mcp生成为distsql返回给客户端，或者直接作用于proxy服务。

It looks like we need something similar with a copilot to help people to communicate the shardingsphere-proxy or applications by using the natural languages, right? I 'm not sure not if there is anything about management with shardingsphere-proxy or we alway use the DistSQL like the regular SQL to do management? If that is the case, I think thte commone sql-mcp-server or jdbc-mcp-server is enough. We need to focus to generate the accurate result through the LLM by leveraging on the prompts or RAG things.

There doesn't seem to be a way, similar to testcontainers-java, to test third-party MCP servers like https://hub.docker.com/mcp/server/database-server/overview in the shardingsphere CI file.
To use GPUs in Github Actions, we must use the Larger runners. ASF uses the Standard GitHub-hosted runners, which means LLM models can only be tested using the CPU by launching them in the Github Actions runner. Github Actions' device performance doesn't seem to allow for simultaneous compilation of Java projects and running LLM models. Not sure if this problem will improve over time.
Considering the need to use https://github.com/modelcontextprotocol/java-sdk, ShardingSphere JDBC must at least change the runtime requirement from JDK 8 to JDK 17.
Update in 2025.12.24: Or considering that issue Integrated AMD GPU support ollama/ollama#2637 was resolved today, perhaps we can test the performance of the igpu used in the CPU of GitHub Actions...

I'm wondering if MCP is truly necessary for ShardingSphere Proxy, especially since OpenClaw has been around for so long without supporting it. The occurrence of security incidents like https://www.stepsecurity.io/blog/hackerbot-claw-github-actions-exploitation basically represents a world of agents that completely disregard MCP. Perhaps we just need a CLI for ShardingSphere Proxy?

Another potential approach is to port the concepts of data sharding, read/write separation, data encryption, data anonymization, and shadow units from the database to the agent. However, I'm not entirely sure if agents like OpenClaw will implement these functionalities themselves. Perhaps an article like The Future of sharding everything: From Database to OpenClaw could be relevant? The main reason I haven't written this article is that I'm unsure how long OpenClaw will remain popular; at least for now, OpenClaw seems far more popular than https://github.com/modelcontextprotocol/servers and Kubernetes.😆

Let's look ahead to some other things that ASF members have built that correspond to JDBC URLs, such as https://github.com/Xuanwo/xurl .

The Future of sharding everything: From Database to OpenClaw - Wow, insane idea but I like it 👍

So I assume that you are talking about the fundamental of the database sharding which is driven by human written rules and will be replaced by the LLM dynamically and autonomously. Then what should we do now? just provide the capability for the AIt to manage the large number of the backend databaases and a simple interface ( maybe cli or chatbox) for people to use.

Ah ha, I would say I don't know either. Maybe in a few months, OpenClaw will be outdated again? However, if the capabilities of the LLM model continue to advance, it seems quite reasonable for OpenClaw to be replaced by something new that hasn't been invented yet.

What I'm actually referring to is, based on the existing ShardingSphere Java SPI, adding a unit as a parent interface to the database abstract class, and the unit interface should have a sub-abstract class called agent.

Therefore, under the assumption that there will no longer be a shortage of LLM Tokens in the future, for multiple remote OpenCode instances, OpenClaw instances, Docker cagent instances, Claude Code instances, Codex CLI instances, GitHub Copilot CLI instances, Kiro CLI instances, and Gemini CLI instances of agents, at the very least, Shardingsphere should expose an AI API key that aggregates multiple agents, just as it exposes a JDBC URL that aggregates multiple databases.

Data sharding should function similarly to https://www.kimi.com/blog/agent-swarm . However, ShardingSphere should be able to simultaneously manage multiple remote agent instances, not just be limited to the Kimi model.
Read/write splitting should function similarly to https://github.com/farion1231/cc-switch , but that's a TypeScript project. In ShardingSphere, it might need to be rewritten in Java, at least exposing a Java SPI for writing custom functions to switch to different agent instances under different hooks.
Data Encryption and Data Anonymization are essentially data processing. This functionality should be similar to what the Claude Opus model in https://www.stepsecurity.io/blog/hackerbot-claw-github-actions-exploitation does. One agent uses prompt injection to attack other agents on the internet, and the hacker account controlled by that agent is still active. Most agents using local models are unlikely to be protected against security incidents like prompt injection. But could a breakthrough in model capabilities solve this problem?
The functionality of the shadow unit should be able to map to agent honeypots like https://github.com/mrwadams/honeyagents .
Regarding the observability provided by the ShardingSphere agent... well, at least most agents don't integrate with https://github.com/open-telemetry/opentelemetry-collector at all, but the OpenTelemetry community doesn't seem to care about this kind of topic; there's no reference to be found.

So you are not sharding the databases anymore but the AI agents!

So you are not sharding the databases anymore but the AI agents!

Of course, as a disclaimer, I do not believe that such a branch currently exists to add so many vaguely detailed features to Shardingsphere.

ShardingSphere MCP PRD

Product Name: ShardingSphere MCP
Document Version: Final Draft Candidate
Document Type: PRD
Current Stage: Requirements Confirmation Version

1. Product Overview

ShardingSphere MCP is a unified database MCP entry point for large language models, agents, and AI platforms.

It provides a unified public interface for database access and SQL execution to upper layers, while shielding lower layers from differences across databases in object exposure, SQL execution, result models, error semantics, and governance boundaries.

In one sentence:

Upper-layer systems only need to connect to one ShardingSphere MCP service to access structural information, permission boundaries, and SQL execution capabilities across multiple databases in a consistent way.

2. Background and Problems

Current database integration mainly has the following issues:

Different database MCPs are inconsistent in resource naming, tool definitions, response structures, and capability boundaries.
Upper-layer agents must adapt to multiple databases separately, which creates high integration costs.
Enterprises want unified control over permissions, auditing, timeouts, result limits, and policy enforcement for database access.
Even for the same operations such as listing databases, inspecting structures, and executing SQL, different databases behave differently and express errors and results differently.

ShardingSphere already has the capability to access multiple databases in a unified way, so it is well suited to serve as the unified MCP endpoint.

3. Product Goals

Unify MCP integration across different databases.
Unify database object discovery and metadata retrieval capabilities.
Unify SQL execution tools and execution result models.
Unify error semantics, permission boundaries, transaction semantics, and governance policies.
Unify capability declaration and access policy representation.
Reduce the adaptation cost for agents integrating with multiple database MCPs.
Ensure schema changes and permission changes can be reflected at the MCP endpoint within minutes.

4. Non-Goals

Not responsible for natural language understanding, automatic SQL generation, or Q&A experience.
Not intended to flatten all database-specific objects and proprietary semantics.
Not intended to expose ShardingSphere-specific extensions as part of the core public interface.
Not intended to be compatible with the naming or behavioral details of third-party database MCP servers.
Does not support USE, SET, COPY, LOAD, or CALL.
Does not support database-specific high-risk meta-commands.

5. Version Scope

Databases officially supported in V1:

MySQL
PostgreSQL
openGauss
SQL Server
MariaDB
Oracle
ClickHouse
Doris
Hive
Presto
Firebird
H2

Not included in formal V1 acceptance scope:

Other SQL-like or test-oriented dialects

6. Target Users

LLM agent developers
IDE / CLI intelligent assistant integrators
Enterprise internal AI platforms
Data analysis agents
Data Q&A agents
Operations troubleshooting agents
Platform teams that need unified access to multiple databases

7. Typical Use Cases

Agents uniformly discover accessible objects across multiple databases.
Agents uniformly read metadata such as table schemas, columns, and views.
Agents use the same toolset to execute SQL across different databases.
The platform uniformly enforces permissions, auditing, timeouts, result limits, and policy controls.
After database schema changes, agents can see MCP resource updates within minutes.
Transaction boundaries are managed within an MCP session through transaction control statements.

8. Product Principles

Provide only the common surface, not the extension surface.
Standardize high-frequency objects and high-frequency tools first, then expand to long-tail capabilities.
Unifying invocation semantics takes priority over unifying underlying implementation mechanisms.
Capabilities that can be unified must be stable and consistent; capabilities that cannot be unified must be explicitly identified.
Conservative security takes priority over aggressive capability exposure.
All V1 public objects must have an official exposure path.
All officially supported V1 databases must satisfy the unified contract.
For databases that natively support transaction control, V1 must uniformly support transaction control semantics.
For databases that natively support savepoints, V1 must uniformly support savepoint semantics.
For databases that do not natively support transaction control or savepoints, the lack of support must be explicitly declared via capability, and related statements must uniformly return unsupported.

9. Unified Object Model

9.1 V1 Core Unified Objects

database
schema
table
view
column
capability
access policy

9.2 V1 Optional Public Object

index

Notes:

index is not a mandatory baseline object across all officially supported databases.
Whether index is supported is explicitly declared by the database-level capability field supported_object_types.

9.3 Objects Not Unified in V1

materialized view
sequence
function / procedure
trigger
event
synonym
Database-specific objects

10. Object Semantic Definitions

database: The top-level access target exposed externally by MCP, and the target that must be explicitly specified for every SQL execution.
schema: A namespace within a database.
table / view: Uniquely identified by (database, schema, name).
column: Belongs to a table or view.
index: Belongs to a table and is exposed only when supported by the current database.
capability: A declaration object describing service-level or database-level capability boundaries.
access policy: Describes the current identity's access boundaries and execution policies within the database scope.

Unified constraints:

Every SQL execution must target one and only one database.
V1 does not support cross-database SQL execution.
For databases without an independent schema concept, the system must still expose unified schema semantics.
For such databases, the default schema name must remain consistent with the database name.
In this document, database refers to the logical database identifier exposed externally by ShardingSphere MCP, not the underlying physical database instance, storage unit, or native catalog.

11. V1 Public Resources

Official resource list:

shardingsphere://capabilities
shardingsphere://databases
shardingsphere://databases/{database}
shardingsphere://databases/{database}/capabilities
shardingsphere://databases/{database}/schemas
shardingsphere://databases/{database}/schemas/{schema}
shardingsphere://databases/{database}/schemas/{schema}/tables
shardingsphere://databases/{database}/schemas/{schema}/views
shardingsphere://databases/{database}/schemas/{schema}/tables/{table}
shardingsphere://databases/{database}/schemas/{schema}/tables/{table}/columns
shardingsphere://databases/{database}/schemas/{schema}/tables/{table}/columns/{column}
shardingsphere://databases/{database}/schemas/{schema}/views/{view}
shardingsphere://databases/{database}/schemas/{schema}/views/{view}/columns
shardingsphere://databases/{database}/schemas/{schema}/views/{view}/columns/{column}
shardingsphere://databases/{database}/schemas/{schema}/tables/{table}/indexes
shardingsphere://databases/{database}/schemas/{schema}/tables/{table}/indexes/{index}
shardingsphere://access-policies
shardingsphere://access-policies/{database}

Resource principles:

Resources express only stable, readable, and understandable database structure and governance information.
Resource content must reflect the scope accessible to the current identity.
Any object listed as a V1 public object must have an official exposure path in resources and/or tools.
When the current database does not include index in supported_object_types, all index-related resources must uniformly return unsupported.

12. V1 Public Tools

Official tool list:

list_databases
list_schemas
list_tables
list_views
list_columns
list_indexes
search_metadata
describe_table
describe_view
get_capabilities
get_access_policy
execute_query

Tool definitions:

list_databases
list_schemas(database)
list_tables(database, schema, search?, page_size?, page_token?)
list_views(database, schema, search?, page_size?, page_token?)
list_columns(database, schema, object_type, object_name, search?, page_size?, page_token?)
list_indexes(database, schema, table, search?, page_size?, page_token?)
search_metadata(database?, schema?, query, object_types?, page_size?, page_token?)
describe_table(database, schema, table)
describe_view(database, schema, view)
get_capabilities(database?)
get_access_policy(database?)
execute_query(database, sql, max_rows?, timeout_ms?)

Supplementary definitions:

object_type can be table or view.
Each execute_query call may execute only one SQL statement. Multiple statements must uniformly return invalid_request.
Each search_metadata result item should include at least:
database
schema
object_type
object_name
parent_object_type
parent_object_name
For table / view, parent object fields may be empty.
For column / index, parent object fields are mandatory.
get_capabilities() returns the service-level capability.
get_capabilities(database) returns the database-level capability.
get_access_policy() returns the global access summary for the current identity.
get_access_policy(database) returns the detailed access policy for the current identity within that database.
When the current database does not support index, list_indexes must uniformly return unsupported.

13. Search, Filtering, and Pagination

V1 must support:

Keyword search for list queries.
Pagination for list queries.
Object-type filtering for metadata search.
A next-page identifier or equivalent mechanism in paginated results.
Clients must not be allowed to discover large database structures through full enumeration.

Officially supported values for search_metadata.object_types:

database
schema
table
view
column
index

Supplementary rules:

If database is omitted in search_metadata, it means metadata is searched across all databases accessible to the current identity.
This behavior is not equivalent to cross-database SQL execution.
When schema is specified, database must also be specified; otherwise, return invalid_request.

14. SQL Execution Capability

SQL execution is uniformly exposed through execute_query.
Every SQL execution must explicitly specify database.
V1 does not support cross-database SQL execution.
Cross-schema SQL within the same database may be supported if the database capability allows it.
If not supported, unsupported must be returned explicitly.
Once a transaction begins, the current transaction is bound to a single database.
If execute_query specifies another database during an active transaction, it must uniformly return conflict.

15. SQL Types Allowed in V1

Allowed:

SELECT
WITH ... SELECT
INSERT
UPDATE
DELETE
MERGE
TRUNCATE
CREATE
ALTER
DROP
GRANT
REVOKE
EXPLAIN ANALYZE
BEGIN
START TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT
ROLLBACK TO SAVEPOINT
RELEASE SAVEPOINT

Not allowed:

USE
SET
COPY
LOAD
CALL
Database-specific high-risk meta-commands
Statements explicitly forbidden by platform policy or access policy

Supplementary notes:

Allowed SQL types indicate the statement range that the V1 protocol may carry. They do not mean that every officially supported database must support every statement in that range.
Support is determined by database-level capability, and unsupported cases must uniformly return unsupported.
V1's consistency commitment for transaction semantics has two layers.
Transaction control semantics: BEGIN, START TRANSACTION, COMMIT, ROLLBACK
Savepoint semantics: SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT
For databases that natively support transaction control, unified transaction control semantics are guaranteed.
For databases that natively support savepoints, unified savepoint semantics are guaranteed.
For databases that do not support transaction control or savepoints, related statements must uniformly return unsupported.
The behavior of DDL, DCL, and EXPLAIN ANALYZE inside a transaction follows native database semantics and is explicitly declared by capability.

16. MCP Session, Identity, and Transaction Semantics

Each MCP session corresponds to one logical session context and one unique caller identity.
The current identity is determined by the authentication context established when the MCP session is created and remains unchanged throughout the session.
Identity cannot be switched during the session.
Sessions are not resumable; when a session ends, its logical context also ends.
The default execution mode is autocommit = true.
For databases that natively support transaction control, explicitly executing BEGIN or START TRANSACTION enters transaction state.
For databases that natively support savepoints, SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT are supported.
The current transaction context is valid only within the current MCP session.
During an active transaction, it is bound to a single database, and switching the target database is not allowed.
When an MCP session ends, all uncommitted transactions are automatically rolled back.
When a transaction exceeds the transaction_policy limit, ShardingSphere MCP should terminate the transaction and automatically roll it back.
V1 does not support changing session context through USE or SET.
For databases that do not natively support transaction control or savepoints, related transaction statements must uniformly return unsupported.

17. Unified Execution Result Model

`result_set`

Used for query statements and must include at least:

result_kind
columns
rows
truncated

`update_count`

Used for DML and must include at least:

result_kind
affected_rows

`statement_ack`

Used for DDL, DCL, transaction control, and other non-result-set statements, and must include at least:

result_kind
statement_type
status
message

Supplementary constraints:

INSERT, UPDATE, DELETE, and MERGE return update_count.
CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, and transaction control statements return statement_ack by default.
The return shape of EXPLAIN ANALYZE is determined by database-level capability.
Each execute_query call returns only one result object.
V1 does not support multiple result sets.

18. Unified Result Data Type Constraints

columns must include at least field name, unified logical type, underlying native type, and nullability.
rows must align with columns in order.
null values must be represented consistently.
Dates, times, and timestamps must be returned in a unified parseable format.
High-precision decimal / numeric values should be returned as strings to avoid precision loss.
Complex types such as JSON, arrays, and binary data must use a unified recognizable representation.
When results are truncated, truncated=true must be returned explicitly.

19. Capability Declaration

19.1 Service-Level Capability

Service-level capability expresses only the protocol's common capabilities and must include at least:

supported_resources
supported_tools
supported_statement_classes

19.2 Database-Level Capability

Database-level capability expresses the concrete behavioral boundaries of a given database and must include at least:

supported_object_types
supported_statement_classes
supports_transaction_control
supports_savepoint
supported_transaction_statements
default_autocommit
max_rows_default
max_timeout_ms_default
default_schema_semantics
supports_cross_schema_sql
supports_explain_analyze
ddl_transaction_behavior
dcl_transaction_behavior
explain_analyze_result_behavior
explain_analyze_transaction_behavior

19.3 Enum Constraints

ddl_transaction_behavior = uniform | native | unsupported
dcl_transaction_behavior = uniform | native | unsupported
explain_analyze_result_behavior = result_set | statement_ack | unsupported
explain_analyze_transaction_behavior = uniform | native | unsupported

19.4 Notes

default_autocommit refers to the default behavior of a ShardingSphere MCP session and is not the same as the underlying database's native default autocommit.
For databases that natively support transaction control, supports_transaction_control = true.
For databases that do not natively support transaction control, supports_transaction_control = false.
For databases that natively support savepoints, supports_savepoint = true.
For databases that do not natively support savepoints, supports_savepoint = false.
Whether index is supported is explicitly declared by supported_object_types.
The transaction-boundary behavior of DDL, DCL, and EXPLAIN ANALYZE must be explicitly declared by database-level capability.

20. Permissions and Access Boundaries

V1 must support:

Database-level permissions
Schema-level permissions
Table / view-level permissions
Statement-class permissions

Statement classes must include at least:

select
dml
ddl
dcl
transaction_control
savepoint
explain_analyze

Behavior requirements:

Resource discovery should return only objects accessible to the current identity by default.
Visibility of column and index is inherited from the parent table / view.
Direct client access to an unauthorized object must return unauthorized.
SQL execution must be governed by both object-level permissions and statement-class permissions.
CREATE statements are judged based on database-level or schema-level permissions.
ALTER, DROP, and TRUNCATE statements are judged based on permissions on the target object.
GRANT and REVOKE are judged jointly by database-level permissions and platform policy.
In addition to requiring explain_analyze permission, EXPLAIN ANALYZE must also satisfy the statement-class permission corresponding to its underlying target statement.
Both metadata reads and SQL executions must be included in auditing.

Not included in formal V1 acceptance:

Column-level permissions
Field masking
Row-level data permissions

21. Access Policy

Must include at least:

allowed_databases
allowed_schemas
allowed_objects
allowed_statement_classes
max_rows
max_timeout_ms
transaction_policy
advanced_statement_policy

transaction_policy must support at least:

allow_explicit_transaction
allow_savepoint
max_transaction_duration_ms

advanced_statement_policy must support at least:

allow_ddl
allow_dcl
allow_explain_analyze

Supplementary notes:

transaction_policy applies only to databases that natively support transaction control.
allow_savepoint applies only to databases that natively support savepoints.
If a statement class is allowed at the protocol level but disabled by the current policy, it must uniformly return policy_denied.

22. Metadata Change Detection and Synchronization SLA

V1 formally commits to:

Schema changes and permission changes must be reflected in the MCP public surface within 1 minute.
ShardingSphere is responsible for detecting changes and synchronizing them to the MCP endpoint.

Covered changes include:

schema creation, deletion, and rename
table / view creation, deletion, and rename
column creation, deletion, rename, and type change
index creation and deletion
Access permission changes

Supplementary rules:

If a structural change is successfully executed through the current MCP session, it should become visible in the current session as soon as possible.
If a permission change is successfully executed through the current MCP session, the permission evaluation result in the current session should become visible as soon as possible.
From a global perspective, the synchronization target remains within 1 minute.

23. Audit Baseline

Audit records must include at least:

identity
database
operation_class
operation_digest
success_or_failure
error_code
transaction_marker
timestamp

Supplementary notes:

operation_class is uniformly represented as resource_read, metadata_tool, or query_execution.
For SQL, operation_digest represents a statement digest.
For resources / tools, operation_digest represents a resource path or tool invocation summary.
transaction_marker must be able to identify at least BEGIN, COMMIT, ROLLBACK, and SAVEPOINT.

24. Error Model

Unified error codes:

invalid_request
unauthorized
policy_denied
not_found
unsupported
conflict
timeout
unavailable
transaction_state_error
query_failed

Supplementary rules:

Executing COMMIT, ROLLBACK, or referencing a nonexistent savepoint when there is no active transaction returns transaction_state_error.
Executing transaction control statements when the current database does not support transaction control returns unsupported.
Executing savepoint statements when the current database does not support savepoints returns unsupported.
When a transaction is terminated because it exceeds the transaction_policy limit, the current call returns timeout.
After the transaction is automatically rolled back, any subsequent operation against the invalidated transaction context returns transaction_state_error.

25. Minimum Unified Capability Baseline for V1

Officially supported V1 databases must uniformly satisfy the following minimum capability baseline:

Unified support for the core object model: database / schema / table / view / column / capability / access policy
Unified support for public tools: list_databases / list_schemas / list_tables / list_views / list_columns / search_metadata / describe_table / describe_view / get_capabilities / get_access_policy / execute_query
Unified support for the three result models: result_set / update_count / statement_ack
Unified support for object-level permission control, statement-class permission control, and the unified error model
Unified support for synchronizing schema changes and permission changes to the MCP endpoint within 1 minute
For databases that natively support transaction control, unified support for BEGIN / START TRANSACTION / COMMIT / ROLLBACK
For databases that natively support savepoints, unified support for SAVEPOINT / ROLLBACK TO SAVEPOINT / RELEASE SAVEPOINT
For databases that do not support transaction control or savepoints, the relevant capability must be explicitly declared, and related statements must uniformly return unsupported
index is not a mandatory unified baseline object in V1; support is declared through supported_object_types
Database-level capability must explicitly declare supported_statement_classes

26. V1 Transaction Capability Matrix

MySQL: supports_transaction_control=true, supports_savepoint=true
PostgreSQL: true, true
openGauss: true, true
SQL Server: true, true
MariaDB: true, true
Oracle: true, true
ClickHouse: false, false
Doris: true, false
Hive: false, false
Presto: true, false
Firebird: true, true
H2: true, true

Notes:

The above matrix must remain consistent with the actual return values of database-level capability.
If transaction capabilities for any database change in later versions, deployment modes, or product scope, both capability and acceptance criteria must be updated accordingly.

27. Success Metrics

Number of officially integrated supported databases
One-time integration success rate for upper-layer agents
Applicability rate of unified tools across multiple databases
Percentage of metadata synchronization meeting the within-1-minute target
SQL execution success rate
Transaction control success rate
Savepoint success rate
Effectiveness of permission and privilege-escalation risk control
Percentage reduction in multi-database MCP adaptation code on upper-layer platforms

28. Key Risks

Semantic differences among database objects may be too great, causing over-abstraction of the common surface.
As the support matrix expands, differences across databases in schema semantics, object models, result types, and execution capabilities will increase significantly.
Databases such as ClickHouse, Doris, Hive, and Presto will become key risk areas for consistency of the unified contract.
Unstable result models will force upper layers to keep writing database-specific branches.
Unstable metadata synchronization will directly undermine trust in MCP.
Unclear transaction semantics, savepoint semantics, write-operation permission boundaries, and multi-database consistency controls will introduce security risks.

29. Standard Demo

Principles:

Service-level capability expresses only the protocol's common capabilities.
Database-level capability determines the transaction behavior, advanced statements, object types, and boundary behavior of a specific database.
The behavior of DDL, DCL, and EXPLAIN ANALYZE inside transactions is governed by database-level capability.
Index-related capabilities are exposed only when supported_object_types includes index.

30. Compressed Requirements List

A unified public interface for database access and SQL execution must be provided.
The object semantics of database / schema / table / view / column / capability / access policy must be unified.
index must be a database-level optional public object and explicitly declared through capability.
Search, filtering, and pagination for object discovery must be supported.
The official public tool execute_query must be provided.
General SQL execution must be supported, but V1 does not include USE, SET, COPY, LOAD, CALL, or database-specific high-risk meta-commands.
MCP session semantics, identity binding, and transaction semantics must be defined.
Transaction control semantics and savepoint semantics must be distinguished.
A unified execution result model must be provided.
Dual-layer permission control at the object level and statement-class level must be provided.
The formal content models of service-level capability and database-level capability must be defined.
Schema changes and permission changes must be synchronized to the MCP endpoint within 1 minute.
A unified error model covering permissions, policy, conflicts, transaction issues, and execution failures must be provided.
The behavior of DDL, DCL, and EXPLAIN ANALYZE in transactions must be explicitly defined as following native database semantics and declared through capability.
A formal transaction capability matrix for officially supported databases must be provided and kept consistent with capability.

31. Compressed Acceptance Checklist

An agent can access and execute unified SQL capabilities across multiple databases by integrating with only one ShardingSphere MCP service.
All officially supported V1 databases satisfy the unified object model, unified tools, unified result model, and unified error semantics.
search_metadata supports database / schema / table / view / column / index.
For databases that natively support transaction control, transaction control semantics are stable and consistent.
For databases that natively support savepoints, savepoint semantics are stable and consistent.
For databases that do not support transaction control or savepoints, related statements uniformly return unsupported.
index is exposed only when supported_object_types includes that object type.
Access to unauthorized objects uniformly returns unauthorized, and policy-based denials uniformly return policy_denied.
Structural changes successfully executed through MCP become visible quickly in the current session and globally visible within 1 minute.
USE, SET, COPY, LOAD, and CALL are uniformly rejected in V1 and return consistent error semantics.
Transactional behavior differences for DDL, DCL, and EXPLAIN ANALYZE are explicitly declared through database-level capability.
An MCP session is bound to a single identity; session context is not resumed after the session ends, and uncommitted transactions are automatically rolled back.
The transaction capability matrix is consistent with actual capability return values.
Upper layers no longer need database-specific adaptation for object discovery, execution tools, and response structures.

ShardingSphere MCP Technical Design Proposal

1. Document Information

Document Name: ShardingSphere MCP Technical Design Proposal
Document Version: Final Version
Document Type: Technical Design
Status: Ready for formal architecture review
Target Scope: ShardingSphere MCP V1

2. Proposal Summary

ShardingSphere MCP will be introduced as a new access surface within the main ShardingSphere repository, but it will be organized using the model of independent code modules + a root-level distribution release module.

Code modules
- mcp/core
- mcp/bootstrap
Release module
- distribution/mcp
- artifactId: shardingsphere-mcp-distribution

At the protocol layer, V1 uses the official MCP Java SDK, with the following default technology choices:

mcp-core
mcp-json-jackson2

At the runtime layer, Spring AI is not used. HTTP service hosting is based on:

the official SDK's Servlet support
Embedded Tomcat 11

Local debugging uses:

STDIO

At the same time, the MCP sub-chain will coexist with the current Java 8 mainline in the main repository through a JDK 17 sub-chain + dedicated profile + dedicated CI + independent distribution.

3. Background

According to the earlier PRD, the goal of ShardingSphere MCP is not simply to "get MCP running," but to establish a formal and unified AI/Agent access surface for databases, covering:

unified metadata discovery
unified capability declaration
unified access policy declaration
a unified SQL execution entry point: execute_query
a unified error model
unified transaction and savepoint capability boundaries
unified auditing and governance capabilities

The current repository and ecosystem facts are as follows:

The root project module structure is defined in pom.xml (line 33).
The root project Java baseline is 8, in pom.xml (line 55).
The root project Jackson version is 2.16.1, in pom.xml (line 93).
The root distribution currently aggregates:
- src
- agent
- jdbc
- proxy
- proxy-native

See distribution/pom.xml (line 30).

The official MCP Java SDK currently requires Java 17+.

Source: official Java SDK README

Therefore, the technical proposal must satisfy all of the following:

MCP must enter the main repository.
The root project's default Java 8 build must not be broken.
The existing distribution structure must remain consistent.
The PRD-defined capability, access policy, execute_query, and transaction semantics must be covered.

4. Goals

The goals of this proposal are as follows:

Introduce MCP as an official access subsystem of ShardingSphere.
Use the official MCP Java SDK instead of a self-developed protocol layer.
Introduce Java 17 capability for the MCP sub-chain without requiring the entire repository to upgrade to JDK 17.
Form an independently deployable, independently runnable, and independently releasable MCP service.
Support the PRD-defined:
- capability
- access policy
- execute_query
- transaction matrix
- audit
- unified error model

5. Non-Goals

This proposal does not include the following:

It does not expand class diagrams, interface signatures, or method-level implementation details.
It does not push the entire main repository to upgrade uniformly to JDK 17.
It does not introduce Spring AI or Spring Boot as the primary implementation framework.
It does not embed MCP into proxy or jdbc.
It does not implement distributed session storage in V1.
It does not promise lossless failover for transaction state in V1.
It does not define every configuration field in detail at this stage.

6. Key Constraints

6.1 Repository Constraints

The root project's current default module chain is fixed, in pom.xml (line 33).
The root project's Java baseline is 8, in pom.xml (line 55).
The root project currently uses Jakarta BOM 8.0.0, in pom.xml (line 102).
The root distribution is currently part of the default build chain, in pom.xml (line 49).

6.2 Official SDK Constraints

As of 2026-03-21:

the official MCP Java SDK requires Java 17+
mcp-core provides core protocol capabilities
the README lists support for both STDIO and Servlet directions

Source: official Java SDK README

6.3 HTTP Runtime Constraints

Tomcat 11 requires Java 17 or higher.

Source: Tomcat 11 Migration Guide

Therefore, Tomcat 11 and Servlet 6 related dependencies must be strictly limited to the MCP sub-chain.

6.4 Compliance Constraints

The official Java SDK uses the MIT license.
MIT/X11 is ASF Category A and may be included in Apache product distribution.

Source: ASF 3rd Party License Policy

7. Key Design Decisions

7.1 MCP Enters the Main Repository, but Not as a Root Default Module

Final decision
- Place mcp under the root directory of the main repository.
- Treat it as an independent code subproject.
- Do not add it to the default <modules> of the root pom.xml.
- Enable it through a dedicated mcp profile.
Reason
- This preserves unified versioning, a unified repository, and unified evolution.
- It also avoids breaking the root project's default Java 8 build chain.

7.2 `distribution/mcp` Is Placed Under the Root `distribution`

Final decision
- Place the release module at distribution/mcp.
- Set the artifactId to shardingsphere-mcp-distribution.
Reason
- It is consistent with the existing distribution structure.
- It better matches the repository's release organization.
- It follows the same pattern as distribution/proxy and distribution/jdbc.

7.3 Both `mcp` and `distribution/mcp` Use Profiles

Final decision
- Add an mcp profile to the root pom.xml.
- Add mcp to the build chain inside that profile.
- Add a profile with the same name, mcp, to distribution/pom.xml.
- Add distribution/mcp to the distribution build chain inside that profile.
- Do not activate either profile by default.
Reason
- Only this approach can satisfy all of the following at the same time:
  - MCP code modules are buildable.
  - MCP distribution is buildable.
  - The root project's default build still preserves Java 8 semantics.

7.4 Do Not Use Spring AI

Final decision
- MCP does not use Spring AI as the core implementation approach.
Reason
- Spring AI mainly provides Boot integration enhancements.
- The main complexity of this project is not in the starter or annotation layer.
- The main repository is not currently a Spring project.
- Introducing Spring AI would expand stack intrusion and long-term maintenance cost.

7.5 Default SDK Selection

Final decision
- Use mcp-core.
- Use mcp-json-jackson2.
Do not use
- mcp
- mcp-json-jackson3
Reason
- The mcp aggregate package brings in Jackson 3.
- Jackson 3 diverges more sharply from the repository's Jackson 2.x path.
- mcp-core + mcp-json-jackson2 is currently the safest combination.

7.6 JSON Fallback Plan

Final decision
- V1 default: mcp-core + mcp-json-jackson2
- Fallback plan: mcp-core + custom McpJsonMapperSupplier
Reason
- This avoids hard-coding the JSON plan in the document as the only irreversible technical choice.
- It preserves room for future dependency reduction.

7.7 HTTP Service Hosting

Final decision
- Use the official SDK's Servlet support.
- Use Embedded Tomcat 11.
Reason
- No Spring is introduced.
- The runtime is mature.
- It aligns with the Java 17 direction.

7.8 HTTP Session and Transaction State

Final decision
- STDIO is naturally session-oriented.
- HTTP also maintains logical MCP sessions.
- V1 adopts:
  - sticky session
  - local in-memory session state
- V1 does not include:
  - distributed session store
  - lossless failover for transaction state
Reason
- The PRD has already defined transaction and savepoint semantics.
- HTTP cannot be implemented as a fully stateless request model.

8. Repository Organization and Module Design

8.1 Recommended Directory Structure

shardingsphere
├── mcp
│   ├── pom.xml
│   ├── core
│   └── bootstrap
├── distribution
│   └── mcp
└── pom.xml

8.2 Module Responsibilities

`mcp/core`

MCP common object model
capability assembly
access policy assembly
metadata discovery facade
execute_query facade
session / transaction facade
error model
audit facade
a unified facade over ShardingSphere kernel capabilities

`mcp/bootstrap`

MCP server bootstrap entry
HTTP / STDIO transport assembly
configuration loading
auth SPI integration
tools/resources registration
lifecycle management

`distribution/mcp`

shardingsphere-mcp-distribution
binary package
Docker image
startup scripts
configuration templates
release assets

8.3 Parent and Version Alignment

Recommended
- mcp/pom.xml inherits from the root shardingsphere parent to keep versions aligned.
- distribution/mcp/pom.xml inherits from the shardingsphere-distribution parent to keep the release structure aligned.
Result
- This ensures unified versions while keeping module layering clear.

8.4 Dependency Direction

Allowed
- mcp/core depends on infra
- mcp/core depends on database
- mcp/core depends on parser
- mcp/core depends on mode
- mcp/core depends on kernel
- mcp/core may depend on a small number of features modules when necessary
Forbidden
- kernel -> mcp
- proxy -> mcp
- jdbc -> mcp

8.5 Relationship Between MCP and Proxy/JDBC

The relationship between MCP and Proxy/JDBC is as follows:

At the product layer
- all are ShardingSphere access surfaces
At the implementation layer
- all consume ShardingSphere kernel capabilities
At the runtime layer
- they are independently deployed, independently governed, and independently released

MCP is neither a Proxy facade nor a JDBC wrapper.

9. Build and Release Model

9.1 Overall Strategy

Adopt a strategy where both code modules and release modules use a dedicated profile / dedicated CI.

The root project's default build chain does not include mcp.
The root distribution default build chain does not include distribution/mcp.
After activating the mcp profile, both are enabled:
- mcp
- distribution/mcp

9.2 Recommended Profile Organization

Root `pom.xml`

Add an mcp profile.
Inside the profile, add:
- <module>mcp</module>

`distribution/pom.xml`

Add a profile with the same name: mcp.
Inside the profile, add:
- <module>mcp</module>

9.3 Build Chains

Default Build Chain

targets the current Java 8 modules in the main repository
does not include MCP

MCP Build Chain

fixed to JDK 17
builds:
- mcp/core
- mcp/bootstrap
- distribution/mcp

9.4 JDK 17 Isolation Strategy

Recommended
- Maven Toolchains
- explicitly set maven.compiler.release=17 in mcp/pom.xml
- use a dedicated MCP CI lane fixed to JDK 17

9.5 Dependency Management Isolation

Must follow
- Do not introduce the MCP BOM into the root project's dependencyManagement.
- Do not spread Tomcat, Servlet, Reactor, or Jackson 2.20.x into the mainline of the repository.
- Manage dependency versions independently within the MCP sub-chain.

9.6 Why `distribution/mcp` Must Also Be Isolated

Because distribution/mcp depends on the Java 17-based mcp/bootstrap.

If only the mcp code modules are isolated while distribution/mcp is not, the root distribution default build chain would still be pulled into Java 17 dependencies, which conflicts with the current Java 8 mainline.

10. Detailed Technology Choices

10.1 Protocol and JSON

Default adoption
- io.modelcontextprotocol.sdk:mcp-core
- io.modelcontextprotocol.sdk:mcp-json-jackson2
Do not adopt
- io.modelcontextprotocol.sdk:mcp
- io.modelcontextprotocol.sdk:mcp-json-jackson3
Fallback path
- mcp-core + custom McpJsonMapperSupplier

10.2 HTTP Runtime

Adopt
- the official SDK's Servlet support
- Embedded Tomcat 11
Constraints
- Tomcat 11 and related Servlet 6 dependencies may appear only in:
  - mcp/bootstrap
  - distribution/mcp
- They must not enter the root project's unified dependency management.
- They must not reuse the root project's current Jakarta 8 BOM.

10.3 Local Debugging

Adopt
- STDIO

10.4 Authentication Integration

Do not introduce
- Spring Security
Use high-level SPIs
- Identity Provider SPI
- Auth Context Resolver SPI
V1 recommended modes
- Trusted Gateway mode
- Static API Key mode

10.5 Integration and Debugging Tools

Recommended
- official MCP Inspector
- local STDIO mode
- HTTP smoke integration testing
- dedicated integration testing for capability, transaction, and access policy

11. Overall Architecture

flowchart TB
    A["Agent / IDE / AI Platform"] --> B["MCP Transport Layer"]
    B --> C["MCP Protocol Layer"]
    C --> D["MCP Domain Facade"]
    D --> E["ShardingSphere Integration Layer"]
    E --> F["Metadata / Governance / Execution"]
    F --> G["Underlying Databases"]

    H["Identity / Auth SPI"] --> C
    I["Audit / Metrics / Logging"] --> C
    I --> D

11.1 Layer Responsibilities

MCP Transport Layer

carries HTTP and STDIO
manages session, connection, and request/response lifecycle
does not carry business capability judgment

MCP Protocol Layer

handles MCP concepts:
- resources
- tools
- capability
- errors
converts protocol objects into domain commands

MCP Domain Facade

the core semantic layer of the MCP subsystem
uniformly provides:
- metadata discovery
- capability
- access policy
- execute_query
- transaction / savepoint handling

ShardingSphere Integration Layer

adapts core capabilities such as metadata, parser, mode, authority, and kernel
shields differences among kernel modules
does not leak MCP concepts into the kernel

12. Capability and Access Policy Design

12.1 Basic Principle

The following must always be distinguished:

capability = objective capability boundary
access policy = boundary allowed for the current identity

12.2 Service-Level Capability

Service-level capability describes only protocol-wide public capabilities, such as:

supported resources
supported tools
supported statement classes

Service-level capability does not carry per-database differences in transaction or object support.

12.3 Database-Level Capability

Database-level capability must cover at least:

supported_object_types
supported_statement_classes
supports_transaction_control
supports_savepoint
supported_transaction_statements
default_autocommit
supports_cross_schema_sql
supports_explain_analyze
ddl_transaction_behavior
dcl_transaction_behavior
explain_analyze_result_behavior
explain_analyze_transaction_behavior

12.4 Access Policy

Access Policy must cover at least:

allowed_databases
allowed_schemas
allowed_objects
allowed_statement_classes
max_rows
max_timeout_ms
transaction_policy
advanced_statement_policy

12.5 Optional Object Types

Mandatory unified object baseline in V1
- database
- schema
- table
- view
- column
- capability
- access policy
Optional object in V1
- index
Rule
- index is declared by the database-level capability's supported_object_types.
- When unsupported, the direct API returns unsupported.

13. Unified Execution Entry Design for `execute_query`

13.1 Design Principle

execute_query is the only SQL execution entry point in V1.

It is not a raw SQL passthrough interface, but a unified execution and governance entry point.

13.2 High-Level Processing Stages

The processing flow is uniformly divided into six stages:

request validity validation
MCP session and transaction state validation
access policy validation
capability validation
ShardingSphere parse / route / execute adaptation
unified result and error mapping

13.3 Unified Statement Class Classification

select
dml
ddl
dcl
transaction_control
savepoint
explain_analyze

13.4 Unified Result Model

result_set
update_count
statement_ack

13.5 Unified Error Model

invalid_request
unauthorized
policy_denied
not_found
unsupported
conflict
timeout
unavailable
transaction_state_error
query_failed

14. Session, Transaction, and Savepoint Design

14.1 Session Model

Each MCP session binds:

identity
current database context
current transaction state
savepoint state
current policy context

14.2 HTTP Session Strategy

Since the PRD has already defined transaction and savepoint semantics, the HTTP path must maintain logical MCP sessions.

Therefore, V1 does not adopt a fully stateless HTTP model.

14.3 V1 Cluster Strategy

Adopt
- sticky session
- local in-memory session state
Not included
- distributed session store
- transaction recovery without stickiness across arbitrary nodes
- lossless failover for transaction state

14.4 Failure Semantics

When a node fails, restarts, or a session is lost:

any uncommitted transaction on that node is treated as failed
the related MCP session becomes invalid
the client must re-establish the session and restart the transaction

This is an explicit runtime constraint of V1, not an omission in implementation detail.

14.5 Transaction Capability Matrix

Transaction capability must be explicitly maintained as a matrix.

The matrix must include at least:

database_type
min_supported_version
supports_transaction_control
supports_savepoint
default_autocommit
supported_transaction_statements
supported_object_types
supported_statement_classes
supports_explain_analyze
ddl_transaction_behavior
dcl_transaction_behavior
explain_analyze_result_behavior
explain_analyze_transaction_behavior

The matrix serves the following roles:

the default fact source for capability
the technical source for acceptance criteria
the unified asset supporting matrix maintenance

15. Security, Governance, and Audit Design

15.1 Authentication

Do not bind to
- Spring Security
Use
- Identity Provider SPI
- Auth Context Resolver SPI
V1 recommended modes
- Trusted Gateway mode
- Static API Key mode

15.2 Authorization

Authorization has two layers:

object-level permissions
statement-class permissions

Advanced statement rules:

CREATE is governed by database/schema permissions.
ALTER, DROP, and TRUNCATE are governed by target object permissions.
GRANT and REVOKE are governed by database-level permissions and governance policy.
EXPLAIN ANALYZE validates both:
- explain analyze permission
- the permissions corresponding to the underlying target statement

15.3 Governance

V1 uniformly supports:

max_rows
timeout_ms
policy_denied
transaction policy
advanced statement policy
audit switch

15.4 Audit Baseline

Uniform output includes:

identity
database
operation_class
operation_digest
success_or_failure
error_code
transaction_marker
timestamp

16. Runtime and Deployment Model

16.1 Primary Production Model

Remote HTTP MCP service

Characteristics
- independent process
- independent container
- independent configuration directory
- independent logs and audit
- independent version release

16.2 Debugging Model

Local STDIO mode

Characteristics
- suitable for Inspector
- suitable for IDE / local Agent integration testing
- not the primary production model

16.3 Recommended Cluster Topology

MCP service cluster
fronting L7 gateway
sticky session enabled
each instance maintains local session state
metadata / governance sources are shared

16.4 Why Not Embed into Proxy

Because:

proxy is a database protocol proxy
mcp is a public control and execution surface for AI / Agents

Putting both into the same runtime would make the following worse:

operation boundaries
governance boundaries
version and resource isolation

17. Interaction Flows

17.1 Capability Discovery

sequenceDiagram
    participant C as Client
    participant M as MCP Server
    participant S as Service Capability
    participant D as Database Capability

    C->>M: Read service-level capability
    M->>S: Assemble protocol-level public capabilities
    S-->>M: supported resources/tools/classes
    M-->>C: Service-level capability

    C->>M: get_capabilities(database)
    M->>D: Assemble database-level capability
    D-->>M: database capability
    M-->>C: Database-level capability

17.2 Metadata Discovery

sequenceDiagram
    participant C as Client
    participant M as MCP Server
    participant F as Metadata Facade
    participant S as ShardingSphere Metadata

    C->>M: list_tables / list_views / search_metadata
    M->>F: metadata discovery
    F->>S: query metadata + policy
    S-->>F: accessible objects
    F-->>M: unified object result
    M-->>C: MCP tool response

17.3 SQL Execution

sequenceDiagram
    participant C as Client
    participant M as MCP Server
    participant P as Policy/Session Facade
    participant E as ExecuteQuery Facade
    participant S as ShardingSphere Core

    C->>M: execute_query(database, sql)
    M->>P: validate identity / policy / tx state / capability
    P-->>M: allow or reject
    M->>E: execute unified SQL request
    E->>S: parse / route / execute
    S-->>E: raw result
    E-->>M: result_set / update_count / statement_ack
    M-->>C: MCP response

18. Distribution and Operations Model

18.1 Release Module

The release module is located at:

distribution/mcp
artifactId: shardingsphere-mcp-distribution

18.2 Distribution Layout

Recommended output:

apache-shardingsphere-mcp-<version>/
├── bin/
├── conf/
├── lib/
├── logs/
└── LICENSE / NOTICE / README

18.3 Configuration Layering

Configuration is divided into four categories:

service configuration
exposure configuration
governance configuration
access configuration

18.4 Containerization Requirements

V1 should provide:

independent Dockerfile
independent image
independent startup parameters
independent configuration mount conventions

18.5 Observability

Recommended default capabilities:

readiness / liveness
service logs
audit logs
capability snapshots
tool invocation statistics
session statistics

19. Third-Party Dependencies and Compliance

19.1 Dependency Strategy

Default adoption
- mcp-core
- mcp-json-jackson2
Do not adopt
- mcp
- mcp-json-jackson3

19.2 Dependency Management Boundary

The following dependencies are allowed only in the MCP sub-chain:

Jackson 2.20.x
Reactor
Servlet 6
Embedded Tomcat 11

They must not:

enter the root project's unified dependency management
override the root project's existing dependency versions
propagate backward into kernel, proxy, or jdbc

19.3 Compliance Requirements

Because the official Java SDK uses the MIT license, the following must be completed:

LICENSE / NOTICE verification
third-party dependency license review
dependency list confirmation before release

20. Risks and Mitigations

20.1 Dual JDK Baseline Risk

Risk
- the root project uses Java 8
- MCP uses Java 17
Mitigation
- mcp and distribution/mcp use a dedicated profile and dedicated CI together
- Maven Toolchains
- exclusion from the default build chain

20.2 Dependency Conflict Risk

Risk
- Jackson
- Reactor
- Servlet API
- Tomcat 11 / Jakarta 6 dependencies
Mitigation
- manage them independently within the MCP sub-chain
- do not introduce the root BOM
- do not let dependencies leak into the mainline of the repository
- fall back to the custom JSON mapper solution if necessary

20.3 Database Capability Difference Risk

Risk
- transaction
- savepoint
- index
- explain analyze
Mitigation
- explicitly maintain the transaction capability matrix
- explicitly declare capability
- clearly control optional objects

20.4 Cluster Transaction Session Risk

Risk
- transaction context loss in an HTTP cluster
- node failure causing session invalidation
Mitigation
- sticky session
- local in-memory session state
- distributed session store is not included in V1
- failover semantics are explicitly defined, and lossless recovery is not promised

21. Implementation Plan

Phase 1: Structural Setup

establish the mcp subproject
establish core and bootstrap
establish distribution/mcp under the root distribution
establish a dedicated JDK 17 build chain for MCP

Phase 2: Protocol Public Surface

complete the resources and tools framework
complete the capability and access policy assembly chain
complete the unified error model

Phase 3: Execution and Governance

complete execute_query
complete the transaction capability matrix
complete the audit and policy governance pipeline

Phase 4: Deployment and Integration Testing

complete HTTP service deployment
complete local STDIO debugging
complete Inspector integration testing
complete capability, transaction, and policy validation

22. Acceptance Criteria

Technical acceptance should cover at least the following:

whether MCP exists as an independent code module
whether distribution/mcp exists as an official release module under the root distribution
whether the main repository Java 8 mainline remains unpolluted
whether the official SDK is used instead of a self-developed protocol implementation
whether capability and access policy are implemented with clear layering
whether execute_query becomes the unified execution entry point
whether the transaction capability matrix is explicitly maintained
whether HTTP session and transaction semantics are closed and consistent
whether the distribution becomes an independent operations unit

23. Final Conclusion

The final proposal can be summarized in four sentences:

MCP enters the ShardingSphere main repository, but the code modules and release modules are organized in layers.
The code modules are located in mcp/core and mcp/bootstrap, while the release module is located in the root distribution/mcp, with artifactId shardingsphere-mcp-distribution.
The protocol layer uses the official MCP Java SDK; V1 defaults to mcp-core + mcp-json-jackson2; the runtime layer uses the official Servlet support plus Embedded Tomcat 11; and STDIO is used for local debugging.
The MCP sub-chain coexists with the Java 8 mainline of the main repository through JDK 17, a dedicated profile, dedicated CI, and an independent distribution.

Nice work! so the final output artifact is a mcp server running in a image container?

Nice work! so the final output artifact is a mcp server running in a image container?

Yes, it should be.

ShardingSphere MCP Detailed Design Specification

1. Document Information

Document Name: ShardingSphere MCP Detailed Design Specification
Document Version: Final
Document Type: Detailed Design
Status: Ready for Coding Implementation
Applicable Scope: ShardingSphere MCP V1

2. Document Objective

This document translates the preceding PRD and technical design proposal into an implementation-level specification. Its goal is to make the MCP subsystem ready for direct development, without rework during implementation caused by unclear module structure, protocol contracts, test placement, or build pipelines.

This document primarily answers the following questions:

How should the code modules be organized?
How should Maven, profiles, and the JDK 17 sub-chain be implemented?
How should MCP Streamable HTTP be implemented?
How should sessions, transactions, and savepoints be maintained?
How should the capability, access policy, and transaction matrix be modeled?
How should configuration be organized?
How should unit tests, integration tests, and E2E tests be implemented?
How should distribution be released?

3. Known Inputs and Constraints

3.1 Current Repository State

The current repository structure and baseline constraints are as follows:

The root project module chain is defined in pom.xml (line 33).
The root project Java baseline is 8, as defined in pom.xml (line 55).
The current Jackson version in the root project is 2.16.1, as defined in pom.xml (line 93).
The root distribution aggregation structure is defined in distribution/pom.xml (line 30).
The root test aggregation structure is defined in test/pom.xml (line 30).
The test/e2e aggregation structure is defined in test/e2e/pom.xml (line 30).

3.2 Official SDK and Protocol Constraints

As of 2026-03-21, the key facts about the official MCP Java SDK and protocol are as follows:

The official MCP Java SDK baseline is Java 17+.
mcp-core is the core module.
mcp-json-jackson2 provides the Jackson 2 adapter.
mcp-json-jackson3 provides the Jackson 3 adapter.
The current standard HTTP transport for MCP is Streamable HTTP.
The latest protocol version page is currently 2025-11-25.

Sources:

Official Java SDK README
MCP Transports 2025-11-25

3.3 Current Design Boundaries

This specification fixes the following assumptions:

MCP is located inside the main repository.
Code modules are located under the root directory mcp/.
Release modules are located under the root directory distribution/mcp.
E2E modules are located under test/e2e/mcp.
Spring AI will not be introduced.
A repository-wide upgrade to JDK 17 will not be promoted.
Distributed session storage will not be implemented.
Lossless failover for transaction state will not be implemented.

4. Overall Implementation Conclusion

The final implementation structure for V1 is as follows:

shardingsphere
├── mcp
│   ├── pom.xml
│   ├── core
│   └── bootstrap
├── distribution
│   └── mcp
├── test
│   └── e2e
│       └── mcp
└── pom.xml

The responsibilities of the three sub-chains are as follows:

mcp
- Implements MCP runtime code.
distribution/mcp
- Produces shardingsphere-mcp-distribution.
test/e2e/mcp
- Provides MCP end-to-end verification.

These three chains must all be attached to the same mcp profile and must not enter the main build chain by default.

5. Maven and Module Design

5.1 Artifact Naming

The following names are recommended and should be fixed:

shardingsphere-mcp
shardingsphere-mcp-core
shardingsphere-mcp-bootstrap
shardingsphere-mcp-distribution
shardingsphere-test-e2e-mcp

5.2 Parent Relationships

It is recommended that:

mcp/pom.xml
- Parent: org.apache.shardingsphere:shardingsphere
- Packaging: pom
- Modules:
  - core
  - bootstrap
distribution/mcp/pom.xml
- Parent: org.apache.shardingsphere:shardingsphere-distribution
- Packaging: pom
test/e2e/mcp/pom.xml
- Parent: org.apache.shardingsphere:shardingsphere-test-e2e
- Packaging: jar

This ensures all of the following:

Versions remain aligned with the main repository.
The aggregation logic for distribution and test remains consistent.
Module boundaries remain clear.

5.3 Profile Organization

This is the most critical structural design in the entire implementation.

Root `pom.xml`

Add a new profile: mcp

Purpose:

Introduce <module>mcp</module> inside the profile.

Requirement:

It must not be activated by default.

`distribution/pom.xml`

Add a new profile: mcp

Purpose:

Introduce <module>mcp</module> inside the profile.

Requirement:

It must not be activated by default.

`test/e2e/pom.xml`

Add a new profile: mcp

Purpose:

Introduce <module>mcp</module> inside the profile.

Requirement:

It must not be activated by default.

Final Effect

Default build: MCP is not included.
MCP build: enable with -Pmcp.

Only this approach can satisfy all of the following at the same time:

The root project default Java 8 build remains stable.
The mcp code can be built.
distribution/mcp can be released.
test/e2e/mcp can be executed.

5.4 JDK 17 Isolation Strategy

Recommended:

Declare maven.compiler.release=17 locally in mcp/pom.xml.
Use Maven Toolchains.
Use a dedicated MCP CI lane fixed to JDK 17.

Forbidden:

Do not spread Java 17-related settings into the root project unified configuration.
Do not add Tomcat, Servlet, Reactor, or Jackson 2.20.x into the root project unified dependency management.

6. Package Structure Design

6.1 `mcp/core`

Recommended root package:

org.apache.shardingsphere.mcp

Recommended sub-packages:

protocol
resource
tool
capability
policy
session
metadata
execute
audit
config
common

Descriptions:

protocol
- Protocol objects and DTOs
resource
- Resource mapping and reading
tool
- Tool handlers and dispatcher
capability
- Capability matrix and assembler
policy
- Access policy assembler and evaluator
session
- Session, transaction, and savepoint management
metadata
- Metadata discovery facade
execute
- execute_query facade
audit
- Audit facade
config
- Internal MCP configuration model
common
- Shared constants, enums, and exceptions

6.2 `mcp/bootstrap`

Recommended sub-packages:

transport.http
transport.stdio
server
auth
wiring
config
lifecycle

Design principles:

core must not depend on bootstrap.
bootstrap depends on core.
Transport-specific details must not enter core.

7. Protocol and Transport Detailed Design

7.1 HTTP Transport Is Fixed as Streamable HTTP

For V1, the HTTP transport is fixed as follows:

MCP Streamable HTTP
Protocol baseline version: 2025-11-25

This means vague wording such as "HTTP mode" will no longer be used. Instead, the implementation explicitly complies with the current MCP standard transport.

7.2 MCP Endpoint

V1 uses a single MCP endpoint, for example:

/mcp

This endpoint must support:

POST
GET
DELETE

`POST /mcp`

Purpose:

The client sends MCP JSON-RPC requests, notifications, and responses to the server.

Rules:

Content-Type uses application/json.
Accept must include at least:
- application/json
- text/event-stream

`GET /mcp`

Purpose:

Satisfy the Streamable HTTP specification.
Support server-to-client SSE streams.

V1 notes:

MCP business functionality itself does not depend on server push.
However, the server still provides GET capability to satisfy the transport specification and for future-proofing.

`DELETE /mcp`

Purpose:

The client explicitly terminates the MCP session.

V1 design choice:

Implement DELETE.
Do not use 405 as the default behavior.

Reason:

This design uses a stateful session model.
Explicit session closure helps promptly release transaction and session resources.

7.3 Session Headers and Version Header

V1 uses the headers currently defined by the protocol:

MCP-Session-Id
MCP-Protocol-Version

Rules:

After successful initialization, the server returns MCP-Session-Id in the HTTP response header corresponding to InitializeResult.
Subsequent HTTP requests must include MCP-Session-Id.
Subsequent HTTP requests should include MCP-Protocol-Version.
If a request is missing the required session ID, return 400 Bad Request.
If the session has been terminated or does not exist, return 404 Not Found.

7.4 Protocol Version Handling

V1 adopts the following strategy:

The server protocol baseline is fixed at 2025-11-25.
The negotiated result is recorded during initialization.
For subsequent requests:
- If the header is present, it must match the session-negotiated version.
- If the header is missing but the session exists, the server may fall back to the session-negotiated version and record a warning log.
- If neither the header nor the session exists, the upstream request is considered invalid and returns 400.

This approach both complies with the protocol and avoids excessive fragility.

7.5 Local Mode Security Constraints

According to the transport specification, V1 requires:

Local mode binds only to 127.0.0.1 by default.
The server validates Origin.
Remote mode must enable authentication.

8. Session, Transaction, and Savepoint Detailed Design

8.1 Session States

Define the following session states:

NEW
ACTIVE
IN_TRANSACTION
CLOSED
EXPIRED

8.2 Transaction States

Define the following transaction states:

NONE
ACTIVE
ROLLBACK_ONLY
FINISHED

8.3 Savepoint Preconditions

A savepoint is available only if all of the following conditions are met:

The current database capability has supportsSavepoint = true.
The current transaction state is ACTIVE.
The current access policy has allowSavepoint = true.

Otherwise:

Return unsupported.
Or return transaction_state_error.

8.4 State Transitions

stateDiagram-v2
    [*] --> NEW
    NEW --> ACTIVE: Initialize Success
    ACTIVE --> IN_TRANSACTION: BEGIN / START TRANSACTION
    IN_TRANSACTION --> ACTIVE: COMMIT / ROLLBACK
    ACTIVE --> CLOSED: DELETE / Session Close
    IN_TRANSACTION --> CLOSED: Disconnect with rollback
    ACTIVE --> EXPIRED: Idle timeout
    IN_TRANSACTION --> EXPIRED: Timeout with rollback

8.5 Timeout Design

There are two timeout categories:

Session idle timeout
Transaction timeout

Rules:

When the session idle timeout expires:
- The session transitions to EXPIRED.
When the transaction timeout expires:
- The current transaction is rolled back automatically.
- The session transitions to ACTIVE or EXPIRED.
- Subsequent operations against the old transaction context return transaction_state_error.

8.6 Failure Semantics

V1 adopts:

Sticky session
In-memory local session store

Therefore, node failure semantics must be explicit:

When a node fails or restarts, sessions on that node are lost.
Active transactions are not recovered across nodes.
Uncommitted transactions are treated as failed and rolled back.
The client must reinitialize the session.

V1 does not support:

Distributed session store
Transaction recovery without sticky routing
Cross-node recovery of the savepoint stack

9. Capability / Access Policy / Transaction Matrix Detailed Design

9.1 Data Sources

Capability Sources

Capability is composed from three stacked layers:

Static matrix
Runtime metadata
Deployment-time overrides

Access Policy Sources

Access policy is composed from three stacked layers:

Identity / authority
Platform governance rules
Current MCP session context

9.2 Storage Method for the Transaction Matrix

V1 recommends:

A Java strongly typed registry

YAML or JSON is not preferred as the first-version source of truth.

Reasons:

It is more stable.
It is easier to test.
It is less likely to be polluted by incorrect configuration.
It is more suitable as the default fact source for capability.

9.3 Transaction Matrix Fields

It must include at least:

databaseType
minSupportedVersion
supportsTransactionControl
supportsSavepoint
defaultAutocommit
supportedTransactionStatements
supportedObjectTypes
supportedStatementClasses
supportsExplainAnalyze
ddlTransactionBehavior
dclTransactionBehavior
explainAnalyzeResultBehavior
explainAnalyzeTransactionBehavior

9.4 Capability Assembly Order

The assembly order is fixed as follows:

Read the static matrix.
Read runtime metadata.
Apply deployment-time overrides.
Generate database-level capability.

9.5 Access Policy Assembly Order

The assembly order is fixed as follows:

Read identity permissions.
Read governance policies.
Apply session context.
Generate access policy.

10. `execute_query` Detailed Design

10.1 Responsibilities

execute_query is responsible for:

Single-statement validation
Statement class classification
Capability validation
Access policy validation
Session / transaction validation
Invoking ShardingSphere parse / route / execute
Unified result mapping
Unified error mapping
Audit output

10.2 Execution Stages

Internally, it is fixed to 7 stages:

Request validation
Statement classification
Session validation
Capability validation
Policy validation
Kernel execution
Result / error mapping

10.3 Statement Class

Governance is unified across the following dimensions:

select
dml
ddl
dcl
transaction_control
savepoint
explain_analyze

10.4 Unified Result Types

Only three categories are allowed:

result_set
update_count
statement_ack

10.5 Error Mapping

Underlying exceptions must not be passed through directly.

They are uniformly mapped to:

invalid_request
unauthorized
policy_denied
not_found
unsupported
conflict
timeout
unavailable
transaction_state_error
query_failed

11. Configuration Design

11.1 Configuration Files

The distribution is recommended to provide:

conf/mcp.yaml
conf/logback.xml

11.2 `mcp.yaml` Layers

It is recommended to include the following top-level sections:

server
transport
session
exposure
governance
auth
mode

11.3 Configuration Responsibilities

`server`

Bind host
Port
Endpoint path

`transport`

enableHttp
enableStdio
protocolVersion

`session`

idleTimeout
transactionTimeout
requireStickySession

`exposure`

enabledDatabases
enabledTools
enabledResources
optionalObjectSwitches

`governance`

maxRowsDefault
timeoutDefault
auditEnabled

`auth`

Auth mode
Trusted gateway
API key

`mode`

Metadata / governance source integration

12. Security and Governance Detailed Design

12.1 Authentication Modes

V1 supports exactly two modes:

Trusted Gateway
Static API Key

V1 does not implement:

Full MCP Authorization / OAuth flow

This is an explicit scope decision, not an omission.

12.2 Authorization Model

Authorization is divided into two layers:

Object-level permissions
Statement-class permissions

Rules for advanced statements:

CREATE
- Governed by database / schema permissions
ALTER / DROP / TRUNCATE
- Governed by target object permissions
GRANT / REVOKE
- Governed by database-level permissions and governance policies
EXPLAIN ANALYZE
- Must validate both explain_analyze permission and the permission of the underlying statement

12.3 Audit Model

Unified output includes:

identity
database
operationClass
operationDigest
successOrFailure
errorCode
transactionMarker
timestamp

13. Test Design

13.1 Test Layers

V1 testing is divided into 4 layers:

Unit tests
Module integration tests
Protocol interoperability tests
E2E tests

13.2 Unit Test Focus

Key coverage areas:

Capability assembler
Access policy assembler
Transaction matrix registry
Session manager
execute_query facade
Error mapper

13.3 Module Integration Test Focus

Key coverage areas:

/mcp endpoint behavior
MCP-Protocol-Version
MCP-Session-Id
Transaction and savepoint state transitions
Idle timeout and transaction timeout
DELETE /mcp

13.4 E2E Module Placement

It is recommended to add:

test/e2e/mcp

artifactId:

shardingsphere-test-e2e-mcp

parent:

shardingsphere-test-e2e

Do not place it under:

test/e2e/operation/mcp

Reasons:

MCP is a new access surface, not an operation subtype.
The top-level aggregation in test/e2e/pom.xml is more suitable for carrying MCP as a first-level test module.

13.5 E2E Profile Constraint

Like code and distribution, test/e2e/mcp must be enabled together with the mcp profile.

That means:

Add an mcp profile to test/e2e/pom.xml.
Inside that profile, add:
- <module>mcp</module>

This ensures the default test/e2e build is not pulled into JDK 17 MCP tests.

13.6 E2E Dependencies

Recommended direct dependencies for test/e2e/mcp include:

shardingsphere-mcp-bootstrap
shardingsphere-test-e2e-env
shardingsphere-test-e2e-fixture

Add the following when necessary:

Inspector or HTTP client dependencies
Database driver dependencies

13.7 Minimum E2E Coverage Matrix

A. Transaction + Savepoint Group

MySQL
PostgreSQL
openGauss

B. Transaction but Savepoint Unsupported Group

Doris
Presto

C. Transaction Control Unsupported Group

Hive
ClickHouse

D. Optional Object Group

One database that supports index
One database that does not support index

E. Capability / Policy Group

Capability matches the matrix
unauthorized and policy_denied are distinguished correctly

13.8 Minimum E2E Smoke Scenarios

At least the following 12 scenarios must be fixed:

Service-level capability
Database-level capability
list_databases
list_schemas
list_tables / search_metadata
describe_table
execute_query(SELECT)
execute_query(DML)
BEGIN / COMMIT / ROLLBACK
Successful SAVEPOINT scenario
Unsupported SAVEPOINT scenario
DELETE close / timeout / unauthorized scenarios

14. Distribution Detailed Design

14.1 `distribution/mcp` Dependencies

distribution/mcp directly depends on:

shardingsphere-mcp-bootstrap

The recommended runtime dependency categories should refer to distribution/proxy and distribution/jdbc:

Authority implementations
Mode repositories
Parser and runtime dialect support
JDBC drivers
Logging

14.2 Directory Structure

The recommended output is:

apache-shardingsphere-mcp-<version>/
├── bin/
├── conf/
├── lib/
├── logs/
└── LICENSE / NOTICE / README

14.3 Startup Scripts

It is recommended to provide at least:

bin/start.sh
bin/stop.sh
bin/status.sh

14.4 Containerization

It is recommended to provide:

Dockerfile
Health checks
Configuration mounting conventions

15. Implementation Order

The recommended implementation order should be fixed as follows:

Maven, profile, and toolchain structure
Shared model and transaction matrix in mcp/core
Capability, policy, error, and session in mcp/core
HTTP and STDIO transport in mcp/bootstrap
Streamable HTTP endpoint for /mcp
execute_query and metadata discovery
distribution/mcp
Unit tests
Module integration tests
test/e2e/mcp

16. Pre-Implementation Checklist

Before coding begins, all of the following must be satisfied:

The profile organization for mcp and distribution/mcp has been finalized.
The profile placement for test/e2e/mcp has been finalized.
JDK 17 Toolchains have been finalized.
The Streamable HTTP version has been fixed to 2025-11-25.
The behavior of MCP-Session-Id and MCP-Protocol-Version has been finalized.
The session close behavior of DELETE /mcp has been finalized.
The storage method of the transaction matrix has been finalized.
The input and output boundaries of capability, access policy, and execute_query have been finalized.

17. Final Conclusion

The purpose of this detailed design specification is to push the preceding PRD and technical proposal into a state where coding can actually begin.

The following items have now been explicitly clarified and fixed:

Repository structure
Maven and profile structure
JDK 17 isolation strategy
Streamable HTTP protocol baseline
Session, transaction, and savepoint model
Capability, access policy, and matrix design
Configuration structure
Testing and E2E implementation approach
Distribution implementation approach

It would be good if we can have a PoC at first to see how it works? Or you want the AI to implement the whole mcp server?

Is it possible that we can have a cli tool as well since it might be another way for AI Agent using?

It would be good if we can have a PoC at first to see how it works? Or you want the AI to implement the whole mcp server?

I am trying to use codex to write the mcp module for now.

While #38541 has been merged, is there still no documentation on https://shardingsphere.apache.org/document/current/en/user-manual/ ?

I'm unlikely to know how to test MCP on the master branch, as the Agent I'm using doesn't even properly support MCP. Of course, I'm referring to anomalyco/opencode#28567 . What I mean is, could the documentation mention which agents are typically recommended for testing ShardingSphere's MCP?

Collect ideas with ShardingSphere and MCP #35294

Description

Activity

ChenXun1989 commented on Apr 30, 2025

fanpipi commented on Apr 30, 2025

zhfeng commented on Apr 30, 2025

linghengqian commented on Aug 2, 2025

linghengqian commented on Mar 2, 2026

zhfeng commented on Mar 2, 2026

linghengqian commented on Mar 2, 2026

zhfeng commented on Mar 2, 2026

linghengqian commented on Mar 2, 2026

terrymanu commented on Mar 21, 2026

ShardingSphere MCP PRD

1. Product Overview

2. Background and Problems

3. Product Goals

4. Non-Goals

5. Version Scope

6. Target Users

7. Typical Use Cases

8. Product Principles

9. Unified Object Model

9.1 V1 Core Unified Objects

9.2 V1 Optional Public Object

9.3 Objects Not Unified in V1

10. Object Semantic Definitions

11. V1 Public Resources

12. V1 Public Tools

13. Search, Filtering, and Pagination

14. SQL Execution Capability

15. SQL Types Allowed in V1

16. MCP Session, Identity, and Transaction Semantics

17. Unified Execution Result Model

result_set

update_count

statement_ack

18. Unified Result Data Type Constraints

19. Capability Declaration

19.1 Service-Level Capability

19.2 Database-Level Capability

19.3 Enum Constraints

19.4 Notes

20. Permissions and Access Boundaries

21. Access Policy

22. Metadata Change Detection and Synchronization SLA

23. Audit Baseline

24. Error Model

25. Minimum Unified Capability Baseline for V1

26. V1 Transaction Capability Matrix

27. Success Metrics

28. Key Risks

29. Standard Demo

30. Compressed Requirements List

31. Compressed Acceptance Checklist

terrymanu commented on Mar 21, 2026

ShardingSphere MCP Technical Design Proposal

1. Document Information

2. Proposal Summary

3. Background

4. Goals

5. Non-Goals

6. Key Constraints

6.1 Repository Constraints

6.2 Official SDK Constraints

6.3 HTTP Runtime Constraints

6.4 Compliance Constraints

7. Key Design Decisions

7.1 MCP Enters the Main Repository, but Not as a Root Default Module

7.2 distribution/mcp Is Placed Under the Root distribution

7.3 Both mcp and distribution/mcp Use Profiles

7.4 Do Not Use Spring AI

7.5 Default SDK Selection

7.6 JSON Fallback Plan

7.7 HTTP Service Hosting

7.8 HTTP Session and Transaction State

8. Repository Organization and Module Design

8.1 Recommended Directory Structure

8.2 Module Responsibilities

mcp/core

`result_set`

`update_count`

`statement_ack`

7.2 `distribution/mcp` Is Placed Under the Root `distribution`

7.3 Both `mcp` and `distribution/mcp` Use Profiles

`mcp/core`

`mcp/bootstrap`

`distribution/mcp`

Root `pom.xml`

`distribution/pom.xml`

9.6 Why `distribution/mcp` Must Also Be Isolated

13. Unified Execution Entry Design for `execute_query`