README.md•10.3 kB
# Paprika MCP Server
A Model Context Protocol (MCP) desktop extension for connecting Claude to your custom SQL database. This server allows Claude to query and interact with your database in real-time during conversations.
## Overview
MCP desktop extensions enable you to create custom tools that Claude can use directly through the desktop app. This implementation provides a SQL database interface that allows Claude to execute queries, explore schema, and analyze your data.
## Project Setup
### 1. Initialize the Project
```bash
cd /Users/jonaheaton/Documents/paprika_mcp_server
npm init -y
```
### 2. Install Dependencies
```bash
npm install @modelcontextprotocol/sdk
npm install better-sqlite3 # For SQLite
# OR for other databases:
# npm install mysql2 # For MySQL
# npm install pg # For PostgreSQL
```
### 3. Create the MCP Server
Create a file called `server.js`:
```javascript
#!/usr/bin/env node
import { Server } from '@modelcontextprotocol/sdk/server/index.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import {
CallToolRequestSchema,
ListToolsRequestSchema,
} from '@modelcontextprotocol/sdk/types.js';
import Database from 'better-sqlite3'; // or your preferred SQL driver
class SQLMCPServer {
constructor() {
this.server = new Server(
{
name: 'paprika-sql-server',
version: '0.1.0',
},
{
capabilities: {
tools: {},
},
}
);
// Initialize your database connection
this.db = new Database('path/to/your/database.db'); // Update with your database path
this.setupToolHandlers();
}
setupToolHandlers() {
// List available tools
this.server.setRequestHandler(ListToolsRequestSchema, async () => {
return {
tools: [
{
name: 'query_sql',
description: 'Execute a SQL query on the database',
inputSchema: {
type: 'object',
properties: {
query: {
type: 'string',
description: 'SQL query to execute',
},
},
required: ['query'],
},
},
{
name: 'describe_tables',
description: 'Get schema information for all tables',
inputSchema: {
type: 'object',
properties: {},
},
},
{
name: 'get_table_schema',
description: 'Get detailed schema for a specific table',
inputSchema: {
type: 'object',
properties: {
table_name: {
type: 'string',
description: 'Name of the table to describe',
},
},
required: ['table_name'],
},
},
],
};
});
// Handle tool calls
this.server.setRequestHandler(CallToolRequestSchema, async (request) => {
const { name, arguments: args } = request.params;
try {
switch (name) {
case 'query_sql':
return await this.executeQuery(args.query);
case 'describe_tables':
return await this.describeTables();
case 'get_table_schema':
return await this.getTableSchema(args.table_name);
default:
throw new Error(`Unknown tool: ${name}`);
}
} catch (error) {
return {
content: [
{
type: 'text',
text: `Error: ${error.message}`,
},
],
};
}
});
}
async executeQuery(query) {
try {
// Prevent destructive operations if needed
const lowerQuery = query.toLowerCase().trim();
if (lowerQuery.startsWith('drop') || lowerQuery.startsWith('delete') || lowerQuery.startsWith('truncate')) {
throw new Error('Destructive operations are not allowed');
}
const result = this.db.prepare(query).all();
return {
content: [
{
type: 'text',
text: JSON.stringify(result, null, 2),
},
],
};
} catch (error) {
throw new Error(`SQL execution failed: ${error.message}`);
}
}
async describeTables() {
try {
const tables = this.db.prepare(`
SELECT name FROM sqlite_master
WHERE type='table' AND name NOT LIKE 'sqlite_%'
`).all();
return {
content: [
{
type: 'text',
text: `Available tables: ${tables.map(t => t.name).join(', ')}`,
},
],
};
} catch (error) {
throw new Error(`Failed to describe tables: ${error.message}`);
}
}
async getTableSchema(tableName) {
try {
const schema = this.db.prepare(`PRAGMA table_info(${tableName})`).all();
return {
content: [
{
type: 'text',
text: JSON.stringify(schema, null, 2),
},
],
};
} catch (error) {
throw new Error(`Failed to get schema for ${tableName}: ${error.message}`);
}
}
async run() {
const transport = new StdioServerTransport();
await this.server.connect(transport);
}
}
const server = new SQLMCPServer();
server.run().catch(console.error);
```
### 4. Configure Package.json
Update your `package.json`:
```json
{
"name": "paprika-mcp-server",
"version": "1.0.0",
"type": "module",
"main": "server.js",
"bin": {
"paprika-mcp-server": "./server.js"
},
"scripts": {
"start": "node server.js"
},
"dependencies": {
"@modelcontextprotocol/sdk": "^0.1.0",
"better-sqlite3": "^8.7.0"
}
}
```
## Installation
### 1. Install the Package Globally
```bash
npm install -g .
```
### 2. Configure Claude Desktop
Create or edit the Claude desktop configuration file:
**Location:** `~/Library/Application Support/Claude/claude_desktop_config.json`
```json
{
"mcpServers": {
"paprika-database": {
"command": "paprika-mcp-server",
"args": []
}
}
}
```
### 3. Restart Claude Desktop
After making the configuration changes, restart the Claude Desktop application.
## Database-Specific Configurations
### SQLite (Default)
```javascript
import Database from 'better-sqlite3';
this.db = new Database('/path/to/your/database.db');
```
### PostgreSQL
```javascript
import pg from 'pg';
const { Pool } = pg;
this.db = new Pool({
user: 'your_user',
host: 'localhost',
database: 'your_database',
password: 'your_password',
port: 5432,
});
// Update query execution method for async/await
async executeQuery(query) {
const result = await this.db.query(query);
return {
content: [
{
type: 'text',
text: JSON.stringify(result.rows, null, 2),
},
],
};
}
```
### MySQL
```javascript
import mysql from 'mysql2/promise';
this.db = await mysql.createConnection({
host: 'localhost',
user: 'your_user',
password: 'your_password',
database: 'your_database'
});
// Update query execution method
async executeQuery(query) {
const [rows] = await this.db.execute(query);
return {
content: [
{
type: 'text',
text: JSON.stringify(rows, null, 2),
},
],
};
}
```
## Usage Examples
Once configured, you can ask Claude to interact with your database:
- **"Show me all tables in the database"**
- **"Query the users table for recent entries"**
- **"What's the schema of the products table?"**
- **"Find all records where status is 'active'"**
- **"Get a count of records by category"**
## Security Considerations
### Query Restrictions
The server includes basic protection against destructive operations:
- `DROP` statements are blocked
- `DELETE` statements are blocked
- `TRUNCATE` statements are blocked
### Additional Security Measures
- Consider implementing read-only database access
- Add input validation and sanitization
- Implement query result size limits
- Add authentication if needed
- Use environment variables for sensitive configuration
### Example Environment Configuration
```javascript
// Use environment variables for database connection
this.db = new Database(process.env.DATABASE_PATH || './default.db');
```
## Advanced Features
### 1. Query Result Formatting
Add methods to format results as tables or charts:
```javascript
formatAsTable(data) {
// Convert JSON to formatted table
}
formatAsChart(data) {
// Generate chart data
}
```
### 2. Query History and Caching
Implement query caching for performance:
```javascript
this.queryCache = new Map();
async executeQuery(query) {
if (this.queryCache.has(query)) {
return this.queryCache.get(query);
}
// Execute and cache result
}
```
### 3. Multiple Database Support
Extend to support multiple database connections:
```javascript
this.databases = {
primary: new Database('./primary.db'),
analytics: new Database('./analytics.db')
};
```
### 4. Custom Business Logic
Add domain-specific functions:
```javascript
{
name: 'get_recipe_by_ingredient',
description: 'Find recipes containing specific ingredients',
// ... implementation
}
```
## Troubleshooting
### Common Issues
1. **Server not appearing in Claude**
- Check that the configuration file path is correct
- Verify the server is installed globally
- Restart Claude Desktop
2. **Database connection errors**
- Verify database file path is correct
- Check database permissions
- Ensure database driver is installed
3. **Query execution errors**
- Verify SQL syntax
- Check table and column names
- Review error messages in Claude
### Debugging
Enable verbose logging by adding debug statements:
```javascript
console.error('Debug:', JSON.stringify(request, null, 2));
```
## Development
### Testing the Server
```bash
# Test the server directly
echo '{"jsonrpc": "2.0", "id": 1, "method": "tools/list"}' | node server.js
```
### Making Changes
1. Edit `server.js`
2. Reinstall globally: `npm install -g .`
3. Restart Claude Desktop
## Contributing
1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Test thoroughly
5. Submit a pull request
## License
MIT License - see LICENSE file for details.