nile-mcp-server

<p align="center">
 <a href="https://thenile.dev" target="_blank"><img width="96px" src="https://www.thenile.dev/about-logo.png" /></a>
 <h2 align="center">Nile MCP Server
  <br/>
  <img src="https://img.shields.io/npm/v/@niledatabase/server"/>
 </h2>
 <p align="center">
  <a href="https://thenile.dev/docs/ai-embeddings/nile-mcp-server"><strong>Explore the Documentation ↗️</strong></a>
  <br />
  <br />
  <a href="https://discord.gg/akRKRPKA">Join our Discord Community</a>
  🔵
  <a href="https://thenile.dev">Visit the Nile Website</a>
  🔵 
  <a href="https://github.com/orgs/niledatabase/discussions">Report Issues and Suggest Enhancements</a>
 </p>
</p>

[![smithery badge](https://smithery.ai/badge/@niledatabase/nile-mcp-server)](https://smithery.ai/server/@niledatabase/nile-mcp-server)

# Unleash the Power of LLMs with Nile: An Expert's Guide to the Model Context Protocol (MCP) Server

This document provides an in-depth guide to the Nile MCP Server, a robust implementation of the Model Context Protocol (MCP) designed to seamlessly integrate Large Language Models (LLMs) with the Nile database platform. This server empowers LLM applications with direct access to your data, enabling sophisticated AI-driven workflows.

## Core Capabilities

The Nile MCP Server offers a comprehensive suite of features, meticulously crafted for optimal performance and ease of integration:

*   **Database Lifecycle Management:** Effortlessly create, list, retrieve details, and delete databases within your Nile environment.
*   **Secure Credential Handling:** Generate and manage database credentials with robust security protocols.
*   **Geographic Region Selection:** Dynamically list available regions for database deployment, optimizing for latency and compliance.
*   **Direct SQL Query Execution:** Execute complex SQL queries directly against your Nile databases, unlocking powerful data analysis capabilities.
*   **Full MCP Compliance:** Adherence to the Model Context Protocol ensures seamless interoperability with a wide range of LLM applications.
*   **TypeScript Foundation:** Built with TypeScript, guaranteeing type safety, maintainability, and a superior developer experience.
*   **Comprehensive Error Management:** Benefit from detailed error handling and informative messages, streamlining debugging and issue resolution.
*   **Rigorous Testing:** A comprehensive test suite, powered by Jest, ensures the reliability and stability of the server.
*   **Automated Environment Configuration:** Streamlined configuration through automatic loading of environment variables from `.env` files.
*   **Schema-Driven Input Validation:** Robust input validation using Zod, preventing errors and ensuring data integrity.

## Installation: A Step-by-Step Guide

### Stable Release

For production environments, install the stable version:

```bash
npm install @niledatabase/nile-mcp-server

Cutting-Edge Preview

To explore the latest features and enhancements, install the alpha/preview version:

npm install @niledatabase/nile-mcp-server@alpha

This command installs the package within your node_modules directory (e.g., node_modules/@niledatabase/nile-mcp-server/dist/).

Advanced: Manual Installation

For granular control and customization:

# Clone the repository
git clone https://github.com/yourusername/nile-mcp-server.git
cd nile-mcp-server

# Install dependencies
npm install

# Build the project
npm run build

Server Startup: Mastering the Launch Sequence

Choose the method that best suits your development or production needs:

1. Direct Node Execution:

   node dist/index.js

2. Development Mode (with Hot Reloading):

   npm run dev

Upon successful startup, the server will log key initialization steps:

• Environment variable loading
• Server instance creation
• Tool initialization
• Transport connection establishment

To gracefully shut down the server, press Ctrl+C.

Verifying Operational Status

Look for the following log messages to confirm the server is running correctly:

[info] Starting Nile MCP Server...
[info] Loading environment variables...
[info] Environment variables loaded successfully
[info] Creating server instance...
[info] Tools initialized successfully
[info] Setting up stdio transport...
[info] Server started successfully

These logs indicate that the server is ready to receive commands from LLM clients like Claude Desktop.

Configuration: Securing Your Connection

Create a .env file in the root directory to store your Nile credentials:

NILE_API_KEY=your_api_key_here
NILE_WORKSPACE_SLUG=your_workspace_slug

To obtain your Nile API key, log in to the Nile console, navigate to your workspace's Security section, and generate a new key.

Integration with Claude Desktop: A Practical Guide

Prerequisites

1. Install Claude Desktop.
2. Build the project: npm run build

Configuration Steps

1. Open Claude Desktop.
2. Navigate to Settings > MCP Servers.
3. Click "Add Server".
4. Enter the following configuration:

{
  "mcpServers": {
    "nile-database": {
      "command": "node",
      "args": [
        "/path/to/your/nile-mcp-server/dist/index.js"
      ],
      "env": {
        "NILE_API_KEY": "your_api_key_here",
        "NILE_WORKSPACE_SLUG": "your_workspace_slug"
      }
    }
  }
}

Important: Replace the placeholders with your actual values:

• /path/to/your/nile-mcp-server: The absolute path to your project directory.
• your_api_key_here: Your Nile API key.
• your_workspace_slug: Your Nile workspace slug.

Integration with Cursor: Elevating Your Coding Experience

Prerequisites

1. Install Cursor.
2. Build the project: npm run build

Configuration Steps

1. Open Cursor.

2. Navigate to Settings (⌘,) > Features > MCP Servers.

3. Click "Add New MCP Server".

4. Configure the server:

   • Name: nile-database (or any descriptive name).

   • Command:

     env NILE_API_KEY=your_key NILE_WORKSPACE_SLUG=your_workspace node /absolute/path/to/nile-mcp-server/dist/index.js

     Important: Replace the placeholders with your actual values:

     • your_key: Your Nile API key.
     • your_workspace: Your Nile workspace slug.
     • /absolute/path/to: The absolute path to your project directory.

5. Click "Save".

6. Verify the green indicator confirms a successful connection.

7. Restart Cursor for the changes to take effect.

Server Modes: Tailoring to Your Architecture

The server supports two distinct operational modes:

STDIO Mode (Default)

This mode leverages standard input/output for communication, ensuring compatibility with Claude Desktop and Cursor.

SSE Mode: Real-Time Event Streaming

Server-Sent Events (SSE) mode enables real-time, event-driven communication over HTTP.

To enable SSE mode:

1. Set MCP_SERVER_MODE=sse in your .env file.
2. The server will start an HTTP server (default port 3000).
3. Connect to the SSE endpoint: http://localhost:3000/sse
4. Send commands to: http://localhost:3000/messages

Example SSE usage with curl:

# In terminal 1 - Listen for events
curl -N http://localhost:3000/sse

# In terminal 2 - Send commands
curl -X POST http://localhost:3000/messages \
  -H "Content-Type: application/json" \
  -d '{
    "type": "function",
    "name": "list-databases",
    "parameters": {}
  }'

Example Prompts: Unleashing the Power of Natural Language

After configuring the MCP server in Cursor, use natural language to interact with your Nile databases.

Database Management

Create a new database named "my_app" in AWS_US_WEST_2 region

List all my databases

Get details for database "my_app"

Delete database "test_db"

Schema Definition

Create a users table in my_app database with columns:
- tenant_id (UUID, references tenants)
- id (INTEGER)
- email (VARCHAR, unique per tenant)
- name (VARCHAR)
- created_at (TIMESTAMP)

Create a products table in my_app database with columns:
- tenant_id (UUID, references tenants)
- id (INTEGER)
- name (VARCHAR)
- price (DECIMAL)
- description (TEXT)
- created_at (TIMESTAMP)

Data Querying

Execute this query on my_app database:
SELECT * FROM users WHERE tenant_id = 'your-tenant-id' LIMIT 5

Run this query on my_app:
INSERT INTO users (tenant_id, id, email, name) 
VALUES ('tenant-id', 1, '[email protected]', 'John Doe')

Show me all products in my_app database with price > 100

Schema Exploration

Show me the schema for the users table in my_app database

Add a new column 'status' to the users table in my_app database

Create an index on the email column of the users table in my_app

Available Tools: A Comprehensive Toolkit

The server provides a rich set of tools for interacting with Nile databases:

Database Management

1. create-database

   • Creates a new Nile database.
   • Parameters:
     • name (string): Database name.
     • region (string): AWS_US_WEST_2 (Oregon) or AWS_EU_CENTRAL_1 (Frankfurt).
   • Returns: Database details (ID, name, region, status).
   • Example: "Create a database named 'my-app' in AWS_US_WEST_2"

2. list-databases

   • Lists all databases in your workspace.
   • Parameters: None.
   • Returns: List of databases (ID, name, region, status).
   • Example: "List all my databases"

3. get-database

   • Retrieves detailed information about a specific database.
   • Parameters:
     • name (string): Database name.
   • Returns: Detailed database information (API host, DB host).
   • Example: "Get details for database 'my-app'"

4. delete-database

   • Deletes a database.
   • Parameters:
     • name (string): Database name.
   • Returns: Confirmation message.
   • Example: "Delete database 'my-app'"

Credential Management

1. list-credentials

   • Lists all credentials for a database.
   • Parameters:
     • databaseName (string): Database name.
   • Returns: List of credentials (ID, username, creation date).
   • Example: "List credentials for database 'my-app'"

2. create-credential

   • Creates new credentials for a database.
   • Parameters:
     • databaseName (string): Database name.
   • Returns: New credential details (username, one-time password).
   • Example: "Create new credentials for database 'my-app'"
   • Important: Store the password securely, as it will not be displayed again.

Region Management

1. list-regions

   • Lists all available regions for creating databases.
   • Parameters: None.
   • Returns: List of available AWS regions.
   • Example: "What regions are available for creating databases?"

SQL Query Execution

1. execute-sql

   • Executes SQL queries on a Nile database.
   • Parameters:
     • databaseName (string): Database name.
     • query (string): SQL query to execute.
     • connectionString (string, optional): Pre-existing connection string.
   • Returns: Query results formatted as a markdown table (column headers, row count).
   • Features:
     • Automatic credential management.
     • Secure SSL connection.
     • Markdown table formatting.
     • Detailed error messages with hints.
     • Support for existing connection strings.
   • Example: "Execute SELECT * FROM users LIMIT 5 on database 'my-app'"

Resource Management

1. read-resource

   • Retrieves schema information for database resources (tables, views).
   • Parameters:
     • databaseName (string): Database name.
     • resourceName (string): Resource name (table/view).
   • Returns: Detailed schema information (column names, types, primary keys, indexes, foreign keys, constraints).
   • Example: "Show me the schema for the users table in my-app"

2. list-resources

   • Lists all resources (tables, views) in a database.
   • Parameters:
     • databaseName (string): Database name.
   • Returns: List of all resources with their types.
   • Example: "List all tables in my-app database"

Tenant Management

1. list-tenants

   • Lists all tenants in a database.
   • Parameters:
     • databaseName (string): Database name.
   • Returns: List of tenants (ID, metadata).
   • Example: "Show all tenants in my-app database"

2. create-tenant

   • Creates a new tenant in a database.
   • Parameters:
     • databaseName (string): Database name.
     • tenantName (string): Name for the new tenant.
   • Returns: New tenant details (ID).
   • Example: "Create a tenant named 'acme-corp' in my-app"

3. delete-tenant

   • Deletes tenants in the database.
   • Parameters:
     • databaseName (string): Database name.
     • tenantName (string): Name for the tenant.
   • Returns: Success if the tenant is deleted
   • Example: "Delete tenant named 'acme-corp' in my-app"

Example Usage: Real-World Scenarios

Here are example commands for Claude Desktop:

# Database Management
Please create a new database named "my-app" in the AWS_US_WEST_2 region.
Can you list all my databases?
Get the details for database "my-app".
Delete the database named "test-db".

# Connection String Management
Get a connection string for database "my-app".
# Connection string format: postgres://<user>:<password>@<region>.db.thenile.dev:5432/<database>
# Example: postgres://cred-123:[email protected]:5432/my-app

# SQL Queries
Execute SELECT * FROM users LIMIT 5 on database "my-app"
Run this query on my-app database: SELECT COUNT(*) FROM orders WHERE status = 'completed'
Using connection string "postgres://user:pass@host:5432/db", execute this query on my-app: SELECT * FROM products WHERE price > 100

Response Format: Consistent and Informative

All tools return responses in a standardized format:

• Success responses include relevant data and confirmation messages.
• Error responses include detailed error messages and HTTP status codes.
• SQL query results are formatted as markdown tables.
• All responses are formatted for easy reading in Claude Desktop.

Error Handling: Proactive Issue Resolution

The server handles various error scenarios:

• Invalid API credentials
• Network connectivity issues
• Invalid database names or regions
• Missing required parameters
• Database operation failures
• SQL syntax errors with helpful hints
• Rate limiting and API restrictions

Troubleshooting: Resolving Common Issues

1. Claude cannot access the tools:

   • Verify the server path in the configuration.
   • Ensure the project is built (npm run build).
   • Confirm your API key and workspace slug are correct.
   • Restart Claude Desktop.

2. Database creation fails:

   • Check your API key permissions.
   • Ensure the database name is unique in your workspace.
   • Verify the region is supported.

3. Credential operations fail:

   • Verify the database exists and is in the READY state.
   • Check that your API key has the necessary permissions.

Development: Contributing to the Project

Project Structure

nile-mcp-server/
├── src/
│   ├── server.ts      # MCP server implementation
│   ├── tools.ts       # Tool implementations
│   ├── types.ts       # Type definitions
│   ├── logger.ts      # Logging utilities
│   ├── index.ts       # Entry point
│   └── __tests__/     # Test files
│       └── server.test.ts
├── dist/             # Compiled JavaScript
├── logs/            # Log files directory
├── .env             # Environment configuration
├── .gitignore       # Git ignore file
├── package.json     # Project dependencies
└── tsconfig.json    # TypeScript configuration

Key Files

• server.ts: Main server implementation with tool registration and transport handling.
• tools.ts: Implementation of database operations and SQL query execution.
• types.ts: TypeScript interfaces for database operations and responses.
• logger.ts: Structured logging with daily rotation and debug support.
• index.ts: Server startup and environment configuration.
• server.test.ts: Comprehensive test suite.

Development Workflow

# Install dependencies
npm install

# Build the project
npm run build

# Start the server in production mode
node dist/index.js

# Start the server using npm script
npm start

# Start in development mode with auto-rebuild
npm run dev

# Run tests
npm test

Development Scripts

• npm run build: Compiles TypeScript to JavaScript.
• npm start: Starts the server in production mode.
• npm run dev: Starts the server in development mode with auto-rebuild.
• npm test: Runs the test suite.
• npm run lint: Runs ESLint for code quality checking.
• npm run clean: Removes build artifacts.

Testing

The project includes a comprehensive test suite that covers:

• Tool registration and schema validation
• Database management operations
• Connection string generation
• SQL query execution and error handling
• Response formatting and error cases

Run the tests with:

npm test

Logging

The server uses structured logging with the following features:

• Daily rotating log files
• Separate debug logs
• JSON formatted logs with timestamps
• Console output for development
• Log categories: info, error, debug, api, sql, startup

Related Links

• Model Context Protocol
• Nile Database
• Claude Desktop
• Cursor