# codemode-sqlite-mcp

A high-performance SQLite MCP (Model Context Protocol) server with an experimental code generation approach called **Codemode** that enables LLMs to execute database operations through generated Go code rather than sequential tool calls.

## Quickstart

### Installation

```bash
go install github.com/imran31415/codemode-sqlite-mcp@latest
```

Or build from source:

```bash
git clone https://github.com/imran31415/codemode-sqlite-mcp.git
cd codemode-sqlite-mcp
go build -o codemode-sqlite-mcp .
```

### Basic Usage

**As an MCP Server (for Claude Desktop):**

```bash
codemode-sqlite-mcp --mode=stdio --db=./mydata.db
```

**As an HTTP Server:**

```bash
codemode-sqlite-mcp --mode=http --port=8084 --db=./mydata.db
```

**As an Interactive Codemode Agent:**

```bash
export ANTHROPIC_API_KEY="your-api-key"
codemode-sqlite-mcp --mode=codemode --db=./mydata.db
```

### Claude Desktop Configuration

Add to your Claude Desktop MCP configuration file:

```json
{
  "mcpServers": {
    "codemode-sqlite": {
      "command": "codemode-sqlite-mcp",
      "args": ["--mode=stdio", "--db=/path/to/database.db"]
    }
  }
}
```

---

## Overview

This package provides two distinct approaches for LLM-database interaction:

1. **Standard MCP Mode**: Exposes SQLite operations as MCP tools that an LLM calls sequentially
2. **Codemode**: The LLM generates complete Go programs that execute database operations in a single pass

The Codemode approach demonstrates significant efficiency gains in token usage and latency for complex database operations.

## Architecture

```
┌─────────────────────────────────────────────────────────────┐
│                    codemode-sqlite-mcp                      │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  ┌─────────────┐   ┌─────────────┐   ┌─────────────────┐   │
│  │   MCP       │   │  Codemode   │   │    SQLite       │   │
│  │   Server    │   │    Agent    │   │    Tools        │   │
│  │             │   │             │   │                 │   │
│  │  - stdio    │   │  - LLM API  │   │  - db_info      │   │
│  │  - http     │   │  - Code Gen │   │  - list_tables  │   │
│  │             │   │  - Executor │   │  - query        │   │
│  └──────┬──────┘   └──────┬──────┘   │  - CRUD ops     │   │
│         │                 │          └────────┬────────┘   │
│         └────────┬────────┘                   │            │
│                  │                            │            │
│           ┌──────▼────────────────────────────▼──────┐     │
│           │              Tool Registry               │     │
│           │    (Unified interface for all tools)     │     │
│           └──────────────────────────────────────────┘     │
│                              │                             │
│                    ┌─────────▼─────────┐                   │
│                    │   SQLite Database │                   │
│                    │  (modernc.org/    │                   │
│                    │   sqlite - pure   │                   │
│                    │   Go, no CGO)     │                   │
│                    └───────────────────┘                   │
└─────────────────────────────────────────────────────────────┘
```

## Available Tools

The package exposes 8 SQLite operations via MCP:

| Tool | Description |
|------|-------------|
| `db_info` | Get database metadata (path, size, table count) |
| `list_tables` | List all tables in the database |
| `get_table_schema` | Get column definitions for a table |
| `create_record` | Insert a new record |
| `read_records` | Query records with filtering and pagination |
| `update_records` | Update records matching conditions |
| `delete_records` | Delete records matching conditions |
| `query` | Execute arbitrary SQL with parameterized values |

## Codemode: Code Generation Approach

### How It Works

Instead of making multiple tool calls, the Codemode agent:

1. Receives a natural language task (e.g., "Find the top 5 customers by spending")
2. Generates a complete Go program that accomplishes the task
3. Executes the program in a sandboxed interpreter (yaegi)
4. Returns the output

The generated code has access to a `registryCall` function that invokes database tools:

