Building a Safe SQLite MCP Server in Go — Why Agents Shouldn't Have Raw Database Access

Giving an AI agent direct access to your database sounds intoxicating. You wire up an agent, tell it where your database is, and ask questions in plain English: “How many active subscriptions churned last week?” or “Find the top five users with failed background jobs.”

And because modern LLMs are surprisingly proficient at writing SQL, the naive solution is tempting: give the agent raw shell access or a generic database tool that accepts an arbitrary query string and runs db.Exec(query).

In a production or staging environment, this is an accident waiting to happen.

It only takes one subtle hallucination—or a poorly phrased user prompt—to run a destructive UPDATE, trigger an accidental DROP TABLE, or run an unindexed SELECT * FROM audit_logs that dumps 400,000 rows, blowing out your context window and costing $30 in API tokens.

This is why the Model Context Protocol (MCP) matters. MCP is not just an integration abstraction; it is your architectural boundary and security guardrail.

In this article, we will build a production-ready, single-binary MCP server in Go that connects to a local SQLite database. It exposes schema exploration and querying capabilities while enforcing immutable, connection-level safety constraints.

Get the code: The complete, tested source code for this project is available on GitHub:
⭐️ github.com/raza-basit/safe-sqlite-mcp-go
Install directly via: go install github.com/raza-basit/safe-sqlite-mcp-go@latest

---

Why Go for MCP Servers?

Most official and community MCP tutorials use TypeScript (@modelcontextprotocol/sdk) or Python (mcp). While great for prototyping, they come with substantial operational baggage:

• Runtime dependencies: You need Node.js, npm, or Python virtual environments configured on every developer machine.
• Memory footprint: A Node or Python runtime idling in the background consumes 50MB to 150MB of RSS just to listen on stdin.
• Startup latency: Spawning a Node runtime takes 150ms–300ms.

A Go MCP server compiles down to a single, static 12MB binary. It starts in 2 milliseconds, consumes less than 8MB of RAM, and runs identically on macOS, Linux, and Windows with zero host dependencies.

---

The Core Invariants: Safety by Default

Before writing protocol code, let’s establish the non-negotiable safety invariants our server must enforce:

┌────────────────────────────────────────────────────────┐
│                      AI Client                         │
│             (Claude Desktop, Cursor, etc.)             │
└──────────────────────────┬─────────────────────────────┘
                           │ JSON-RPC 2.0 (stdin/stdout)
                           ▼
┌────────────────────────────────────────────────────────┐
│                 Go MCP Server Boundary                 │
│                                                        │
│  [1] Query Timeout      --> context.WithTimeout(2s)    │
│  [2] Context Protection --> Hard Cap (LIMIT 50)        │
│  [3] Stdout Isolation   --> Logs strictly to stderr    │
└──────────────────────────┬─────────────────────────────┘
                           │ Read-Only SQL Connection
                           ▼
┌────────────────────────────────────────────────────────┐
│                   SQLite Database                      │
│                                                        │
│  [4] Immutability       --> PRAGMA query_only = ON     │
│                             file:app.db?mode=ro        │
└────────────────────────────────────────────────────────┘

1. Connection-Level Immutability: We do not rely on regex string matching to block DROP or DELETE. Regex can be bypassed with comments, casing, or CTEs. Instead, we configure SQLite at the driver level with mode=ro and PRAGMA query_only = ON. The database engine itself refuses any write.
2. Context Protection (Row Capping): If a query returns 10,000 rows, our server caps the result to a safe limit (e.g., 50 rows) and injects a visible warning notice.
3. Execution Deadlines: Every query is bound to a strict Go context.WithTimeout(ctx, 2*time.Second). Runaway joins or full-table scans cannot hang the server.
4. Stdout Isolation: MCP transports messages over os.Stdout. A single errant fmt.Println("debug") will corrupt the JSON frame and disconnect the client. All logging must go to os.Stderr.

---

What Actually Is MCP Under the Hood?

