# Relational DB Seeder MCP Server

An intelligent, schema-aware, fully asynchronous **Model Context Protocol (MCP)** server that enables LLMs (like Claude) to inspect database structures and seed relational databases with custom, semantic data while preserving foreign key integrity in a single payload.

This is a developer tool designed to bridge the gap between AI reasoning and database populating. Instead of requiring the LLM to make multiple slow, sequential tool calls to resolve auto-generated IDs, the server parses constraints, sorts tables topologically, and maps generated IDs to foreign keys automatically.

---

## 🚀 Key Features

* **Multi-Database Support**: Out-of-the-box support for both **SQLite** (local files) and **PostgreSQL** databases, configured on startup via environment variables.
* **Automatic Relative Path Resolution**: Any relative SQLite path (e.g. `sqlite:///employee.db`) is automatically resolved relative to the server's project root directory, keeping database files in a predictable location regardless of where the client runs.
* **Relational Graph Insertion**: Seeds complex tables with relationships in one batch. LLMs can reference parent rows using labels like `ref:users:alice_temp` and the engine automatically resolves them to real database-generated primary keys.
* **Fully Asynchronous**: High-performance database operations powered by `aiosqlite` and the modern `psycopg` (v3) async library.
* **Schema Auto-Discovery**: Queries database catalogs (`information_schema` and SQLite pragma lists) to dump tables, columns, constraints, and relationships for the LLM to reason about.
* **Credential Masking**: Built-in security that automatically hides database passwords and user credentials in server logs and returned status states.

---

## 🛠️ Architecture

```
db_seeder/
├── adapters/
│   ├── __init__.py
│   ├── base.py           # Base connection interface using abc.ABC
│   ├── sqlite.py         # SQLite async adapter using aiosqlite
│   └── postgres.py       # PostgreSQL async adapter using psycopg
├── core/
│   ├── __init__.py
│   ├── graph.py          # Topological sort & cycle detector
│   └── seeder.py         # Relational graph insertion engine
└── tests/                # Unit & integration tests
```

---

## 📦 Installation & Setup

Ensure you have [uv](https://github.com/astral-sh/uv) or standard Python 3.14+ installed.

### 1. Clone & Install Dependencies
```bash
git clone https://github.com/yourusername/DB_Seeder.git
cd DB_Seeder
uv sync
```

### 2. Run the MCP Server Locally
You can run the server in development mode using `fastmcp`:
```bash
uv run fastmcp dev server.py
```

### 3. Add to Claude Desktop Configuration
To connect this server to your Claude Desktop application, edit your `claude_desktop_config.json`:

* **MacOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`
* **Windows**: `%APPDATA%\Claude\claude_desktop_config.json`
* **Linux**: `~/.config/Claude/claude_desktop_config.json`

Add the server connection (defining the `DATABASE_URL` for your target SQLite or Postgres database):

```json
{
  "mcpServers": {
    "db-seeder": {
      "command": "uv",
      "args": [
        "--directory",
        "/absolute/path/to/DB_Seeder",
        "run",
        "server.py"
      ],
      "env": {
        "DATABASE_URL": "sqlite:///employee.db"
      }
    }
  }
}
```

---

## 🔧 Expose Tools

The server registers the following asynchronous MCP tools with the client:

| Tool | Parameters | When to Use | Description |
|---|---|---|---|
| `get_database_status` | None | At the start of a session or when checking DB configuration. | Returns active database type, table list, and connection details (credentials masked). |
| `get_schema` | None | Before writing queries or generating mock data payloads. | Dumps all columns, types, nullability, primary keys, and foreign keys. |
| `insert_graph` | `payload` | **Preferred tool** for inserting records and database seeding. | Inserts a relational dataset, mapping and resolving parent keys. |
| `execute_query` | `query` | For SELECT checks, DDL schemas (CREATE, ALTER), or updates. | Runs arbitrary SQL statements on the connected database. |

---

## 📊 Relational Graph Seeding Example

When seeding, the LLM agent sends a payload where parent tables have a `__temp_id` and child tables refer to them using the format `ref:parent_table:temp_id`.

**Payload sent by LLM:**
```json
{
  "departments": [
    {
      "__temp_id": "d1",
      "dept_name": "Engineering"
    },
    {
      "__temp_id": "d2",
      "dept_name": "Sales"
    }
  ],
  "employees": [
    {
      "name": "Alice Smith",
      "email": "alice@company.com",
      "number": "555-1234",
      "salary": 85000,
      "dept_id": "ref:departments:d1"
    },
    {
      "name": "Bob Johnson",
      "email": "bob@company.com",
      "number": "555-5678",
      "salary": 72000,
      "dept_id": "ref:departments:d2"
    }
  ]
}
```

**Seeder Execution Steps:**
1. Detects that `employees` depends on `departments` via the foreign key constraint on `dept_id`.
2. Sorts the insertion order: `departments` first, then `employees`.
3. Inserts departments, capturing their database-generated primary keys (e.g. `Engineering -> 1`, `Sales -> 2`).
4. Replaces `"ref:departments:d1"` with `1` and `"ref:departments:d2"` with `2` in the employees payload.
5. Inserts employees, guaranteeing that no foreign key constraint violations occur.

---

## 🧪 Testing

The project has robust unit and integration tests written using `pytest` and `pytest-asyncio`. Run them with:

```bash
PYTHONPATH=. uv run pytest
```