# Multi-Database CRUD MCP Server

This learning project exposes typed MCP tools for product CRUD operations. SQLite works by default, PostgreSQL is supported through configuration, and MySQL can be added later without changing the MCP tools.

## Architecture

```text
User -> AI/MCP client -> MCP tools (server.py)
                         -> database adapter (database.py)
                            -> SQLite or PostgreSQL
                         <- structured result <- database
```

The AI never connects directly to the database or generates unrestricted SQL. It selects a narrow tool such as `create_product`, and the server validates the input before the database adapter executes parameterized SQL.

## Available MCP tools

- `database_health`
- `create_product`
- `get_product`
- `list_products`
- `update_product`
- `delete_product`

## Start with SQLite

SQLite is included with Python. It does not require Docker, a database server, username, or password.

1. Install dependencies:

   ```powershell
   uv sync
   ```

2. Select SQLite in PowerShell:

   ```powershell
   $env:DB_BACKEND="sqlite"
   $env:SQLITE_PATH="data/mcp_demo.db"
   ```

3. Run the tests:

   ```powershell
   uv run pytest
   ```

4. Start MCP Inspector:

   ```powershell
   uv run mcp dev src/mcp_database/server.py
   ```

The first database tool call automatically creates `data/mcp_demo.db` and its `products` table. Call `database_health`; it should return:

```json
{"connected": true, "backend": "sqlite"}
```

Then try `create_product`, `list_products`, `update_product`, and `delete_product` in Inspector.

## Connect an MCP client to SQLite

Use an absolute path for the project and SQLite file:

```json
{
  "mcpServers": {
    "database-crud": {
      "command": "uv",
      "args": [
        "--directory",
        "C:\\absolute\\path\\to\\MCP_Database",
        "run",
        "mcp-database"
      ],
      "env": {
        "DB_BACKEND": "sqlite",
        "SQLITE_PATH": "C:\\absolute\\path\\to\\MCP_Database\\data\\mcp_demo.db"
      }
    }
  }
}
```

The selected database is controlled by `DB_BACKEND`. The older `mcp-postgres` command remains available as a compatibility alias.

## Switch to PostgreSQL later

The MCP tools require no code changes. Start PostgreSQL and change the environment variables.

1. Start the provided PostgreSQL container:

   ```powershell
   docker compose up -d
   docker compose ps
   ```

2. Select PostgreSQL:

   ```powershell
   $env:DB_BACKEND="postgresql"
   $env:DATABASE_URL="postgresql://mcp_user:mcp_password@localhost:5432/mcp_demo"
   uv run mcp dev src/mcp_database/server.py
   ```

The container runs `sql/init.sql` on its first startup. `SQLITE_PATH` is ignored in PostgreSQL mode.

For an MCP client, replace its environment section with:

```json
"env": {
  "DB_BACKEND": "postgresql",
  "DATABASE_URL": "postgresql://mcp_user:mcp_password@localhost:5432/mcp_demo"
}
```

## MySQL in the future

MySQL is not implemented yet. `DB_BACKEND=mysql` deliberately returns a clear configuration error. To add it:

1. Add `PyMySQL` or `mysql-connector-python` to `pyproject.toml`.
2. Add a MySQL connection context manager in `database.py`.
3. Implement the same six data functions using MySQL parameterized queries.
4. Use MySQL schema syntax such as `AUTO_INCREMENT`.
5. Add MySQL integration tests.

`server.py` will remain unchanged because the backend-specific code is isolated in `database.py`.

## Important SQL differences

| Concern | SQLite | PostgreSQL | MySQL |
|---|---|---|---|
| Driver | Built-in `sqlite3` | `psycopg` | Future driver |
| Placeholder | `?` | `%s` | Usually `%s` |
| Generated ID | `AUTOINCREMENT` | `IDENTITY` | `AUTO_INCREMENT` |
| Server needed | No | Yes | Yes |
| Current status | Implemented | Implemented | Planned |

The adapter handles SQLite and PostgreSQL differences while presenting the same functions to the MCP server.

## Project files

- `src/mcp_database/server.py`: stable MCP contract and validation.
- `src/mcp_database/database.py`: backend selection and database-specific SQL.
- `sql/init.sql`: PostgreSQL schema and sample records.
- `compose.yaml`: local PostgreSQL service.
- `tests/test_server.py`: MCP tool unit tests.
- `tests/test_sqlite_database.py`: real SQLite CRUD integration tests.
- `.env.example`: configuration examples. A plain `.env` is not loaded automatically; supply variables through PowerShell or MCP client configuration.

## How to explain it to a manager

“This proof of concept places a controlled MCP service between an AI assistant and a database. Instead of giving the model unrestricted SQL access, it exposes six typed and auditable operations. A database adapter lets developers use zero-setup SQLite locally and move to PostgreSQL later without changing the MCP contract.”

For production, add authentication, user-level authorization, secret management, audit logging, connection pooling, rate limiting, migrations, monitoring, backups, and separate read/write database roles.

## Troubleshooting

- `DATABASE_URL is required for PostgreSQL`: set it when `DB_BACKEND=postgresql`.
- SQLite file missing: call any database tool once; it is created automatically.
- PostgreSQL connection refused: verify `docker compose ps` reports a healthy container.
- Inspector port 6274 is occupied: close the older Inspector terminal/session before starting another.
- Tools do not appear: use an absolute project path and restart the MCP client.

This project uses the official [MCP Python SDK](https://github.com/modelcontextprotocol/python-sdk).