Strip away the marketing terminology, and an MCP server running locally over stdio is remarkably simple:

An MCP stdio server is a long-running process that reads newline-delimited JSON-RPC 2.0 messages from os.Stdin and writes newline-delimited JSON-RPC 2.0 responses to os.Stdout.

The protocol lifecycle consists of three primary phases:

1. initialize: The client and server exchange capabilities and protocol versions.
2. tools/list: The client asks “What tools can you run?” The server responds with a list of tool names, descriptions, and JSON Schemas defining their inputs.
3. tools/call: The client executes a tool with a JSON payload. The server runs the logic and returns text or image content blocks.

Let’s implement this cleanly using Go’s standard library.

---

Step 1: Defining the JSON-RPC & MCP Types

Create a new directory and initialize a Go module:

mkdir mcp-sqlite-go
cd mcp-sqlite-go
go mod init mcp-sqlite-go
go get modernc.org/sqlite

We use modernc.org/sqlite because it is a pure Go SQLite implementation compiled from C using ccgo. It requires zero Cgo (CGO_ENABLED=0), ensuring our binary compiles anywhere effortlessly.

Now, define the core JSON-RPC 2.0 structures:

package main

import (
	"encoding/json"
)

type JSONRPCRequest struct {
	JSONRPC string          `json:"jsonrpc"`
	ID      json.RawMessage `json:"id,omitempty"`
	Method  string          `json:"method"`
	Params  json.RawMessage `json:"params,omitempty"`
}

type JSONRPCResponse struct {
	JSONRPC string          `json:"jsonrpc"`
	ID      json.RawMessage `json:"id"`
	Result  any             `json:"result,omitempty"`
	Error   *RPCError       `json:"error,omitempty"`
}

type RPCError struct {
	Code    int    `json:"code"`
	Message string `json:"message"`
}

// Tool represents an MCP tool definition
type Tool struct {
	Name        string      `json:"name"`
	Description string      `json:"description"`
	InputSchema InputSchema `json:"inputSchema"`
}

type InputSchema struct {
	Type       string                 `json:"type"`
	Properties map[string]PropertyDef `json:"properties"`
	Required   []string               `json:"required,omitempty"`
}

type PropertyDef struct {
	Type        string `json:"type"`
	Description string `json:"description"`
}

// ToolCallParams represents the incoming parameters for tools/call
type ToolCallParams struct {
	Name      string          `json:"name"`
	Arguments json.RawMessage `json:"arguments"`
}

// ToolResult represents the response payload for a tool execution
type ToolResult struct {
	Content []ContentBlock `json:"content"`
	IsError bool           `json:"isError,omitempty"`
}

type ContentBlock struct {
	Type string `json:"type"`
	Text string `json:"text"`
}

---

Step 2: Safe SQLite Driver Initialization

Next, we write the helper that opens our SQLite database with immutable read-only constraints.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"net/url"
	"time"

	_ "modernc.org/sqlite"
)

func openSafeDB(path string) (*sql.DB, error) {
	// Construct connection string with read-only pragma
	// mode=ro guarantees the OS-level file handle cannot write
	dsn := fmt.Sprintf("file:%s?mode=ro", url.PathEscape(path))

	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	// Limit idle connections for embedded use
	db.SetMaxOpenConns(4)
	db.SetMaxIdleConns(2)
	db.SetConnMaxLifetime(5 * time.Minute)

	// Verify connection and enforce query_only pragma
	ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
	defer cancel()

	if err := db.PingContext(ctx); err != nil {
		return nil, fmt.Errorf("failed to reach database: %w", err)
	}

	if _, err := db.ExecContext(ctx, "PRAGMA query_only = ON;"); err != nil {
		return nil, fmt.Errorf("failed to enable query_only mode: %w", err)
	}

	log.Printf("[INFO] Connected safely to SQLite at %s (mode=ro)", path)
	return db, nil
}

