built an mcp server for postgres, rate my schema design before i regret it
wrote a simple MCP server that wraps postgres - exposes tools for read_query, write_query, list_tables, describe_table. trying to decide if i should let the agent write raw SQL or force it through a safer DSL. raw SQL: way more powerful, agent can do joins and aggregates without me writing a tool for every case. but holy hell the injection risk, even with parameterization i don't trust it. DSL: safe, can allowlist operations, way easier to audit logs. but now i'm maintaining a query builder and the agent is dumber. anyone ship something like this? what did you pick and did you regret it. repo is here if you want to roast the code: github.com/newbuilder/mcp-postgres
what's your schema for returning query results? we built one and ended up with a 3-level nested json disaster that the model could barely parse. curious how you're handling joins and what your row limit is?
we return query results as {"columns": [...], "rows": [...], "count": n} and set a hard 100 row limit. anything more and the model starts hallucinating or truncating. do you have a row limit and how are you handling pagination?
we return results as {"columns", "rows", "count", "has_more": bool} with a 25 row limit. if has_more is true we include a message suggesting the agent refine the query. works pretty well for our use case
1. that schema makes sense for most queries but what about queries that naturally return no rows - do you distinguish between "found nothing" vs "query failed"? 2. how do you handle the agent writing SELECT * on a table with 500 columns
We hit this exact issue at work last month - LoRA trained on qwen 2.5 14b for code gen, layers 19-21 completely collapsed after quantization to q4_k_m even though the base model was fine at q4. Switched to q5_k_m and it worked. The failure mode was interesting though - the model would start the function correctly then degrade into repetition around 40 tokens in, which suggests the late-layer cleanup circuit got destroyed by quantization errors propagating through the residual stream.
we return {"columns": [...], "rows": [[...]], "row_count": n, "truncated": bool} and set a 50 row limit. anything over that and we set truncated: true and include a message like "showing first 50 of 284 rows". does your agent handle the truncated case well or does it just ignore it?
we do something similar but also include a "truncated_rows" field with the actual rows that got cut, so the agent can decide if it needs to refine the query. works well imo but adds complexity
schema looks fine but what about querys that return no rows - do you distingiush between empty result vs query faild? also how do u handle sql injectoin if the agent writes the query imo thats the actuall risky part
can you post the schema? also how do you handle sql injection if the agent is writing raw queries