DEV Community

Cover image for Testing SingleStore's MCP Server
SingleStore profile image Akmal Chaudhri
Akmal Chaudhri for SingleStore

Posted on • Edited on

1

Testing SingleStore's MCP Server

#singlestoredb #modelcontextprotocol #mcp

SingleStore MCP Server

Abstract

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 how to Create a SingleStore Cloud account and Create 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
Enter fullscreen mode Exit fullscreen mode

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>"
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Next, we'll install MCPHost, as follows:

go install github.com/mark3labs/mcphost@latest
Enter fullscreen mode Exit fullscreen mode

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>"
Enter fullscreen mode Exit fullscreen mode

Replace <OpenAI API Key> with your key.

Now we'll run MCPHost, as follows:

~/go/bin/mcphost -m openai:gpt-4o-mini
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

The complete set of SingleStore MCP Server commands 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
Enter fullscreen mode Exit fullscreen mode

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?
Enter fullscreen mode Exit fullscreen mode

Example output:

Assistant:


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

  1. portfolio
  2. stock_sentiment
  3. tick
Enter fullscreen mode Exit fullscreen mode

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?
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

Second query

Now a simple count on the tick table:

How many rows are in the tick table?
Enter fullscreen mode Exit fullscreen mode

Example output:

Assistant:


  The tick table contains 22,367,162 rows.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

Example output:

Assistant:


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

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.

Top comments (0)

👋 Kindness is contagious

Explore this insightful post in the vibrant DEV Community. Developers from all walks of life are invited to contribute and elevate our shared know-how.

A simple "thank you" could lift spirits—leave your kudos in the comments!

On DEV, passing on wisdom paves our way and unites us. Enjoyed this piece? A brief note of thanks to the writer goes a long way.

Okay