run_sql_plus_plus_query

Execute SQL++ queries on Couchbase scopes to retrieve JSON data results for analysis and data access.

Instructions

Run a SQL++ query on a scope and return the results as a list of JSON objects.

The query will be run on the specified scope in the specified bucket.
The query should use collection names directly without bucket/scope prefixes, as the scope context is automatically set.

Example:
    query = "SELECT * FROM users WHERE age > 18"
    # Incorrect: "SELECT * FROM bucket.scope.users WHERE age > 18"

Input Schema

TableJSON Schema

Name	Required	Description	Default
`bucket_name`	Yes
`scope_name`	Yes
`query`	Yes

Implementation Reference

src/tools/query.py:39-90 (handler)

The handler function that executes the SQL++ query on the specified bucket and scope. Includes read-only mode validation using Lark parser to check for data/structure modifications, connects to the scope, runs the query, and returns results as list of dicts.

def run_sql_plus_plus_query(
    ctx: Context, bucket_name: str, scope_name: str, query: str
) -> list[dict[str, Any]]:
    """Run a SQL++ query on a scope and return the results as a list of JSON objects.

    The query will be run on the specified scope in the specified bucket.
    The query should use collection names directly without bucket/scope prefixes, as the scope context is automatically set.

    Example:
        query = "SELECT * FROM users WHERE age > 18"
        # Incorrect: "SELECT * FROM bucket.scope.users WHERE age > 18"
    """
    cluster = get_cluster_connection(ctx)

    bucket = connect_to_bucket(cluster, bucket_name)

    app_context = ctx.request_context.lifespan_context
    read_only_query_mode = app_context.read_only_query_mode
    logger.info(f"Running SQL++ queries in read-only mode: {read_only_query_mode}")

    try:
        scope = bucket.scope(scope_name)

        results = []
        # If read-only mode is enabled, check if the query is a data or structure modification query
        if read_only_query_mode:
            parsed_query = parse_sqlpp(query)
            data_modification_query = modifies_data(parsed_query)
            structure_modification_query = modifies_structure(parsed_query)

            if data_modification_query:
                logger.error("Data modification query is not allowed in read-only mode")
                raise ValueError(
                    "Data modification query is not allowed in read-only mode"
                )
            if structure_modification_query:
                logger.error(
                    "Structure modification query is not allowed in read-only mode"
                )
                raise ValueError(
                    "Structure modification query is not allowed in read-only mode"
                )

        # Run the query if it is not a data or structure modification query
        result = scope.query(query)
        for row in result:
            results.append(row)
        return results
    except Exception as e:
        logger.error(f"Error running query: {e!s}", exc_info=True)
        raise

src/mcp_server.py:175-178 (registration)
Registers the run_sql_plus_plus_query tool (along with others) by adding functions from tools.ALL_TOOLS to the FastMCP server instance.
```
# Register all tools
for tool in ALL_TOOLS:
    mcp.add_tool(tool)
```

src/tools/__init__.py:17-28 (registration)

Imports the run_sql_plus_plus_query handler from query.py into the tools package, making it available for export and inclusion in ALL_TOOLS.

# Query tools
from .query import (
    get_longest_running_queries,
    get_most_frequent_queries,
    get_queries_not_selective,
    get_queries_not_using_covering_index,
    get_queries_using_primary_index,
    get_queries_with_large_result_count,
    get_queries_with_largest_response_sizes,
    get_schema_for_collection,
    run_sql_plus_plus_query,
)

src/tools/__init__.py:42-64 (registration)

Includes run_sql_plus_plus_query in the ALL_TOOLS list used by mcp_server.py for registration with the MCP server.

ALL_TOOLS = [
    get_buckets_in_cluster,
    get_server_configuration_status,
    test_cluster_connection,
    get_scopes_and_collections_in_bucket,
    get_collections_in_scope,
    get_scopes_in_bucket,
    get_document_by_id,
    upsert_document_by_id,
    delete_document_by_id,
    get_schema_for_collection,
    run_sql_plus_plus_query,
    get_index_advisor_recommendations,
    list_indexes,
    get_cluster_health_and_services,
    get_queries_not_selective,
    get_queries_not_using_covering_index,
    get_queries_using_primary_index,
    get_queries_with_large_result_count,
    get_queries_with_largest_response_sizes,
    get_longest_running_queries,
    get_most_frequent_queries,
]

Couchbase MCP Server