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

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:

   uv sync
   

2. Select SQLite in PowerShell:

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

3. Run the tests:

   uv run pytest
   

4. Start MCP Inspector:

   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:

{"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:

{
  "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:

   docker compose up -d
   docker compose ps
   

2. Select PostgreSQL:

   $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:

"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.