README.md•9.79 kB
# TimescaleDB MCP Server
A Python-based Model Context Protocol (MCP) server for TimescaleDB that enables AI assistants to interact with your time-series database.
## Features
- **Async Database Operations**: Built on `asyncpg` for high-performance async database access
- **Connection Pooling**: Efficient connection pool management with configurable pool sizes
- **MCP Resources**: Schema introspection via MCP resources for tables and hypertables
- **MCP Prompts**: Pre-built prompts for common operations (query time-series, analyze hypertables, explore schema)
- **SQL Injection Prevention**: Parameterized queries throughout for security
- **Comprehensive Error Handling**: Custom exceptions with clear error messages
- **Type Safety**: Full type hints and TypedDict support
- **6 MCP Tools**: Execute queries, list/describe tables and hypertables, query time-series data
- **Structured Logging**: Comprehensive logging for debugging and monitoring
## Installation
### From PyPI
```bash
pip install timescaledb-mcp
```
Or using `uv` (faster):
```bash
uv pip install timescaledb-mcp
```
The package is available on [PyPI](https://pypi.org/project/timescaledb-mcp/).
### From Source
1. Clone this repository:
```bash
git clone https://github.com/brunoprela/timescaledb-mcp.git
cd timescaledb-mcp
```
2. Install using pip:
```bash
pip install -e .
```
Or using `uv`:
```bash
uv pip install -e .
```
For development with additional tools:
```bash
pip install -e ".[dev]"
# or
uv pip install -e ".[dev]"
```
## Configuration
Configuration is managed via environment variables with the `TIMESCALEDB_` prefix.
### Required Settings
```env
TIMESCALEDB_HOST=localhost
TIMESCALEDB_PORT=5432
TIMESCALEDB_DATABASE=your_database
TIMESCALEDB_USER=your_user
TIMESCALEDB_PASSWORD=your_password
```
### Optional Settings
```env
TIMESCALEDB_MIN_POOL_SIZE=1 # Minimum connection pool size (default: 1)
TIMESCALEDB_MAX_POOL_SIZE=10 # Maximum connection pool size (default: 10)
TIMESCALEDB_QUERY_TIMEOUT=30.0 # Query timeout in seconds (default: None)
```
You can set these as environment variables or create a `.env` file in the project root.
## Usage
### Running the Server
After installation, you can run the MCP server in several ways:
**Using the console script:**
```bash
timescaledb-mcp
```
**As a Python module:**
```bash
python -m timescaledb_mcp
```
The server will start and be ready to accept MCP protocol requests via stdio.
### MCP Client Configuration
To use this server with an MCP client (like Claude Desktop), add it to your MCP configuration.
**Option 1: Using the installed console script (recommended):**
```json
{
"mcpServers": {
"timescaledb": {
"command": "timescaledb-mcp",
"env": {
"TIMESCALEDB_HOST": "localhost",
"TIMESCALEDB_PORT": "5432",
"TIMESCALEDB_DATABASE": "your_database",
"TIMESCALEDB_USER": "your_user",
"TIMESCALEDB_PASSWORD": "your_password"
}
}
}
}
```
**Option 2: Using Python module:**
```json
{
"mcpServers": {
"timescaledb": {
"command": "python",
"args": ["-m", "timescaledb_mcp"],
"env": {
"TIMESCALEDB_HOST": "localhost",
"TIMESCALEDB_PORT": "5432",
"TIMESCALEDB_DATABASE": "your_database",
"TIMESCALEDB_USER": "your_user",
"TIMESCALEDB_PASSWORD": "your_password"
}
}
}
}
```
**Option 3: Using uv (if installed via uv):**
```json
{
"mcpServers": {
"timescaledb": {
"command": "uv",
"args": ["run", "timescaledb-mcp"],
"env": {
"TIMESCALEDB_HOST": "localhost",
"TIMESCALEDB_PORT": "5432",
"TIMESCALEDB_DATABASE": "your_database",
"TIMESCALEDB_USER": "your_user",
"TIMESCALEDB_PASSWORD": "your_password"
}
}
}
}
```
## MCP Tools
The server provides the following tools:
- **`execute_query`**: Execute a SQL query with parameterized support and return results
- **`list_tables`**: List all tables in the database
- **`describe_table`**: Get detailed information about a table (columns, types, row counts)
- **`list_hypertables`**: List all TimescaleDB hypertables
- **`describe_hypertable`**: Get detailed information about a hypertable (dimensions, chunks, compression)
- **`query_timeseries`**: Query time-series data with optional time-bucketing and aggregation
## MCP Resources
The server exposes database schema as MCP resources:
- **Table Resources**: `timescaledb://table/{table_name}` - Access table schemas and metadata
- **Hypertable Resources**: `timescaledb://hypertable/{hypertable_name}` - Access hypertable schemas and metadata
Resources are automatically discovered and listed, making it easy for AI assistants to explore your database structure.
## MCP Prompts
Pre-built prompts for common operations:
- **`query_timeseries_data`**: Generate queries for time-series data retrieval
- **`analyze_hypertable`**: Analyze hypertable structure, chunks, and performance metrics
- **`explore_database_schema`**: Get an overview of all tables and hypertables in the database
## Development
This project uses the official MCP Python SDK to implement the Model Context Protocol.
### Project Structure
The project follows modern Python packaging standards with a `src-layout`:
```
timescaledb-mcp/
├── src/
│ └── timescaledb_mcp/
│ ├── __init__.py
│ ├── __main__.py
│ ├── config.py # Configuration management (Pydantic v2)
│ ├── database.py # Async TimescaleDB client (asyncpg)
│ ├── exceptions.py # Custom exceptions
│ └── server.py # MCP server with tools, resources, prompts
├── tests/ # Pytest test suite
│ ├── conftest.py
│ ├── test_config.py
│ └── test_database.py
├── .github/
│ └── workflows/
│ └── ci.yml # GitHub Actions CI/CD
├── pyproject.toml # Modern Python package configuration
├── pytest.ini # Pytest configuration
├── requirements.txt # Runtime dependencies
├── README.md
└── LICENSE
```
### Development Setup
1. Clone the repository:
```bash
git clone https://github.com/brunoprela/timescaledb-mcp.git
cd timescaledb-mcp
```
2. Install in editable mode with dev dependencies:
```bash
pip install -e ".[dev]"
# or
uv pip install -e ".[dev]"
```
3. Run tests:
```bash
make test
# or
uv run pytest tests/ -v
```
4. Run tests with coverage:
```bash
make test-cov
# or
uv run pytest tests/ -v --cov=timescaledb_mcp --cov-report=html
```
5. Run all checks:
```bash
make check
# This runs: lint, type-check, and test
```
### Testing
The test suite includes both unit tests (that don't require a database) and integration tests (that require a TimescaleDB instance).
**By default, `make test` automatically:**
- Starts a TimescaleDB Docker container
- Waits for it to be ready
- Runs all tests (including database tests)
- Stops and removes the container when done
Simply run:
```bash
make test
```
Or with coverage:
```bash
make test-cov
```
**Manual testing** (if you have your own TimescaleDB instance):
```bash
# Set environment variables
export TIMESCALEDB_HOST=localhost
export TIMESCALEDB_PORT=5432
export TIMESCALEDB_DATABASE=postgres
export TIMESCALEDB_USER=postgres
export TIMESCALEDB_PASSWORD=postgres
# Run tests against your database
make test-local
```
**Requirements**: Docker must be installed and running for `make test` to work. If Docker is not available, database tests will be skipped automatically.
### Code Quality
The project uses:
- **Black** for code formatting
- **Ruff** for linting
- **MyPy** for type checking
- **Pytest** for testing with async support
All checks run automatically in CI via GitHub Actions.
#### Running Checks Locally
You can run all checks locally using the Makefile:
```bash
# Install dev dependencies
make install-dev
# Run all checks (lint, type-check, test)
make check
# Or run individually:
make lint # Run linters
make lint-fix # Fix linting issues automatically
make format # Format code with black
make type-check # Run type checking
make test # Run tests
make test-cov # Run tests with coverage report
```
Alternatively, you can use `uv` directly:
```bash
# Linting
uv run ruff check src/ tests/
uv run black --check src/ tests/
# Type checking
uv run mypy src/
# Testing
uv run pytest tests/ -v
```
## Security
- **SQL Injection Prevention**: All queries use parameterized statements
- **Input Validation**: Table and hypertable names are validated
- **Connection Security**: Supports SSL connections (configure via connection string)
- **Error Handling**: Sensitive information is not exposed in error messages
## Performance
- **Async Operations**: Built on `asyncpg` for non-blocking I/O
- **Connection Pooling**: Efficient connection reuse with configurable pool sizes
- **Query Timeouts**: Configurable timeouts to prevent long-running queries
- **Resource Management**: Proper cleanup of connections and resources
## Publishing
The package is automatically published to PyPI via GitHub Actions when you create a GitHub Release. See [.github/SETUP_PUBLISHING.md](.github/SETUP_PUBLISHING.md) for setup instructions.
**Quick setup:**
1. Set up [PyPI Trusted Publishing](https://pypi.org/manage/account/publishing/) (recommended)
- Or add `PYPI_API_TOKEN` as a GitHub secret
2. Update version in `pyproject.toml`
3. Create a GitHub Release with matching tag (e.g., `v0.1.0`)
4. The workflow will automatically build and publish to PyPI
## License
MIT