{"_id":"@amusphere/mcp-db","_rev":"4-1d77a41db2e672bcddb2708057d0f7e4","name":"@amusphere/mcp-db","dist-tags":{"latest":"0.4.0"},"versions":{"0.1.0":{"name":"@amusphere/mcp-db","version":"0.1.0","keywords":["mcp","model-context-protocol","database","sql","sqlite","postgresql","llm","ai-tools"],"author":{"name":"Shuto"},"license":"MIT","_id":"@amusphere/mcp-db@0.1.0","maintainers":[{"name":"amusphere","email":"shuto@amusphere.dev"}],"homepage":"https://github.com/amusphere/mcp-db#readme","bugs":{"url":"https://github.com/amusphere/mcp-db/issues"},"bin":{"mcp-db":"dist/index.js"},"dist":{"shasum":"a0f2b6900b5f1ba8acb97d6e722177c4982e36ef","tarball":"https://registry.npmjs.org/@amusphere/mcp-db/-/mcp-db-0.1.0.tgz","fileCount":11,"integrity":"sha512-W2c70HIvnHQvNbXFR+nvQYmqJnb0gldBRjKrtEQi5mX0vekVZrkHZxqbspyJHD6qajZwjOOqD6Bf75B2k3jFoA==","signatures":[{"sig":"MEUCIQD3JXALNH7ulJkLFzyN8/utxFfB1aXjvrEpeilJTwv6gAIgTR43S6GX4JVjaf/z3KH9GH7AKnFK6vtWoApbpNMHLhw=","keyid":"SHA256:DhQ8wR5APBvFHLF/+Tc+AYvPOdTpcIDqOhxsBHRwC7U"}],"unpackedSize":63113},"main":"dist/index.js","type":"module","engines":{"node":">=18.0.0"},"gitHead":"197b61207bdf2aa604d4c925079f801a74b1d685","scripts":{"dev":"tsx watch src/index.ts","lint":"eslint . --ext .ts","build":"tsc --project tsconfig.json","start":"node dist/index.js","prepare":"npm run build","typecheck":"tsc --noEmit"},"_npmUser":{"name":"amusphere","email":"shuto@amusphere.dev"},"repository":{"url":"git+https://github.com/amusphere/mcp-db.git","type":"git"},"_npmVersion":"10.9.2","description":"Model Context Protocol (MCP) server for secure database access. Query SQLite and PostgreSQL databases with AI assistants like Claude and Codex.","directories":{},"_nodeVersion":"22.17.1","dependencies":{"pg":"^8.11.5","dotenv":"^16.4.5","sqlite":"^4.2.1","fastify":"^4.27.2","sqlite3":"^5.1.7","@modelcontextprotocol/sdk":"^1.20.1"},"_hasShrinkwrap":false,"devDependencies":{"tsx":"^4.7.0","eslint":"^8.57.0","@types/pg":"^8.15.5","typescript":"^5.4.2","@types/node":"^20.11.30","eslint-config-prettier":"^9.1.0","@typescript-eslint/parser":"^7.1.1","@typescript-eslint/eslint-plugin":"^7.1.1"},"_npmOperationalInternal":{"tmp":"tmp/mcp-db_0.1.0_1760683443097_0.44631727619434747","host":"s3://npm-registry-packages-npm-production"}},"0.2.0":{"name":"@amusphere/mcp-db","version":"0.2.0","keywords":["mcp","model-context-protocol","database","sql","sqlite","postgresql","llm","ai-tools"],"author":{"name":"Shuto"},"license":"MIT","_id":"@amusphere/mcp-db@0.2.0","maintainers":[{"name":"amusphere","email":"shuto@amusphere.dev"}],"homepage":"https://github.com/amusphere/mcp-db#readme","bugs":{"url":"https://github.com/amusphere/mcp-db/issues"},"bin":{"mcp-db":"dist/index.js"},"dist":{"shasum":"3cbb1e000956a0e9ecf08bae296e0a0ed3947db7","tarball":"https://registry.npmjs.org/@amusphere/mcp-db/-/mcp-db-0.2.0.tgz","fileCount":11,"integrity":"sha512-ru/eW+x4LNL8KtMnl2N6ytordmklv44X9lyVlOzPBP6ulnUC8jixVm5lnIKWr4eoZXhg0uedCUkW4k/GRUb1oQ==","signatures":[{"sig":"MEUCID2SDggunFV1XBWExmbtspGxSjHqtuCTrSc0j8Moh07GAiEA9vPmXsLVRc6faGt/DuxWLUMN8OqhT1ewCN6qHeMZiMw=","keyid":"SHA256:DhQ8wR5APBvFHLF/+Tc+AYvPOdTpcIDqOhxsBHRwC7U"}],"unpackedSize":79025},"main":"dist/index.js","type":"module","engines":{"node":">=18.0.0"},"gitHead":"fa7b2169b4c0e468d2af398d7b327681c3eb4631","scripts":{"dev":"tsx watch src/index.ts","lint":"eslint . --ext .ts","build":"tsc --project tsconfig.json","start":"node dist/index.js","prepare":"npm run build","typecheck":"tsc --noEmit"},"_npmUser":{"name":"amusphere","email":"shuto@amusphere.dev"},"repository":{"url":"git+https://github.com/amusphere/mcp-db.git","type":"git"},"_npmVersion":"10.9.2","description":"Model Context Protocol (MCP) server for secure database access. Query SQLite and PostgreSQL databases with AI assistants like Claude and Codex.","directories":{},"_nodeVersion":"22.17.1","dependencies":{"pg":"^8.11.5","dotenv":"^16.4.5","sqlite":"^4.2.1","fastify":"^4.27.2","sqlite3":"^5.1.7","@modelcontextprotocol/sdk":"^1.20.1"},"_hasShrinkwrap":false,"devDependencies":{"tsx":"^4.7.0","eslint":"^8.57.0","@types/pg":"^8.15.5","typescript":"^5.4.2","@types/node":"^20.11.30","eslint-config-prettier":"^9.1.0","@typescript-eslint/parser":"^7.1.1","@typescript-eslint/eslint-plugin":"^7.1.1"},"_npmOperationalInternal":{"tmp":"tmp/mcp-db_0.2.0_1760688904793_0.7980969020902537","host":"s3://npm-registry-packages-npm-production"}},"0.3.0":{"name":"@amusphere/mcp-db","version":"0.3.0","keywords":["mcp","model-context-protocol","database","sql","sqlite","postgresql","mysql","mariadb","llm","ai-tools"],"author":{"name":"Shuto"},"license":"MIT","_id":"@amusphere/mcp-db@0.3.0","maintainers":[{"name":"amusphere","email":"shuto@amusphere.dev"}],"homepage":"https://github.com/amusphere/mcp-db#readme","bugs":{"url":"https://github.com/amusphere/mcp-db/issues"},"bin":{"mcp-db":"dist/index.js"},"dist":{"shasum":"c0d91fba82d20218eedf18cc24bd498cb558bb53","tarball":"https://registry.npmjs.org/@amusphere/mcp-db/-/mcp-db-0.3.0.tgz","fileCount":23,"integrity":"sha512-+TIEG2uATL1okVs2g3D9U1TlUbVZPT7EnGFea9s0ftFcKNxIfcp5p8lESXHsBfkAAGZKKsjUt2U/7I5NgBnMkg==","signatures":[{"sig":"MEUCIAv85mxNggTunST5PqbYL32pcw7kR4/p/ImuHVsc4EzVAiEAnkiponTmMkvPxh9D+ZHo3HXSYuZLxm6/7j0CqO75qkc=","keyid":"SHA256:DhQ8wR5APBvFHLF/+Tc+AYvPOdTpcIDqOhxsBHRwC7U"}],"unpackedSize":200598},"main":"dist/index.js","type":"module","engines":{"node":">=18.0.0"},"gitHead":"48341ba5618bb5dd1b5a3b86602f4b78f066b3f5","scripts":{"dev":"tsx watch src/index.ts","lint":"eslint . --ext .ts","test":"npx tsx tests/run-all-tests.ts","build":"tsc --project tsconfig.json","start":"node dist/index.js","prepare":"npm run build","typecheck":"tsc --noEmit","test:mysql":"MYSQL_URL=mysql://mcp:password@localhost:3306/mcp npx tsx tests/test-mysql.ts","test:docker":"docker compose run --rm test-runner","test:sqlite":"npx tsx tests/test-sqlite.ts","test:mariadb":"MARIADB_URL=mariadb://mcp:password@localhost:3307/mcp npx tsx tests/test-mariadb.ts","test:postgres":"POSTGRES_URL=postgresql://mcp:password@localhost:5432/mcp npx tsx tests/test-postgres.ts"},"_npmUser":{"name":"amusphere","email":"shuto@amusphere.dev"},"repository":{"url":"git+https://github.com/amusphere/mcp-db.git","type":"git"},"_npmVersion":"10.9.2","description":"Model Context Protocol (MCP) server for secure database access. Query SQLite, PostgreSQL, MySQL, and MariaDB databases with AI assistants like Claude and Codex.","directories":{},"_nodeVersion":"22.17.1","dependencies":{"pg":"^8.11.5","dotenv":"^16.4.5","mysql2":"^3.15.2","sqlite":"^4.2.1","fastify":"^4.27.2","sqlite3":"^5.1.7","@modelcontextprotocol/sdk":"^1.20.1"},"_hasShrinkwrap":false,"devDependencies":{"tsx":"^4.7.0","eslint":"^8.57.0","@types/pg":"^8.15.5","typescript":"^5.4.2","@types/node":"^20.11.30","eslint-config-prettier":"^9.1.0","@typescript-eslint/parser":"^7.1.1","@typescript-eslint/eslint-plugin":"^7.1.1"},"_npmOperationalInternal":{"tmp":"tmp/mcp-db_0.3.0_1760695647717_0.06670352537179092","host":"s3://npm-registry-packages-npm-production"}},"0.4.0":{"name":"@amusphere/mcp-db","version":"0.4.0","description":"Model Context Protocol (MCP) server for secure database access. Query SQLite, PostgreSQL, MySQL, and MariaDB databases with AI assistants like Claude and Codex.","main":"dist/src/index.js","types":"dist/src/index.d.ts","type":"module","bin":{"mcp-db":"dist/src/index.js"},"exports":{".":{"import":"./dist/src/index.js","types":"./dist/src/index.d.ts"},"./dist/*":{"import":"./dist/*","types":"./dist/*"}},"scripts":{"build":"tsc --project tsconfig.build.json","start":"node dist/src/index.js","dev":"tsx watch src/index.ts","lint":"eslint . --ext .ts","typecheck":"tsc --noEmit","prepare":"npm run build","test":"npx tsx tests/run-all-tests.ts","test:docker":"docker compose run --rm test-runner","test:sqlite":"npx tsx tests/test-sqlite.ts","test:postgres":"POSTGRES_URL=postgresql://mcp:password@localhost:5432/mcp npx tsx tests/test-postgres.ts","test:mysql":"MYSQL_URL=mysql://mcp:password@localhost:3306/mcp npx tsx tests/test-mysql.ts","test:mariadb":"MARIADB_URL=mariadb://mcp:password@localhost:3307/mcp npx tsx tests/test-mariadb.ts"},"author":{"name":"Shuto"},"license":"MIT","keywords":["mcp","model-context-protocol","database","sql","sqlite","postgresql","mysql","mariadb","llm","ai-tools"],"repository":{"type":"git","url":"git+https://github.com/amusphere/mcp-db.git"},"homepage":"https://github.com/amusphere/mcp-db#readme","bugs":{"url":"https://github.com/amusphere/mcp-db/issues"},"engines":{"node":">=18.0.0"},"dependencies":{"@modelcontextprotocol/sdk":"^1.20.1","dotenv":"^16.4.5","fastify":"^4.27.2","mysql2":"^3.15.2","pg":"^8.11.5","prom-client":"^15.1.3","sqlite":"^4.2.1","sqlite3":"^5.1.7"},"devDependencies":{"@types/node":"^20.11.30","@types/pg":"^8.15.5","@typescript-eslint/eslint-plugin":"^7.1.1","@typescript-eslint/parser":"^7.1.1","eslint":"^8.57.0","eslint-config-prettier":"^9.1.0","tsx":"^4.7.0","typescript":"^5.4.2"},"_id":"@amusphere/mcp-db@0.4.0","gitHead":"c357f0e053c92b0e9f1bccd29336c8f6708ec1c7","_nodeVersion":"22.17.1","_npmVersion":"10.9.2","dist":{"integrity":"sha512-ZwPS5cpSs5/jiqwmj1nYqTnhNh0m1OlF27ANZF6AgKVm5171G6jT+OkvLJjcihceANT3lX9W20Po57Mh5DKK9g==","shasum":"425065ec8cc94b375e9cc9c9e1544dc6947fffaf","tarball":"https://registry.npmjs.org/@amusphere/mcp-db/-/mcp-db-0.4.0.tgz","fileCount":43,"unpackedSize":220716,"signatures":[{"keyid":"SHA256:DhQ8wR5APBvFHLF/+Tc+AYvPOdTpcIDqOhxsBHRwC7U","sig":"MEYCIQC+ny7jCcYax/7g//DM8bwFOy5SIdsp96RatSnpf0rk0wIhAMxRmb0vDsfk8Eyq5YeN7tb7CujPpy3YuftNqimvR0Fp"}]},"_npmUser":{"name":"amusphere","email":"shuto@amusphere.dev"},"directories":{},"maintainers":[{"name":"amusphere","email":"shuto@amusphere.dev"}],"_npmOperationalInternal":{"host":"s3://npm-registry-packages-npm-production","tmp":"tmp/mcp-db_0.4.0_1760709978979_0.43664097902523924"},"_hasShrinkwrap":false}},"time":{"created":"2025-10-17T06:44:03.000Z","modified":"2025-10-17T14:06:19.399Z","0.1.0":"2025-10-17T06:44:03.299Z","0.2.0":"2025-10-17T08:15:04.959Z","0.3.0":"2025-10-17T10:07:27.899Z","0.4.0":"2025-10-17T14:06:19.207Z"},"bugs":{"url":"https://github.com/amusphere/mcp-db/issues"},"author":{"name":"Shuto"},"license":"MIT","homepage":"https://github.com/amusphere/mcp-db#readme","keywords":["mcp","model-context-protocol","database","sql","sqlite","postgresql","mysql","mariadb","llm","ai-tools"],"repository":{"type":"git","url":"git+https://github.com/amusphere/mcp-db.git"},"description":"Model Context Protocol (MCP) server for secure database access. Query SQLite, PostgreSQL, MySQL, and MariaDB databases with AI assistants like Claude and Codex.","maintainers":[{"name":"amusphere","email":"shuto@amusphere.dev"}],"readme":"# MCP Database Server\n\n[![npm version](https://img.shields.io/npm/v/@amusphere/mcp-db.svg)](https://www.npmjs.com/package/@amusphere/mcp-db)\n[![CI](https://github.com/amusphere/mcp-db/actions/workflows/ci.yml/badge.svg)](https://github.com/amusphere/mcp-db/actions/workflows/ci.yml)\n[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)\n\nA Model Context Protocol (MCP) server that provides secure database access for AI assistants and LLM-based tools. Query SQLite, PostgreSQL, MySQL, and MariaDB databases with built-in safety controls, query validation, and audit logging.\n\n## Features\n\n- 🔒 **Secure by Default**: Read-only mode with granular permission controls\n- 🗄️ **Multi-Database**: Support for SQLite, PostgreSQL, MySQL, and MariaDB\n- 🛡️ **SQL Validation**: Automatic query validation and injection prevention\n- 📊 **Table Allowlisting**: Restrict access to specific tables\n- ⏱️ **Query Timeouts**: Prevent long-running queries\n- 📝 **Audit Logging**: JSON-formatted operation logs\n- 🔌 **MCP Protocol**: Native stdio transport for AI assistants\n- 🌐 **HTTP Mode**: Optional REST API for legacy integrations\n\n## Supported MCP Clients\n\n- [Codex CLI](https://github.com/modelcontextprotocol/cli)\n- [Claude Desktop](https://claude.ai/download)\n- [Cline (VS Code Extension)](https://github.com/cline/cline)\n- Any MCP-compatible client\n\n## Quick Start\n\n### Installation\n\nThe easiest way to use this MCP server is via `npx` (no installation required):\n\n```bash\nnpx @amusphere/mcp-db\n```\n\n### Configuration for MCP Clients\n\nThis server is designed to let AI assistants dynamically specify database connections via the `db_url` parameter. You can start the server **without specifying a default database**, and the AI will provide the connection string when needed.\n\n#### Codex CLI\n\nAdd to your Codex configuration file (`~/.codex/mcp.toml` or similar):\n\n```toml\n[mcp_servers.mcp-db]\ncommand = \"npx\"\nargs = [\"-y\", \"@amusphere/mcp-db\"]\n```\n\nThe AI assistant will then specify the database URL in each tool call:\n```\nYou: \"Show me tables in my SQLite database at ./data/app.db\"\nAI: Uses db_url = \"sqlite:///./data/app.db\" in the tool call\n```\n\n#### Claude Desktop\n\nAdd to your Claude Desktop config (`~/Library/Application Support/Claude/claude_desktop_config.json` on macOS):\n\n```json\n{\n  \"mcpServers\": {\n    \"mcp-db\": {\n      \"command\": \"npx\",\n      \"args\": [\"-y\", \"@amusphere/mcp-db\"]\n    }\n  }\n}\n```\n\n#### Optional: Set a Default Database\n\nIf you want to set a default database (can still be overridden by the AI):\n\n```toml\n[mcp_servers.mcp-db]\ncommand = \"npx\"\nargs = [\"-y\", \"@amusphere/mcp-db\", \"--host\", \"sqlite:///./dev.db\"]\n```\n\n## Usage Examples\n\n### Recommended: Dynamic Database Selection\n\nStart the server without a default database and let the AI specify the connection:\n\n```bash\n# Start server (AI will provide db_url in each request)\nnpx @amusphere/mcp-db\n\n# With security controls\nnpx @amusphere/mcp-db --allow-writes --allowlist users,posts,comments\n\n# With custom limits\nnpx @amusphere/mcp-db --max-rows 100 --timeout 30\n```\n\n**User conversation examples:**\n- \"Show tables in sqlite:///./dev.db\"\n- \"Query the production database at postgresql://localhost/prod\"\n- \"Compare user counts between ./dev.db and ./prod.db\"\n\n### Alternative: Default Database\n\nIf you work primarily with one database, you can set a default (can still be overridden):\n\n```bash\n# SQLite default\nnpx @amusphere/mcp-db --host sqlite:///./dev.db\n\n# PostgreSQL default\nnpx @amusphere/mcp-db --host postgresql://user:password@localhost:5432/mydb\n\n# With allowlist for default database\nnpx @amusphere/mcp-db \\\n  --host sqlite:///./dev.db \\\n  --allowlist users,posts,comments\n```\n\n**User conversation examples:**\n- \"Show me the tables\" (uses default database)\n- \"Now check the other database at ./other.db\" (overrides default)\n\n### Local Development\n\nClone and build from source:\n\n```bash\ngit clone https://github.com/amusphere/mcp-db.git\ncd mcp-db\nnpm install\nnpm run build\nnpm start -- --host sqlite:///./dev.db\n```\n\nDevelopment mode with hot-reload:\n\n```bash\nnpm run dev\n```\n\n### Testing\n\nComprehensive tests are available for all supported databases. See [tests/README.md](tests/README.md) for detailed testing documentation.\n\n**Quick test commands:**\n\n```bash\n# Run all tests (requires Docker)\nnpm test\n\n# Run individual database tests\nnpm run test:sqlite      # No Docker required\nnpm run test:postgres    # Requires PostgreSQL container\nnpm run test:mysql       # Requires MySQL container\nnpm run test:mariadb     # Requires MariaDB container\n```\n\n**Docker-based testing:**\n\n```bash\n# Run all tests in Docker (recommended - auto cleanup)\nnpm run test:docker\n\n# Or manually manage containers\ndocker compose up -d postgres mysql mariadb  # Start databases\nnpm test                                     # Run tests\ndocker compose down -v                       # Stop and clean up\n```\n\n### HTTP Server Mode (Legacy)\n\nFor backwards compatibility with HTTP-based integrations:\n\n```bash\nnpx @amusphere/mcp-db --host sqlite:///./dev.db --http-mode --port 8080\n```\n\nThis exposes REST endpoints at `http://localhost:8080/tools/*` for non-MCP clients.\n\n## Configuration Reference\n\n### Command Line Arguments\n\n| Argument | Description | Default |\n|----------|-------------|---------|\n| `--host <url>` | Optional default database URL (can be overridden by AI via `db_url` parameter) | None |\n| `--allow-writes` | Enable INSERT/UPDATE/DELETE operations | `false` |\n| `--allow-ddl` | Enable CREATE/ALTER/DROP operations | `false` |\n| `--allowlist <tables>` | Comma-separated list of allowed tables (applies to all databases) | All tables |\n| `--max-rows <number>` | Maximum rows to return for SELECT queries | `500` |\n| `--timeout <seconds>` | Query timeout in seconds | `20` |\n| `--http-mode` | Run as HTTP server instead of MCP stdio | `false` |\n| `--port <number>` | Port for HTTP mode | `8080` |\n| `--require-api-key` | Require X-API-Key header (HTTP mode only) | `false` |\n| `--api-key <value>` | Expected API key value | - |\n\n**Note:** The `--host` parameter is optional. If not specified, the AI must provide `db_url` in every tool call. If specified, it serves as a default that can be overridden per-request.\n\n### Database URL Formats\n\n**SQLite:**\n```\nsqlite:///./path/to/database.db    # Relative path\nsqlite:////absolute/path/to/db.db  # Absolute path\nsqlite:///:memory:                 # In-memory database\n```\n\n**PostgreSQL:**\n```\npostgresql://username:password@host:port/database\npostgresql://localhost/mydb        # Local with defaults\n```\n\n**MySQL:**\n```\nmysql://username:password@host:port/database\nmysql://root:password@localhost:3306/mydb\n```\n\n**MariaDB:**\n```\nmariadb://username:password@host:port/database\nmariadb://root:password@localhost:3306/mydb\n```\n\nNote: MariaDB URLs are automatically converted to MySQL format internally.\n\n### Environment Variables\n\nAll command-line arguments can also be set via environment variables (command-line args take precedence):\n\n| Environment Variable | Equivalent Argument |\n|---------------------|---------------------|\n| `DB_URL` | `--host` |\n| `ALLOW_WRITES` | `--allow-writes` |\n| `ALLOW_DDL` | `--allow-ddl` |\n| `ALLOWLIST_TABLES` | `--allowlist` |\n| `MAX_ROWS` | `--max-rows` |\n| `QUERY_TIMEOUT_SEC` | `--timeout` |\n| `PORT` | `--port` |\n| `REQUIRE_API_KEY` | `--require-api-key` |\n| `API_KEY` | `--api-key` |\n\n**Example with environment variables:**\n\n```bash\nexport DB_URL=\"postgresql://user:pass@localhost:5432/mydb\"\nexport ALLOW_WRITES=\"true\"\nexport ALLOWLIST_TABLES=\"users,posts,comments\"\nnpx @amusphere/mcp-db\n```\n\n## Available MCP Tools\n\nThis server provides four MCP tools for database operations:\n\n### `db_tables`\nList all tables in the database.\n\n**Parameters:**\n- `db_url` (required/optional): Database URL. Required if no default `--host` is set, otherwise optional to override\n- `schema` (optional): Filter by schema (PostgreSQL only)\n\n**Example - Dynamic database selection:**\n```json\n{\n  \"db_url\": \"sqlite:///./data/myapp.db\"\n}\n```\n\n**Example - PostgreSQL with schema:**\n```json\n{\n  \"db_url\": \"postgresql://user:pass@localhost:5432/mydb\",\n  \"schema\": \"public\"\n}\n```\n\n### `db_describe_table`\nGet column information for a specific table.\n\n**Parameters:**\n- `table` (required): Table name to describe\n- `db_url` (required/optional): Database URL. Required if no default `--host` is set, otherwise optional to override\n- `schema` (optional): Schema name (PostgreSQL only)\n\n**Example - Dynamic database selection:**\n```json\n{\n  \"db_url\": \"sqlite:///./users.db\",\n  \"table\": \"users\"\n}\n```\n\n**Example - With schema (PostgreSQL):**\n```json\n{\n  \"db_url\": \"postgresql://localhost/mydb\",\n  \"table\": \"users\",\n  \"schema\": \"public\"\n}\n```\n\n### `db_execute`\nExecute a SQL statement with safety controls.\n\n**Parameters:**\n- `sql` (required): SQL statement to execute\n- `db_url` (required/optional): Database URL. Required if no default `--host` is set, otherwise optional to override\n- `args` (optional): Named parameters (use `:param` syntax in SQL)\n- `allow_write` (optional): Must be `true` for write operations\n- `row_limit` (optional): Override default max rows\n\n**Example - Dynamic query with parameters:**\n```json\n{\n  \"db_url\": \"sqlite:///./data/app.db\",\n  \"sql\": \"SELECT * FROM users WHERE status = :status LIMIT 10\",\n  \"args\": {\n    \"status\": \"active\"\n  }\n}\n```\n\n**Example - Cross-database query:**\n```json\n{\n  \"db_url\": \"postgresql://user:pass@prod-server:5432/analytics\",\n  \"sql\": \"SELECT COUNT(*) as total FROM events WHERE date >= :start_date\",\n  \"args\": {\n    \"start_date\": \"2024-01-01\"\n  }\n}\n```\n\n### `db_explain`\nGet query execution plan and performance information using EXPLAIN.\n\n**Parameters:**\n- `sql` (required): SQL query to analyze (typically a SELECT statement)\n- `db_url` (required/optional): Database URL. Required if no default `--host` is set, otherwise optional to override\n- `args` (optional): Named parameters (use `:param` syntax in SQL)\n- `analyze` (optional): Run EXPLAIN ANALYZE to get actual execution statistics (executes the query)\n\n**Example - Basic query plan (SQLite):**\n```json\n{\n  \"db_url\": \"sqlite:///./data/app.db\",\n  \"sql\": \"SELECT * FROM users WHERE email = :email\",\n  \"args\": {\n    \"email\": \"user@example.com\"\n  }\n}\n```\n\n**Example - Performance analysis (PostgreSQL):**\n```json\n{\n  \"db_url\": \"postgresql://localhost/mydb\",\n  \"sql\": \"SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name\",\n  \"analyze\": true\n}\n```\n\n**Use cases:**\n- Identify slow queries and missing indexes\n- Analyze JOIN performance and query optimization opportunities\n- Compare execution plans between databases\n- Verify query efficiency before deploying to production\n\n## How AI Assistants Use These Tools\n\nThis server is designed for **dynamic database connections**. AI assistants specify the database URL in each request, allowing you to work with multiple databases seamlessly.\n\n### Example Conversations\n\n**Working with SQLite:**\n```\nYou: \"Connect to my SQLite database at ./data/users.db and show me all tables\"\nAI: Calls db_tables with db_url=\"sqlite:///./data/users.db\"\n```\n\n**Switching between databases:**\n```\nYou: \"Now check the production database at /var/lib/app/prod.db\"\nAI: Calls db_tables with db_url=\"sqlite:////var/lib/app/prod.db\"\n\nYou: \"And also show me tables in the PostgreSQL analytics database\"\nAI: Calls db_tables with db_url=\"postgresql://user:pass@localhost:5432/analytics\"\n```\n\n**Natural language queries:**\n```\nYou: \"How many users are in the SQLite database at ./users.db?\"\nAI: Calls db_execute with:\n    - db_url=\"sqlite:///./users.db\"\n    - sql=\"SELECT COUNT(*) FROM users\"\n```\n\n### Supported Operations\n\nWhen you connect an AI assistant (like Claude or Codex) to this MCP server, it can:\n\n1. **Connect to any database dynamically**: Specify different databases in natural language\n2. **Explore database structure**: \"What tables are in database X?\"\n3. **Understand table schemas**: \"Show me the columns in the users table from database Y\"\n4. **Query data**: \"How many active users in the production database?\"\n5. **Analyze query performance**: \"Explain the execution plan for this query\"\n6. **Compare across databases**: \"Compare user counts between dev.db and prod.db\"\n7. **Optimize queries**: \"Find slow queries and suggest indexes\"\n\nThe AI assistant will automatically extract the database path/URL from your request and use the appropriate tool with the correct `db_url` parameter.\n\n## Configuration Reference\n\n1. **Default is READ-ONLY**: Write and DDL operations require explicit enabling\n2. **Use allowlists**: Restrict access to specific tables with `--allowlist`\n3. **Set query limits**: Use `--max-rows` and `--timeout` to prevent resource exhaustion\n4. **Named parameters**: Always use `:param` syntax to avoid SQL injection\n5. **Audit logging**: All operations are logged to stderr in JSON format\n\n## Security Best Practices\n\n1. **Default is READ-ONLY**: Write and DDL operations require explicit enabling\n2. **Use allowlists**: Restrict access to specific tables with `--allowlist`\n3. **Set query limits**: Use `--max-rows` and `--timeout` to prevent resource exhaustion\n4. **Named parameters**: Always use `:param` syntax to avoid SQL injection\n5. **Audit logging**: All operations are logged to stderr in JSON format\n6. **Separate credentials**: Use read-only database users when possible\n7. **Network security**: For remote databases, use SSL/TLS connections\n\n### Audit Logs\n\nAll database operations are logged to stderr in JSON format:\n\n```json\n{\n  \"timestamp\": \"2024-01-17T10:30:45.123Z\",\n  \"tool\": \"db_execute\",\n  \"category\": \"read\",\n  \"duration_ms\": 42,\n  \"rowcount\": 10,\n  \"sql\": \"SELECT * FROM users LIMIT 10\"\n}\n```\n\n## Troubleshooting\n\n### Connection Issues\n\n**SQLite file not found:**\n```bash\n# Use absolute path\nnpx @amusphere/mcp-db --host sqlite:////absolute/path/to/db.db\n\n# Or relative from current directory\nnpx @amusphere/mcp-db --host sqlite:///./relative/path/db.db\n```\n\n**PostgreSQL connection refused:**\n- Verify the database is running: `pg_isready -h localhost`\n- Check connection string format\n- Ensure network access (firewall, security groups)\n\n### Permission Errors\n\n**\"Write operations disabled\":**\n```bash\n# Enable writes (both server AND request must allow)\nnpx @amusphere/mcp-db --host sqlite:///./dev.db --allow-writes\n```\n\n**\"Table not allowlisted\":**\n```bash\n# Add tables to allowlist\nnpx @amusphere/mcp-db --host sqlite:///./dev.db --allowlist users,posts\n```\n\n### Performance Issues\n\n**Queries timing out:**\n```bash\n# Increase timeout\nnpx @amusphere/mcp-db --host sqlite:///./dev.db --timeout 60\n```\n\n**Too much data returned:**\n```bash\n# Reduce row limit\nnpx @amusphere/mcp-db --host sqlite:///./dev.db --max-rows 100\n```\n\n### MCP Client Configuration\n\n**Server not appearing in Claude Desktop:**\n1. Check config file location: `~/Library/Application Support/Claude/claude_desktop_config.json` (macOS)\n2. Verify JSON syntax is valid\n3. Restart Claude Desktop completely\n\n**Codex not connecting:**\n1. Check `~/.codex/mcp.toml` syntax\n2. Ensure `npx` is in PATH\n3. Try running command manually first\n\n## Docker Deployment\n\n### Using Docker Compose\n\n```bash\n# Start the server with PostgreSQL\ndocker-compose up --build\n\n# Access at http://localhost:8080 (HTTP mode)\n```\n\n### Standalone Container\n\n```bash\n# Build\ndocker build -t mcp-db:latest .\n\n# Run with SQLite (mount volume for persistence)\ndocker run --rm \\\n  -v $(pwd)/data:/data \\\n  -e DB_URL='sqlite:////data/mydb.db' \\\n  mcp-db:latest\n\n# Run with PostgreSQL\ndocker run --rm \\\n  -e DB_URL='postgresql://user:pass@host:5432/db' \\\n  -e ALLOW_WRITES=false \\\n  mcp-db:latest\n```\n\n## Contributing\n\nContributions are welcome! Please:\n\n1. Fork the repository\n2. Create a feature branch (`git checkout -b feature/amazing-feature`)\n3. Commit your changes (`git commit -m 'Add amazing feature'`)\n4. Push to the branch (`git push origin feature/amazing-feature`)\n5. Open a Pull Request\n\n### Development Setup\n\n```bash\ngit clone https://github.com/amusphere/mcp-db.git\ncd mcp-db\nnpm install\nnpm run dev        # Start development server with hot-reload\nnpm run lint       # Run ESLint\nnpm run typecheck  # Run TypeScript type checking\nnpm test           # Run all tests (requires Docker)\nnpm run test:docker # Run tests in Docker (recommended)\n```\n\n### CI/CD Pipeline\n\nAll pull requests automatically run through our CI/CD pipeline:\n\n- ✅ **Security Scanning**: Gitleaks (secrets) and Trivy (vulnerabilities)\n- ✅ **Code Quality**: ESLint and TypeScript type checking\n- ✅ **Build Verification**: Transpile TypeScript to JavaScript\n- ✅ **Comprehensive Testing**: All database tests (SQLite, PostgreSQL, MySQL, MariaDB)\n\nNo additional setup required - all security scans use GitHub's built-in tokens.\n\n## License\n\nMIT License - see [LICENSE](LICENSE) file for details\n\n## Support\n\n- 📖 [Documentation](https://github.com/amusphere/mcp-db)\n- 🐛 [Issue Tracker](https://github.com/amusphere/mcp-db/issues)\n- 💬 [Discussions](https://github.com/amusphere/mcp-db/discussions)\n\n## Related Projects\n\n- [Model Context Protocol](https://modelcontextprotocol.io/) - Official MCP documentation\n- [MCP Servers](https://github.com/modelcontextprotocol/servers) - Collection of MCP servers\n- [Claude Desktop](https://claude.ai/download) - AI assistant with MCP support\n\n---\n\n**Made with ❤️ for the MCP community**\n","readmeFilename":"README.md"}