Model Context Protocol Architecture for Database Query Tools

MCP is the open standard Anthropic introduced in November 2024 for connecting AI systems to outside tools, data, and services over JSON-RPC 2.0. Building a database query tool on top of it comes down to one decision made over and over: does this piece of work belong to a Tool, a Resource, or a Prompt? Get that mapping wrong and the agent still runs, but it runs slow, it burns through query budget, and nobody can say for sure what it did or why.

The three primitives and what each one is for

An MCP server hands clients three kinds of things, and clients find out what's on offer through a */list call for each one. The three don't do the same job, even though it's tempting to treat them as interchangeable buckets for "stuff the AI can use."

Tools are the ones the model decides to run on its own. Each one has a name, a JSON Schema describing what parameters it wants (inputSchema), a plain description, and sometimes a display title. The model calls it through tools/call, and the server hands back a result plus an isError flag. As of the 2026-07-28 spec revision, servers should list tools in a fixed, repeatable order, since that consistency lets clients cache the list and reduces redundant work on the LLM side. That same revision added something called InputRequiredResult, a way for a tool to say "I need more from you before I can finish," so the client and server can go back and forth a few times inside one call instead of failing. The spec is also blunt about one thing: there should be a human somewhere in the loop who can say no to a tool call. For a database agent that can write or delete, that's not a nice-to-have.

Resources work the other way. They're read-only: table structures, file contents, an API response, whatever the model needs to see before it does anything. Nobody executes a Resource. For a database tool this is where schema lives, table names, column types, primary and foreign keys, so the model can look at the real structure before it writes a query instead of guessing from the phrasing of the question. A client asking for a Resource causes it to appear in the conversation as context.

Prompts are templates, and a person picks them, not the model. Think "analyze this table" or "draft a migration script," reusable structures for a conversation. The distinction that matters going forward: Tools act, Resources inform, Prompts shape the interaction. Most design mistakes in an MCP database server come from blurring those three jobs into one.

How the client-server topology shapes each primitive's capabilities

MCP has three participants: the host, which is the agent orchestrating the whole thing; the client, which is one dedicated connection per server; and the server, which exposes the Tools, Resources, and Prompts. Because there is one dedicated connection per server, tool names can collide across servers and negotiating capabilities between them becomes a real headache when a client runs more than one server at once.

Transport choice sets the ceiling on what's practical. Local stdio connections, process to process, are fast and private by default, which fits a developer running queries against a database on their own machine. Streamable HTTP is for remote servers talking to many clients at once, and that's where authentication, rate limits, and audit logs stop being theoretical concerns and start being the whole job. The July 2026 revision moved the protocol toward stateless operation, every request now carries its own protocol version and capabilities in a _meta field, so servers can process each request independently. That's what makes horizontal scaling behind a load balancer realistic for a production database server, without needing sticky sessions to keep track of who's mid-conversation.

An agent that talks to more than one database, an analytics cluster and a separate operational store, say, is running more than one client at once. That opens the door to tool names colliding across servers and to real headaches negotiating capabilities between them. The tools available to a model can change from one request to the next, which is part of what makes dynamic discovery worth building in the first place, so nobody has to bake the full schema into the build. But that same flexibility turns into a liability if a server dumps too many tools into one list. The model has to read through all of them and pick, and routing accuracy gets worse as the list grows. Scoping the list by whatever authorization came with the request, the spec's built-in mechanism for this, is one way to keep that list short and relevant. The spec also lets clients open a standing subscriptions/listen stream so they hear about tool list changes as they happen, useful when access or schema shifts mid-session.

Assigning Tool, Resource, and Prompt responsibilities in a database query server

Every database interaction the server exposes needs to land somewhere. It becomes a Tool if the model should invoke it and it might have side effects, a Resource if it's just context to read, and a Prompt if it's a structure a person picks.

Schema belongs in Resources, not Tools. Table names, column types, keys, constraints, all of it should be something the model reads before it writes a single line of SQL, not something it calls mid-query. Schema awareness is the whole point: the model needs the real structure in front of it up front, not fetched piecemeal through a tool call that adds a round trip and makes the query depend on whatever the model happened to think to ask for. Exposing schema as a cacheable Resource fits neatly with the 2026-07-28 spec's emphasis on stateless, self-contained requests.

Query execution is squarely a Tool's job: model-invoked, potentially has side effects, returns structured results back to the conversation. Read and write should be two separate tools with two separate inputSchema definitions, not one tool with a flag buried in the parameters. That split is what lets an operator hand an agent read-only credentials and mean it. And tool descriptions aren't decoration: the model picks a tool by reading its name and description, so "run query" as a description is going to get misused the first time there's more than one query tool on the list. For a query tool specifically, exposing parameters for row limits, a timeout, and a target database isn't optional polish, it's what keeps an agent that fires queries at machine speed from triggering a scan that never ends.

Prompts handle workflow, not query logic. "Analyze this table" or "walk me through a migration" are Prompts because they shape the conversation, they don't execute anything. Putting actual query logic inside a Prompt and hoping the model executes it correctly is the mistake here. Prompts don't enforce a parameter schema. Tools do. That's the whole reason the split exists.

