Skip to content

Queries

SlashDB allows users to execute custom SQL queries on a database through an HTTP connection using a REST API. This feature is called SQL Pass-thru.

Using the Query Studio, users can write and test SQL queries, and configure options such as the database it runs on and the users who may access it. Queries can be configured to accept SQL parameters through their REST endpoints or in the HTTP request body. The queries and their configurations are stored on the SlashDB server.

Info

Users who need to run queries, without the ability to modify them, should use the Run Queries option in the main menu.

List of Queries

The SQL Pass-thru Queries page provides a list of all the queries that are configured in SlashDB.

The list is accessible from the main menu (SQL Pass-thru Queries) or from the URL /querydef. It is only available to the admin user and users with privileges to view the list of SQL Pass-thru Queries. The admin user can access all queries; other users may be restricted to view only selected queries.

The list can be searched using the Search field above the list or sorted by clicking on column headers. The Select Database ID dropdown filters queries by the database they are associated with.

The Query ID is a unique identifier for the query which is used when making HTTP requests, e.g. these endpoints:

  • /query/songs-by-genre/genre/Rock - executes the songs-by-genre query with parameter genre = Rock
  • /querydef/songs-by-genre - open the songs-by-genre query for editing

Each query in the list also has:

  • a user-defined Description
  • the name of the Database on which the query runs
  • any Parameters required by the query
  • allowed HTTP Methods for executing the query

Info

The list can also be viewed in JSON or XML format by clicking the buttons in the top right corner.

Actions

To view or edit a query, click the Edit icon in the Action column.

Adding a New Query

The admin user can create a new query. Additionally, any user with privileges to do so may create new queries. Users with this ability will see the New button in the top right corner.

From the SQL Pass-thru Queries list, click on the New button. This opens the Query Studio.

Editing a Query

Click on a query ID or the Edit icon to edit a query. This opens the Query Studio.

Deleting a Query

Click on a query ID or the Edit icon to delete a query. This opens the Query Studio.
Then click on the Delete button in the top right corner.

Query Studio

The Query Studio is used to create, configure, and test SQL Pass-thru queries. The screen is divided into sections for configuration, SQL statement editing, and viewing the results of query execution:

Query Configuration Actions

Actions such as saving or deleting a configuration are performed using the row of buttons in the top right corner of the screen. Some buttons may not be enabled depending on the user's privileges or if creating a new query.

The Close button will return to the query list without saving.

Users with privileges to create queries will have the Save button available. This saves the current query.

Users with privileges to manage queries will have the Delete button available. This deletes the current query and returns to the query list.

The New button will create a new query with no query ID and a blank Query Studio form.

Info

Running the query will also trigger a Save operation before execution.

Settings

The Settings tab in the pane on the left hand side contains the fields for configuring the query. You can hover on the tooltip icons to learn more about each field's purpose and configuration tips.

  • Choose a unique Query ID to represent the query; this ID will be used to execute the query via its REST endpoint.

    Important

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

  • Select the Database on which the query will execute. A query can only be associated with one database.

  • Set permissions for users who will be able to Execute, View, or Edit this query.
  • Add a short Description for the query if desired.
  • Select which HTTP Methods can be used to execute the query.

Tip

The usual convention for REST endpoints is:

  • 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 entities, e.g. INSERT, CREATE
  • DELETE for SQL statements that remove entities, e.g DELETE, DROP

SQL Statement

Any valid SQL statement can be entered in the SQL Statement text editor on the right hand side of the screen. This gives the user full control over the SQL operations that are being executed on the database.

Info

The Run button in the bottom right corner of the editor will execute the query when pressed. See Running a Query.

Parameters

The editor recognizes user-defined variables, which allows you to execute queries with parametrized SQL. When a parameter is detected in the editor, a new tab will appear on the left hand side labeled Parameters.

To add a parameter to a SQL statement, enter a colon, followed by the name of the parameter in the editor.

Important

Parameter names must be prefixed with a colon : (e.g. :genre) to be recognized as parameters.

Caution

Parameters can only be used in WHERE clauses to prevent SQL injection attacks. For example, you can use a parameter like this:

