Testing SingleStore's MCP Server

Model Context Protocol (MCP) appears to be gaining traction since its release in 2024. 

SingleStore recently released an official MCP Server, and this article will show how to install, configure, and run this from the CLI using a freely available tool called MCPHost.

Introduction

MCP is an open standard designed to enable seamless interaction between large language models (LLMs) and external tools or data sources.

Introduced by Anthropic in 2024, MCP defines a structured way for LLMs to call functions, access APIs, and retrieve real-time data during a conversation.

By creating a common interface between models and tools, MCP allows developers to integrate model intelligence with business logic, databases, and services in a consistent, scalable manner. This approach enhances the capabilities of LLMs by allowing them to reason over external information instead of relying solely on their internal training.

MCP supports multiple model providers and tool hosts, promoting interoperability across ecosystems. Its adoption is growing rapidly, especially in environments that demand real-time insights and dynamic interactions, such as customer support, data analytics, and intelligent automation.

Create a SingleStore Cloud Account and Database Tables

For our SingleStore test environment, we'll follow the instructions in a previous article on creating a SingleStore Cloud account and rreating database tables.

In addition to the username and password from our SingleStore account, we'll also need an API Key. This can be generated from Configuration > API Keys from the left nav in the SingleStore web portal.

Local Test Environment

First, we'll ensure that we have a Python virtual environment to use. We'll use venv to create it and then activate it.

Next, in our virtual environment, we'll install the SingleStore MCP Server, as follows:

pip install singlestore-mcp-server==0.1.3

We'll now create a JSON file called ~/.mcp.json as follows:

{
  "mcpServers": {
    "singlestore-mcp-server": {
      "command": "/path/to/singlestore-mcp-server",
      "env": {
        "SINGLESTORE_DB_USERNAME": "<username>",
        "SINGLESTORE_DB_PASSWORD": "<password>",
        "SINGLESTORE_API_KEY": "<api-key>"
      }
    }
  }
}

We'll replace /path/to with the actual path to where the SingleStore MCP Server was installed. We'll replace <username>, <password> and <api-key>, with the values that we saved earlier.

Install MCPHost

We'll use a freely available tool called MCPHost, a CLI application that supports communication, using MCP, between LLMs and external tools.

To use MCPHost, we'll need to install Go. For example, on an Apple Mac with Homebrew, this is as simple as:

brew install go

Next, we'll install MCPHost as follows:

go install github.com/mark3labs/mcphost@latest

By default, on an Apple Mac, this will create the file mcphost in the directory ~/go/bin. We could add the bin directory to $PATH.

Run MCPHost

MCPHost can work with a number of different LLMs. Let's test it initially with OpenAI. We'll need to provide an OpenAI API Key in our environment. For example:

export OPENAI_API_KEY="<OpenAI API Key>"

Replace <OpenAI API Key> with your key.

Now we'll run MCPHost as follows:

~/go/bin/mcphost -m openai:gpt-4o-mini

The output should be similar to the following:

2025/04/07 12:42:38 INFO Model loaded provider=openai model=gpt-4o-mini
2025/04/07 12:42:38 INFO Initializing server... name=singlestore-mcp-server
2025/04/07 12:42:39 INFO Server connected name=singlestore-mcp-server
2025/04/07 12:42:39 INFO Tools loaded server=singlestore-mcp-server count=18
┃ Enter your prompt (Type /help for commands, Ctrl+C to quit)
┃
┃
┃
┃
┃
┃
alt+enter / ctrl+j new line • ctrl+e open editor • enter submit

From here, we can enter various commands, such as:

• /help: Show this help message
• /tools: List all available tools
• /servers: List configured MCP servers
• /history: Display conversation history
• /quit: Exit the application

The complete set of SingleStore MCP Server coggfmmands can be found on GitHub. With the Free Shared Tier, for example, we can use several specific commands:

list_virtual_workspaces

create_virtual_workspace

execute_sql_on_virtual_workspace

Some commands require parameters.

Example Queries

Let's test the MCP Server. To start, we'll ask the following:

I am using the Free Shared Tier. What database tables can you see?

Example output:

Assistant:


  In your Free Shared Tier workspace, the following database
  tables are available:

  1. portfolio
  2. stock_sentiment
  3. tick

First Query

Let's try a query on the tick table:

For each stock symbol in the tick table, calculate the volatility as the difference between the highest recorded price and the lowest recorded price over time. Which stock symbol has the least volatility?

Example output:

Assistant:


  The stock symbol with the least volatility is FTR, with a
  volatility of 0.55. This value represents the difference
  between the highest recorded price and the lowest recorded
  price over time.

Second Query

Now, a simple count on the tick table:

How many rows are in the tick table?

Example output:

Assistant:


  The tick table contains 22,367,162 rows.

Third Query

Let's try something a bit more complex using the portfolio and tick tables:

Taking all the stock symbols from the portfolio table, and using the latest value for each stock symbol from the tick table, calculate the grand total value of all the shares listed in the portfolio table.

Example output:

Assistant:


  The grand total value of all the shares listed in the
  portfolio table is $44,540.60.

These results agree with those reported in a previous article.

Security Considerations

In this article, we stored the <username>, <password>, and <api-key> in plain text on a local machine. While this approach is acceptable for small-scale testing, especially when using the Free Shared Tier and working with non-sensitive data, it is not recommended for production environments. The SingleStore MCP Server is actively evolving, with ongoing improvements in security and authorisation to better support secure deployments.

Summary

In this article, we saw how to quickly install, configure, and run the SingleStore MCP Server using MCPHost. We launched MCPHost using an OpenAI model, but MCPHost also supports alternative providers and models.