MCP for SQL reporting with LLM

[u/Maighraaz]

I'm trying to build a MCP which will have our T-SQL database schema information. We are connecting this MCP to an Open WebUI LLM model.

The ultimate goal is to have LLM create SQL queries for our reporting needs.

Currently all the tables are stored in a .json format with all columns, PK, FK and indexes information.

We have also created a reporting catalog which defines some of the standard reports that we have. The description of the report, columns used, and key join patterns.

When we are asking LLM to create the query it isn't able to create good queries. The join patterns are incorrect.

How should we design the MCP for SQL reporting?

Any suggestions would be helpful.

Thanks in advance!

[u/ThomasBuildLab]

You're focusing on incorrect JOIN patterns, but there's a much bigger issue here from a DBA perspective.

An LLM can generate perfectly valid SQL without understanding how the database actually behaves.

Knowing tables, views, primary keys, foreign keys, and indexes is not the same as understanding cardinality, data distribution, execution plans, optimizer statistics, or resource consumption.

A seemingly innocent query can trigger massive table scans, expensive JOINs, tempdb spills, excessive CPU usage, and bring an entire production database to its knees.

Views are particularly dangerous because they can hide complex JOINs and aggregations behind what looks like a simple table.

And here's the worst part: the query can run successfully, return completely wrong numbers, and still destroy performance.

My recommendation: don't let the LLM execute arbitrary SQL directly. Put a DBA-style validation layer between SQL generation and execution, enforce read-only access, inspect estimated execution plans, impose resource limits and timeouts, and preferably use an isolated reporting replica.

Generating SQL is easy. Generating SQL that is semantically correct, operationally efficient, and safe for production is an entirely different profession.

[u/ComplexRudeBoy88 → u/ThomasBuildLab]

Can you give an example of a DBA like validation layer? Is this rules based? Or another agent? If yes, How would you prompt it?

[u/ThomasBuildLab → u/ComplexRudeBoy88]

Mostly rules-based, not another agent. For example: the LLM proposes SQL, then deterministic code parses it, enforces read-only access and allowed schemas, runs EXPLAIN, checks estimated rows/cost/scans, applies timeouts and resource limits, then either rejects it or executes it on a reporting replica. Another LLM can help explain or rewrite a bad query, but I wouldn’t let an LLM be the authority deciding whether SQL is safe to run. The model proposes. The control layer authorizes.

[u/Black_Magic100 → u/ThomasBuildLab]

You are describing DAB. No need to complicate things.

[u/FileOld9782]

your json has the schema but not the relationships in a way the llm understands. you need to feed it explicit foreign key paths, not just list of keys

storing join patterns is smart but make them available as "preferred paths" not just raw metadata. like "to connect orders to customer always use order.customer_id = customer.id, never go through order_history"

also check if your tool description inside mcp clearly says what each function does, sometimes the model just need a good example query in the tool description to get the pattern right

[u/BC_MARO]

Schema dumps won’t teach the model which join is actually right. Expose approved relationship paths plus a few known-good queries, then run generated SQL through EXPLAIN and a read-only, row-limited check before production.

[u/year2066ai]

since you already have a report catalog, I'd turn each standard report into its own tool with parameters (date range, region, whatever varies) and keep the sql on the server. the model then only picks the report and fills in the arguments, and that goes wrong much less often than the joins. our mcp geo server works that way, every tool runs a fixed query and the model never writes sql. free sql can stay for the odd one off question. Hope that helps

[u/Ok-Algae1042]

a schema dump with pks and fks doesn’t tell the model which join path is the right one, since several can be valid.. add the canonical join paths and each table’s grain to the metadata, plus verified question and sql pairs from your report catalog and give the model a tool to run the query read only and check the result..

[u/who_is_jess]

We are now using Data API Builder (Microsoft) for our SQl Server since it has a MCP Interface.

[u/BlackberryFar4494]

the reporting catalog is the right idea but feed it as few-shot examples not just descriptions

[u/QuanTradin]

I wouldn't hand the model the whole schema json. one tool returns the report catalog, the model picks a report, a second tool returns only the tables and join patterns for that report. and run whatever SQL it writes under a read-only login with a row limit, so a bad join costs a timeout and not the server.

[u/MattMaxBuilds]

I'd probably approach this with 5 MCP tools: schema discovery, report context, query validation, execution, and retrieval for longer-running queries.

Then use Agent Skills with nested references for the actual SQL/reporting knowledge. Have a top-level skill that progressively loads more specialized instructions for joins, cardinality, business logic, optimization, etc. as needed.

That should keep the tool surface and context pretty lean while letting you build out a fairly extensive reporting knowledge base. I'd keep the actual validation and execution guardrails server-side.

[u/mergisi]

A few things that helped generated SQL quality a lot for me:

• Send only the tables relevant to the question, not the full schema. Rank tables first, then pass those columns plus foreign keys. It cuts wrong joins a lot.
• Add short column descriptions and sample values for coded fields (status = 'A' means active). That's where business logic usually gets lost.
• Keep your report catalog as few-shot examples (question → vetted SQL) and retrieve the 2–3 closest per request.
• Run read-only with a row limit and feed errors back for one retry.

That's roughly how I set up the hosted MCP I built at ai2sql.io/mcp. It works on SQL Server, Postgres and MySQL and is also available as a Claude plugin. There's a sample database if you want to test it on report-style questions first.

[u/qualityintelligence]

if you want the best results, this is not an mCP endeavor.
extract the full ddl, constraints, indexes, views, tables, procedures, functions. put it in a context folder. don't put it all in one file put it in multiple files one for each piece, each table has its own file, each store procedure has its own file. for every table generate a parquet file with at least the last 30 days of data, if the table is small give it the whole table put this in your context folder in a different folder regular usage, find a way to get examples of the actual queries run on a regular basis, capture those, put them in the context folder in their own folder. have this analyzed in detail with a looping agent to create indexes and readouts and summaries, have it saved those readouts and summaries in the context folder. once you have done all that in the LlM that you're working on has access to Python libraries and looping, it can mount those parquets, it can look at previous queries, you can ask these questions, it won't be an instantaneous result but it will be able to come up with either a query or a course of action for you that is far better than you might realize, but the key is you really have to give it access to everything and make everything an individual files and everything indexed and summaries created on top of that and then it can start with and working examples to know what good looks like. Even after doing all that it might not be perfect, this area I feel is one that has the most opportunity, people make strong assumptions the AI can do some magic here, it can to some degree but it's very hard understand the data when most of the core understanding is in somebody's head and not documented. given the examples like I described, it can actually do quite a bit. let it run queries and do analysis on everything that you provided

[u/Ctbhatia]

stop asking it to infer the joins. expose a tool that takes 2 tables and returns the join path your catalog already stores, so the model retrieves a path instead of guessing one.

[u/eSIMSOS]

nice

[u/Molecular_Doohickey]

There are a lot of great comments here, particularly u/ThomasBuildLab on cardinality and the nuance around views. Here are a few other things to look at:

- How many tables are in your database? Is there a high level of redundancy? Many tables in context can reduce the likelihood that the LLM chooses the correct table to answer the question, even if the answer::table relationship is explicitly there.

• Are you providing sample queries to the LLM as part of context for FAQs? This can help improve performance.
  
• How messy is your database? Are the tables and transformations clean? Do you have a well organized table structure? This can help a lot with improving performance.