Paprika SQL MCP Server

cd /Users/jonaheaton/Documents/paprika_mcp_server npm init -y

npm install @modelcontextprotocol/sdk npm install better-sqlite3 # For SQLite # OR for other databases: # npm install mysql2 # For MySQL # npm install pg # For PostgreSQL

#!/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);

{ "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" } }

npm install -g .

{ "mcpServers": { "paprika-database": { "command": "paprika-mcp-server", "args": [] } } }

import Database from 'better-sqlite3'; this.db = new Database('/path/to/your/database.db');

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), }, ], }; }

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), }, ], }; }

// Use environment variables for database connection this.db = new Database(process.env.DATABASE_PATH || './default.db');

formatAsTable(data) { // Convert JSON to formatted table } formatAsChart(data) { // Generate chart data }

this.queryCache = new Map(); async executeQuery(query) { if (this.queryCache.has(query)) { return this.queryCache.get(query); } // Execute and cache result }

this.databases = { primary: new Database('./primary.db'), analytics: new Database('./analytics.db') };

{ name: 'get_recipe_by_ingredient', description: 'Find recipes containing specific ingredients', // ... implementation }

console.error('Debug:', JSON.stringify(request, null, 2));

# Test the server directly echo '{"jsonrpc": "2.0", "id": 1, "method": "tools/list"}' | node server.js