Introduction

In the previous post gives a tutorial how to write a very simple MCP server with Node.js & TypeScript. That server says hello, world! and adds two numbers.

Today we're going deeper going to write a few MCP tools that talk to a SQL database. We'll also learn how to return a JSON data and see how powerful LLMs become with MCP servers.

Essential knowledge

I assume that you are already a bit familiar with creating MCP serves using the offical TypeScript SDK. If not, have a look at the previouos tutorial.

We'll also be quering SQLite, but no knowledge of it or SQL is really necessary to understand what is going on.

Prerequiquites

The same as in the previous post, but we'll aslo need the better-sqlite3 and @types/better-sqlite3 package for SQLite. The database itself is here in GitHub.

Context

Let's first set the context. Assume we're a small wine merchant. We keep track of our wine and sales using a SQLite database. It's a simple database that has only two table:

• wine - basic information about wine
  • id, name, type, price
• orders - a list of orders for each wine
  • id, created_at, wine_id, quantity

First MCP tool - list-wine

Let's write the first MCP tool that simply return a list of wine. Also let's make it return only the id and name of each wine. The other details will be returned via a different MCP tool. It'll make for a great example later.

./src/index.ts

server.registerTool(
  "list-wines",
  {
    title: "list all wines we're selling",
    description: "list all wines we're selling",
  },
  async () => {
    // import Database from "better-sqlite3";
    const db = new Database("./data/wine.db");

    const wineList = db.prepare("SELECT id, name FROM wine").all();

    return {
      content: [
        {
          type: "text",
          text: JSON.stringify(wineList),
        },
      ],
    };
  }
);

Let's register the server with Claude Desktop. And ask it for which wines we have using this prompt:

Which wine do we sell?

And we should get the following result back:

What's interesting here is that our MCP tool returns a basic JSON array looking like:

[
  {
    "id": "0678950a-23cd-42a9-b2b3-8900404a6eaa",
    "name": "Château Margaux 2015"
  },
  { "id": "7ba569d7-2b7f-49fb-b4a4-5bf52b60015d", "name": "Dom Pérignon 2012" }
]

While Claude Desktop added more flavour to the response.

Why do retun JSON as text?

You may have noticed that we return the following in the tool:

return {
  content: [
    {
      type: "text",
      text: JSON.stringify(wineList),
    },
  ],
};

That's a requirement of the MCP specification that literally states the following:

For backwards compatibility, a tool that returns structured content SHOULD also return the serialized JSON in a TextContent block.

Returning JSON...as JSON

We can also return a JSON properly as JSON and describe it in the outputSchema:

erver.registerTool(
  "list-wines",
  {
    title: "list all wines we're selling",
    description: "list all wines we're selling",
    outputSchema: {
      wineList: z.array(
        z.object({
          id: z.string().describe("The id of the wine"),
          name: z.string().describe("The name of the wine"),
        })
      ),
    },
  },
  async () => {
    const db = new Database("./data/wine.db");

    const wineList = db.prepare("SELECT id, name FROM wine").all();

    return {
      // for back compatibility
      content: [
        {
          type: "text",
          text: JSON.stringify({ wineList }),
        },
      ],
      structuredContent: { wineList },
    };
  }
);

Due to the limitations of the TypeScript SDK we cannot simply return an array. Instead we return a JSON object that has wineList which an array of wine. If we re-run it in Claude Desktop it will still work.

Getting more information on each wine

We've purposfully skipped a few fields in the wine table from the list-wine tool. We don't return type and price. That's to provide an interesting example.

Second MCP tool - get-wine-by-id:

server.registerTool(
  "get-wine-by-id",
  {
    title: "Gets a wine by id",
    description: "Gets a wine by id",
    inputSchema: {
      id: z.string().describe("The id of the wine"),
    },
    outputSchema: {
      id: z.string().describe("The id of the wine"),
      name: z.string().describe("The name of the wine"),
      type: z.string().describe("The type of the wine"),
      price: z.number().describe("The price of the wine"),
    },
  },
  async (input) => {
    const db = new Database("./data/wine.db");

    const wine = db
      .prepare("SELECT * FROM wine WHERE id = ?")
      .get(input.id) as {
      id: string;
      name: string;
      type: string;
      price: number;
    };

    return {
      // for back compatibility
      content: [
        {
          type: "text",
          text: JSON.stringify(wine),
        },
      ],
      structuredContent: wine,
    };
  }
);

