Transcript

Welcome back everybody. My name is Angelik Laboy Torres. I'm a developer advocate here at Retool and today I'll be showing you how to connect and query data. Queries are executable blocks of code that patch, manipulate and transform data. I would think of them as an engine to your app. Without queries, your UI components would just show empty placeholder data. Before we dive in into creating our first query, I would like to talk about organization: a crucial best practice that will save you time and a lot of headaches at the end of the day. So you see, as your Retool apps start growing more complex, you can start having dozens of queries and finding them can take you forever. But for that, that's why we use holders. So I'll go over here into my code tab And then from there, you will see the query panels. If there's a query that is at the global level, then put it in so. But if it's page specific, you can go over here and toggle between them. Then I would say like specify it in there. Now, how do you make them? I'll go into the plus icon and then under utilities, folder. And I can do something like, I don't know, APIs. And then if it's something more global, right, you can be over here, folder. And then I can do this and then... Scripts. At the end of the day, what I recommend is to divide them either by functionality, so stuff like API, scripts, authentication, or you can do something like features, so users, orders, products, and many more. Let's start with the most common type, which is the SQL queries. For that reason, let's go into the code tab. I like it page specific, and I'll click on the plus icon. under data you will see a resource query. Click there and then for this example you have many options. I'm using a Retool database so keep that in mind I'm using this one and then you can see there's two modes here there's the SQL mode which is for reading data where you write raw SQL statements and then you have the GUI mode which is just for those more comfortable using it for input same results okay so let's start with the SQL mode. And then I'll do a simple query like SELECT all from products. The products that I'm referring to here is this table which actually shows just product name, price, category. Cool. And then I'll do where price is greater than 100. I'll save here, run this in a second, I'm renaming greater than and then I'll run this. Okay. What you can see down below is actually just the output section and this is the data that you have retrieved. For now, I have the beach ball and the motorcycle, which is correct actually. So we have two products showing up. What I will do now is actually showing you another way with just the GUI mode. So let's create another one. Let's go over here, this icon, research query, Retool database again, and then I'll do GUI. From there, I'm going to actually select the same tables, products, and then you have the actions that you have like stuff like insert update and existing record delete a record, bulk, and no any type of action like insert update etc. I'll do insert a record and then the key value appears since we selected the table it actually takes the columns that it has and for now i just add a hammer and then i'll do a class and then i'll do this as a prize well must not be realistic but 350 just to show that it works on the other side. Category tool and then cool. I'll name this AddingtoTable. I'll save this, I'll run this. Likewise the output over here and then now I've got to go into the GreaterThan and I run this again. There we have it. So the great thing here is that Retool at the end of the day helps you with the proper SQL specifically while we're talking about the GUI over here, right? The proper SQL behind the scenes so you don't really have to worry about writing complex SQL. Now let's add this data to a UI. For that, we'll go into the add UI section and I'll put a table over here that would open up the inspector window and then actually you can see that it has already detected a query. This sometimes happens otherwise it just selects you know placeholder data but But in this case, I don't want this one. And I'll turn this off because I can actually have a drop down of all the queries that I have. So just to emphasize, the two queries have creator's are these. And for that, I want to do the greater than. Okay. And there you go. Now all that's left to do is really just update it and make it pretty, make this into a tag or leave it however you want. I mean, we can do that very quickly. Let's go category. We can do this one as a tag. and that, it's fine. Self-adjust, so it starts looking better and better. And this is really the power of queries. They transform static components into dynamic data-driven interfaces. So sometimes you might need something more than just database queries. So for that reason, we create JavaScript queries. And that helps you really with the custom logic and the data transformation. So let's do that. I'll go over here again, and I'll click on the plus icon. And you might have seen it already actually. If you see over here it says JavaScript and then just click on the query. Okay, so here you can write custom JavaScript to transform data, control app behavior, and at the same time implement complex business logics. So actually before I actually write what my JavaScript is gonna be, I'm gonna create another query. This one is gonna be just to select all the information from the table. So research query, read the database, and then just exactly like that select all products. Cool. I'll read this and then for the eyes, there you go. Both. The reason why I don't want to use the greater than is because it only has the output of the two, the three now that are greater than a hundred. So it's already segment thing. Now what I want to do with this one, I've already written it out. It's actually just another version of the get prices that are greater than $500. But what I'm trying to show you here really is that JavaScript queries are perfect when you need to manipulate data before displaying it or creating any sort of complex workflows. Now, to make queries really powerful, we need to add event handlers. These allow you to chain actions together. So for example, you can have a button and that's when the button put it there. And then you can add an event and alert that triggers a query to update the database. And let's do that. Right. Go over here, the button, and then that opens up the inspector window. I can rename this, uh, refresh. And then over here, you will see the event handlers, right? Each component has the ability to not only attach one, but multiple of them. And they can even trigger one another. So go over here in the plus icon and this could just be the greater than and then trigger it right. The reason B is because this only it shows the greater than information so then when I click it over here you can see that the tape was even refreshing and at the bottom we get a notification for that. So again really powerful. One of the amazing things about queries and I have a taste there already is that you have the ability to have success and failure handlers within the queries itself. So let's see how that looks. Okay. So over here in the code tab, and then let's do it with GreaterThan. And then from there, you will see event handlers. There you have the success and failure area. So let's make one. Let's do success. And then I'll do this action as a show notification. The title is gonna be data refresh success fully. and then the try is gonna be a success. Great. The failure is gonna be also a show notification. This is gonna be fail to refresh data. And then the description is gonna be, please try again. Let's do an error. It could be a warning, but let's do an error. And then now let's run this. And as you can see, the clear ran. and then also the success of that event handler. Now, the good thing here is that you can also chain actions together, and this way you can also keep your control of queries very synchronized. I'll click here and then I can do the career one, which is actually the JavaScript transformation. And I'll trigger over here. So I can save, I can run this. Boom, boom. And then over here, boom, Iran, giving me the information. So wow. Okay, now you understand how queries work as a bridge between your UI and your data. And whether it is for SQLs in order to fetch stuff in your database, whether it is to transform or manipulate with the JavaScripts, or whether you wanna do a bit and large in order to chain actions together or make your workflows more active, the queries at the end of the day are there to make your Retool app more functional and dynamic. So hope you come back next time. And we're gonna be talking about workflows and automations, so I'll see you then. [MUSIC]