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.
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:
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.
To configure your Function, first select the type of function you want to create. You can choose from Insert, Update, and Select 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:
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.
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.
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.
This type of function is used to read data from your database. Enter a SQL query to create your function. When you create a Select function, it'll automatically include filtering and pagination, so you don't have to worry about it.
This type of function can be used to run any raw SQL statement including Stored Procedures (currently supported for SQL Server, PostgreSQL, and Snowflake only).
This type of function can be used to run custom insert, update, delete, and select queries that return data (currently supported for SQL Server, PostgreSQL, and Snowflake only).
You can add SQL Parameters to your function to create variable inputs that can be provided by your user or configured in your app later. You'll need to use syntax that is specific to your database.
If you want to run a function to confirm it is working as intended, select an environment and click the button to "Run Function".
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.
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.
Filter and Sorting inputs automatically populate in the Inputs tab after you write your query in the Configure tab. These inputs are read-only.
This section is where you can view and configure function outputs. Think of Outputs as table columns or fields displayed in a detail component.
There are no outputs for insert and update functions.
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.
You can configure permissions for SQL Functions in Roles & Permissions within Company Settings. Learn more.