The MCP Vertica server provides a local NLP-to-SQL interface for Vertica databases, running entirely locally with no authentication required. Key capabilities include:
• Execute SQL queries directly against Vertica with streaming results and batching options
• Natural language to SQL conversion using a local LLM (Ollama) for both data queries and DDL operations like table creation
• Data loading via bulk COPY operations into Vertica tables
• Database introspection to retrieve table structures, columns, types, constraints, indexes, and views
• Similar incident analysis to find incidents based on ID or text description
• Multiple access methods: REST API at http://127.0.0.1:8001/api/, terminal commands via uvx mcp-vertica nlp, and SSE MCP server on port 8000
• Dry-run capabilities to preview generated SQL without execution
• Local deployment using Docker and Ollama for the complete stack
Runs Vertica CE database via Docker containers for local development and testing environments
Integrates with Ollama's local LLM service to provide natural language to SQL query translation capabilities for Vertica databases
Provides Python-based tools for executing SQL queries, managing database schemas, copying data, and streaming query results from Vertica databases
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type @ followed by the MCP server name and your instructions, e.g., "@MCP Vertica show me the top 10 customers by total sales this month"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
This repository provisions two isolated stacks on AWS:
DB stack (deploy/db/**) — Spot t3.xlarge Amazon Linux 2023 instance running Vertica CE via Docker (port 5433).
MCP stack (deploy/mcp/**, src/**, tests/**, Dockerfile.mcp) — Spot t3.small instance that pulls the MCP FastAPI server image from ECR and exposes port 8000.
Each stack has its own GitHub Actions workflow with dedicated remote Terraform state, fail-fast credential checks, and post-deploy smoke tests via AWS Systems Manager. Pushes scoped to one stack never trigger the other.
.
├─ deploy/
│ ├─ db/
│ │ ├─ README.md
│ │ └─ terraform/
│ │ ├─ backend-bootstrap.sh
│ │ ├─ main.tf
│ │ ├─ outputs.tf
│ │ ├─ user_data_db.sh
│ │ └─ variables.tf
│ └─ mcp/
│ ├─ README.md
│ └─ terraform/
│ ├─ backend-bootstrap.sh
│ ├─ main.tf
│ ├─ outputs.tf
│ ├─ user_data_mcp.sh
│ └─ variables.tf
├─ .github/workflows/
│ ├─ db-apply-destroy.yml
│ └─ mcp-apply-destroy.yml
├─ src/mcp_vertica/
│ ├─ __init__.py
│ └─ server.py
├─ Dockerfile.mcp
├─ tests/
│ ├─ test_health.py
│ └─ test_sql_rendering.py
├─ PROMPTS.md
├─ pyproject.toml
├─ uv.lock
└─ docker-compose.ymlRelated MCP server: Teradata MCP Server
Set these under Settings → Secrets and variables → Actions before running any workflow:
AWS_REGION (default ap-south-1)
AWS_ACCOUNT_ID
Either OIDC: AWS_ROLE_TO_ASSUME and AWS_OIDC_ROLE_SESSION_NAME, or static keys: AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY
Optional:
ALLOWED_CIDRS — comma-separated IPv4 CIDRs (e.g. "49.37.x.x/32","122.166.x.x/32") to open ports 5433/8000 only to those networks
MCP_HTTP_TOKEN — if set, the MCP HTTP server requires Authorization: Bearer <token>
Triggered by pushes to deploy/db/** or manual workflow_dispatch.
Bootstraps the Terraform backend (vertica-mcp-tf-<account>-<region> bucket + DynamoDB lock table).
Applies Terraform with defaults: Spot t3.xlarge, 50 GiB gp3 volume, Vertica CE image 957650740525.dkr.ecr.ap-south-1.amazonaws.com/vertica-ce:v1.0.
Runs /usr/local/bin/db-smoke.sh through SSM (executes SELECT NOW(); via vsql).
Job summary prints the public IP and a copy/paste connection string (HOST=<ip> PORT=5433 USER=dbadmin DB=VMart).
Destroy by dispatching the workflow with action=destroy.
Triggered by pushes to deploy/mcp/**, src/**, tests/**, or Dockerfile.mcp.
Runs uv sync --frozen, ruff, and pytest before touching AWS.
Builds Dockerfile.mcp, pushes to mcp-vertica ECR repo, then applies Terraform for the MCP EC2 instance.
Terraform reads the DB stack’s remote state to populate DB_HOST and writes /opt/mcp.env for the container.
Smoke test hits GET /healthz via SSM; summary prints the MCP URL (http://<ip>:8000).
Destroy by dispatching with action=destroy.
The MCP FastAPI server (src/mcp_vertica/server.py) supports both stdio and HTTP transports. Environment variables at startup:
DB_HOST, DB_PORT (default 5433), DB_USER, DB_PASSWORD, DB_NAME
Optional MCP_HTTP_TOKEN enabling bearer-token auth
Endpoints:
GET /healthz
POST /api/render
POST /api/query
For Claude Desktop (local stdio):
{
"mcpServers": {
"vertica-local": {
"command": "uvx",
"args": ["mcp-vertica", "--transport", "stdio"]
}
}
}For remote HTTP (beta):
{
"mcpServers": {
"vertica-remote": {
"command": "npx",
"args": ["-y", "mcp-remote", "http://<MCP-PUBLIC-IP>:8000/sse"],
"env": {
"AUTH_HEADER": "Authorization: Bearer <MCP_HTTP_TOKEN>"
}
}
}
}uv sync --frozen
uv run ruff check
uv run pytest -q
MCP_HTTP_TOKEN=local DB_HOST=localhost DB_PORT=5433 DB_USER=dbadmin DB_NAME=VMart \
docker compose up --build
./scripts/wait-for-port.py localhost 8000 --timeout 120
curl -H "Authorization: Bearer local" http://127.0.0.1:8000/healthzDestroy AWS resources when idle to minimize costs; both stacks default to Spot instances with security-group ingress restricted to ALLOWED_CIDRS.
Copy data into a Vertica table using COPY command.
Args:
ctx: FastMCP context for progress reporting and logging
schema: vertica schema to execute the copy against
table: Target table name
data: List of rows to insert
Returns:
Status message indicating success or failure
| Name | Required | Description | Default |
|---|---|---|---|
| data | Yes | ||
| schema | Yes | ||
| table | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
With no annotations, the description carries full burden. It mentions using COPY command but lacks details on permissions, error handling, or side effects. Returns a status message but no specifics.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
Description is concise with Args and Returns sections, front-loading the purpose. No unnecessary sentences.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given no output schema, no annotations, and 3 required params, the description is incomplete. It doesn't mention table existence requirements, data format constraints, or behavior on conflict.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema coverage is 0%, but description adds minimal meaning: 'vertica schema', 'Target table name', 'List of rows to insert'. The 'data' parameter lacks structure details beyond list of rows, leaving ambiguity.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states it copies data into a Vertica table using COPY command, which is a specific verb and resource. It is easily distinguishable from siblings like execute_query or list_indexes.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No guidance on when to use this tool vs alternatives like execute_query for single inserts. No prerequisites or exclusions are mentioned.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Execute a SQL query and return the results.
Args:
ctx: FastMCP context for progress reporting and logging
query: SQL query to execute
database: Optional database name to execute the query against
Returns:
Query results as a string
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
With no annotations, the description must disclose behavioral traits. It only states the action and return type, omitting crucial details such as whether the query is read-only, impacts on data, or required permissions.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is reasonably concise with a clear structure (Args, Returns). The purpose sentence is front-loaded, and every sentence serves a purpose despite the parameter mismatch.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given that there is no output schema and no annotations, the description is insufficient. It lacks details about error handling, supported SQL syntax, database selection, and whether the tool is safe for read-only queries.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The description adds meaning for the 'query' parameter ('SQL query to execute'), but it also mentions 'ctx' and 'database' which are not in the input schema, causing confusion. Schema description coverage is 0%, so the description should compensate but instead introduces inconsistency.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states 'Execute a SQL query and return the results,' which is a specific verb and resource. However, the mention of parameters not present in the input schema (ctx, database) slightly reduces clarity.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No guidance on when to use this tool versus siblings like stream_query or copy_data. The description does not specify prerequisites or alternatives.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Get the structure of a table including columns, data types, and constraints.
Args:
ctx: FastMCP context for progress reporting and logging
table_name: Name of the table to inspect
schema: Schema name (default: public)
Returns:
Table structure information as a string
| Name | Required | Description | Default |
|---|---|---|---|
| schema | No | public | |
| table_name | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
No annotations are provided, so the description must fully disclose behavior. It mentions returning 'Table structure information as a string' but does not describe the format, side effects (e.g., read-only), error behavior, or performance characteristics.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is relatively concise, with the purpose stated in the first line. However, the Args/Returns block adds some verbosity without significant extra value, and could be streamlined.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given no output schema and a potentially complex return value, the description should elaborate on the format of the returned string (e.g., JSON, plain text). It also lacks error handling details and examples.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0%, so the description adds value beyond the schema. It defines table_name and schema with a default, but does not provide examples, constraints (e.g., required format for table_name), or clarification of schema parameter semantics.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states the tool retrieves table structure including columns, data types, and constraints. It uses a specific verb (Get) and resource (table structure), and is distinct from sibling tools like execute_query or list_views.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The description does not provide any guidance on when to use this tool versus alternatives. No mention of prerequisites, cases where other tools are preferred, or scope limitations.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
List all indexes for a specific table.
Args:
ctx: FastMCP context for progress reporting and logging
table_name: Name of the table to inspect
schema: Schema name (default: public)
Returns:
Index information as a string
| Name | Required | Description | Default |
|---|---|---|---|
| schema | No | public | |
| table_name | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
No annotations are present, so the description must disclose behavioral traits. It states returns are a string but omits details on performance, side effects, permissions, or error handling, leaving the agent under-informed.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is short and includes an Args/Returns structure, making it reasonably scannable. It could be slightly more concise by merging the header, but overall it is efficient.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given the tool has 2 parameters and no output schema, the description is minimal. It lacks details on the return format, edge cases, or database prerequisites, making it only barely adequate for operation.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema coverage is 0%, but the description adds meaningful parameter descriptions ('Name of the table to inspect' and 'Schema name (default: public)'), compensating for the lack of schema descriptions. This adds value beyond the schema.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states 'List all indexes for a specific table,' specifying the verb and resource. However, it does not differentiate from sibling tools like get_table_structure, which might have similar functionality.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No guidance is provided on when to use this tool versus alternative tools. The description only states what the tool does, without any when-not or alternative references.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
List all views in a schema.
Args:
ctx: FastMCP context for progress reporting and logging
schema: Schema name (default: public)
Returns:
View information as a string
| Name | Required | Description | Default |
|---|---|---|---|
| schema | No | public |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
With no annotations, the description must disclose behavioral traits. It mentions returning a string but does not reveal side effects, authentication needs, or performance implications. The included 'ctx' parameter is not in the input schema, causing inconsistency.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is short and uses a structured 'Args/Returns' format. However, it includes redundant technical formatting and a parameter (ctx) that does not appear in the schema, which could confuse an agent.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Missing important details: the return format of the string, progress reporting behavior (despite mentioning ctx), and how to handle large results. The discrepancy between description params and schema further reduces completeness.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0% (low), so the description must compensate. The description provides meaning for the 'schema' parameter ('Schema name (default: public)'), adding semantic value beyond the schema's default and type. However, it includes an undocumented 'ctx' parameter, slightly reducing clarity.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description explicitly states 'List all views in a schema,' clearly identifying the resource (views) and action (list). It distinguishes from sibling tools like list_indexes and get_table_structure by specifying it is for views.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No guidance on when to use this tool versus alternatives. It does not mention prerequisites, exclusions, or comparison with siblings. The description only states what it does, not the context for use.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Execute a SQL query and return the results in batches as a single string.
Args:
ctx: FastMCP context for progress reporting and logging
query: SQL query to execute
batch_size: Number of rows to fetch at once
Returns:
Query results as a concatenated string
| Name | Required | Description | Default |
|---|---|---|---|
| batch_size | No | ||
| query | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
With no annotations, the description should fully disclose behavioral traits. It mentions batching and progress reporting but omits critical details like whether the tool modifies data, safety concerns, or authentication requirements. The description does not warn about potentially destructive SQL operations.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is concise with a single-purpose summary and structured Args/Returns. No extraneous text, though it could be more informative without losing conciseness.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given the tool's complexity (SQL execution with optional batching, no output schema) and zero annotations, the description is incomplete. It fails to explain return value format, error handling, or when to adjust batch_size. The agent lacks sufficient context to use it safely.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema coverage is 0%, so the description must add meaning. It provides minimal descriptions for 'query' and 'batch_size' (e.g., 'SQL query to execute') but lacks constraints, valid ranges, or examples. The batch_size default and meaning are implied but not elaborated.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states the action ('Execute a SQL query') and the unique output format ('return the results in batches as a single string'). This distinguishes it from siblings like execute_query, which likely returns results differently.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No guidance is provided on when to use this tool versus alternatives (e.g., execute_query). There is no mention of prerequisites, limitations, or exclusions.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
v1.0.0copy_dataexecute_queryget_table_structurelist_indexeslist_viewsstream_queryScored across 6 tools
Each tool has a clearly distinct purpose with no ambiguity: copy_data handles data insertion, execute_query and stream_query handle query execution with different output methods, get_table_structure inspects table schema, list_indexes lists indexes, and list_views lists views. The descriptions clearly differentiate their functions, making misselection unlikely.
All tools follow a consistent verb_noun naming pattern (copy_data, execute_query, get_table_structure, list_indexes, list_views, stream_query). The naming is uniform throughout, using snake_case and clear action-object pairs, making the tool set predictable and easy to understand.
With 6 tools, the count is well-scoped for a database server, covering core operations like querying, data insertion, and schema inspection. It feels slightly lean but reasonable, as it includes essential functions without being overwhelming. A few more tools for updates or deletions might enhance coverage, but it's not a significant gap.
The tool set covers query execution, data copying, and schema inspection well, but there are notable gaps in CRUD operations. It lacks tools for updating or deleting data, creating or modifying tables, or managing users/roles. This could cause agent failures for common database workflows beyond basic querying and insertion.
PostgreSQL, MySQL, OpenAPI/Swagger, and shared Agent Memory with scoped access.
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Connect to PlanetScale databases, branches, schema, query insights, and execute SQL
A collaborative substrate over your data: vector, knowledge graph, SQL, geospatial, streaming.
