Functions
/
SQL Functions

SQL Functions

A SQL Function can be used to read, insert, update, or delete data from your SQL database. When you connect your database to Internal, basic SQL functions corresponding to each table in your database are automatically generated for you. You can create additional SQL functions using the Function Editor. 

Getting Started with the Function Editor

To create or edit a Function, you’ll need to use the Function Editor. You can access the Function Editor from one of two places:

  1. From a Space: Go into Edit Mode and click on the App Data icon in the left nav.
  2. From Company Settings: Go to Data & Functions, click on the desired database, and click on the Functions tab.

Once you open the Function Editor, name your Function and select the database (if not selected already) for which you want to create a function.

Configure

To configure your Function, first select the type of function you want to create. You can choose from Insert, Update, and Select Functions. 

Insert and Update Functions

Use an Insert Function to create new records in a database and an Update Function to update records in your database. To create a function, first select the table that you want the function to modify. Then, you’ll specify the exact data that will be modified within that resource:

Filter by

Use filters to specify the record(s) that this function will modify. (You’ll skip this step if you’re creating an Insert function). You can either enter a value or dynamic parameters for variable values. Each filter is an “AND” statement so records must pass all filter criteria.

Example 1: Function that updates records where company_id is equal to 1:

Example 2: Function that updates records where company_id is equal to the dynamic parameter company_id.

Note: company_id is a string so the dynamic parameter must be in the form of a Javascript template literal with the syntax ${expression}. Non-string fields accept standard JavaScript expressions.
Set

Set the fields that your function should modify for each record. You can either set a specific value for each field or use dynamic parameters for variable inputs. 

Example: Function that sets the company_id to new_company_id for all records where company_id is equal to specified company_id.


Allow multiple records to be updated

‍This checkbox provides an additional safety net in case you do not intend for this function to perform bulk update actions. If this is unchecked, the function will fail if it attempts to act on more than one record. 

Select Functions

This type of function is used to read data from your database. Enter a SQL query to create your function.


Run Function

If you want to run a function to confirm it is working as intended, select an environment and click the button to "Run Function". 

Note: Note: These functions run against your database. If you are creating an Insert or Update function, be extra careful as running your function will modify data in your database. We recommend setting up a staging environment and running your function against it.

Inputs

This section is where you can view and configure function inputs. Think of inputs as fields in a form component, a primary key input that is used to retrieve a record, or filter or sorting inputs for a table component. 

Inputs for Insert or Update Functions 

When you use a dynamic parameter in the Configure tab, they automatically appear in the Inputs tab. You can specify the field type and mark a field as required.

Inputs for Select Functions

Filter and Sorting inputs automatically populate in the Inputs tab after you write your query in the Configure tab. These inputs are read-only. 

Outputs

This section is where you can view and configure function outputs. Think of Outputs as table columns or fields displayed in a detail component.

Outputs for Insert or Update Functions 

There are no outputs for insert and update functions. 

Outputs for SelectFunctions

Outputs automatically populate based on the SQL query you entered in the Configure tab. You can set the key (the combination of attributes that make the row unique) by checking the box(es).  This is used by additional features like automatic row updates and auto linking.

Permissions

You can configure permissions for SQL Functions in Roles & Permissions within Company Settings. Learn more.