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

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

Or build from source:

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

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

As an HTTP Server:

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

As an Interactive Codemode Agent:

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:

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

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

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

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

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, 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

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 - Go interpreter for sandboxed execution
• 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:

# 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

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