JAMYPG NL2SQL MCP

Metadata-grounded NL2SQL MCP server for PostgreSQL, MySQL, and MariaDB with safe read-only SQL generation, validation, execution, and an admin UI.

Comment

JAMYPG NL2SQL MCP

Inspiration

Natural-language-to-SQL technology has advanced rapidly, but applying it to real business databases remains much harder than simply sending a schema to a large language model.

In practical environments, we repeatedly encountered problems such as:

  • Hallucinated tables and columns
  • Incorrect join paths
  • Mismatches between business terminology and physical database schemas
  • Potentially dangerous SQL statements
  • SQL dialect differences across database engines
  • Difficulty evaluating generated SQL objectively
  • Inconsistent tool-calling behavior across AI clients

JAMYPG was created to address these challenges.

The goal was not to build another simple SQL generator. The goal was to create a safe, metadata-grounded NL2SQL MCP server that controls the entire workflow from schema discovery and context preparation to validation and read-only execution.

What It Does

JAMYPG analyzes a user’s natural-language question and prepares the metadata context required to generate accurate SQL.

It manages the following information as a unified metadata catalog:

  • Databases and schemas
  • Tables and columns
  • Primary and foreign keys
  • Table relationships and join paths
  • Business terms and synonyms
  • Metrics and calculation formulas
  • Code values and domains
  • Column statistics
  • Example SQL queries
  • Golden datasets for evaluation

Instead of allowing an LLM to guess the database structure, JAMYPG provides verified search results, join graphs, SQL skeletons, dialect information, and validation rules.

Generated SQL is never executed immediately. It first passes through static validation, SQL dialect checks, read-only policy enforcement, and query-plan analysis.

JAMYPG currently supports:

  • PostgreSQL
  • MySQL
  • MariaDB

It also supports the Model Context Protocol, allowing AI clients such as Codex, Claude, Qwen Code, and OpenCode to connect to databases through a standardized tool interface.

How I Built It

JAMYPG was developed in Go.

Go was selected because it provides fast startup times, efficient resource usage, strong concurrency support, and simple deployment as a single executable binary.

The system is organized into several major components.

Metadata Catalog

JAMYPG loads JSON-based metadata and compiles it into in-memory structures, including:

  • Table and column catalogs
  • Search indexes
  • Relationship and join graphs
  • Business glossaries
  • Metric registries
  • Prompt registries
  • SQL validation policies

Keeping metadata separate from production data makes it possible to prepare SQL-generation context without exposing the underlying business records.

NL2SQL Context Preparation

When JAMYPG receives a natural-language question, it searches for relevant tables, columns, business terms, metrics, and join paths.

When the question is ambiguous or incomplete, the system does not generate SQL immediately. Instead, it returns clarification questions.

Once sufficient context is available, JAMYPG provides:

  • Recommended tables
  • Recommended columns
  • Candidate join paths
  • Time-filter requirements
  • Metric definitions
  • SQL dialect information
  • Validation rules
  • Query-specific warnings
  • A structured SQL skeleton

This approach produces more reliable results than allowing an LLM to generate the entire query without constraints.

SQL Validation and Guardrails

JAMYPG validates generated SQL through multiple layers.

Validation includes:

  • Read-only query verification
  • Detection of prohibited commands
  • Table and column existence checks
  • Missing join-condition detection
  • SQL dialect validation
  • Result-size limits
  • Dangerous function detection
  • Query-plan analysis

Commands such as INSERT, UPDATE, DELETE, DROP, and ALTER are blocked.

Database connections are also configured as read-only sessions. This provides an additional protection layer even if application-level validation is bypassed.

MCP and REST Interfaces

JAMYPG supports two MCP transport methods:

  • Standard input and output using stdio
  • Streamable HTTP

It also provides:

  • REST APIs
  • Swagger UI
  • A web-based administration interface
  • Health-check endpoints

This allows developers and administrators to manage metadata, database profiles, validation policies, and connection status without requiring a dedicated MCP client.

Multi-Database Execution

JAMYPG supports PostgreSQL, MySQL, and MariaDB through a unified database-profile system.

Database-specific behavior is handled through a dialect abstraction layer covering differences in:

  • Date and time functions
  • String operations
  • Pagination syntax
  • Query-plan commands
  • Session-level read-only settings

