Skip to content

PostGraphile MCP Server - PostgreSQL GraphQL API Management

Overview

Create an MCP server that provides comprehensive integration with PostGraphile, enabling AI assistants to manage GraphQL schema generation, introspection, plugin management, JWT configuration, and PostGraphile-specific development workflows.

Use Cases

Primary Use Cases

  1. Schema Management: Introspect and analyze PostGraphile-generated GraphQL schemas
  2. Plugin Configuration: Manage PostGraphile plugins and their settings
  3. JWT & Authentication: Configure and validate JWT authentication patterns
  4. Performance Analysis: Analyze query performance and N+1 issues
  5. Development Workflows: Hot-reload schema changes and development server management

Specific Scenarios

  • Multi-Tenant Applications: Managing RLS policies and schema visibility
  • API Evolution: Schema versioning and breaking change detection
  • Security Auditing: Authentication, authorization, and data exposure analysis
  • Performance Optimization: Query analysis and index recommendations
  • Plugin Development: Custom plugin testing and validation

Ideal API Structure

Tools (MCP Functions)

Schema Management

// Introspect current PostGraphile schema
introspect_schema(database_url?: string): Promise<GraphQLSchemaInfo>

// Compare schemas between databases/versions
compare_schemas(current_db: string, target_db: string): Promise<SchemaComparison>

// Generate schema documentation
generate_schema_docs(output_format?: 'json'|'markdown'|'html'): Promise<SchemaDocs>

// Validate schema against best practices
validate_schema(database_url?: string): Promise<SchemaValidation>

PostGraphile Server Management

// Start PostGraphile development server
start_dev_server(options: PostGraphileOptions): Promise<ServerResult>

// Stop PostGraphile server
stop_server(instance_id?: string): Promise<void>

// Reload schema without server restart
reload_schema(instance_id?: string): Promise<ReloadResult>

// Get server health and status
get_server_status(instance_id?: string): Promise<ServerStatus>

JWT & Authentication

// Configure JWT settings and validate configuration
configure_jwt(jwt_config: JWTConfiguration): Promise<JWTValidation>

// Generate JWT tokens for testing
generate_test_jwt(payload: object, secret?: string): Promise<string>

// Validate JWT configuration against database schema
validate_jwt_setup(database_url?: string): Promise<JWTSetupValidation>

// Analyze authentication patterns in schema
analyze_auth_patterns(): Promise<AuthPatternAnalysis>

Plugin Management

// List available PostGraphile plugins
list_available_plugins(): Promise<PluginInfo[]>

// Install and configure plugins
install_plugin(plugin_name: string, config?: object): Promise<PluginResult>

// Validate plugin configuration
validate_plugin_config(plugin_name: string, config: object): Promise<PluginValidation>

// Generate plugin usage examples
generate_plugin_examples(plugin_name: string): Promise<PluginExamples>

Performance & Analysis

// Analyze GraphQL query performance
analyze_query_performance(query: string, variables?: object): Promise<PerformanceAnalysis>

// Detect N+1 query issues
detect_n_plus_one_issues(schema_analysis?: boolean): Promise<N1Detection[]>

// Generate database index recommendations
recommend_indexes(query_logs?: string[]): Promise<IndexRecommendations>

// Analyze schema complexity and depth
analyze_schema_complexity(): Promise<ComplexityAnalysis>

Database Integration

// Analyze PostgreSQL schema for PostGraphile compatibility
analyze_pg_schema(schema_names?: string[]): Promise<PostgresSchemaAnalysis>

// Generate PostGraphile-optimized database migrations
generate_pg_migrations(changes: SchemaChange[]): Promise<MigrationScript[]>

// Validate Row Level Security (RLS) setup
validate_rls_policies(table_names?: string[]): Promise<RLSValidation>

// Analyze function and procedure definitions
analyze_pg_functions(schema_names?: string[]): Promise<FunctionAnalysis>

Resources (MCP Data Sources)

Schema Information

// Current GraphQL schema SDL
postgraphile://schema/current -> string

// Schema statistics and metrics  
postgraphile://schema/stats -> SchemaStatistics

// Plugin configuration and status
postgraphile://plugins/active -> PluginStatus[]

// JWT configuration details
postgraphile://auth/jwt-config -> JWTConfiguration

Performance Data

// Query performance logs
postgraphile://performance/queries -> QueryPerformanceLog[]

// Server metrics and health
postgraphile://server/metrics -> ServerMetrics

// Database connection pool status
postgraphile://db/pool-status -> ConnectionPoolStatus

Prompts (MCP Templates)

// Common PostGraphile setup workflows
"setup-jwt-auth" -> Guide for configuring JWT authentication
"configure-rls-policies" -> Template for Row Level Security setup
"optimize-query-performance" -> Steps for query performance optimization
"setup-multi-tenant" -> Multi-tenant application configuration guide
"plugin-development" -> Template for developing custom PostGraphile plugins

