Retool Queries
Adding a Query
Now that we have some data in our demo database, we need to return to the app dashboard so we can query it and test that our queries are working okay. To do this return to the editing tab in your browser.
We are going to work with some code-functions in Retool now. For this example we will create a simple PGSQL query which will return all players in our table, sorted by most recently logged in and paginated to X players per page.
You can access the raw table data by navigating to the ‘Code’ tab in the dashboard. click the ‘+’ button and add a new ‘Resource Query’.
Each database and API integration you create in Retool is available as a ‘Resource’. A set of resources is our apps API for accessing different datasets.
Select ‘Retool Database’ as the resource and name the query ‘getPlayers’.
We will now paste the following query to get all player records and once that is done we need to change the run type to “run only when manually triggered”.
SELECT player_id, last_login, player_email, player_name, access_type
FROM players
ORDER BY last_login
DESC
Click “Save & Run” to run your query for the first time and verify that the player records are returned. You should see the record(s) output in a panel below where you can edit your query.
Back on the editor window, select the table by clicking on it to highlight it. You can now change the data source in the right-hand-side toolbar.
Triggering a Query
You may see a warning to let you know that the data source is empty, so we will add a trigger to the search button to call the query.
Select the button and under ‘Interaction’, add an event handler that will trigger the query on a click action.
Clicking the button does not update the table schema, so we need to regenerate the columns. Select the table again and click ‘Clear columns’ and then ‘Regenerate columns’.
Now the player records will be displayed in the table.
Summary
Now we can query a dataset from the Retool database.
From here you can build UI components to add, update and delete from the dataset.