Although Data Discovery covers most use cases there are situations in which we need more flexibility of pure SQL. Using SlashDB we can define a required SQL and make it conveniently available in REST API. We call this feature the SQL Pass-Thru.
Let's have a look how to create and use a defined query.
First, we log in with administrative privileges which allows us to add a new query.
The admin creates a new query called invoices-total.
Next, we learn that only privileged uses are allowed to execute the query.
List of query definitions
The Query Definitions page provides a list all SQL Pass-Thry Queries already setup in SlashDB.
The list is accessible from the main Menu Configure > Queries or direct URL
/querydef. It is available only admin and users with Administrative privilege to view list of SQL Pass-thru Queries.
Listed are queries to which currently logged in user has view or edit permissions. User "admin" has always access to all queries.
The list can be searched using search field above the list or sorted by clicking on headers of the columns.
To View, Edit or Run certain query click on or Query ID.
Query ID is a unique identifier for the query which is used when making HTTP requests. For example.
- /query/songs-by-genre/genre/Rock.json - execute the query with parameter genre: Rock
- /querystudio/songs-by-genre - open the query definition in Query Studio
The list contains also some Description of the query, the Database in which the query will execute, Parameters required by the query, allowed HTTP Methods and links to query definitions in , or .
Adding a new query
To create a new query a currently logged in user must have Administrative Privilege to do so.
Click on Configure > Queries in menu and then on the button. A new page with Query Studio and empty form will appear.
Hover cursor over icon to learn more about the field's purpose and configuration tips.
Choose unique Query ID to represent the query, select Database in which the query will execute. Assign which users will be able to View, Edit, or Execute this query.
Select which HTTP Methods triggers the query. Naturally it makes sense to use:
GETfor SQL statements that return data e.g.
PUTfor SQL statements that modify existing data e.g.
POSTfor SQL statements that create things e.g.
DELETEfor SQL statements that remove things e.g
Additionally you may add a short Ddescription of this query.
Finally, the most important, write the SQL Statement that will execute in database.
Save the query using green save button.
The query can have a parametrized SQL. Syntax for a parameters is a colon followed by the name of the parameter e.g. :genre. The parameter can be used only in values to prevent SQL injection. It means it is fine to put parameter for example in where clause
WHERE g.Name = :genre but it won't work in select clause
SELECT :columns FROM.
ctrl+spaceshows SQL hints
ctrl+entersaves and executes the query
Use radial play button to save and execute the query.
While writing the SQL a database schema may become useful. Switch Config tab to Schema to see all tables, views, columns, primary keys and foreign keys with reference. The database must be connected and selected from Database dropdown in Config.
While working on the query you'll probably want to test it often. Click on the radial play button in SQL Statement section to Save & execute the query. Returned data or any error message will be shown in Result tab. Hover cursor over play button to slide out links to result in other formats which will be opened in new window.
If query requires parameters e.g. genre then it must be provided in a form on Parameters tab. Use keyboard key
enter when the cursors is focused on a field of parameters form to execute the query.
The dynamically generated URL visible above parameters form is constructed based on values provided in the form and is the URL for executing the query. It can be copied with click on and used in your application.
More options for execute are available in this section of documentation.
When query is executed the data returned from database is presented in a table in Results tab
Config errors are shown above green save button in Config tab if there were any problems when saving the definition of the query like missing.
SQL errors are shown in Results tab if there were any problems executing the statement like SQL syntax error.