If anyone attempts to issue a CREATE, DROP, INSERT, or UPDATE, the SQLite engine itself will return attempt to write a readonly database.

---

Step 3: Implementing the Tools

We will expose three tools to the agent:

1. list_tables: Enumerates user tables in the database.
2. describe_table: Returns the schema, column types, nullability, and primary key status of a specific table.
3. read_query: Runs an arbitrary SELECT query, with automated row capping.

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"strings"
	"time"
)

const maxResultRows = 50

func listTables(ctx context.Context, db *sql.DB) (string, error) {
	query := `
		SELECT name 
		FROM sqlite_master 
		WHERE type='table' AND name NOT LIKE 'sqlite_%'
		ORDER BY name ASC;
	`
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return "", err
	}
	defer rows.Close()

	var tables []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return "", err
		}
		tables = append(tables, name)
	}

	if len(tables) == 0 {
		return "Database contains no user tables.", nil
	}

	return strings.Join(tables, "\n"), nil
}

func describeTable(ctx context.Context, db *sql.DB, tableName string) (string, error) {
	// Sanitize tableName: only alphanumeric and underscores allowed
	for _, ch := range tableName {
		if !((ch >= 'a' && ch <= 'z') || (ch >= 'A' && ch <= 'Z') || (ch >= '0' && ch <= '9') || ch == '_') {
			return "", fmt.Errorf("invalid table name: %q", tableName)
		}
	}

	rows, err := db.QueryContext(ctx, fmt.Sprintf("PRAGMA table_info(%s);", tableName))
	if err != nil {
		return "", err
	}
	defer rows.Close()

	var builder strings.Builder
	builder.WriteString(fmt.Sprintf("Schema for table %s:\n", tableName))
	builder.WriteString("cid | name | type | notnull | dflt_value | pk\n")
	builder.WriteString("----+------+------+---------+------------+---\n")

	var found bool
	for rows.Next() {
		found = true
		var cid, notnull, pk int
		var name, colType string
		var dfltValue sql.NullString

		if err := rows.Scan(&cid, &name, &colType, &notnull, &dfltValue, &pk); err != nil {
			return "", err
		}
		builder.WriteString(fmt.Sprintf("%d | %s | %s | %d | %s | %d\n",
			cid, name, colType, notnull, dfltValue.String, pk))
	}

	if !found {
		return "", fmt.Errorf("table %q not found", tableName)
	}

	return builder.String(), nil
}

func readQuery(ctx context.Context, db *sql.DB, query string) (string, error) {
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return "", fmt.Errorf("query execution failed: %w", err)
	}
	defer rows.Close()

	cols, err := rows.Columns()
	if err != nil {
		return "", err
	}

	var results []map[string]any
	count := 0
	truncated := false

	for rows.Next() {
		if count >= maxResultRows {
			truncated = true
			break
		}

		values := make([]any, len(cols))
		valuePtrs := make([]any, len(cols))
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return "", err
		}

		rowMap := make(map[string]any, len(cols))
		for i, col := range cols {
			val := values[i]
			if b, ok := val.([]byte); ok {
				rowMap[col] = string(b)
			} else {
				rowMap[col] = val
			}
		}
		results = append(results, rowMap)
		count++
	}

	jsonBytes, err := json.MarshalIndent(results, "", "  ")
	if err != nil {
		return "", err
	}

	output := string(jsonBytes)
	if truncated {
		output += fmt.Sprintf("\n\n[NOTICE: Output truncated at %d rows to protect context limits. Add a specific WHERE clause or LIMIT to refine.]", maxResultRows)
	}

	return output, nil
}

Notice the defensive row cap: if an agent runs SELECT * FROM large_table, it will receive the first 50 rows formatted as clean JSON, followed by an actionable instruction telling the LLM to refine its query.

---

Step 4: The Stdio Event Loop

Now, wire up the dispatching loop in main.go. We read lines from os.Stdin using bufio.Scanner and route requests:

package main