The system uses pure-Go database drivers, so no CGO dependency or external database client library is required.

Challenges I Faced

Preventing Schema Hallucination

One of the biggest challenges was preventing the LLM from generating tables or columns that did not exist.

Instead of including an entire schema in every prompt, JAMYPG retrieves only the metadata relevant to the user’s question. Validation then ensures that generated SQL uses only verified database objects.

Selecting the Correct Join Path

Real-world data models often contain multiple possible paths between two tables.

A simple foreign-key search is not always enough to identify the correct business relationship. JAMYPG therefore combines relationship graphs, metadata descriptions, business terminology, and predefined join rules.

Supporting Multiple SQL Dialects

PostgreSQL, MySQL, and MariaDB share many SQL concepts, but they differ in date functions, string processing, pagination, query-plan syntax, and session configuration.

To manage these differences, JAMYPG separates common SQL behavior from database-specific dialect rules and selects the appropriate dialect through dataset or connection-profile metadata.

Maintaining MCP Client Compatibility

MCP clients do not always behave consistently.

Some clients correctly preserve session headers, while others omit the server-issued session identifier in subsequent requests. JAMYPG implements a flexible session policy that remains compatible with the MCP specification while supporting a wider range of clients.

Balancing Security and Usability

Overly restrictive SQL policies can block legitimate analytical queries. Weak policies, however, can put production databases at risk.

JAMYPG addresses this through defense in depth:

  1. Metadata-constrained SQL generation
  2. Static SQL validation
  3. Query-plan analysis
  4. Result-size limits
  5. Execution timeouts
  6. Read-only database sessions
  7. Audit logging

What I Learned

The most important lesson was that NL2SQL quality is not determined by the LLM alone.

Reliable results require a complete supporting system that includes:

  • Accurate and continuously maintained metadata
  • Mapping between business terms and physical schemas
  • Explicit join relationships
  • Database-specific dialect handling
  • Static validation after generation
  • Query-plan analysis before execution
  • Repeatable evaluation datasets
  • Clarification workflows for ambiguous questions

I also learned that MCP can serve as more than a simple tool-calling interface.

An MCP server can become a governance layer between AI agents and enterprise data by enforcing access policies, recording audit events, exposing trusted metadata, and controlling how generated SQL is validated and executed.

Accomplishments

With JAMYPG, I was able to achieve the following:

  • Built a deployable NL2SQL MCP server as a single Go binary
  • Added support for PostgreSQL, MySQL, and MariaDB
  • Implemented metadata-grounded table and column discovery
  • Created graph-based join-path exploration
  • Added clarification handling for ambiguous questions
  • Generated structured SQL contexts and SQL skeletons
  • Implemented multi-stage SQL validation
  • Enforced read-only query execution
  • Added MCP, REST API, Swagger UI, and admin UI support
  • Built Docker-based integration-test environments
  • Added repeatable NL2SQL evaluation using open-source schemas and golden datasets
  • Improved compatibility across different MCP clients

What Is Next

The next goal is to evolve JAMYPG from an NL2SQL tool into a broader enterprise data-AI platform.

Planned improvements include:

  • Automatic metadata extraction from live databases
  • Schema-change detection
  • Data lineage and impact analysis
  • Data-quality metadata integration
  • User-feedback-based SQL improvement
  • Automated evaluation of question-to-SQL results
  • Role-based access control for tables and columns
  • Sensitive-data detection and masking
  • Federated queries across multiple databases
  • LLM quality, latency, and cost comparison
  • Enterprise audit logs and policy management
  • Metadata synchronization with data catalog platforms

The long-term vision is to allow users to explore enterprise data safely through natural language while ensuring that every AI-generated query is explainable, validated, governed, and auditable.

Built With

  • ai-agents
  • database-security
  • docker
  • go
  • integration
  • json
  • knowledge-graph
  • large-language-models
  • mariadb
  • mcp
  • metadata
  • model-context-protocol
  • mysql
  • nl2sql
  • openapi
  • postgresql
  • read-only-database
  • rest-api
  • schema-discovery
  • server-sent-events
  • sql
  • sql-validation
  • streamable-http
  • swagger
  • web-ui
Share this project:

Updates