Create an MCP Resource

[00:00:00]
>> Brian Holt: Before we spin wheels any further, let's just go build it and then you can kind of see what I'm talking about. If you have not cloned the MCP issue tracker, please do so I had you do it at the beginning of the course and then let's go into MCP here.

[00:00:17]
So I have it on my desktop. So I'm going to open that MCP issue tracker here and I'm going to open that in VS code.
>> Brian Holt: So, close that. Just a quick lay of the land here. This is kind of laid out like a model repo where I have a bunch of commands that run things in various different folders.

[00:00:52]
There's a backend directory that runs Fastify. Okay, so it is Fastify, which is just an express style API server. There's a front end and I think this is just straight up react. I don't think I have anything like next or anything fancy in here. React, react-dom, some other stuff, tailwind.

[00:01:14]
This is definitely vibe coded cuz it felt like it was fitting with the course, right? So as a good vibe coder, I'm taking no responsibility for any of the code in here. If it's broken, it's the agent's fault, [LAUGH]. Then we have an MCP directory. I have left the solutions in here, so if you get lost or anything like that, by all means feel free to just go ahead and use those.

[00:01:41]
But I'm going to create a new directory in here and we're going to code it from scratch together. New folder and we'll call it my MCP or something like that. Okay, and then we're going to go into my MCP. We're gonna say npin init -y and we're gonna install a few things in here.

[00:02:08]
We're gonna npm install @modelcontextprotocol/sdk@1.16 and zod@3.25.76.
>> Brian Holt: Okay, you should get some packages with that. And then here in my MCP I'm just going to create a new file here. We're gonna call it like main or something like that. Yeah, I called it main.js.
>> Brian Holt: Okay? And a lot of this is gonna feel pretty similar.

[00:02:58]
I'll go ahead and code this along with you. But like really wouldn't be too ill served by just copying and pasting this as well if you don't want to do it. Modelcontext/sdk/server/mcp.js and we're gonna import stdioServerTransport from @modelcontextprotocol/sdk/server/stdio.js. We need to get an import SQLite 3 as well.

[00:03:34]
So you have to go install that.
>> Brian Holt: Import path from path and import file to URLPath from URL, okay? I'm just going to check what version of MCP. This one, 517. Okay, so in here we're going to say npm install SQLite 3@blah5.1.7 as well.
>> Brian Holt: So I chose SQLite here.

[00:04:17]
It's funny because I work at a postgres company, right? But it's really easy to just do all this stuff locally. Okay, so we're here in main. Keep going. From here we're going to say const server equals new MCP server. We'll call this one like the issue server name issue server and the version will be 1.0.

[00:04:49]
And then here last time we were saying register tool, right? Server.register, but we're going to do register resource.
>> Brian Holt: Okay, and then we're gonna give it a title of Database Schema. One thing, if you ask like Claude code to generate this for you, which certainly was how I started, it's almost always going to generate for you a resource template, because that is generally the better way of doing this.

[00:05:30]
However, Claude desktop doesn't support it, right? Neither does Tome, right? And so it's like Whack a mole game of what are my tools support? What does my client support? What am I trying to support? So in this particular case, we have to do it with just a normal resource, okay?

[00:05:47]
Then you have to give it this schema. This is a protocol. I'm calling the schema cuz it's the database schema, but you could call this whatever, right? It's just whatever you choose to call it the protocol. Because this is how internally it's going to refer to as this like unique identifier of like, on this protocol, I can access these particular resources, right?

[00:06:13]
It's treating it like a URL essentially. So there's nothing magical about the word schema here, I just wanted it to be very clear about that. And then I'm just gonna give it this schema for the database. We're not going to give it any additional URL, so it's actually really nonsensical what we choose to call it here.

[00:06:35]
>> Brian Holt: Okay, and then we're gonna say the title is Database Schema. The description is SQLite schema for the issues database. And the MIME type is text/plain, which is just like you're telling the LM how to interpret it.
>> Brian Holt: Okay, and then we're going to say async, and here we're just going to do the actual whatever we want to do.

[00:07:20]
In our case, we're just going to request from the database what we actually want. So, here we're gonna say const db = new sqlite3.Database and we're gonna give it the dbPath. Did I do all that magic? No, we'll do that up here in just a second and we'll do sqlite3.open readonly.

