Readme.md•9.05 kB
# 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.