SQL

Use SQL in the backend of your Applications, in your Workflows, and Scheduled Jobs 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

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.

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

Table bulk insert, update and delete SQL form

Insert, update, and/or delete multiple rows in your SQL databases at once.

info

SQL-type databases that support bulk insert, update, and delete are: PostgreSQL, MySQL, MariaDB, and Microsoft SQL Server.

info

For a full example of how to use the Table Bulk Edit feature in your Superblocks Applications, see the tutorial on how to Create a credit card disputes dashboard.

The Table bulk insert, update and delete SQL form is available in Applications, Workflows, and Scheduled Jobs. The following page introduces the feature using an Application as an example.

Advantages of using the table bulk Insert, Update and Delete form

• It's fast - SQL queries are optimized by Superblocks before they are executed against the database
• It's safe - Superblocks runs sanity checks to limit the risk of accidental overwrites or deletions of production data
• It's simple - Table primary keys as well as multi-column primary keys are automatically matched

Using the table bulk insert, update and delete form

Databases can be added as Steps to Backend APIs of Superblocks Applications by clicking the Add Backend API button:

note

Learn how to add a Database step to a Workflow or how to add a Database step to a Scheduled Job instead.

Operation

API steps that are SQL integrations support different types of operations. The operation selected by default is Run SQL, which allows users to execute SQL queries against their database. Select Insert, Update, or Delete rows using form from the Operation dropdown to use the table bulk insert, update and delete form:

Table

Select the database Table to be updated. In the example below the user table from the PostgreSQL database was selected:

Row matching mode

The Row matching mode allows users to define how database rows are matched when running updates. The following options are supported:

1. Automatically match against database table primary key

   When this option is selected, the table must have a primary key. Updates that modify primary key columns are not allowed. Rows will be updated and/or deleted by matching the values of the primary key id for every row that needs to be updated.

   

2. Manually match against any column(s)

   This option lets users match against any column. Updates that modify existing primary key columns are allowed.

   tip

   When the Table Component is used to display the data in the frontend, the original data can be referenced using {{Table1.editedRows.originalRows}} (replace Table1 with the name of the frontend Table component).

   • An array of original rows to be updated must be provided that are used to identify the database rows that will be updated
   • The database columns to match on must be defined by selecting one or more table columns from the dropdown menu
   

Array of Updated, Inserted and/or Deleted Rows

Set the rows to be updated, inserted, and/or deleted. Each value must be formatted as an array of objects [{}, {}, {}].

tip

When a Table Component is used to make updates to the data in the frontend, the inserted rows can be referenced using {{Table1.editedRows.insertedRows}}, the updated rows using {{Table1.editedRows.updatedRows}}, and the deleted rows using {{Table1.editedRows.deletedRows}}. (replace Table1 with the name of the frontend Table component).

Map JSON keys to Database columns

The setting allows user to define how the JSON keys should be mapped to the database columns names. The following options are supported:

1. Automatically map all JSON keys to database table columns

   This is the preferred way to use when all JSON keys exactly match the SQL columns in the database.

   

2. Manually map JSON keys to table columns

   This option should be used when the JSON keys do not exactly match SQL columns in the database, for example when using aliases or JOINs. The user can set the correct JSON keys to be mapped to the SQL columns via the input fields in the form.

   

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.

Troubleshooting

info

This page focuses on troubleshooting errors that occur when using existing SQL database integrations in your Applications, Workflows, and Scheduled Jobs. Navigate to Troubleshooting Database Integrations to get help when setting up new database integrations in Superblocks.

Working with Parameterized SQL Statements

Parameterized SQL Statements are enabled by default for all SQL databases to protect against SQL injection. When enabled, you do not need to insert quotes around data bindings passed to SQL parameters and using quotes will cause the query to fail. Parameterized SQL Statements can be enabled/disabled on the individual database API step.

For example, the correct syntax to write a SQL query to select all orders that match a name provided in an Input component is:

SELECT * FROM orders WHERE product={{Input1.value}};

If Parameterized SQL Statements are disabled for an API step, then the correct syntax would be:

SELECT * FROM orders WHERE product='{{Input1.value}}';

Disable Parametrized SQL when using JavaScript to generate SQL

When you are using JavaScript to generate SQL, Parametrized SQL must be turned off:

SELECT * FROM orders {{ Step1.output == true ? "WHERE id in (30000,30001, 30002)" : ";" }}

The following is a possible error messages indicating that Parametrized SQL must be turned off:

error: syntax error at or near "$1"

If you continue having any issues connecting to your database, contact help@superblocks.com and our support team will assist promptly.