README.md•10.6 kB
---
# MCP Server ODBC via SQLAlchemy
A lightweight MCP (Model Context Protocol) server for ODBC built with **FastAPI**, **pyodbc**, and **SQLAlchemy**. This server is compatible with Virtuoso DBMS and other DBMS backends that implement a SQLAlchemy provider.
---
## Features
- **Get Schemas**: Fetch and list all schema names from the connected database.
- **Get Tables**: Retrieve table information for specific schemas or all schemas.
- **Describe Table**: Generate a detailed description of table structures, including:
- Column names and data types
- Nullable attributes
- Primary and foreign keys
- **Search Tables**: Filter and retrieve tables based on name substrings.
- **Execute Stored Procedures**: In the case of Virtuoso, execute stored procedures and retrieve results.
- **Execute Queries**:
- JSONL result format: Optimized for structured responses.
- Markdown table format: Ideal for reporting and visualization.
---
## Prerequisites
1. **Install uv**:
```bash
pip install uv
```
Or use Homebrew:
```bash
brew install uv
```
2. **unixODBC Runtime Environment Checks**:
1. Check installation configuration (i.e., location of key INI files) by running: `odbcinst -j`
2. List available data source names by running: `odbcinst -q -s`
3. **ODBC DSN Setup**: Configure your ODBC Data Source Name (`~/.odbc.ini`) for the target database. Example for Virtuoso DBMS:
```
[VOS]
Description = OpenLink Virtuoso
Driver = /path/to/virtodbcu_r.so
Database = Demo
Address = localhost:1111
WideAsUTF16 = Yes
```
3. **SQLAlchemy URL Binding**: Use the format:
```
virtuoso+pyodbc://user:password@VOS
```
---
## Installation
Clone this repository:
```bash
git clone https://github.com/OpenLinkSoftware/mcp-sqlalchemy-server.git
cd mcp-sqlalchemy-server
```
## Environment Variables
Update your `.env`by overriding the defaults to match your preferences
```
ODBC_DSN=VOS
ODBC_USER=dba
ODBC_PASSWORD=dba
API_KEY=xxx
```
---
## Configuration
For **Claude Desktop** users:
Add the following to `claude_desktop_config.json`:
```json
{
"mcpServers": {
"my_database": {
"command": "uv",
"args": ["--directory", "/path/to/mcp-sqlalchemy-server", "run", "mcp-sqlalchemy-server"],
"env": {
"ODBC_DSN": "dsn_name",
"ODBC_USER": "username",
"ODBC_PASSWORD": "password",
"API_KEY": "sk-xxx"
}
}
}
}
```
---
# Usage
## Database Management System (DBMS) Connection URLs
Here are the pyodbc URL examples for connecting to DBMS systems that have been tested using this mcp-server.
| Database | URL Format |
|---------------|-----------------------------------------------|
| Virtuoso DBMS | `virtuoso+pyodbc://user:password@ODBC_DSN` |
| PostgreSQL | `postgresql://user:password@localhost/dbname` |
| MySQL | `mysql+pymysql://user:password@localhost/dbname` |
| SQLite | `sqlite:///path/to/database.db` |
Once connected, you can interact with your WhatsApp contacts through Claude, leveraging Claude's AI capabilities in your WhatsApp conversations.
## Tools Provided
### Overview
|name|description|
|---|---|
|podbc_get_schemas|List database schemas accessible to connected database management system (DBMS).|
|podbc_get_tables|List tables associated with a selected database schema.|
|podbc_describe_table|Provide the description of a table associated with a designated database schema. This includes information about column names, data types, nulls handling, autoincrement, primary key, and foreign keys|
|podbc_filter_table_names|List tables, based on a substring pattern from the `q` input field, associated with a selected database schema.|
|podbc_query_database|Execute a SQL query and return results in JSONL format.|
|podbc_execute_query|Execute a SQL query and return results in JSONL format.|
|podbc_execute_query_md|Execute a SQL query and return results in Markdown table format.|
|podbc_spasql_query|Execute a SPASQL query and return results.|
|podbc_sparql_query|Execute a SPARQL query and return results.|
|podbc_virtuoso_support_ai|Interact with the Virtuoso Support Assistant/Agent -- a Virtuoso-specific feature for interacting with LLMs|
### Detailed Description
- **podbc_get_schemas**
- Retrieve and return a list of all schema names from the connected database.
- Input parameters:
- `user` (string, optional): Database username. Defaults to "demo".
- `password` (string, optional): Database password. Defaults to "demo".
- `dsn` (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
- Returns a JSON string array of schema names.
- **podbc_get_tables**
- Retrieve and return a list containing information about tables in a specified schema. If no schema is provided, uses the connection's default schema.
- Input parameters:
- `schema` (string, optional): Database schema to filter tables. Defaults to connection default.
- `user` (string, optional): Database username. Defaults to "demo".
- `password` (string, optional): Database password. Defaults to "demo".
- `dsn` (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
- Returns a JSON string containing table information (e.g., TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE).
- **podbc_filter_table_names**
- Filters and returns information about tables whose names contain a specific substring.
- Input parameters:
- `q` (string, required): The substring to search for within table names.
- `schema` (string, optional): Database schema to filter tables. Defaults to connection default.
- `user` (string, optional): Database username. Defaults to "demo".
- `password` (string, optional): Database password. Defaults to "demo".
- `dsn` (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
- Returns a JSON string containing information for matching tables.
- **podbc_describe_table**
- Retrieve and return detailed information about the columns of a specific table.
- Input parameters:
- `schema` (string, required): The database schema name containing the table.
- `table` (string, required): The name of the table to describe.
- `user` (string, optional): Database username. Defaults to "demo".
- `password` (string, optional): Database password. Defaults to "demo".
- `dsn` (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
- Returns a JSON string describing the table's columns (e.g., COLUMN_NAME, TYPE_NAME, COLUMN_SIZE, IS_NULLABLE).
- **podbc_query_database**
- Execute a standard SQL query and return the results in JSON format.
- Input parameters:
- `query` (string, required): The SQL query string to execute.
- `user` (string, optional): Database username. Defaults to "demo".
- `password` (string, optional): Database password. Defaults to "demo".
- `dsn` (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
- Returns query results as a JSON string.
- **podbc_query_database_md**
- Execute a standard SQL query and return the results formatted as a Markdown table.
- Input parameters:
- `query` (string, required): The SQL query string to execute.
- `user` (string, optional): Database username. Defaults to "demo".
- `password` (string, optional): Database password. Defaults to "demo".
- `dsn` (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
- Returns query results as a Markdown table string.
- **podbc_query_database_jsonl**
- Execute a standard SQL query and return the results in JSON Lines (JSONL) format (one JSON object per line).
- Input parameters:
- `query` (string, required): The SQL query string to execute.
- `user` (string, optional): Database username. Defaults to "demo".
- `password` (string, optional): Database password. Defaults to "demo".
- `dsn` (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
- Returns query results as a JSONL string.
- **podbc_spasql_query**
- Execute a SPASQL (SQL/SPARQL hybrid) query return results. This is a Virtuoso-specific feature.
- Input parameters:
- `query` (string, required): The SPASQL query string.
- `max_rows` (number, optional): Maximum number of rows to return. Defaults to 20.
- `timeout` (number, optional): Query timeout in milliseconds. Defaults to 30000.
- `user` (string, optional): Database username. Defaults to "demo".
- `password` (string, optional): Database password. Defaults to "demo".
- `dsn` (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
- Returns the result from the underlying stored procedure call (e.g., `Demo.demo.execute_spasql_query`).
- **podbc_sparql_query**
- Execute a SPARQL query and return results. This is a Virtuoso-specific feature.
- Input parameters:
- `query` (string, required): The SPARQL query string.
- `format` (string, optional): Desired result format. Defaults to 'json'.
- `timeout` (number, optional): Query timeout in milliseconds. Defaults to 30000.
- `user` (string, optional): Database username. Defaults to "demo".
- `password` (string, optional): Database password. Defaults to "demo".
- `dsn` (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
- Returns the result from the underlying function call (e.g., `"UB".dba."sparqlQuery"`).
- **podbc_virtuoso_support_ai**
- Utilizes a Virtuoso-specific AI Assistant function, passing a prompt and optional API key. This is a Virtuoso-specific feature.
- Input parameters:
- `prompt` (string, required): The prompt text for the AI function.
- `api_key` (string, optional): API key for the AI service. Defaults to "none".
- `user` (string, optional): Database username. Defaults to "demo".
- `password` (string, optional): Database password. Defaults to "demo".
- `dsn` (string, optional): ODBC data source name. Defaults to "Local Virtuoso".
- Returns the result from the AI Support Assistant function call (e.g., `DEMO.DBA.OAI_VIRTUOSO_SUPPORT_AI`).
---
## Troubleshooting
For easier troubleshooting:
1. Install the MCP Inspector:
```bash
npm install -g @modelcontextprotocol/inspector
```
2. Start the inspector:
```bash
npx @modelcontextprotocol/inspector uv --directory /path/to/mcp-sqlalchemy-server run mcp-sqlalchemy-server
```
Access the provided URL to troubleshoot server interactions.