import (
	"bufio"
	"context"
	"encoding/json"
	"flag"
	"fmt"
	"io"
	"log"
	"os"
	"time"
)

var toolsList = []Tool{
	{
		Name:        "list_tables",
		Description: "List all user tables in the SQLite database.",
		InputSchema: InputSchema{
			Type:       "object",
			Properties: map[string]PropertyDef{},
		},
	},
	{
		Name:        "describe_table",
		Description: "Retrieve schema information (columns, types, keys) for a specific table.",
		InputSchema: InputSchema{
			Type: "object",
			Properties: map[string]PropertyDef{
				"table": {
					Type:        "string",
					Description: "The name of the table to inspect.",
				},
			},
			Required: []string{"table"},
		},
	},
	{
		Name:        "read_query",
		Description: "Execute a read-only SQL SELECT query against the SQLite database. Write operations will fail.",
		InputSchema: InputSchema{
			Type: "object",
			Properties: map[string]PropertyDef{
				"query": {
					Type:        "string",
					Description: "The SELECT query to execute.",
				},
			},
			Required: []string{"query"},
		},
	},
}

func main() {
	dbPath := flag.String("db", "", "Path to the SQLite database file")
	flag.Parse()

	// Direct all standard logging to stderr so stdout remains purely JSON-RPC
	log.SetOutput(os.Stderr)
	log.SetFlags(log.Ltime | log.Lshortfile)

	if *dbPath == "" {
		log.Fatal("[FATAL] --db argument is required")
	}

	db, err := openSafeDB(*dbPath)
	if err != nil {
		log.Fatalf("[FATAL] %v", err)
	}
	defer db.Close()

	reader := bufio.NewReader(os.Stdin)
	for {
		line, err := reader.ReadBytes('\n')
		if err != nil {
			if err == io.EOF {
				break
			}
			log.Printf("[ERROR] read error: %v", err)
			continue
		}

		if len(line) == 0 || line[0] == '\n' {
			continue
		}

		var req JSONRPCRequest
		if err := json.Unmarshal(line, &req); err != nil {
			log.Printf("[ERROR] json parse error: %v", err)
			continue
		}

		handleRequest(&req, db)
	}
}

func handleRequest(req *JSONRPCRequest, db *sql.DB) {
	switch req.Method {
	case "initialize":
		sendResponse(req.ID, map[string]any{
			"protocolVersion": "2024-11-05",
			"serverInfo": map[string]string{
				"name":    "safe-sqlite-mcp-go",
				"version": "1.0.0",
			},
			"capabilities": map[string]any{
				"tools": map[string]any{},
			},
		})

	case "notifications/initialized":
		// Notification - no response required

	case "tools/list":
		sendResponse(req.ID, map[string]any{
			"tools": toolsList,
		})

	case "tools/call":
		var params ToolCallParams
		if err := json.Unmarshal(req.Params, &params); err != nil {
			sendError(req.ID, -32602, "Invalid params")
			return
		}

		// Enforce a strict 2-second timeout on all tool executions
		ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
		defer cancel()

		var resultText string
		var execErr error

		switch params.Name {
		case "list_tables":
			resultText, execErr = listTables(ctx, db)

		case "describe_table":
			var args struct {
				Table string `json:"table"`
			}
			_ = json.Unmarshal(params.Arguments, &args)
			resultText, execErr = describeTable(ctx, db, args.Table)

		case "read_query":
			var args struct {
				Query string `json:"query"`
			}
			_ = json.Unmarshal(params.Arguments, &args)
			resultText, execErr = readQuery(ctx, db, args.Query)

		default:
			sendError(req.ID, -32601, fmt.Sprintf("Unknown tool: %s", params.Name))
			return
		}

		if execErr != nil {
			sendResponse(req.ID, ToolResult{
				IsError: true,
				Content: []ContentBlock{
					{Type: "text", Text: execErr.Error()},
				},
			})
			return
		}

		sendResponse(req.ID, ToolResult{
			Content: []ContentBlock{
				{Type: "text", Text: resultText},
			},
		})

	default:
		// Unknown method
		if len(req.ID) > 0 {
			sendError(req.ID, -32601, fmt.Sprintf("Method not found: %s", req.Method))
		}
	}
}