SELECT * FROM "Genre" WHERE "Name" = :genre

But you cannot use a parameter in a SELECT clause like:

SELECT :columns FROM "Genre"

Attempting to execute a query like this will return an error.

You can set parameter values by entering values into the fields in the Parameters pane on the left hand side. Values may be set to NULL by toggling the NULL button on the parameter field. On execution, the query will be executed with the values you provided.

Tip

Use the Enter key when a parameter field has keyboard focus to immediately save and execute the query.

Info

For queries using the GET or DELETE methods, parameters will be passed to the query directly in the URL as parameter/value segments. For queries that use PUT or POST methods, parameters may also be passed in request bodies.

Modifiers

Modifiers let you set options which will affect the query output. The tooltip icons provide information about some of the options.

For example, you can set a limit or offset, transpose the output, or get a count of all rows.

Details about each of these options are available here.

Schema

It may be useful to have access to the database schema while developing queries. The Schema tab on the left hand side provides a view of the database tables and column properties. The database must be connected and selected from the Database dropdown in Settings.

The schema view shows column names and data types, and highlights primary and foreign keys.

Running a Query

You can test a query while developing in the Query Studio to see the results it will output. The Run button in the bottom right corner of the editor will execute the query when pressed.

When hovered over, a menu will pop out that allows you to execute the query with JSON, XML, or CSV output. Clicking the button will automatically save the query before executing.

Output from the query will appear in the output pane.

Results

Results from running a query will appear in the output pane. Results appear in a table with sortable column headers.

The dynamically generated URL at the top of the pane is constructed based on values provided in Settings, Modifiers, and Parameters (if applicable). It can be copied to the clipboard by clicking on the copy button.

Details about the modifiers for URLs are available here.

Errors

Query configuration errors are shown at the top right corner of the page. They appear if any issues are encountered when peforming a configuration action like saving or deleting a query.

Query runtime errors are shown in the output pane. They appear if there are issues encountered while executing the query, like errors in the SQL statement.

Configuration File

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

Caution

It is recommended to modify query definitions using the GUI whenever possible. Typos or formatting errors in this file may prevent SlashDB from starting.

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

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: []

Each query definition is defined under its unique ID - e.g. songs-by-genre - and contains several attributes.

Attributes

creator

The user that created this query definition.

Example

Query songs-by-genre was created by admin:

songs-by-genre:
  creator: admin

database

Defines the database that the query should be executed on. It should match the db_id field of a database configured in SlashDB.

Important

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

desc

A user-defined description of the query.

Example

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

http_methods

Dictionary of HTTP methods that can be used to run the query. Each HTTP method must be set to true to be enabled. Undefined methods will be treated as false.

Accepted keys:

  • GET
  • PUT
  • POST
  • DELETE

Accepted values:

  • true
  • false

Example

Query songs-by-genre will be executed if an HTTP request is sent to its endpoint using the GET method:

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

Or simply:

songs-by-genre:
  http_methods:
    GET: true

query_id

A unique query id. It must be the same as its parent key in the querydefs.cfg file.

Important

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

A list of users allowed to view this query. The admin user doesn't have to be added to the list because it has full access to all configs.

Important

All user 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 the songs_by_genre query:

songs-by-genre:
  read: [mike]

write

A list of users allowed to modify this query. The admin user doesn't have to be added to the list because it has full access to all configs.

Important

All user 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 the songs_by_genre query:

songs-by-genre:
  write: [mike]

execute

A list of users allowed to execute this query. The admin user doesn't have to be added to the list because it has full access to all configs.

Important

All user 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 execute the songs-by-genre query:

songs-by-genre:
  execute: [mike]

When user public is added to the execute list, unauthenticated users will be able to execute the query:

songs-by-genre:
  execute: [mike, public]

sqlstr

The SQL statement to be executed on the database. The query may contain user-provided parameters.

Important

Parameter names must be prefixed with a colon : (e.g. :genre) to be recognized as parameters.

Example

SQL that selects data from the Artist table. The WHERE clause is parametrized so that the query accepts parameter genre to be passed in the HTTP 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'