The tool builds on what we have already learnt:

• It accepts an input argument - id
  • it described in the inputSchema
• It also returns a JSON object that we've also outlined in outputSchema

Seeing power of an LLM + MCP

Now let's do the following:

• Let's run the same prompt in Claude desktop - Which wine do we sell?
• It'll give us a list of wine, plus some fluff around it
• Among the wine we'll see Opus One 2018. I don't know about you. Personally I've never heard about that wine before.
• So let's ask Claude in the second prompt, but in the same conversation, - Give me more information on Opus

What happened here is truly magical 💫! Let's take it step by step:

• We asked for a list of wine
  • Claude called out list-wine tool
  • It returned a JSON array with id and name
• One of the wines was Opus One 2018
• In the meanwhile we've also written another tool get-wine-by-id
  • As the name suggests it returns more information about wine if we provide its ID
• But then we ask Claude the following prompt - Give me more information on Opus
  • We don't provide its ID
  • We don't even give the full name - Opus One 2018
  • We simply say Opus
• But Claude figures out what we're asking for
  • It finds Opus One 2018 from the call to list-wine when we just say Opus
  • It retrieves its ID from the list-wine results
  • And passes onto get-wine-by-id

Let's take it a step further - and analyse sales 📈

We have another table in our database - orders. Let's return data from it and extend out get-wine-by-id MCP tool.

server.registerTool(
  "get-wine-by-id",
  {
    title: "Gets a wine by id including sales data",
    description: "Gets a wine by id including sales data",
    inputSchema: {
      id: z.string().describe("The id of the wine"),
    },
    outputSchema: {
      id: z.string().describe("The id of the wine"),
      name: z.string().describe("The name of the wine"),
      type: z.string().describe("The type of the wine"),
      price: z.number().describe("The price of the wine"),
      sales: z.number().describe("The sales of the wine"),
    },
  },
  async (input) => {
    const db = new Database("./data/wine.db");

    const wine = db
      .prepare(
        `SELECT 
              wine.*
              ,SUM(orders.quantity * wine.price) as sales
              FROM
                 wine 
              LEFT JOIN 
                orders 
                  ON
                    wine.id = orders.wine_id 
              WHERE wine.id = ?
              GROUP BY wine.id`
      )
      .get(input.id) as {
      id: string;
      name: string;
      type: string;
      price: number;
      sales: number;
    };

    return {
      // for back compatibility
      content: [
        {
          type: "text",
          text: JSON.stringify(wine),
        },
      ],
      structuredContent: wine,
    };
  }
);

They key changes:

• Returning sales data - obviously
• But it's also important that we've changed the title and description of the tool to highlight that the endpoint now returns sales data.

Let's see more magic 🪄

Let's start a new conversation and a get a list of wine again.

After we'll ask Claude the following:

What sells better Opus or Dom?

And Claude will come back with this magic:

So what happened here?

• Again we asked for a list of wine to fill the context of Claude
• But then we for which wine sells better - Opus or Dom?
• Claude realised it could get that data by calling get-wine-by-id twice - one for each wine
• It got the results and presented them back to us

And we've achived that all by creating only two MCP tools

• list-wines - to get a list of all wine
• get-wine-by-id - to retrieve one specific one

We haven't created any dedicated endpoints for getting a list of sales data or comparing wine. Claude has figured out what to do with very limited tools. Isn't it magic?

And let's also show it on a chart

We can ask Claude to plot that data on a chart, can't we?

Let's prompt it:

Now show it on a pie chart

More magic

Here Claude a bit of JavaScript and React to visualise our data!

Again we didn't have to do anything extra!

Conclusion

• We've learnt how write an MCP server that talks to a SQL database
• And returns structured data
• More importantly, we've seen that by providing just very few MCP tools an LLM becomes incredibly powerful
  • As long as we give accurate explanation of the MCP tools and the input schema, an MCP client can figure out how to use it.