[00:07:53]
So we were talking about this earlier, not that this really matters here, because the LLM won't really have access to this. But generally speaking, when you're working with agents and LLMs, if it should only be read only, please mark your connections as being read only. So now if for whatever reason this gets like repurposed to be a tool or something like that, where, the LLM could have more influence of how this is used, or you're passing an SQL or something like that, you're still stuck in read only mode, which is just good for not dropping databases.

[00:08:27]
Let's go define DBPath up here, because I did not do that yet.
>> Brian Holt: I've written so much note over the years that I'm just used to dirname being available, but it's not available in ES modules, so you have to write this special ritual incantation to make it work.

[00:08:52]
Or normally I just ask, why is this on son of four, okay, there we go. Normally I just ask my agent to do it for me. So define_dir name for an es module file. Because I can never remember how to do this, but it's like the same thing every single time.

[00:09:13]
It's exactly this. Const file name = file to path, which is why we imported that up there. And then const_dirname = path.dirname(-- file name). And now this is dirname as it would be defined in a common JS module. If none of that makes any sense to you, just don't worry about it.

[00:09:30]
Bless you, sweet child, that you never have to know this, [LAUGH]. Okay, so now dbpath is. And we still have to define what dbpath is, right? Where do I define what DBPATH is? Yeah, right there. Okay, const dbPath is assigned path.join(--dirname, ..backend, database.sqlite. We're in this MyMCP directory we want.

[00:10:10]
In the backend, there's this database file that we want to reference. So it is the dirname, which is where this is being run up one directory in the backend directory, database.sqlite. Okay, and then we're going to use this DB but we want to wrap this all in a promise just so that this all resolves correctly.

[00:10:33]
So we're just going to do that. I think that's going to be much easier if we do this. Const schema equals await, new promise, resolve, reject. Okay, and then I'm just going to move this DB column to here, inside of this schema promise that we just created. And because we're awaiting this promise here, and this is an async file, everything should just work out, right?

[00:11:05]
Whatever this ends up returning should work. And this probably should be an async. Is it? No, no, stop. Okay, we're going to say db.all, and we're just going to write a query to say select SQL from SQLite master. This is a little SQLite Blackmagic where type equals table and SQL is not null, order by name.

[00:11:46]
Oops, other way around, order by name, there we go. This is the way that you just dump all the schema out of a SQLite database.
>> Brian Holt: Okay, it's gonna do that. It'll give you a callback error rows and then you say db.close because we're done with it. If error, reject, error.

[00:12:29]
>> Brian Holt: I guess let's be less clever about this else, resolve rows.map.
>> Brian Holt: Again, we're kinda just formatting this to make it a little bit easier for the agent to read or the LLM.
>> Brian Holt: And row.sql.
>> Brian Holt: And then here we will join with a new line character. Where am I messed up here?

[00:13:20]
>> Speaker 2: It's up on line 17 or something, 16.
>> Brian Holt: All right, there.
>> Brian Holt: That's-
>> Speaker 2: Should just be a string.
>> Brian Holt: Yeah, it should just be a string, right? Database schema. That's really what I wanted to hear. Just something like this, yep.
>> Brian Holt: So, this is a little opaque here, but essentially this line is going to dump the entire schema from the database.

[00:13:55]
If there's an error, it'll call reject, which will fail the entire promise here, this promise. Otherwise it's just going to format this nicely so it can be given back to the agent. That's the entire thing.
>> Brian Holt: Not a database class, so I'm not gonna dwell too much on this.

[00:14:16]
And again, feel free to just copy and paste that if that's too opaque for you. All right, so at the end of this, this is being called resolve with that string, right? So then schema here will be the results of whatever resolved is called with, right? So now schema is that string, make sense?

[00:14:39]
Or if it's not, then there was an error and it rejects and everything blows up and everybody dies, it's fine. Okay, so now at the end here, we're gonna call return, and it's gonna have contents. This should look very familiar from what we did last time. And it's gonna have essentially a URI: so again, the URI is this up here, and it's letting Claude know how it can cache this.

[00:15:15]
So it'll just be this, right? It'll cache it with that as the cache key.
>> Brian Holt: MimeType as being text/plain, and then text will be schema, okay? Const transport = new standard IO transport and await server connect transport, which we did that before, so this should feel pretty familiar.

[00:15:57]
Okay, good so far? So this takes this entire database right here. Obviously, I can't open it cuz it's a SQLite file. I probably can open it, I think I have an extension. Anyway, it doesn't matter, it's just a big SQLite database with a bunch of issues being tracked in it.

[00:16:22]
And then it's dumping that out and returning as a resource.