Database Connector REVOLUTIONIZES Your Xano Experience

The Xano database is a big part of the value proposition of why people, you know, hire or get Xano, right? The idea is that you have this instance and these function stacks that are integrated with having this database, which we all know is a PostgreSQL database, which is a type of relational database that is, that can just sort of store tables and tables can have records. So in here I could look at say this list of tables and the tables can, and within a table I can have these, you know, structure, which are the fields within the table. It's like the columns, right? Going across. And then I can have additional records or whatever that will contain data with them and all sorts of different data types that are available to us. And the way that we usually interact with these is going to be using the, you know, the database request functions are available to us inside our XANA, right? Like the, being able to query our records, get a whole bunch of things or get record or making edits or whatever. But we all know that there's a database behind the scenes and that because there's a database, there's more than one way for us to access this information. In fact, there are certain plugins that like, I think the Softr plugin that for Xano that allows us to get information out of the Xano database without going through the Xano function stack. And this can be important for, you know, other, you know, ways we're getting more leverage from our Xano. This can be important for administrative purposes for being able to do easy backups, that kind of thing. And just being able to express control over the data so it's not just sitting inside our one instance. And so to do this, there's actually a feature within Xano that allows us to access that database under the covers, right? So rather than going through the function stack or going through the newer metadata API, there is an additional feature within Xano called the database connector. And that database connector is basically just, you know, gives access to a username password combo that allows you to go talk to that database from outside of the Xano instance and that then you can, you know, get, you know, go ask questions directly of the database from wherever by just sending SQL to it. Now, this will also uncover some weirdness in the way the Xano database is set up. But we today are going to take a look at the Xano database connector how to get activated and how to, you know, get some, get some value out of it right away. So let's turn our attention back to our Xano function stack or Xano instance where we were looking at this database and we can take a look at the dashboard and be able to see all the stuff going on. And this is with the extension, of course. And the, and what we can do is go up a level because the workspace is not where we have our databases and, and or do we have our tables. Now I'm going to quickly note that inside these tables we have a name, then we also have a number and a number and a number. And these numbers are going to be very important for us to remember as we're getting deeper into how these databases work. But for us to get access to the connector, we need to go up a level and we need to say, let's go take a look at our workspaces. And then from there we need to go take a look at our instances. And this is where we can change up our billing and like what, what, what features our given instance has. So when you look at the billing, you're able to set up, you know, what's going on with your particular instance and you can, you know, go from like launch to scale or whatever else. And this feature that we're talking about is mostly for the Xano paid plans as well, because the Xano build plan, which is like their, the free tier, works completely differently than the paid ones do. And they don't actually have any of these features. But once you have your own instance, when you're paying either the launch or the scale level, you have the opportunity to get an upgrade, which is this database connector. You can just do that by clicking change plan in here and being able to, you know, manage your add-ons, right? And here you'll be able to see that I have an add-on in here, which is the database connector, right? Which I activated. And because I'm on annual billing, it's costing me, you know, $300 a year to enable it, which is about $25 a month. I gotta say, as compared with the cost of the rest of the Xano plan, which is about $200 a month, but I'm paying for the year, it's not that, you know, $25 tax is, you know, a little over 10%. It's not that big a deal, but it does unlock quite a bit of value. So let's step back here and then take a look at our instance. And once you've actually purchased this, you're going to have access to this little gear here. And inside the gear you can click on where it says database connector. And this allows, this starts to give us the information necessary for us to go talk to our database. Now, I'm going to encourage a couple things from a security point of view. The first is underneath settings, this is where you can set up an allow list of IPs and you don't necessarily want every Tom, Dick, and Harry going attacking your instance to find out whether or not you have the database counter activated and then start to try to log into it. Now by doing this, you can make sure that only of traffic from particular IPs on the internet will actually be allowed. Now when you're trying to do this for yourself, I would actually recommend do something like, you know, what's my IP? And there are services out there that will just tell you what your IP address is from the point of view of the outside world. You can highlight this and then, you know, go back over to your Xano and add that in. Now later on, we have like a third-party server or service or something. You probably want to add that as well. And initially it's probably okay to not set this, but I recommend doing that as a way to to handle real lockdown because this is unhackable, right? There's no way they can get around it by figuring out your password. They're not even allowed to check the password if they're not coming from the correct IP address. So I can say save here and that will just make sure that only I and nobody else will be able to make that kind of communication. And if I click back into database connector and I can now, you know, see what the IP address of the database is, which shouldn't be that surprising because database IP is going to be the IP of your instance. The situation which is not gonna be exactly the same as your instance can be when you have some Xano clustering going on, but that tends to only happen at, you know, high levels of Xano, like when you're actually using auto scaling and stuff. But for most of us in the launch or, you know, ordinary scale plans, you're gonna find that this IP address is exactly the same as the IP address of your Xano instance. So with that out of the way, the real interesting question is gonna be your database credentials. Which you can click get here. Of importance, I'm gonna show you some database credentials, but those database credentials aren't gonna do you any good. And the reason they're not gonna do you any good is because I can, and I will at the end of this meeting, revoke and regenerate them. But here you can see how it's set up with a username, a password, right? The host, which is exactly what we're just looking at before, and a database, which will also become important in just a minute. So what you're going to want to do is use a client for talking to PostgreSQL. There are a bunch of good clients out there and you can go surfing around, but I've actually been doing a lot of my exploration using something called dBeaver, which is a newish GUI that runs locally in your Windows, Mac, or Linux environments. And I'm running on macOS right now. And so I can just download it for Apple Silicon. It gives me a DMG that will just let me copy it right into Applications. Now I've already downloaded it and put it into my system, so I'm just going to click on my Launchpad and click DBE, and there it is, DBever, and just open this up. Note that this is the Community Edition, which is free and probably has everything you really want at the outset, although they have a Pro version as well that, you know, has some, has additional functionality that seems like some pretty cool stuff. Maybe one wants to check it out. So I will say You know, that's fine and close it up. Okay, now when we're in here, we're going to need to set up a new connection to the system and everything's sort of hidden. You can see here it says new database connection. We can click on that and we will open up PostgreSQL, which is the big blue elephant. You're gonna find the elephant shows up a lot for all these PostgreSQL things. And here is where we need to set up the connection to the database. We need the host and the database and the username and the password. And if we go over to our database connector in Xano, right here we are, we have a user. Now we can pick which user we're going to use. I'm going to use the full user to demonstrate a wider range of functionality, but you have both full and you have read. The read is going to be particularly useful for something like the Softr integration, which is, you know, pulling down your, you know, lists of stuff and turning your database into a site. And when you just want people to access the information, but you don't want people to make changes to it. Full is read and write access, and that's what I'm going to demonstrate today. So I'm gonna grab the full username and we go back over to, you know, our friends at Debeaver, and I'm going to say username is paste, and then I'm gonna click the password. Password's always gonna look like this sort of hexadecimal notation, the password. And what, where is the host? Copy that. The host is here, is no, so I'm just doing it by IP address. And the database is here. And the database I can do here. Now as a matter of interest, I'm gonna tell it to show all databases because when you have access to full, it's actually giving you access to both this database as well as another database that shows up in all PostgreSQL instances, which is called postgres. But there's not more of the need to worry about with this. Like this is just, you know, using database native authentication, nothing, nothing else very special about it. And you're off to the races. So you can just click finish, right? And then we can open it up. So it's loading and we have a few things in here including databases. Note that the database Postgres is less interesting because it's going to contain information basically about how the whole database is set up and the arrangement of other databases. But there's only one interesting database in your classic Xano instance, which is going to be the ones called xano-whatever, whatever, whatever. You'll probably recognize the whatever, whatever, whatever as being the same as the name of your Xano instance. And the, if you don't have like, you know, custom naming, for example, mine is api/stagingai, which is, you know, that's a custom name that I put on top of it, but the original name would still look like these bits in here. So I can open up the most interesting parts in here. There's some stuff you can go goof around with, but the most interesting parts here are in schemas and public. And inside public, you have two big sections that are going to be of importance. The first one is called tables. Tables are the actual places where data is stored. So we've talked before about tables and fields and your records and your rows and the, and you have all sorts of information in here, which is actually kind of cool because you can run direct requests on all this stuff, which is, which is pretty neat. You know, we can say let's go generate some SQL on it and we can say select and you can see that it will, you know, create some SQL that works with it. And you'll notice it shows ID, create, update at XDO. Wait, that doesn't seem like a lot of information. And that actually gets kind of neat, you know? So let's copy this for a second and then we're going to say let's make a new SQL. By clicking here, it just sort of creates a little SQL board and the rest is only going to be very interesting if you actually know a little bit of SQL. Which is SQL, the Structured Query Language, and you can, you can go get all the information, MBB request, and I'm just gonna say limit 10 so we don't get all the information on the planet. And I can press this little play button up here, which says, which will execute that SQL script. And this will bring down the information, showing it to me in the kind of tabular form that you're probably familiar with if you've worked in the Xano database preview environment. However, it doesn't look like the Xano database preview environment. You're going to find these all have IDs and these all have these XDOs. And these XDOs are important because this is called a JSONB field. And in fact, if we were to open up the columns, you'll see here that's a JSONB field, this XDO. This shows up all the time in Xano. You're going to find MVP process columns. Hey, look, there's an XDO. This is just the way Xano is set up. So Xano is basically set up in the global a semi-document structure where you have your ID, which is primary key, you have a couple of housekeeping fields maybe, and then you have what they call XDO, which is the, which is gonna be a JSON blob that holds all the other information about the table. So basically everything you think of as being a column in the table isn't really a column in the table. They do get turned into columns, what are called views. We'll look at those in just a minute. But basically everything in here you can see as being, you know, a big old piece of JSON. And you can sort of look inside what each of these keys are, right? You have a log and then the log contains, you know, text or an array in this case, which actually seems like exactly what's going on here. And this is going to be for the request history. And then, you know, some of these are for housekeeping. Some of these are larger than others, which are of interest, right? Like this is, you know, the query query log, which again, you'll notice doesn't tell you anything about what's in it. So I can just do MBB query. And this becomes like, I think one of the big values of Game Database Center is just being able to look around at how Xano is working under the covers and being able to see more of the information that's been going on inside each of these queries and structures that they've got. Okay, well let's, and the queries, by the way, those are all definitions of the the API queries and what, and the way that they work on the inside. And you can sort of get more into what's inside there. But the, but, but the, while those are the housekeeping ones, then they show MVP_MVP_MVP_ and then they drop the underscore part, right? You see MVPW19_100 and 101, 102, 103, 104, et cetera. Now the reason why these are important is that these represent the database. These are the individual tables that are sitting inside your Xano environment. Remember I told you to look at those numbers associated with the tables? Well, this is the workspace ID and this is the number of the table. So if I go back over to my Xano, for example, and I say, you know what, don't worry about this stuff. And I say, let's click into the instance. And from the instance I click into one of my workspaces, and from the workspace I click in the database, and from the database I'm going to just take a look at, you know, I don't know, like a particular, you know, one of the contracts from like a project I was working on that was a crypto-related thing, fine. You'll notice it shows the number 16, but it's also associated with workspace number 1. So HS Contracts is actually number 16. So the way that we make use of that in here is we say MBWO, these are all 19, these are all 19, 1_16, right? So if I could say, you know, MBPW1_16, you'll notice it says actually these two columns don't exist. The tool's actually kind of helpful for that 'cause it identifies this actually as two columns 'cause this created_at and updated_at are actually from the point of view of the user tables all sitting inside XDO because of things that you have control over. And you know, when we look at this, we sort of get a better understanding of why it is Xano says we can't update the ID field or rename it or whatever. It's because it's not inside the XDO field. The XDO field is where the user has control and that's where they sort of, you know, goof around and mutate things. Whereas the ID field is completely outside and is not something that the user has direct control over. This also explains some quirks in terms of the performance of Xano where the, you know, the Xano table under the covers, you know, every time we request a record from one of these tables where we're getting, you know, we can't actually limit the fields we're getting back, right? We're actually looking through this entire XDO blob. So the XDO blob has a lot of data in it on a per record basis. That's actually going to slow us down. And I've certainly seen that in the past when I start to add more data, like even though I'm not selecting from the field, right, which is the item within, within the XDO, I've noticed that it seems to be slowed down by the presence of more data. And the reason for that is because it still has to sift through all this stuff because it's not using the primary PostgreSQL field separation. When you're setting up your tables, right? It's all just these elements that are sitting inside this little XDO thing, which is actually kind of interesting to me because it creates, it sets up Xano if they wanted to, to become more of a document database as well, but they haven't really gone down that road yet. Instead, they use the views. So this is the way the data is actually stored in Xano, the ID and the XDO. But it's not the way that you would usually interact with the data in Zillow. Usually you were seeing those columns or whatever, right? And that's not just happening at the code level, that's actually happening in the database as well. Because if I were to go up here, I said there were two things that are of interest. One was the table and the other is the views. The views are where we can scroll down to, you know, go take a look at, you know, that same thing, you know, one underscore 16, which I was alluding to before. 1, the HS contracts. Now these views are basically aliases of a table. So if I wanted to, I could, you know, view the view, which that's a great way of putting it. And I can take a look at source and the views are basically like, you know, downstream representations of SQL, right? So they ran SQL on the original table and converted all the stuff that it found inside the table into these individual columns. Now what this does, which is kind of neat, is if I were to, you know, go back over to, you know, here, right? I could say, well, let's take a look at X1_16. And by the way, this X1_16 is probably something you're going to find kind of familiar if you were to open up the database, database requests and go to direct database query, right? Because if I were to say use a wizard panel and show me all of the, you know, HS contracts, right? And see X1_16, which is exactly what we're looking at right now. So it's so, so the direct database query is actually using the views under the covers. Let's see, x1_16 instead, right? So now I can say, now it's not showing me x1 ID comma xdo, I can just select star, right? And now look, this looks so much more like what we would have in the database view inside Xano, right? Because now it goes from looking like a series of, you know, the xdo thing, which just looks like a big old document, to having the additional columns because it is downstream of having extracted all of this stuff using the view creation process. And now we can sort of imagine how Xano is kind of trying to cache those views, get a little more performance out of it, but keeping things actually in the XCO, which also explains why you can't update because, because this is a view and because the view isn't tied to directly to the underlying columns, a thing I can't do is update, right? So if I want to say update, right? And this would be a perfectly valid SQL, right? UPDATE X116 SET chain_id equals, let's see, 8002, right? So this would be, this looking at this seems like a perfectly valid SQL that I could run, right? 'Cause there's a column here and whatever. And if I tried to do this, when I said, okay, now run this, and then I said, please run that, it'll tell me that chain ID doesn't exist. The reason why this is never going to work is that the chain ID is associated with the view. 'Cause x116 is not table, it's a view. This thing, this table here doesn't actually exist 'cause it's actually part of This is when we understand that we can understand the difference between how we read from the database and how we write to the database. We're working within this and now we have more options where we can say that now we want to read whatever we want to read from it and we're going to write using updates, not to the view, but to the table. And I think that in general, that's a pretty good rule of thumb. You want to read from the, a view and you want to write to the table. So let's talk about how we do that. So the, so here we have all of our views and all these views are read-only as we already established. Like what I want to do is I want to take the chain ID, right, for line 6 and I want to modify it. What would I do? And here I would probably go to the table, it goes underneath it, which is going to be the table1_16, right? Many more of these defined. Oh, and by the way, the test dataset, you know, you usually have a production data source, you have a test data source, you're allowed to set up more. What we will have in here, and I think 19 has a bunch of them, you'll find that there's _test_ to be getting the versions of these that are actually for your test data sources because those are actually different tables underneath the covers. So here we go to the database and we have an ID and we have an XTO. So If I were to say, well, what I really want to do is I want to update, you know, MVPW116, right? And to do that, we do a set, right? But set what? We have an XDO field here. How do we update the XDO field? There are a couple ways of doing it, but like the way I like to do it is to use JSONB set. And to do that, we pass the original we set xdo equals, right? So we're gonna update xdo as this new value and using JSONB set, we're gonna set, you know, we pass the original field, we tell it what path. So we should say where ID equals 6. Now it says it modified one thing. All right, all right. So let's cut that, save, and now we're gonna take a look at the view that's based on that same table. And we're going to go 6, press play, and look at this, 8,000 to 8,002. So this becomes the way that you can update tables alongside being able to get data from them. Easiest way is to get data from the view, although you can always get it from directly from the XDO, but whenever you write, you need to update the XDO to be setting the path that you're really looking for. And if this feels like it's a bit of a long way around, this starts to also explain some of the points at which you might have had, you know, performance bottlenecks or opportunities within Xano. I find the more we understand the mental model of the technology we use, the more opportunity we have to really put it to work. Like this kind of XDO means it's much more extensible. It's a flexible system. It works a lot better with a lot of little fields than having really, really big fields. Like I tried to store large documents in, I was wondering why it was taking forever for the thing to query. And the reason is that even though I was saying please exclude these columns, the fact that all the columns were sitting inside this one XDO field was actually slowing it down. And that was sort of a remarkable insight for me. But this is how we can use the database connector for fun and profit. You can, you know, all of this stuff that we've been doing here through DBeaver is something we can do using other tools, something you can do using like a Node or Python or PHP or whatever. You know, a PostgreSQL library as long as you know where to look. And then where to look is gonna be inside the database that's specified in your connector. It's going to be looking inside public and the public schema and largely reading from the views like X116 and writing to the tables. And then yeah, everything after this is gonna be some exploration and fun. So I'm gonna wish you some happy hacking and Good luck on this. And hopefully this becomes a little bit less being your hardest 5%. I'll see you soon.