Implementation Details

Core Technologies

  • Language: TypeScript (for consistency with MCP ecosystem)
  • PostGraphile Integration: Direct API integration with postgraphile library
  • GraphQL Tools: Schema introspection using graphql-js and related tools
  • PostgreSQL: Direct database connection for schema analysis
  • Process Management: PostGraphile server lifecycle management

Required PostGraphile Integrations

import { postgraphile, makePluginByCombiningPlugins } from 'postgraphile';
import { makeExtendSchemaPlugin, gql } from 'graphile-utils';
import { Pool } from 'pg';

// Core integrations needed:
// - Schema introspection via postgraphile's introspection system
// - Plugin loading and configuration management
// - JWT token validation and generation
// - Performance monitoring hooks
// - Database connection management

Configuration Options

interface PostGraphileMcpConfig {
  // Default database connection
  database_url?: string;
  
  // Default schema names to expose
  schemas?: string[];
  
  // JWT configuration
  jwt_secret?: string;
  jwt_token_identifier?: string;
  
  // Plugin configuration
  plugins?: Array<string | [string, object]>;
  
  // Server options
  server_options?: {
    port?: number;
    host?: string;
    cors?: boolean;
  };
  
  // Performance monitoring
  enable_query_log?: boolean;
  enable_metrics?: boolean;
}

Error Handling Requirements

  • Database Connection Issues: Clear messages for connection failures
  • Schema Validation Errors: Detailed validation feedback with suggestions
  • Plugin Loading Failures: Comprehensive plugin error diagnostics
  • JWT Configuration Issues: Step-by-step JWT setup validation
  • Performance Issues: Query optimization recommendations

Example Usage Scenarios

JWT Authentication Setup

// AI assistant helping configure JWT authentication
const jwtValidation = await tools.validate_jwt_setup("postgresql://localhost/mydb");
const jwtConfig = {
  jwtTokenIdentifier: "app_public.jwt",
  jwtSecret: process.env.JWT_SECRET,
  defaultRole: "app_anonymous"
};
await tools.configure_jwt(jwtConfig);

Schema Analysis and Optimization

// AI analyzing schema for performance issues
const schemaInfo = await tools.introspect_schema();
const complexity = await tools.analyze_schema_complexity();
const n1Issues = await tools.detect_n_plus_one_issues(true);
const indexRecs = await tools.recommend_indexes();

Multi-Tenant Configuration

// Setting up multi-tenant application
const rlsValidation = await tools.validate_rls_policies(["users", "projects", "tasks"]);
const authPatterns = await tools.analyze_auth_patterns();
// AI guides through RLS policy setup

Plugin Development Workflow

// AI helping develop custom PostGraphile plugin
const availablePlugins = await tools.list_available_plugins();
const examples = await tools.generate_plugin_examples("@graphile-contrib/pg-simplify-inflector");
// Generate plugin template and validation

Testing Requirements

Unit Tests

  • PostGraphile schema introspection and comparison
  • JWT token generation and validation
  • Plugin configuration validation
  • Database schema analysis
  • Performance metrics collection

Integration Tests

  • Real PostGraphile server lifecycle management
  • Database connection and query execution
  • Plugin loading and configuration scenarios
  • JWT authentication flows
  • Multi-schema and multi-tenant setups

Mock Test Data

  • Sample PostgreSQL schemas with PostGraphile patterns
  • Mock GraphQL schemas with various complexity levels
  • JWT configuration examples for different auth patterns
  • Plugin configuration scenarios
  • Performance test queries and expected optimizations

Documentation Requirements

User Documentation

  • PostGraphile + MCP integration setup guide
  • Common workflow examples (JWT setup, plugin config, etc.)
  • Performance optimization best practices
  • Troubleshooting guide for common PostGraphile issues

Developer Documentation

  • API reference with TypeScript types
  • Plugin development integration patterns
  • Custom authentication strategy examples
  • Performance monitoring integration guide

Success Criteria

Core Functionality ✅

  • Complete PostGraphile schema introspection and analysis
  • JWT authentication configuration and validation
  • Plugin management and configuration
  • Performance analysis and query optimization recommendations

Advanced Features ✅

  • Multi-tenant and RLS policy analysis
  • Real-time schema change detection and reload
  • Custom plugin development assistance
  • Integration with PostgreSQL-specific features (functions, triggers, etc.)

Quality Standards ✅

  • Comprehensive error handling with actionable recommendations
  • Full TypeScript type coverage with PostGraphile type integration
  • Integration test suite covering real PostGraphile scenarios
  • Performance benchmarking and optimization validation

Related Resources

Implementation Priority: HIGH

This server is essential for modern GraphQL API development with PostgreSQL and would significantly enhance PostGraphile development workflows. It addresses the gap between database schema management and GraphQL API optimization that many developers face when building production-ready APIs.