```go
package main

import "fmt"

func main() {
    result, err := registryCall("query", map[string]interface{}{
        "sql": `SELECT c.Name, SUM(i.Total) as TotalSpent
                FROM customers c
                JOIN invoices i ON c.CustomerId = i.CustomerId
                GROUP BY c.CustomerId
                ORDER BY TotalSpent DESC
                LIMIT 5`,
    })
    if err != nil {
        fmt.Println("Error:", err)
        return
    }

    // Process and format results...
}
```

### Why Code Generation?

Traditional MCP tool calling requires multiple round-trips between the LLM and the tool server:

```
LLM → list_tables → result → LLM → get_schema → result → LLM → query → result → LLM
```

Each round-trip adds latency and token overhead. Complex operations may require 5-15 tool calls.

With Codemode, the LLM reasons about the entire task upfront and generates a single program:

```
LLM → generates code → execute once → result
```

### Benchmark Results

Testing on complex multi-table queries (Chinook database):

| Metric | Standard MCP | Codemode | Improvement |
|--------|--------------|----------|-------------|
| Success Rate | 100% | 100% | - |
| Avg Latency | 18.8s | 9.0s | 2.1x faster |
| Avg Tokens | 10,940 | 1,859 | 5.9x fewer |
| Tool Calls | 5.6 avg | 1 | - |

Token savings of 83% and latency reduction of 52% on complex analytical queries.

## Package Structure

```
codemode-sqlite-mcp/
├── main.go                 # CLI entry point
├── pkg/
│   ├── executor/           # Sandboxed code execution
│   │   ├── executor.go     # Yaegi interpreter wrapper
│   │   ├── preprocessor.go # Code preprocessing (import fixing, etc.)
│   │   └── errors.go       # Error types
│   ├── validator/          # Security validation
│   │   └── validator.go    # Code safety checks
│   ├── tools/              # SQLite operations
│   │   ├── registry.go     # Tool registry
│   │   ├── tools.go        # Tool implementations
│   │   └── types.go        # Type definitions
│   └── mcp/                # MCP protocol
│       ├── server.go       # Core MCP logic
│       ├── stdio.go        # STDIO transport
│       ├── http.go         # HTTP transport
│       └── types.go        # JSON-RPC types
├── codemode/               # Codemode agent
│   ├── agent.go            # LLM integration
│   └── prompts.go          # System prompts
├── examples/               # Usage examples
│   ├── basic/              # Basic library usage
│   ├── embed-server/       # Embedding MCP server
│   └── custom-executor/    # Custom code execution
└── benchmark/              # Performance testing
    ├── chinook.go          # Chinook database scenarios
    └── runner.go           # Benchmark runner
```

## Programmatic Usage

### Using as a Library

```go
package main

import (
    "context"
    "fmt"
    "log"

    "github.com/imran31415/codemode-sqlite-mcp/codemode"
    "github.com/imran31415/codemode-sqlite-mcp/pkg/tools"
)

func main() {
    // Initialize database
    if err := tools.InitDB("./mydata.db"); err != nil {
        log.Fatal(err)
    }
    defer tools.CloseDB()

    // Create tool registry
    registry := tools.NewRegistry()

    // Option 1: Use tools directly
    result, err := registry.Call("list_tables", nil)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Tables: %v\n", result)

    // Option 2: Use the Codemode agent
    agent := codemode.NewAgent(registry, codemode.AgentConfig{
        APIKey: "your-anthropic-api-key",
    })

    execResult, err := agent.Execute(context.Background(),
        "List all users and count how many are active")
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(execResult.Output)
}
```

### Custom Tool Registration

```go
registry := tools.NewRegistry()

// Add a custom tool
registry.Register(&tools.ToolInfo{
    Name:        "custom_analytics",
    Description: "Run custom analytics query",
    Parameters: []tools.ParamInfo{
        {Name: "metric", Type: "string", Required: true},
    },
    Function: func(args map[string]interface{}) (interface{}, error) {
        metric := args["metric"].(string)
        // Custom implementation...
        return result, nil
    },
})
```

### Embedding the MCP Server

