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:

Standard MCP Mode: Exposes SQLite operations as MCP tools that an LLM calls sequentially
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:

Receives a natural language task (e.g., "Find the top 5 customers by spending")
Generates a complete Go program that accomplishes the task
Executes the program in a sandboxed interpreter (yaegi)
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:

Extracts Go code from markdown code blocks
Automatically adds missing imports
Injects the tool registry symbol
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:

Sandboxed Execution: Code runs in an interpreter, not as compiled binaries
Limited Imports: Only safe standard library packages are available
Execution Timeout: Configurable timeout prevents infinite loops
Code Validation: Basic structural validation before execution
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.

MCP Servers