Introduction to Query Tool
- Onesait Platform
User Interface: 5.3.1-Ultimate
Introduction
Query Tool is Onesait Platform's query tool. With it, we will be able to perform queries on the Platform Entities in a quick and easy way using SQL language.
This is possible because, regardless of the type of database being used by the Platform, thanks to the use of Entities it is possible to isolate the Platform from the underlying persistence technologies, offering a layer of SQL abstraction.
In any case, if necessary, it will always be possible to perform a native language query. By default, MongoDB is used, so queries will be performed for this database.
Access to the tool
To access the tool, it is located in the Tools > Query Tool menu.
The tool screen looks like this:
From here we will be able to select the Entity we want to query, as well as generate and launch the SQL (or native) query we need.
In the case of "administrator" type users, they will be able to perform queries to both the RealtimeDB and the Platform's ConfigDB. For all other types of users, they will only be able to perform queries on the RealtimeDB (i.e. to the Entities).
Performing queries
Next, we are going to see some examples of queries using this tool. To do this, we will use a user with a role of type "developer", as well as the Entity with the name "Restaurants".
Query configuration
From the Entity selector, we will perform a search until we find the Entity of the restaurants.
Once selected, a series of options will be enabled that will allow us to specify how we are going to perform the query. These are:
Type: the language of the query to be performed. By default it is "SQL", but it can be modified to "native".
Format: the form in which the query will be returned. Currently it is only possible to get the result in JSON format.
Query Wizard: this toggle switch will allow us to show or hide a small query generation wizard, where by selecting different options, it will create the SQL query we need.
Query options
When we have selected the Entity to query, in addition to more configuration options, a code window has been enabled where we will be able to enter the query.
By default, a query is prepared that will retrieve all the information of the selected Entity:
SELECT * FROM Restaurants AS c LIMIT 3Clicking on the 
Starting from this initial query, we will be able to modify it as much as we need to filter the information as we want to obtain.
Query history
As we make queries on the Entity, these will be stored in a query history, which we will be able to consult at any time. By selecting any of them, the query entry space will be updated with the selected query.
Download query results
Although queries are usually performed to check the data, we may want to save the data of the queries performed. This will be possible by downloading a file with the result of the query, either in CSV or JSON format.
Once the query has been made, click on the "Generate CSV file" button to create the file with the result.
Once we click on the button, we will get a notification indicating that the file generation is being performed in the background.
If the query is not very large, it will be ready in seconds. In cases where the response is very large, it may take a few minutes.
Next, click on the "Download file" button to download the result of the query to your premises.
Immediately, the file will be downloaded in CSV format.
If we open it, we can see that it contains all the information of the query we have carried out:
In case we are interested in having the result in JSON format, instead of clicking on the "Generate CSV file" button, we will click on the "Generate JSON File" button.
To download it, click on the "Download file" button, and it will be downloaded in the same way as the CSV file.
Compile the query
Once we have our query ready, we can compile it to obtain the query code, both in SQL language and in the native language of the database (which, as we have already mentioned, by default is MongoDB).
To do this, click on the 
Query generation
When creating queries on the Entities, we will be able to do it in two ways: manually or by using generative artificial intelligence.
Manual queries
In this type of queries, we will enter the query we want to perform in the space provided for this purpose. For example, if we want to retrieve all the restaurants, we will enter:
SELECT *
FROM RestaurantsIn case we want to filter the results and get only those whose type of cuisine is Irish, then we will enter:
SELECT Restaurant
FROM Restaurants
WHERE Restaurant.cuisine = "Irish"Although the number of records is indicated in the answer, we can also consult only the number of records. To do this:
If we also want to sort the query alphabetically by, say, its name field, the query would then be:
Queries using generative artificial intelligence
We have incorporated generative AI tools in the Platform, and specifically we can use it to make SQL queries using natural language.
We are currently using ChatGPT 4.0 from OpenAI, so a valid API Token is required to be able to use this functionality.
To launch the query, click on the button with the logo of the service provider (ChatGPT in this case).
This will open a window in which we can write the query we want to perform.
Below is an example of a real use of this tool:
In this article we explain in detail how generative artificial intelligence works in the query tool.
Display mode
The data are displayed, as we have indicated in the configuration of the queries, in JSON format. There is the possibility of displaying the data in table format, making it easier to read.
To do this, once a query has been made, the 
For example, for the following query:
The results in table mode would look as follows:
Â