Skip to main content

Writing SQL

Use SQL to:

  • Query data from your data stores referencing UI components (ex. Table1.selectedRow.id)
  • Leverage the output of previous Steps to dynamically update your queries by referencing their output (ex. Step1.output)
  • Return data to be consumed by subsequent steps and UI components

Referencing UI components and previous steps

Query your databases by writing SQL in Application APIs, Workflows, or Scheduled Jobs. You can reference the outputs of previous steps with the syntax Step1.output.<attribute>. Similarly, you can reference the UI component's states in order to power the queries. For example, SELECT * FROM {{Dropdown1.selectedOptionValue}}.

Examples

Create a dropdown with all available tables

  1. Add a dropdown component to the canvas
  2. Create an API that references your postgres database and update the API name to getTables
  3. Use the following SQL query to grab the table names:
        SELECT *
    FROM pg_catalog.pg_tables
    WHERE schemaname != 'pg_catalog' AND
    schemaname != 'information_schema';
  4. Click the cog icon at the top of the SQL step and turn off "Use Parameterized SQL"
  5. Update the "Options" field in the Dropdown to be {{getTables.response}}
  6. Update the "Value Field" and "Label Field" fields in the Dropdown to tablename
  7. Update the "onOptionChange" field in the Dropdown to run the API where your Table data is being queried and reference the Dropdown component's value for the table:
    SELECT * FROM {{Dropdown1.selectedOptionValue}}
  8. [optional] Update the "default selected value string(s)" to the name of the table you want to be selected by default

Use event handlers to control table output from a dropdown component

Conditionally alter SQL queries with bindings

info

This requires disabling Parameterized SQL.

You can alter what SQL is executed by writing JavaScript bindings inside of SQL steps that reference UI components and previous API steps. For example, you might have a code step like a Python function (Step1) that runs some business logic and returns different data under different conditions. In a following SQL step (Step2) you want to run a dynamic query based on the previous step's output.

Backend API sql step

Some examples include:

  • Use the ternary operator to add a WHERE clause based on an if / else condition. Here, if the output of Step1 is true, include the WHERE clause for specific order ids. Else, end the query so all orders are retrieved:
SELECT * FROM orders {{ Step1.output == true ? "WHERE id in (30000,30001, 30002)" : ";" }}
  • Put the entire SQL query inside bindings. This is useful if you need to change the type of query depending on the output of a previous step. Here, run either an UPDATE or SELECT statement on a specific user id with template strings:
{{ Step1.output.method == "update" ? `UPDATE orders SET user_email = ${Step1.output.email} WHERE user_id=${Step1.output.id}` : `SELECT DISTINCT user_email FROM orders where user_id=${Step1.output.id}` }}
  • Check multiple conditions with inline JavaScript that invokes an anonymous function. Here, select either the product, the user email, or everything (*) from the table based on the previous step's output:
SELECT {{ (() => { if(Step1.output == "product"){ return "product" } else if(Step1.output == "email"){return "user_email"} else{return "*"} })() }} FROM orders

Note, you can see what SQL is run under the "Executed SQL" tab.

SQL query with JavaScript if statement in bindings

Parameterized SQL queries

A parameterized query is a query that uses placeholders for parameters. Those parameters' values are then supplied at the time of execution. This is important because parameterized queries are used to avoid SQL injection attacks.

By default, all SQL queries in Superblocks use parameterization. You can disable Parameterized SQL Queries by clicking the cog icon at the top of the step and toggling off this feature.

Using Parameterized SQL provides SQL injection protection but inhibits the use of JavaScript to generate SQL. For example, the above example will not work with parameterized SQL because the table name is being dynamically updated based on the state of Dropdown component.

Toggle Parameterized SQL via Advanced Settings