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:
- From a Space: Go into Edit Mode and click on the App Data icon in the left nav.
- 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.
To configure your Function, first select the type of function you want to create. You can choose from Insert, Update, and Select Functions.
Insert, Update, Delete 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.
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.
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.
Raw SQL Exec
This type of function can be used to run any raw SQL statement including Stored Procedures (currently supported for SQL Server, PostgreSQL, MySQL, BigQuery, and Snowflake only).
Raw SQL Query
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, MySQL, BigQuery, and Snowflake only).
Should You Use Raw SQL Exec or Raw SQL query?
A Raw SQL Query reads rows from the server and returns them while Raw Exec does not.
If you will use the rows being returned, use Raw SQL query, otherwise Exec will be (sometimes significantly) faster.
Generally this means it is best to use Raw SQL query for SELECT, and Raw Exec for anything else (INSERT/UPDATE/DELETE/etc) but as always, there are edge cases - for example: you would use query with INSERT … RETURNING in Postgresql.
This also applies to stored procedures: if a request returns rows, then Raw SQL query is required, otherwise use Raw SQL exec.
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.
SQL Server (named parameters using the syntax @name)
MySql (positional parameters using the syntax ?. Their order in the query determines the values given to them when executing)
PostgreSQL & Amazon Redshift (numbered parameters using the syntax $n. $1 refers to the first argument, $2 to the second, etc.)
The Run Preview function does not use the "Auth" flow for executing functions. As a result, any flow which utilizes auth has to be tested from its associated Space, or have their auth temporarily suspended.
If you want to run a function to confirm it is working as intended, select an environment and click the button to "Run Function".
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.
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.
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
Outputs for INSERT, UPDATE, and DELETE functions are automatically generated by Internal and are available as bindings for components within a Space. These Outputs can be edited.
To use the output of one of the above-mentioned methods, create a component which makes a call to the database. Then, create another component which will bind to the output of that components execution of that method. You can find that binding in the "Source" menu in the righthand configuration panel of a given Space.
This functionality is particularly useful for situations where the creation of one DB object is dependent on another, but intrinsically linked. For example, creating a functional pipeline to create a new USER and an associated GROUP for that user by first creating the GROUP and then using the returned GROUP_ID from the function as an input in the function to create the new USER.
Outputs for Select 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.
Single Object Returns
It is possible to return a single object from an SQL query using the Transformer. By default, SQL queries return an array. As a result, you'll use the Transformer input body, to call the first item in the return array by referencing them with the index value:
Outputs are automatically populated for single object returns. These outputs can be edited and used as filters. If you use some mixture of
data, auto-generated outputs for the function will not work; Outputs will need to be configured by hand.
You can configure permissions for SQL Functions in Roles & Permissions within Company Settings. Learn more.
Updated 3 months ago