--- title: Advanced template functions for dynamic API endpoint meta: description: Learn more about creating dynamic API endpoint using advanced templates. headingMaxLevels: 2 --- # Advanced template functions for dynamic API endpoint Learn how to use [template functions](../../../dev-reference/template-functions) to create dynamic API endpoint with Tinybird. ## Prerequisites Make sure you're familiar with template functions and [query parameters](../../../work-with-data/query-parameters). ## Example data This guide uses the eCommerce events data enriched with products. The data looks like the following: ```sql {% title="Events and products data" %} SELECT *, price, city, day FROM events_mat ANY LEFT JOIN products_join_sku ON product_id = sku ``` ## Tips and tricks When the complexity of pipes and API endpoints grows, developing them and knowing what's going-on to debug problems can become challenging. Here are some useful tricks for using Tinybird's product: ### WHERE 1=1 When you filter by different criteria, given by dynamic parameters that can be omitted, you'll need a `WHERE` clause. But if none of the parameters are present, you'll need to add a `WHERE` statement with a dummy condition (like `1=1`) that's always true, and then add the other filter statements dynamically if the parameters are defined, like you do in the [defined](#defined) example of this guide. ### Use the set function The `set` function present in the previous snippet lets you set the value of a parameter in a node, so that you can check the output of a query depending on the value of the parameters it takes. Otherwise, you'd have to publish an API endpoint and make requests to it with different parameters. Using `set`, you don't have to exit the Tinybird UI while creating an API endpoint and the whole process is faster, without needing to go back and forth between your browser or IDE and Postman or cURL. Another example of its usage: ```sql {% title="Using set to try out different parameter values" %} % {\% set select_cols = 'date,user_id,event,city' %} SELECT {{columns(select_cols)}} FROM events_mat ``` You can use more than one `set` statement. Put each one on a separate line at the beginning of a node. `set` is also a way to set defaults for parameters. If you used `set` statements to test your API endpoint while developing, remember to remove them before publishing your code, because if not, the `set` function overrides any incoming parameter. ### Default argument Another way to set default values for parameters is using the `default` argument that most Tinybird template functions accept. The previous code could be rewritten as follows: ```sql {% title="Using the default argument" %} % SELECT {{columns(select_cols, 'date,user_id,event,city')}} FROM events_mat ``` Keep in mind that defining the same parameter in more than one place in your code in different ways can lead to inconsistent behavior. Here's a solution to avoid that: ### Using WITH statements to avoid duplicating code If you plan to use the same dynamic parameters more than once in a node of a pipe, define them in one place to avoid duplicating code. This also makes it clearer which parameters will appear in the node. You can do this with one or more statements at the beginning of a node, using the `WITH` clause. The WITH clause supports CTEs. These are preprocessed before executing the query, and can only return one row. This is different to other databases such as Postgres. For example: ```sql {% title="DRY with the with clause" %} % {\% set terms='orchid' %} WITH {{split_to_array(terms, '1,2,3')}} AS needles SELECT *, joinGet(products_join_sku, 'color', product_id) color, joinGet(products_join_sku, 'title', product_id) title FROM events WHERE multiMatchAny(lower(color), needles) OR multiMatchAny(lower(title), needles) ``` ### Documenting your API endpoints Tinybird creates auto-generated documentation for all your published API endpoints, taking the information from the dynamic parameters found in the pipe. It's best practice to set default values and descriptions for every parameter in one place (also because some functions don't accept a description, for example). This is typically done in the final node, with `WITH` statements at the beginning. See how to do it in the [last section](#putting-it-all-together) of this guide. ### Hidden parameters If you use some functions like `enumerate_with_last` in the [enumarate with last example](#enumerate-with-last), you might end up with some variables (called `x`, `last` in that code snippet) that Tinybird interprets as if they were parameters that you can set. They appear in the auto-generated documentation page. To avoid that, add a leading underscore to their name, renaming `x` to `_x` and `last` to `_last`. ## Advanced functions The following are practical examples of advanced template functions usage so that it's easier for you to understand how to use them. ### defined The `defined` function lets you check if a query string parameter exists in the request URL or not. Imagine you want to filter events with a price within a minimum or a maximum price, set by two dynamic parameters that could be omitted. A way to define the API endpoint would be like this: ```sql {% title="filter by price" %} % {\% set min_price=20 %} {\% set max_price=50 %} SELECT *, price FROM events_mat WHERE 1 = 1 {\% if defined(min_price) %} AND price >= {{Float32(min_price)}} {\% end %} {\% if defined(max_price) %} AND price <= {{Float32(max_price)}} {\% end %} ``` To see the effect of having a parameter not defined, use `set` to set its value to `None` like this: ```sql {% title="filter by price, price not defined" %} % {\% set min_price=None %} {\% set max_price=None %} SELECT *, price FROM events_mat WHERE 1 = 1 {\% if defined(min_price) %} AND price >= {{Float32(min_price)}} {\% end %} {\% if defined(max_price) %} AND price <= {{Float32(max_price)}} {\% end %} ``` It's also possible to provide smart defaults to avoid needing to use the `defined` function at all: ```sql {% title="filter by price with default values" %} % SELECT *, price FROM events_mat_cols WHERE price >= {{Float32(min_price, 0)}} AND price <= {{Float32(max_price, 999999999)}} ``` ### Array(variable_name, 'type', [default]) Transforms a comma-separated list of values into a Tuple. You can provide a default value for it or not: ```sql % SELECT {{Array(code, 'UInt32', default='13412,1234123,4123')}} AS codes_1, {{Array(code, 'UInt32', '13412,1234123,4123')}} AS codes_2, {{Array(code, 'UInt32')}} AS codes_3 ``` To filter events whose type belongs to the ones provided in a dynamic parameter, separated by commas, you'd define the API endpoint like this: ```sql {% title="Filter by list of elements" %} % SELECT * FROM events WHERE event IN {{Array(event_types, 'String', default='buy,view')}} ``` And then the URL of the API endpoint would be something like `{% user("apiHost") %}/v0/pipes/your_pipe_name.json?event_types=buy,view` ### sql_and `sql_and` lets you create a filter with `AND` operators and several expressions dynamically, taking into account if the dynamic parameters in a template it are present in the request URL. It's not possible to use Tinybird functions inside the `{{ }}` brackets in templates. `sql_and` can only be used with the`{column_name}__{operand}` syntax. This function does the same as what you saw in the previous query: filtering a column by the values that are present in a tuple generated by `Array(...)` if `operand` is `in`, are greater than (with the `gt` operand), or less than (with the `lt` operand). Let's see an example to make it clearer: {% tabs %} {% tab label="Endpoint template code" %} ```sql {% title="SQL_AND AND COLUMN__IN" %} % SELECT *, joinGet(products_join_sku, 'section_id', product_id) section_id FROM events WHERE {{sql_and(event__in=Array(event_types, 'String', default='buy,view'), section_id__in=Array(sections, 'Int16', default='1,2'))}} ``` {% /tab %} {% tab label="Generated SQL" %} ```sql {% title="SQL_AND AND COLUMN__IN" %} SELECT *, joinGet(products_join_sku, 'section_id', product_id) AS section_id FROM events WHERE (event IN ('buy', 'view')) AND (section_id IN (1, 2)) ``` {% /tab %} {% /tabs %} You don't have to provide default values. If you set the `defined` argument of `Array` to `False`, when that parameter isn't provided, no SQL expression will be generated. You can see this in the next code snippet: {% tabs %} {% tab label="Endpoint template code" %} ```sql {% title="defined=False" %} % SELECT *, joinGet(products_join_sku, 'section_id', product_id) section_id FROM events WHERE {{sql_and(event__in=Array(event_types, 'String', default='buy,view'), section_id__in=Array(sections, 'Int16', defined=False))}} ``` {% /tab %} {% tab label="Generated SQL" %} ```sql {% title="defined=False" %} SELECT *, joinGet(products_join_sku, 'section_id', product_id) AS section_id FROM events WHERE event IN ('buy', 'view') ``` {% /tab %} {% /tabs %} ### split_to_array(name, [default]) This works similarly to `Array`, but it returns an Array of Strings (instead of a tuple). You'll have to cast the result to the type you want after. As you can see here too, they behave in a similar way: ```sql {% title="array and split_to_array" %} % SELECT {{Array(code, 'UInt32', default='1,2,3')}}, {{split_to_array(code, '1,2,3')}}, arrayMap(x->toInt32(x), {{split_to_array(code, '1,2,3')}}), 1 in {{Array(code, 'UInt32', default='1,2,3')}}, '1' in {{split_to_array(code, '1,2,3')}} ``` One thing that you want to keep in mind is that you can't pass non-constant values (arrays, for example) to operations that require them. For example, this would fail: ```sql {% title="using a non-constant expression where one is required" %} % SELECT 1 IN arrayMap(x->toInt32(x), {{split_to_array(code, '1,2,3')}}) ``` If you find an error like this, you should use a Tuple instead (remember that `{{Array(...)}}` returns a tuple). This will work: ```sql {% title="Use a tuple instead" %} % SELECT 1 IN {{Array(code, 'Int32', default='1,2,3')}} ``` `split_to_array` is often used with [enumerate_with_last](#enumerate-with-last). ### column and columns They let you select one or several columns from a data source or pipe, given their name. You can also provide a default value. ```sql {% title="columns" %} % SELECT {{columns(cols, 'date,user_id,event')}} FROM events ``` ```sql {% title="column" %} % SELECT date, {{column(user, 'user_id')}} FROM events ``` ### enumerate_with_last Creates an iterable array, returning a Boolean value that allows checking if the current element is the last element in the array. Its most common usage is to select several columns, or compute some function over them. See an example of `columns` and `enumerate_with_last` here: {% tabs %} {% tab label="Endpoint template code" %} ```sql {% title="enumerate_with_last + columns" %} % SELECT {\% if defined(group_by) %} {{columns(group_by)}}, {\% end %} sum(price) AS revenue, {\% for last, x in enumerate_with_last(split_to_array(count_unique_vals_columns, 'section_id,city')) %} uniq({{symbol(x)}}) as {{symbol(x)}} {\% if not last %},{\% end %} {\% end %} FROM events_enriched {\% if defined(group_by) %} GROUP BY {{columns(group_by)}} ORDER BY {{columns(group_by)}} {\% end %} ``` {% /tab %} {% tab label="Generated SQL" %} ```sql {% title="enumerate_with_last + columns" %} -- given that group_by=day and count_unique_cals_columns=section_id,city SELECT day, sum(price) AS revenue, uniq(section_id) AS section_id, uniq(city) AS city FROM events_enriched GROUP BY day ORDER BY day ASC ``` {% /tab %} {% /tabs %} If you use the `defined` function around a parameter it doesn't make sense to give it a default value because if it's not provided, that line will never be run. ### error and custom_error They let you return customized error responses. With `error` you can customize the error message: ```sql {% title="error" %} % {\% if not defined(event_types) %} {{error('You need to provide a value for event_types')}} {\% end %} SELECT *, joinGet(products_join_sku, 'section_id', product_id) section_id FROM events WHERE event IN {{Array(event_types, 'String')}} ``` ```json {% title="error response using error" %} {"error": "You need to provide a value for event_types"} ``` And with `custom_error` you can also customize the response code: ```sql {% title="custom_error" %} % {\% if not defined(event_types) %} {{custom_error({'error': 'You need to provide a value for event_types', 'code': 400})}} {\% end %} SELECT *, joinGet(products_join_sku, 'section_id', product_id) section_id FROM events WHERE event IN {{Array(event_types, 'String')}} ``` ```json {% title="error response using custom_error" %} {"error": "You need to provide a value for event_types", "code": 400} ``` **Note:** `error` and `custom_error` have to be placed at the start of a node or they won't work. The order should be: 1. `set` lines, to give some parameter a default value (optional) 2. Parameter validation functions: `error` and `custom_error` definitions 3. The SQL query itself ## Putting it all together You've created a pipe where you use most of these advanced techniques to filter ecommerce events. This is its code: ```sql {% title="advanced_dynamic_endpoints.pipe" %} NODE events_enriched SQL > SELECT *, price, city, day FROM events_mat_cols ANY LEFT JOIN products_join_sku ON product_id = sku NODE filter_by_price SQL > % SELECT * FROM events_enriched WHERE 1 = 1 {\% if defined(min_price) %} AND price >= {{Float32(min_price)}} {\% end %} {\% if defined(max_price) %} AND price <= {{Float32(max_price)}} {\% end %} NODE filter_by_event_type_and_section_id SQL > % SELECT * FROM filter_by_price {\% if defined(event_types) or defined(section_ids) %} ... WHERE {{sql_and(event__in=Array(event_types, 'String', defined=False, enum=['remove_item_from_cart','view','search','buy','add_item_to_cart']), section_id__in=Array(section_ids, 'Int32', defined=False))}} {\% end %} NODE filter_by_title_or_color SQL > % SELECT * FROM filter_by_event_type_and_section_id {\% if defined(search_terms) %} WHERE multiMatchAny(lower(color), {{split_to_array(search_terms)}}) OR multiMatchAny(lower(title), {{split_to_array(search_terms)}}) {\% end %} NODE group_by_or_not SQL > % SELECT {\% if defined(group_by) %} {{columns(group_by)}}, sum(price) AS revenue, {\% for _last, _x in enumerate_with_last(split_to_array(count_unique_vals_columns)) %} uniq({{symbol(_x)}}) as {{symbol(_x)}} {\% if not _last %},{\% end %} {\% end %} {\% else %} * {\% end %} FROM filter_by_title_or_color {\% if defined(group_by) %} GROUP BY {{columns(group_by)}} ORDER BY {{columns(group_by)}} {\% end %} NODE pagination SQL > % WITH {{Array(group_by, 'String', '', description='Comma-separated name of columns. If defined, group by and order the results by these columns. The sum of revenue will be returned')}}, {{Array(count_unique_vals_columns, 'String', '', description='Comma-separated name of columns. If both group_by and count_unique_vals_columns are defined, the number of unique values in the columns given in count_unique_vals_columns will be returned as well')}}, {{Array(search_terms, 'String', '', description='Comma-separated list of search terms present in the color or title of products')}}, {{Array(event_types, 'String', '', description="Comma-separated list of event name types", enum=['remove_item_from_cart','view','search','buy','add_item_to_cart'])}}, {{Array(section_ids, 'String', '', description="Comma-separated list of section IDs. The minimum value for an ID is 0 and the max is 50.")}} SELECT * FROM group_by_or_not LIMIT {{Int32(page_size, 100)}} OFFSET {{Int32(page, 0) * Int32(page_size, 100)}} ``` To replicate it in your account, copy the previous code to a new endpoint file called `advanced_dynamic_endpoints.pipe` locally and deploy your changes.