AMAZING! Talk To Your Xano Database with AI and MCP!

Xano is of course a backend, and that means it has this database functionality that is core to a lot of the way people get value out of working with their Xano. And when they are doing that, you know, we work with these various tables, we create tables, we work with them, etc. Now previously, the main way you would get access to all these tables is through Xano's own function stack, right, where I could, you know, pop in, I could make, you know, API endpoints, and I can, you know, from there, you know, if I can go make a function, go add a new function, say, you know, test, test query, right? You can do things like, you know, create a new record, uh, what have you, they're going to just work for you. Okay, so Xano databases are often a big part of the way people are getting value out of Xano as a backend, right? You can have lots of tables, those tables can have relationships, et cetera. And we've talked about other tools like being able to export the schema out to DBML using the extension and what have you. But when Xano introduced its new pricing, you know, a couple months ago, they introduced the starter package for a lot of people and that would make things cheaper. But they also introduced the idea that all of their so-called pro plans would come with a built-in feature that previously was just an upsell, and that is called the database connector. So the idea is you are now able to talk to all these tables, not just through working with the function stack, but also by, you know, talking, just using the usual PostgreSQL connectivity. At the same time, they changed up the way date tables are structured, right? So if I go back to my dashboard, you know, you can see over here that there is this feature, right, to be able to convert tables, right, into the, you know, to migrate what they call migrate to standard SQL columns. And that, you know, which means your tables now look, well, normal. Previously they were in this, you know, XDO structure, which was essentially a document structure, made them look more like Mongo tables and look like PostgreSQL tables just because they stuffed everything into a single JSON column. But now these tables are just regular and tabular. And the— Xano's storage of data in tables is one of its big features as an infrastructure vendor. You can store your data, right, in Xano. And it has a, you know, table stakes, you know, a transactional database available to you. Now previously, the main way you would have worked with these tables is through Xano's own function stack, where you can do like a get record or query all records, etc. And all these kind of penetrated the way that Xano worked, because which has kind of a weird database setup. Right? They had something called an XDO table that I've talked about in a previous video where I talked about the database connector and how to talk directly to Xano tables. But they're actually much more useful now, and there are two big reasons for that. The first reason is that all Pro plans in Xano, which is, say, everything, you know, above Starter, which is probably anything you'd use for any significant work, is, uh, you know, contain the database connector. Used to be just an upsell, right? You have to pay extra for it, and now it's just built into what they now call the Pro plan. Previously it was called Scale, did not have the connector. Now it's called Pro and does have the connector. But the second feature they put in was they started converting their tables into what they call standard SQL schema. And that has to do with the way that the tables were really funky before. And they were in a structure that, you know, had a— basically they took all the data from each record and stuffed it into a single column called XDO. I sort of call that the Xano Data Object. As a result, the tables looked a lot more like— As a result, the tables looked a lot more like Mongo tables or NoSQL tables than they looked like regular relational database tables. That actually made them really hard to work with because you could use the database connector, but largely you could read data relatively easily, but you couldn't write data. All that easily. So one, it was an upsell, and two, it was relatively limited value because of this whole XDO thing. And you could write data into it. It was just hard to work with because they use this sort of weird mechanic about stuffing things into the JSON field. But now there's this idea of migrating to standard SQL, and you can even— if you are a longtime Xano user, you can convert your tables. Bing, bing, bing, bing, bing. And here I can take like a table and I can just say I want to confirm and convert it. And it will convert it fairly instantly. Some big ones, it can take a little bit longer. And really, as long as you're converting, it might be shut down. So do this outside of business hours. But the idea is you can now have all these tables in a regular structured setup, which is basically just tabular. So if I were to bring up DBViewer for a second, I can look at my Xano databases and my tables. I can go into my public schema and I can take a look at my tables. You can see there are a whole bunch of tables in here. But what gets really exciting is that you see these ones are named after these particular, the numbers of the tables. Well, if I just go back over here and I say, well, I've got a table here, it's called circle_posts, right? You see how it was number 270? Well, I can go back over here, I can say MVP19, 19 is actually the number of my workspace, you see that up here in the URL, 19_270. And I can, you know, look at that and understand it more data-wise, but also I can say let's go make a query on that. Right. So what if I wanted to— that's actually not nearly as exciting as it could be. I want to take this particular, you know, table, right, which is— let's sort of take a look at the Navigator. And we'll take a look at the databases inside and this one and take a look at my schemas and my public schema and my tables. And I'm going to look at my, you know, MVP pw for the workspace, right? And I'm going to take a look at 270, 270. Oh, there's my guy. And then I can right-click on it and I can say, let's generate some SQL, right? I want to just say, let's just select, okay? Copy that. And I'm going to say, let's make a new SQL query, right? And let's try running that for a second. Right? And it will just like pull down the data just like I have in my Xano, right? So if I were to go back over here to circle, you know, posts here and take a look at the, you know, the 271, right? You can see this view in here looks a lot like the view in here. And the cool thing about that is because I'm talking to it directly, right, from the outside world, that means I can use other code. I don't have to use Xano code to go look at it. And I can do even something better. I can look at it through an MCP. And that is something that's now built into Xano that was not there before, which is super, super handy. So let's talk about how to get access to that because that, that I think is not as obvious to a lot of folks. So when you're inside your Xano, you can say up here, switch workspace, and you can say go back to my workspace, which will bring back your instance level view. And then you can click on instances here and that will bring you up to your top level view, right? Which will let you see your various instances. And here's my state change one. And this will bring me back to that same view that I had before, right? But what if what I wanted to do was, you know, connect to the database? Well, I can do that now because Database Connector is now available to you for all Pro plans, which is super, super useful. I can click on Database Connector and I can go find out what my IP is and I can figure out what my, you know, database credentials are. And so I can get database credentials. Right? And I can— which will then let me read them: username, password, host, what have you. And I can also, if I want to, revoke those credentials and then bring them back, which becomes really important if you're gonna do things like make a YouTube video about it, right? So I can show, hey, how all these things work, right? I can see what the host is and what the database is, what the password is. And this is the information I can give to my, um, uh, DBever, right? And there's also information I can give to, say, a, you know, an MCP server that knows how to talk to PostgreSQL. So let's, let's talk about doing that for a second. So let's talk about the, you know, PostgreSQL MCP server, right? And now what we can do is take a look here, and we have a few different guys out here. And Pulse MCP is a great directory of these servers. And here is the one that's about PostgreSQL, which is by— it's deprecated right now, but it is by far the most popular one. It's actually made by Anthropic, so it has a little bit more trust. And right now it only does read-only, which I find to be actually kind of encouraging because that's all I want to do right now, right? So if I go over to its GitHub repository, I can find out how to work with it. And one of the easiest ways to work with it is to use this NPX-based version. So you can see, oh, well, here's how to do it with NPX. That's super cool. So now it's connected to my preferred MSP server. And in my case, you can, do this through cursor or whatever, which actually can be really great for finding out how the tables work, because then you can make code that will then go talk to this particular server, which is super slick. But let's, let's go, you know, make a new MCP server. And you— here you can see that, like, you know, if I were in, you know, Warp, which is this AI-enhanced terminal server, which I think is a great way to be doing MCP, you know, what I can do is I'm just going to say— I was going to say this is offline. Fine. So I'm going to add a new one. And let's, you know, we'll paste this. Oh no, don't paste this equal in. Let's paste in, you know, the command here, right? So we'll say copy, right? And paste. And actually, I don't need the mcpservice part, right? So now I've got my Postgres, the name is postgres and the command is whatever, whatever, whatever. I can say save. It doesn't like that. Why doesn't it like that? Uh, because it probably has one extra in here. Yep. Uh-huh. And I can say save. Yep. All right, so now you can see that there's a, uh, Postgres in here, right, which has my local DB. Uh-huh. It didn't like that at all. No, it did not at all, because that's not actually the real server. So if we go edit it again, I can change this PostgreSQL to the correct host. And I go back over to my XAML, I'm going to say, okay, what was host? Well, the host is just going to be this guy. And what's the database? The database is this guy. And then the, uh, in order for this to work, and, and this also where the documentation can be helpful here is you need a connection string that's going to work with the username, right? So what we need to do is make sure that the user and password are correct in here in order for this URL to work, right? So you have the URL, you have the URL, right? And so the, you know, what I would do is say, well, the user and user colon pass at, right? And so the way you'd handle that is you would say colon You know, user, pass, at. I think that goes after the schema, right? And user, right, slash slash user colon pass at. Okay, and user colon pass at. Good. Now I need my user, my password. So what's the username going to be? I go back to my Xano and like, here's my user. My user is not very exciting. Copy, and I'm gonna put that in. Paste. And then my password is not very exciting, right? It's just a bunch of hexadecimal characters. And I'm gonna say paste. Right? And, you know, I'm only doing this in a nice simple server like this, but you, you know, I'm sure the wheels turning here, how you might be able to use this like in the context of Cursor or WinSurf or what have you. And the, and now I can say save, right? And now I can say Postgres, see Postgres running, has Query as a tool. Right, so that seems pretty good. So let's, uh, now that we've added that, um, let us, uh, look up, uh, well, now it's gonna be able to run queries, right? So let's make a new tab here and we will say, you know, let's, uh, you know, show me the titles of posts in Circle that are about mental models, right? And what it will do is it will make use of the MCP server to do the job, right? Query blah blah blah blah blah blah blah from Circle posts. Okay, all right, sure, why not. Find out schema. See, none of this requires Xano for me to— Xano as a build tool or Xano even as a data explorer, right? I can now do this entirely through MCP. And the fact that I have this kind of access sort of, you know, allows me to subvert ideas about like what administrative interfaces look like. And now I can, you know, learn more about those and I've got all this stuff going. Now there are other MCP servers that will allow me to do writes as well. Usually creating tables is something I recommend doing inside the context of Xano because that way Xano's recording metadata about those tables. So restructuring a table is something I really recommend doing, you know, from inside the Xano context or using its metadata API, or you could even use like the Snappy MCP server, which is also pretty cool. But if you want to just be reading and writing records from your tables, it can do that, and it will do that pretty intelligently because of this nice tabular view where you can both explore it using something like dbViewer. And by the way, if you want to know how to add something else to dbViewer, you can just, you know, click this little button in the corner here, add a PostgreSQL, and then provide it the same kind of information that we just talked about, right? Which are, you know, what's the host, what's the database, what's username, what's password, right? All stuff that you can get from your Xano. And if you missed how to get this, you know, it's— you just get here by, you know, going to your appxano.com/instance, right? You go into your instance, you say database connector, you say get me some credentials, and then you see— and then you just say show credentials and it shows us. Don't forget, it can be very important too, as we're about to do, uh, you know, we're about to regenerate If you actually show these things to anyone else, it's probably even probably just good practice to rotate these on some regular basis because these credentials don't affect the way your Xano is going to work, but they do affect the way that like anybody else who'd be depending on this is going to work. And now you can imagine, right, the expanded universe of being able to analyze your data, of being able to ask interesting questions about it, being able to write interesting things to it, being able to just populate data just using MCP, right? You don't have to go through the Xano build interface anymore. You can just be using Xano as infrastructure and be allowing the Xano, you know, the function stack, right, to be for the things the function stack does really well and allow MCP to be able to do this stuff directly and go talk to your database, even connect it to other databases, pipe data back and forth. There's lots of fun that can be had with it. Anyway, I hope that you find this to be helpful. Certainly getting into the guts of Xano is something that we talk about quite a bit over at StateChange where we work on the hardest 5%. And if you find this to be useful, like, subscribe. We'll have more videos like this. And if you're finding these kinds of challenges, you might check us out over at StateChange as well. Talk to you next time.