Database MCP Server

# Database MCP Server A remote web-based Model Context Protocol (MCP) server that provides comprehensive database tools for both **Supabase** and **general PostgreSQL** databases. This server allows AI assistants like Claude and Cursor to interact with PostgreSQL databases through natural language. ## 🚀 Features ### 🔐 User-Provided Database Credentials - **Secure Web Form** - Users provide their own database credentials through a beautiful, responsive web interface - **Multi-Database Support** - Works with any PostgreSQL database including Supabase, AWS RDS, Google Cloud SQL, etc. - **No Shared Databases** - Each user connects to their own database with their own credentials - **Real-time Validation** - Instant validation of connection parameters and database connectivity - **Encrypted Storage** - Database credentials are encrypted and only used for the user's session ### Core Database Tools - **`list_tables`** - List all tables in specified schemas - **`list_extensions`** - List all PostgreSQL extensions installed in the database - **`list_migrations`** - List all migrations applied to the database (supports common migration patterns) - **`apply_migration`** - Apply SQL migrations (DDL operations with tracking) - **`execute_sql`** - Execute any raw SQL statement for data queries or updates - **`query`** - Run read-only SQL queries (legacy tool) ### Additional Tools - **Steampipe Integration** - Table and plugin management tools - **GitHub Authentication** - OAuth-based user authentication - **Connection Testing** - Automatic database connection validation ## 🏗️ Architecture This MCP server is built on **Cloudflare Workers** and features: ### Authentication Flow 1. **User Authorization** - Users access the MCP server through their AI assistant (Claude, Cursor, etc.) 2. **Database Credentials Form** - Users fill out a secure web form with their database connection details 3. **GitHub OAuth** - Users authenticate via GitHub for identity verification 4. **Secure Connection** - Individual database pools are created per user with their provided credentials ### Key Features - **Remote deployment** on Cloudflare Workers or Railway - **No shared databases** - Each user connects to their own database - **Per-user database pools** with connection management - **Both Supabase and general PostgreSQL** database support - **Secure credential handling** with encryption and validation ## 📋 Prerequisites - Node.js 18+ - A Cloudflare account (for Cloudflare Workers deployment) - A GitHub account (for OAuth setup) - A PostgreSQL or Supabase database ## 🛠️ Setup ### 1. Clone and Install ```bash git clone <your-repo-url> cd database-mcp npm install ``` ### 2. GitHub OAuth Application Setup Create a GitHub OAuth App for authentication: 1. Go to GitHub → Settings → Developer Settings → [OAuth Apps](https://github.com/settings/apps) 2. Click **"New OAuth App"** 3. Fill in the details: - **Application name**: `Database MCP Server` - **Homepage URL**: `https://your-worker-name.your-account.workers.dev` - **Authorization callback URL**: `https://your-worker-name.your-account.workers.dev/callback` ### 3. Environment Variables Set up the following environment variables in your Cloudflare Worker: | Variable Name | Type | Description | Example | |---------------|------|-------------|---------| | `DATABASE_URL` | Secret | PostgreSQL connection string | `postgresql://user:password@host:5432/database` | | `GITHUB_CLIENT_ID` | Secret | GitHub OAuth App Client ID | `your_github_client_id` | | `GITHUB_CLIENT_SECRET` | Secret | GitHub OAuth App Client Secret | `your_github_client_secret` | | `COOKIE_ENCRYPTION_KEY` | Secret | Random key for cookie encryption | `openssl rand -hex 32` | | `ALLOWED_USERNAMES` | Secret | Comma-separated GitHub usernames | `user1,user2,user3` | #### Setting Secrets via Wrangler CLI: ```bash npx wrangler secret put DATABASE_URL npx wrangler secret put GITHUB_CLIENT_ID npx wrangler secret put GITHUB_CLIENT_SECRET npx wrangler secret put COOKIE_ENCRYPTION_KEY npx wrangler secret put ALLOWED_USERNAMES ``` ### 4. Deploy to Cloudflare Workers ```bash npm run deploy ``` ## 🔧 Usage ### Connecting to AI Clients #### Claude Desktop 1. Install the [mcp-remote proxy](https://www.npmjs.com/package/mcp-remote) 2. Update your Claude Desktop configuration: ```json { "mcpServers": { "database-mcp": { "command": "npx", "args": [ "mcp-remote", "https://your-worker-name.your-account.workers.dev/sse" ] } } } ``` #### Cursor AI Add to your MCP configuration: ```json { "mcpServers": { "database-mcp": { "url": "https://your-worker-name.your-account.workers.dev/sse" } } } ``` #### Cloudflare AI Playground 1. Go to https://playground.ai.cloudflare.com/ 2. Enter your MCP server URL: `https://your-worker-name.your-account.workers.dev/sse` 3. Authenticate with GitHub 4. Start using the database tools! ### Database Connection The server supports both **Supabase** and **general PostgreSQL** databases. Users provide their own database credentials through the `DATABASE_URL` environment variable. #### Supabase Connection String Format: ``` postgresql://postgres:[YOUR-PASSWORD]@db.[YOUR-PROJECT-REF].supabase.co:5432/postgres ``` #### General PostgreSQL Connection String Format: ``` postgresql://username:password@hostname:port/database_name ``` ## 🔍 Available Tools ### Core Database Operations #### `list_tables` Lists all tables in specified schemas. ``` Parameters: - schemas (optional): Array of schema names (default: ["public"]) ``` #### `list_extensions` Lists all PostgreSQL extensions installed in the database. ``` No parameters required. ``` #### `list_migrations` Lists all migrations applied to the database. Supports common migration table patterns: - `supabase_migrations.schema_migrations` - `public.schema_migrations` - `public.migrations` - And more... #### `apply_migration` Applies a SQL migration with automatic tracking. ``` Parameters: - name: Migration name in snake_case - query: SQL migration query (DDL operations only) ``` #### `execute_sql` Executes any raw SQL statement. ``` Parameters: - sql: The SQL statement to execute - read_only (optional): Execute in read-only mode (default: false) ``` #### `query` Runs read-only SQL queries (legacy tool). ``` Parameters: - sql: The SQL query to execute ``` ## 🚀 Deployment Options ### Cloudflare Workers (Recommended) 1. **Deploy**: `npm run deploy` 2. **Set secrets**: Use `wrangler secret put` commands 3. **Configure OAuth**: Update GitHub OAuth app URLs 4. **Test**: Visit your worker URL ### Railway (Alternative) 1. Connect your GitHub repository to Railway 2. Set environment variables in Railway dashboard 3. Deploy automatically on git push ## 🔒 Security - **OAuth Authentication**: Only authorized GitHub users can access the tools - **User Allowlist**: Configure specific GitHub usernames in `ALLOWED_USERNAMES` - **Connection Security**: All database connections use secure connection strings - **Read-only Options**: Tools support read-only execution modes - **Transaction Safety**: Automatic rollback on errors ## 🛠️ Development ### Local Development ```bash npm run dev ``` ### Type Checking ```bash npx tsc --noEmit ``` ### Generate Types ```bash npm run cf-typegen ``` ## 📚 Examples ### List all tables in public schema ```sql -- This will be executed via the list_tables tool -- No direct SQL needed, just call the tool through your AI client ``` ### Execute a data query ```sql -- Use execute_sql tool SELECT * FROM users WHERE created_at > '2024-01-01'; ``` ### Apply a migration ```sql -- Use apply_migration tool with name: "add_user_email_index" CREATE INDEX CONCURRENTLY idx_users_email ON users(email); ``` ## 🤝 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. ## 🆘 Troubleshooting ### Common Issues 1. **"Database pool not initialized"** - Check your `DATABASE_URL` environment variable - Ensure the connection string is valid 2. **"OAuth authentication failed"** - Verify GitHub OAuth app configuration - Check `GITHUB_CLIENT_ID` and `GITHUB_CLIENT_SECRET` 3. **"User not authorized"** - Add your GitHub username to `ALLOWED_USERNAMES` - Ensure the username is spelled correctly 4. **Connection timeout** - Check database server accessibility - Verify firewall settings - Ensure SSL/TLS configuration is correct ### Getting Help - Check the [Cloudflare Workers documentation](https://developers.cloudflare.com/workers/) - Review the [Model Context Protocol specification](https://modelcontextprotocol.io/) - Open an issue in this repository --- Built with ❤️ for the AI community. This MCP server bridges the gap between AI assistants and database management, making database operations accessible through natural language.