The intended sequence looks like this: connect, list the tools and resources, read the schema Resource, generate SQL against the real schema, call the query Tool, then iterate based on the result. A well-designed server makes that sequence the obvious path, not something the model has to figure out through trial and error.

Where the mapping goes wrong

Putting schema lookup inside a Tool causes the model to call "describe table" at the start of nearly every query, sometimes more than once in the same multi-step task. That's a wasted round trip every time, and at real agentic volume the cost adds up fast, not in a straight line either. Uber's QueryGPT reportedly handles over 1.2 million interactive queries a month; redundant tool calls at that scale don't just add cost, they add cost on top of cost.

A single "run any SQL" tool with no constraints is its own kind of trouble. No row limit, no timeout, no split between read and write, and there's nothing standing between an agent that's probing data in parallel and a scan that touches far more data than anyone intended. It also closes off scoping entirely: a tool that can both read and write can't be handed to a read-only agent, because there's no read-only version to hand over. The human-in-the-loop idea the spec leans on doesn't mean much if every query, safe or not, routes through the same opaque call.

Query logic buried inside a Prompt runs into the same wall from a different angle. Prompts don't check types, don't validate table names, don't enforce anything. A model working from a Prompt that just says "generate SQL for this table" is working blind, and the errors that follow have to get fixed mid-task, which means more round trips, not fewer.

Then there's the quieter failure: ignoring list size and order. The spec calls for deterministic tool ordering, so a server that shuffles the list on every call is paying a cost cycle after cycle for no reason. And a long, undifferentiated list of tools forces the model to sort through more options than it needs to, degrading routing accuracy as the list grows. Schema that's hardcoded into a tool description or baked into a Prompt instead of served live as a Resource is fragile in the most ordinary way: rename a column, add a table, and the agent's queries break quietly, with nothing in the protocol to warn anyone it happened.

Agentic database access changes the scale and stakes of every mapping decision

Agents don't query like people do. A person asks one question, gets one answer. An agent probes, often firing many queries in parallel to explore a problem instead of settling for a single pass. OpenAI's internal data agent reportedly queries across 70,000 datasets totaling 600 petabytes, which gives some sense of what "iterative" means at that scale. Sessions spin up and shut down fast, often faster than any human workflow would, so the server on the other end needs to handle a lot of concurrent, stateless connections cleanly. And a lot of that data doesn't live in one place: data is frequently spread across disparate silos in practice, so answering one question from an agent often means touching more than one MCP server at once, compounding the client and tool-namespace issues raised earlier.

Billing is where this turns into a real budget problem, not just an engineering one. An agent firing many short queries a day runs straight into minimum-duration billing: motherduck.com states a 60-second minimum increment can work out to a cost multiplier several times over compared to per-second billing. A cost model built around the assumption of occasional human queries punishes exactly the kind of constant, curious querying an agent produces by default. And the way a Tool is built can make this worse or better: a Tool that triggers a cold start on the warehouse every time it's called compounds the billing cost directly, so lazy resource loading and connection pooling at the server layer aren't optimizations, they're necessary.

Accountability gets harder too, for an obvious reason: the spec's human-in-the-loop idea assumes someone is watching, and an agent firing off queries continuously doesn't leave much room for that. The mechanism exists on paper, confirmation prompts, visual indicators, but whether it's usable in practice comes down to how the server's tools are built. Autonomous doesn't mean unaccountable. Every tool call, not just the final answer shown to a person, should be logged, including the tool name, the arguments, the result, and who or what triggered it. The stateless design from the July 2026 spec actually helps here, since each request carries its own _meta context and is self-contained, which makes per-request audit logging a natural fit rather than something bolted on.

Read-only isolation deserves to be a first-class decision, not an afterthought. Separate read and write tools, hand agent credentials the read-only ones, keep write access behind tighter authorization. That's the spec's per-request credential scoping applied directly to the question of database safety. Sandboxed or branched environments let an agent poke around real data without ever touching production, and the tool design should say so out loud, in the tool's name and description, not hide the boundary inside server logic the model has no way to see.

Considerations for column-oriented, analytics-style databases that generic MCP guides skip over

Analytical, column-store databases raise a schema question that row-oriented databases mostly don't: the physical sort order of the data. Many of these engines physically order rows on disk according to a defined sort key, and query performance depends heavily on whether a query touches that key. One widely cited example: a sparse index storing one entry per 8,192 rows can cover a substantial number of rows in a small fraction of a megabyte. An agent that doesn't know the sort key has no way to write a query that takes advantage of that index, and it'll happily write one that forces a scan of every block in the table.

That means the schema Resource for one of these databases needs to carry more than column names and types. Sort keys, partition keys, and whatever data-skipping indexes exist all belong in there, because that's the exact information the model needs to write a query that doesn't force a full scan. Query on a column outside the sort key and the engine has to read every block looking for a match, even in the best case reading thousands of rows to find one. At agentic query volume, that inefficiency doesn't stay small, it compounds every time the pattern repeats.

Tool parameters should reflect how these engines actually process a query. Exposing something like an early-filter clause as its own explicit parameter, separate from the general WHERE conditions, gives the model a way to signal filtering that should happen before heavier processing kicks in, rather than leaving that decision buried inside a block of SQL text the model has to get exactly right on its own.