prsingh1982/mcp-server

MCP-Server – CRUD Service with SQLite Backing

Inspired by Medium article by Felix Pappe

This project demonstrates how to build a simple yet powerful MCP (Model Context Protocol) server that connects to a SQLite database and provides full CRUD (Create, Read, Update, Delete) functionality.
It’s designed as an instructional example that covers both the conceptual model and practical implementation of tool-exposing services and data access.

🎯 Why this project matters

Modern applications often need to expose data and operations in standardized, machine-readable ways. By combining:

• the MCP protocol (which lets clients request tools/data in a uniform interface), and
• a lightweight embedded database (SQLite) for persistence,

you get a pattern that is easy to adopt, extend and integrate.

Use-cases include:

• exposing internal data models to AI/agent systems
• building modular backend services that offer both data and operations
• learning how to implement a service-interface + data layer + tooling layer in one cohesive package

🧠 Key Concepts

Model Context Protocol (MCP)

At a high level, MCP defines how a host (e.g., an AI agent, assistant, or UI) interacts with a server which offers tools and/or resources. The server exposes endpoints or methods and the host can call them to get data, execute operations, etc.
In this project, the server offers tools to interact with persistent data via CRUD operations.

CRUD (Create, Read, Update, Delete)

The basic operations for persistent data stores:

• Create: Insert new records
• Read: Query or fetch existing records
• Update: Modify existing records
• Delete: Remove records
  The SQLite database provides the backing store for these operations.

SQLite

A lightweight, embedded relational database engine. Great for simple services, demos, proofs of concept, or situations where a full DB server is over-kill.
In this project the database is likely stored in a local file (e.g., data.db) and accessed via standard SQLite libraries.

Project Structure

mcp-server/
├── .gitignore
├── .python-version
├── README.md
├── main.py           # application entrypoint
├── pyproject.toml    # project metadata / build tool config
├── server.py         # MCP server implementation (CRUD + DB access)
└── uv.lock           # lockfile (tool-specific)

Note: file names above are taken directly from the repository. See main.py for how the application is started and server.py for the server and CRUD logic.

What each file does (quick reference)

• main.py — The repository entrypoint. Run this file to start the MCP server (it bootstraps the app, config, and server loop).
• server.py — Contains the server implementation: MCP tool registration, request handling, data access functions and CRUD operations. This is where the server connects to SQLite and exposes tools/endpoints.
• pyproject.toml — Declares project metadata and dependencies (used by modern Python tooling such as Poetry/PEP 517 tools). Use this to see required packages and tooling configuration.
• .python-version — Indicates the intended Python runtime version for the project environment (useful with pyenv).
• .gitignore — Files and folders that should not be committed (virtualenvs, DB files, caches, etc.).
• uv.lock — Tool-specific lock file included in the repo; keep in sync with the tool that created it.
• README.md — This documentation file.

Getting started (run the code)

1. Clone the repo

git clone https://github.com/prsingh1982/mcp-server.git
cd mcp-server

2. Create a virtual environment (recommended)

python -m venv .venv
source .venv/bin/activate    # macOS / Linux
.venv\Scripts\activate       # Windows

3. Install dependencies

Check pyproject.toml for dependency instructions. If pyproject.toml lists dependencies for a tool like Poetry, install via that tool; otherwise, install requirements using pip if a requirements.txt or an installable list is present. Example (if pyproject uses Poetry):

poetry install

Or, if you prefer pip and the project has a simple dependency list you can manually pip install the needed packages.

4. Start the server

python main.py

The server should start and bind to the address/port defined in the code. Check the console output from main.py for the exact host/port.

How the server works (conceptual overview)

• MCP server: The codebase implements a small MCP-style server (Model/Method/Tool Registry) that registers and exposes a set of tools or handlers. Clients invoke these tools to perform operations.
• SQLite persistence: The server uses a local SQLite database to persist data. SQLite is embedded (file-based) and requires no separate DB server — the database file is usually stored on disk (the code will create or open it).
• CRUD operations: The server implements Create, Read, Update, and Delete operations against the SQLite backing store. These are the primary “tools” a client uses to manipulate data.
• Entrypoint & server loop: main.py bootstraps configuration and starts the server loop. server.py contains the handlers for incoming requests and the logic to translate tool calls into DB operations and responses.

Usage (inspect the code for exact endpoints/tools)

The repository exposes tools and handlers in server.py. To understand what tools/endpoints are available and their payload formats:

1. Open server.py and look for the functions that register tools or define routes/handlers. Typical clues:

• Decorators, registration calls, or a tools dict/registry.
• Function names such as create_, get_, update_, delete_.

2. Read main.py to see how the server is started and whether it sets hosts, ports, or any security/config flags.

Example (generic): the server is likely to provide functions or endpoints to:

• Create a record (e.g., accepts JSON payload)
• Read/list records
• Update an existing record (by id)
• Delete a record (by id)

Because exact function names and payload schema live in server.py, consult that file for exact usage examples and payload contracts.

Database notes

• SQLite file: The DB file (commonly data.db or similar) may be created automatically the first time the server runs. Check server.py for the DB filename and any schema creation logic.
• Schema creation: Look in server.py for CREATE TABLE SQL or ORM/migration logic that initializes the schema. If present, the server will set up tables automatically or provide a helper function to initialize the DB.
• Backups & migrations: For production or multi-developer workflows, consider adding schema migration tooling (e.g., Alembic) and a plan for DB backups if you move away from SQLite.

Development tips

• Use the Python version in .python-version to match the repo author’s environment (pyenv users can rely on this).
• To iterate quickly:
  • Run python main.py and call the server tools with curl or a REST client (if the server exposes HTTP), or via the MCP client that the server expects.
  • Add logging statements in server.py to monitor incoming tool calls and SQL statements.
• Keep uv.lock and pyproject.toml in sync with your environment if you update dependencies.

Extending the project

You can extend the repository in several directions:

• Add explicit API docs — create an API.md or integrate OpenAPI/Swagger documentation if the server exposes HTTP endpoints.
• Add tests — add unit tests and integration tests around the CRUD functions and DB initialization (use pytest).
• Add CI — create GitHub Actions workflows to run tests on push/PR and validate code quality / formatting.
• Add migrations — add Alembic (or similar) to handle schema changes over time.
• Add authentication — protect CRUD tools with an authentication/authorization layer if exposing to untrusted clients.

Troubleshooting & Tips

• If the server fails to start, check the console output in main.py for exceptions. Typical issues include missing dependencies or permission issues when creating the SQLite file.
• If you cannot find the DB schema, search server.py for SQL strings like CREATE TABLE or for calls to an ORM constructor.
• If you plan to run multiple instances or deploy remotely, SQLite is not suitable for concurrent multi-writer setups — consider moving to PostgreSQL or another client/server RDBMS.