```go
package main

import (
    "github.com/imran31415/codemode-sqlite-mcp/pkg/mcp"
    "github.com/imran31415/codemode-sqlite-mcp/pkg/tools"
)

func main() {
    tools.InitDB("./data.db")
    defer tools.CloseDB()

    registry := tools.NewRegistry()
    server := mcp.NewServer(registry)

    // Use STDIO transport (for Claude Desktop integration)
    transport := mcp.NewStdioTransport(server)
    transport.Run()

    // Or use HTTP transport
    // httpTransport := mcp.NewHTTPTransport(server, "8084")
    // httpTransport.Run()
}
```

## Executor: Sandboxed Code Execution

The executor uses [yaegi](https://github.com/traefik/yaegi), a Go interpreter, to run LLM-generated code safely:

- No compilation step required
- Restricted standard library access
- Injected symbols for database access
- Configurable execution timeout
- Captured stdout/stderr

```go
executor := executor.NewExecutor()

// Execute with custom symbols (e.g., database tools)
symbols := map[string]map[string]reflect.Value{
    "tools/tools": {
        "registryCall": reflect.ValueOf(registryCallFunc),
    },
}

result, err := executor.ExecuteWithSymbols(ctx, code, 30*time.Second, symbols)
```

### Preprocessing

The executor includes a preprocessor that:

1. Extracts Go code from markdown code blocks
2. Automatically adds missing imports
3. Injects the tool registry symbol
4. Validates basic code structure

## CLI Reference

```
codemode-sqlite-mcp [OPTIONS]

OPTIONS:
  --mode=MODE       Server mode: stdio, http, or codemode (default: stdio)
  --port=PORT       HTTP port for http mode (default: 8084)
  --db=PATH         Path to SQLite database (default: codemode.db)
  --api-key=KEY     Anthropic API key (required for codemode mode)
  --model=MODEL     LLM model to use (optional)
  --init-db         Initialize database with sample data and exit
  --help            Show help message

MODES:
  stdio     MCP server with stdio transport (for Claude Desktop)
  http      MCP server with HTTP transport
  codemode  Interactive agent with LLM code generation
```

## Dependencies

- **[yaegi](https://github.com/traefik/yaegi)** - Go interpreter for sandboxed execution
- **[modernc.org/sqlite](https://modernc.org/sqlite)** - Pure Go SQLite implementation (no CGO required)

## Security Considerations

The executor implements several safety measures:

1. **Sandboxed Execution**: Code runs in an interpreter, not as compiled binaries
2. **Limited Imports**: Only safe standard library packages are available
3. **Execution Timeout**: Configurable timeout prevents infinite loops
4. **Code Validation**: Basic structural validation before execution
5. **No Filesystem Access**: Generated code cannot access the filesystem directly

For production use, additional review of the validator rules is recommended based on your security requirements.

## Limitations

- Generated code is limited to available standard library packages
- Complex data transformations may require multiple LLM attempts
- The interpreter is slower than compiled Go (acceptable for database I/O bound tasks)
- Currently supports Anthropic API only (OpenAI support planned)

## Examples

The `examples/` directory contains runnable examples:

```bash
# Basic usage - direct tool calls and Codemode agent
go run ./examples/basic

# Embed MCP server in your own HTTP application
go run ./examples/embed-server

# Custom executor with injected symbols
go run ./examples/custom-executor
```

## Running Benchmarks

```bash
# Build the benchmark tool
go build -o bin/benchmark ./cmd/benchmark/...

# Run Chinook database benchmark
ANTHROPIC_API_KEY="your-key" ./bin/benchmark --mode=chinook

# Run simple comparison benchmark
ANTHROPIC_API_KEY="your-key" ./bin/benchmark --mode=comparison
```

## License

MIT License - see LICENSE file for details.

## Authors

- Imran Hassanali
- Arsheen Ali

Contact: develop.imran@gmail.com

## Contributing

Contributions are welcome. Please open an issue to discuss significant changes before submitting a pull request.