Queries

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-Thru 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.

1. Click on Configure > Queries in menu to show list of query definitions

2. Next, click on the button.

3. A new page with Query Studio and an empty form will appear.

Config

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: * GET for SQL statements that return data e.g. SELECT, * PUT for SQL statements that modify existing data e.g. UPDATE, * POST for SQL statements that create things e.g. INSERT, CREATE, * DELETE for SQL statements that remove things e.g DELETE, DROP.

Additionally you may add a short Description of this query.

Finally, the most important, write the SQL Statement that will execute in database.

Save the query using green save button.

SQL

The query can include user-provided parameters using parametrized SQL. The syntax to enter a parameter in a SQL statement is a colon followed by the name of the parameter (e.g. :genre). Parameters may only be used in values to prevent SQL injection. In other words, you can use a parameter in a WHERE clause (e.g. WHERE g.Name = :genre) but you cannot use a parameter in a SELECT clause (e.g. SELECT :columns FROM is not allowed). Parameter values may be set to NULL in the query studio by toggling the NULL button on the parameter field.

Key combination: * ctrl+space shows SQL hints * ctrl+enter saves and executes the query

Use the play button to save and execute the query.

Schema

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.

Run query

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.

Results

When query is executed the data returned from database is presented in a table in Results tab

Errors

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.

Configuration File

All query definitions are saved in the YAML file /etc/slashdb/querydefs.cfg.

Below you can a find a sample query definition and details about available options.

songs-by-genre:
  creator: admin
  database: Chinook
  desc: List of songs by genre (i.e. Rock)
  execute: [public]
  http_methods: {GET: true}
  query_id: songs-by-genre
  read: []
  sqlstr: 'SELECT ar.Name as Artist, t.Name as Song, t.UnitPrice as Price

    FROM  Artist ar

    JOIN Album al ON ar.ArtistId = al.ArtistId

    JOIN Track t ON al.AlbumId = t.AlbumId

    JOIN Genre g ON t.genreId = g.genreId

    WHERE g.Name = :genre'
  write: []

It's easier to modify query definitions using Query Studio. Some features are hidden in GUI and require modifying querydefs.cfg manually.

Each query definition is defined under it's unique id e.g. songs-by-genre and contains several options.

User Configuration Attributes

The file keeps all configurations in dictionary like structure. The keys of the highest level are unique query ids. Each query configuration has several attributes. Below you can find all attributes explained and some examples.

creator

Information about user that has created this query definition.

Example:

SlashDB account app1 was created by admin.

songs-by-genre:
  creator: admin

database

Defines database the query should be executed on. It's db_id of a certain database configured in SlashDB.

Note - The database must start with a letter or underscore, and may only contain letters, numbers, and the symbols @, _, -, and ~.

desc

Additional description of the query.

Example:

songs-by-genre:
  desc: List of songs by genre (i.e. Rock)

http_methods

Dictionary of HTTP methods that would trigger the query. HTTP method must be set to true to be enabled.

Accepted keys: * GET * PUT * POST * DELETE

Accepted values: * true * false

Example:

Query songs-by-genre will be executed if request is sent with GET method.

songs-by-genre:
  http_methods:
    GET: true
    PUT: false
    POST: false
    DELETE: false

Or simpler

songs-by-genre:
  http_methods:
    GET: true

query_id

Unique query id. Must be the same as the main key in the querydefs.cfg file.

Note - The query_id must start with a letter or underscore, and may only contain letters, numbers, and the symbols @, _, -, and ~.

Example:

songs-by-genre:
  query_id: songs-by-genre

read

List of users allowed to view this this query definition. User admin doesn't have to be added to the list because he's granted full access to all configs anyway.

Note - All user's names must start with a letter or underscore, and may only contain letters, numbers, and the symbols @, _, -, and ~.

Example:

Only admin and mike are allowed to view definition of the query songs-by-genre.

songs-by-genre:
  read: [mike]

write

List of users allowed to modify this this query definition. User admin doesn't have to be added to the list because he's granted full access to all configs anyway.

Note - All user's names must start with a letter or underscore, and may only contain letters, numbers, and the symbols @, _, -, and ~.

Example:

Only admin and mike are allowed to change definition of the query songs-by-genre.

songs-by-genre:
  write: [mike]

execute

List of users allowed to view this query definition. User admin doesn't have to be added to the list because he's granted full access to all configs anyway.

Note - All user's names must start with a letter or underscore, and may only contain letters, numbers, and the symbols @, _, -, and ~.

Example:

Only admin and mike are allowed to view definition of the query songs-by-genre.

songs-by-genre:
  execute: [public]

When user public is added to execute list then unauthenticated users will be able to execute the query songs-by-genre.

songs-by-genre:
  execute: [public]

sqlstr

The SQL statement to be executed on the database. The query can be defined with parameters. The parameter name must start with a colon e.g. :genre.

Example:

SQL that selects artist's name, track name and price. The where clause is parametrized and query accepts parameter genre to be passed in request e.g. /query/songs-by-genre/genre/Rock.json

songs-by-genre:  
  sqlstr: 'SELECT ar.Name as Artist, t.Name as Song, t.UnitPrice as Price
    FROM  Artist ar
    JOIN Album al ON ar.ArtistId = al.ArtistId
    JOIN Track t ON al.AlbumId = t.AlbumId
    JOIN Genre g ON t.genreId = g.genreId
    WHERE g.Name = :genre'