telegraph-it/timescaledb-mcp-server

TimescaleDB MCP Server

An MCP (Model Context Protocol) server that enables AI assistants to interact with TimescaleDB databases. This server provides tools for executing queries, managing data, and handling TimescaleDB-specific features like hypertables.

Features

• Execute SQL Queries: Run any SQL query with parameterized query support
• Insert Data: Bulk insert data into tables
• Update Data: Update existing records with WHERE clauses
• Create Tables: Create regular tables or TimescaleDB hypertables
• Alter Tables: Add/drop columns, modify constraints
• List Tables: View all tables including hypertable information
• Describe Tables: Get detailed table structure information

Prerequisites

• Node.js 18+ and npm
• Access to a TimescaleDB instance (local or cloud)
• MCP-compatible client (like Claude Desktop)

Installation

1. Clone or create a new directory for your MCP server:

mkdir timescaledb-mcp-server
cd timescaledb-mcp-server

2. Create the following project structure:

timescaledb-mcp-server/
├── src/
│   └── index.ts
├── package.json
├── tsconfig.json
├── .env
├── .env.example
└── README.md

3. Copy all the provided files into their respective locations

4. Install dependencies:

npm install

5. Build the TypeScript code:

npm run build

Configuration

1. Database Configuration

Create a .env file based on .env.example:

cp .env.example .env

Edit .env with your TimescaleDB connection details:

TIMESCALE_HOST=localhost
TIMESCALE_PORT=5432
TIMESCALE_DATABASE=your_database
TIMESCALE_USER=your_username
TIMESCALE_PASSWORD=your_password
TIMESCALE_SSL=false

2. MCP Client Configuration

For Claude Desktop, add this to your claude_desktop_config.json:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json Windows: %APPDATA%\Claude\claude_desktop_config.json

{
  "mcpServers": {
    "timescaledb": {
      "command": "node",
      "args": ["/path/to/your/timescaledb-mcp-server/build/index.js"],
      "env": {
        "TIMESCALE_HOST": "localhost",
        "TIMESCALE_PORT": "5432",
        "TIMESCALE_DATABASE": "your_database",
        "TIMESCALE_USER": "your_username",
        "TIMESCALE_PASSWORD": "your_password",
        "TIMESCALE_SSL": "false"
      }
    }
  }
}

Usage Examples

Once configured, you can use the following tools in your MCP client:

1. Create a Hypertable

// Tool: create_table
{
  "table": "sensor_data",
  "columns": [
    {"name": "time", "type": "TIMESTAMPTZ", "constraints": "NOT NULL"},
    {"name": "sensor_id", "type": "INTEGER", "constraints": "NOT NULL"},
    {"name": "temperature", "type": "DOUBLE PRECISION"},
    {"name": "humidity", "type": "DOUBLE PRECISION"}
  ],
  "hypertable": true,
  "timeColumn": "time"
}

2. Insert Sensor Data

// Tool: insert_data
{
  "table": "sensor_data",
  "columns": ["time", "sensor_id", "temperature", "humidity"],
  "values": [
    ["2024-01-01 10:00:00", 1, 22.5, 45.2],
    ["2024-01-01 10:00:00", 2, 23.1, 44.8],
    ["2024-01-01 10:01:00", 1, 22.6, 45.1]
  ]
}

3. Query with Time-Based Aggregation

// Tool: execute_query
{
  "query": `
    SELECT 
      time_bucket('5 minutes', time) AS five_min_bucket,
      sensor_id,
      AVG(temperature) as avg_temp,
      AVG(humidity) as avg_humidity
    FROM sensor_data
    WHERE time > NOW() - INTERVAL '1 hour'
    GROUP BY five_min_bucket, sensor_id
    ORDER BY five_min_bucket DESC
  `
}

4. Update Data

// Tool: update_data
{
  "table": "sensor_data",
  "set": {
    "temperature": 23.5,
    "humidity": 46.0
  },
  "where": "sensor_id = $1 AND time = $2",
  "params": [1, "2024-01-01 10:00:00"]
}

5. Add a Column

// Tool: alter_table
{
  "table": "sensor_data",
  "operation": "ADD_COLUMN",
  "columnName": "location",
  "columnType": "VARCHAR(100)"
}

6. Create Continuous Aggregate

// Tool: execute_query
{
  "query": `
    CREATE MATERIALIZED VIEW sensor_hourly_avg
    WITH (timescaledb.continuous) AS
    SELECT 
      time_bucket('1 hour', time) AS hour,
      sensor_id,
      AVG(temperature) as avg_temp,
      AVG(humidity) as avg_humidity,
      COUNT(*) as reading_count
    FROM sensor_data
    GROUP BY hour, sensor_id
  `
}

Development

For development with hot reload:

npm run dev

Troubleshooting

1. Connection Issues:

   • Verify your database credentials
   • Check if TimescaleDB is running
   • Ensure the host is accessible from your machine

2. SSL Issues:

   • For cloud instances, set TIMESCALE_SSL=true
   • For self-signed certificates, the server uses rejectUnauthorized: false

3. Permission Errors:

   • Ensure your database user has necessary permissions
   • For hypertable creation, user needs CREATE permission

4. MCP Client Not Finding Server:

   • Check the path in your MCP client configuration
   • Ensure the build directory exists (npm run build)
   • Restart your MCP client after configuration changes

Security Considerations

• Store database credentials securely
• Use environment variables for sensitive data
• Consider using connection pooling limits for production
• Implement query timeouts for long-running queries
• Use parameterized queries to prevent SQL injection

License

MIT