func sendResponse(id json.RawMessage, result any) {
	resp := JSONRPCResponse{
		JSONRPC: "2.0",
		ID:      id,
		Result:  result,
	}
	bytes, _ := json.Marshal(resp)
	os.Stdout.Write(append(bytes, '\n'))
}

func sendError(id json.RawMessage, code int, msg string) {
	resp := JSONRPCResponse{
		JSONRPC: "2.0",
		ID:      id,
		Error:   &RPCError{Code: code, Message: msg},
	}
	bytes, _ := json.Marshal(resp)
	os.Stdout.Write(append(bytes, '\n'))
}

---

Testing via the Terminal

Before connecting the server to an AI client, test it using standard Unix pipes.

Build the standalone binary:

CGO_ENABLED=0 go build -o mcp-sqlite

Create a quick test database:

sqlite3 test.db "CREATE TABLE users (id INT, name TEXT, role TEXT); INSERT INTO users VALUES (1, 'Alice', 'Admin'), (2, 'Bob', 'User');"

Now send an MCP initialize request via printf:

printf '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{}}\n' | ./mcp-sqlite --db test.db

Output:

{"jsonrpc":"2.0","id":1,"result":{"capabilities":{"tools":{}},"protocolVersion":"2024-11-05","serverInfo":{"name":"safe-sqlite-mcp-go","version":"1.0.0"}}}

Now try executing a malicious write command via read_query:

printf '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"read_query","arguments":{"query":"DROP TABLE users;"}}}\n' | ./mcp-sqlite --db test.db

Output:

{"jsonrpc":"2.0","id":2,"result":{"content":[{"type":"text","text":"query execution failed: attempt to write a readonly database"}],"isError":true}}

The database driver rejected the query instantly. Even if the AI hallucinated or got tricked into dropping your table, your data remains untouched.

---

Connecting to Claude Desktop or Cursor

To use this with Claude Desktop, open your configuration file:

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• Windows: %APPDATA%\Claude\claude_desktop_config.json

Add your server under mcpServers:

{
  "mcpServers": {
    "local-db": {
      "command": "/absolute/path/to/mcp-sqlite",
      "args": ["--db", "/absolute/path/to/production_backup.db"]
    }
  }
}

Restart Claude. You will see a hammer icon appear in the chat bar. You can now prompt Claude:

“What tables exist in this database? Summarize the recent records in the users table.”

Claude will call list_tables, follow up with describe_table, and run a safe read_query to fulfill your request.

---

Beyond Read-Only: Defending Against Exfiltration & Query Drift

Enforcing mode=ro, strict timeouts, and a 50-row cap establishes a solid operational baseline. It guarantees database integrity (an agent cannot corrupt, mutate, or drop tables) and availability (an agent cannot lock the database with a 30-second runaway query).

However, read-only is an integrity floor—it is not an exfiltration defense.

In autonomous agent workflows, an agent that is strictly read-only can still compromise confidentiality through three distinct attack vectors:

1. The Threat Model of a Read-Only Agent

1. Sensitive Column Snooping:
   An agent running SELECT email, password_hash, api_token FROM users is 100% read-only. Yet it extracts sensitive credentials directly into the LLM context window—which might subsequently be leaked via external tool calls or chat responses.
2. Iterative Pagination Harvesting:
   Our server limits any single query to 50 rows. A compromised or misaligned agent can simply run automated loops:

   SELECT * FROM users LIMIT 50 OFFSET 0;
   SELECT * FROM users LIMIT 50 OFFSET 50;
   SELECT * FROM users LIMIT 50 OFFSET 100;

   Across 20 tool invocations, the agent has harvested the entire table 50 rows at a time without triggering a single single-query alarm.
