Writing SQL queries

Last updated: August 8, 2022

Writing SQL queries using Slight's template syntax.

Writing SQL queries

Inside apps SQL can be used to query data. Slight uses BigQuery to run the queries. For the supported SQL features and syntax we refer to the BigQuery SQL syntax reference documentation.

However SQL can be limiting in where variables (parameters in SQL's terminology) are allowed. For example it doesn't allow variables as column names. So, in additional to standard SQL, Slight also supports templating to provide more flexibility and additional features. The templating syntax is explained in the next section.

Template syntax

To support features beyond standard SQL Slight uses templating. A templated query looks like the following.

Loading...

Anything between {{ and }} is called an expression. In this example we have only a single expression column, which refers to a variable (see next section on using variables). Slight uses simple substitution templating, meaning variables are replaced in the query with the value represent.*

We can use the query above to generate a report of our (example) invoices, showing the client's ID, the date when an invoice is paid (paid_date) and a third column as specified by the column variable. Here we could use the price of the invoice, or the date when a service was provided. The thing to note here is that any user can change the variable value easily, which means a user without knowledge of SQL can change the report without assistance.

The following sections explain how you can use variables (of an app) in your query and how to reference datasets in queries.

* Technical note: variables are used as bound SQL parameters where possible.

Using variables

Variables can be accessed directly by name inside an expression (inside {{ and }}). Note the name might be differ from the label, be sure to use name! The variables are shown in a list on the right side of screen. The image below highlights the variable names.

Example of variable name location

In this example we have two variables min_paid_date and max_paid_date which we can use in the app. To use a variable in a query simply put them inbetween {{ and }}, such as in the following query example.

Loading...

As shown in the example image above, our code editor will highlight expressions using a variable in green.

Learn more about how to use variable by reading the Using variables section.

Referring to datasets

Similar to using variables, Slight's templating can also be used to refer to a dataset. When referring to a dataset the expression must be in the form: @user/dataset, e.g. joe/coffee-analysis refers to the coffee-analysis dataset owned by user joe.

The example query and image below shows a query using the joe/coffee-analysis dataset.

Loading...

Example of using dataset

Learn more about how to use datasets in apps by reading the Using datasets section.