3. Query Shape Drift:
   When a user approves a tool call like “Check recent order volume”, the expected query shape is SELECT count(*) FROM orders WHERE created_at > ?. If the agent instead executes SELECT * FROM secret_keys, the executor shouldn’t permit it merely because it begins with SELECT.

---

Enforcing Defense-in-Depth: 3 Architectural Layers

To guard against query drift and data exfiltration, a production MCP database server should enforce three additional security controls:

Layer 1: Schema Allowlisting via SQLite’s Native Authorizer

Rather than attempting to parse SQL with fragile regular expressions, SQLite provides a native C callback mechanism: sqlite3_set_authorizer.

During query preparation (before any bytecode is executed), SQLite invokes this hook for every table access (SQLITE_READ), column read, and PRAGMA lookup. If the query touches an unapproved table or attempts to read sensitive columns (e.g. password, ssn, secret), the callback returns SQLITE_DENY:

// Native SQLite Authorizer Hook during query compilation
func authorizerHook(action int, arg1, arg2, dbName, triggerName string) int {
    switch action {
    case SQLITE_READ:
        tableName := arg1
        columnName := arg2
        
        // Deny forbidden columns across all queries
        if isSensitiveColumn(columnName) { // e.g., "password_hash", "api_key"
            return SQLITE_DENY
        }
        
        // Deny access to internal or unapproved tables
        if !isAllowedTable(tableName) {
            return SQLITE_DENY
        }
        
    case SQLITE_PRAGMA:
        // Deny PRAGMA statements that reveal database internals
        return SQLITE_DENY
    }
    
    return SQLITE_OK
}

Layer 2: Cumulative Session Row & Budget Quotas

To thwart iterative harvesting via LIMIT/OFFSET pagination, the MCP server must track state across tool calls within a session.

Instead of only capping individual queries, maintain an atomic session counter:

• Max Queries per Session: e.g., 20 queries.
• Max Cumulative Rows per Session: e.g., 200 total rows.

Once an agent exceeds its session budget, the executor rejects further queries until an explicit human re-authorization occurs.

Layer 3: Query Shape Binding & AST Fingerprinting

For mission-critical deployments, you can bind tool approval directly to a normalized query shape (an Abstract Syntax Tree stripped of literal values).

If a query’s AST does not match the approved pattern (e.g. introducing unexpected JOINs, subqueries, or wildcard * projections), the executor rejects the query drift before hitting the database engine.

---

The Next Frontier: Semantic Domain Tools

Exposing read_query with read-only constraints solves basic safety. But the ultimate value of MCP is moving beyond SQL altogether.

Instead of having the LLM guess how your tables join, you can write Go functions that expose high-intent domain tools:

// Instead of letting the agent write:
// SELECT * FROM orders JOIN refunds ON ... WHERE ...
// You expose:
{
    Name: "get_customer_refund_summary",
    Description: "Returns aggregated refunds and return reasons for a customer ID.",
    InputSchema: ...
}

With domain tools:

1. Zero SQL Exposure: The agent never writes SQL.
2. Business Logic in Go: Calculations like revenue or churn risk are computed predictably in Go code, not hallucinated by an LLM.
3. Auditing: Every domain call can be logged, rate-limited, and authorized with precision.

---

Summary

• Raw SQL is a liability: Giving autonomous agents unconstrained database or shell access will eventually lead to context blowouts or accidental writes.
• Security belongs at the driver level: Never rely on regex to catch bad queries. Enforce mode=ro and PRAGMA query_only = ON.
• Read-only is not exfiltration defense: Protect against sensitive column harvesting and pagination loops using schema authorizers and cumulative session budgets.
• Cap your output: Protect LLM context windows by truncating result sets at the server boundary.
• Go is the ideal MCP runtime: A single static Go binary gives you sub-millisecond startup, minimal memory consumption, and zero dependency friction across developer machines.