Using query parameters¶
Query parameters allow you to control query values dynamically from your application. This makes it really quick and easy to get your API Endpoint answering different questions, just by passing in a different value as the query parameter.
Using dynamic parameters means you can do things like:
- Filtering as part of a
WHERE
clause. - Changing the number of results as part of a
LIMIT
clause. - Sorting order as part of an
ORDER BY
clause. - Selecting specific columns for
ORDER BY
orGROUP BY
clauses.
Define dynamic parameters¶
In order to make a query dynamic, start the query with a %
character. That signals the engine that it needs to parse potential parameters.
Note that Tinybird automatically inserts the %
character in the first line when you add a Parameter to a Node.
Once you have a dynamic query, you can define parameters by using the following pattern {{<data_type>(<name_of_parameter>[,<default_value>, description=<"This is a description">, required=<True|False>])}}
. Example:
Simple select clause using dynamic parameters
% SELECT * FROM TR LIMIT {{Int32(lim, 10, description="Limit the number of rows in the response", required=False)}}
The above query returns 10 results by default, or however many are specified on the lim
parameter when requesting data from that API Endpoint.
Use Pipes API Endpoints with dynamic parameters¶
When using a Data Pipes API Endpoint that uses parameters, pass in the desired parameters. Using the example above where lim
sets the amount of maximum rows you want to get, the request would look like this:
Using a data Pipes API Endpoint containing dynamic parameters
curl -d https://api.tinybird.co/v0/pipes/tr_pipe?lim=20&token=....
Note that parameters can be specified in more than one Node in a Data Pipe and, when invoking the API Endpoint through its URL, the passed parameters are included in the request.
Available data types for dynamic parameters¶
Basic data types¶
Boolean
: AcceptsTrue
andFalse
as values, as well as strings like'TRUE'
,'FALSE'
,'true'
,'false'
,'1'
, or'0'
, or the integers1
and0
.String
: For any string values.DateTime64
,DateTime
andDate
: Accepts values likeYYYY-MM-DD HH:MM:SS.MMM
,YYYY-MM-DD HH:MM:SS
andYYYYMMDD
respectively.Float32
andFloat64
: Accepts floating point numbers of either 32 or 64 bit precision.Int
orInteger
: Accepts integer numbers of any precision.Int8
,Int16
,Int32
,Int64
,Int128
,Int256
andUInt8
,UInt16
,UInt32
,UInt64
,UInt128
,UInt256
: Accepts signed or unsigned integer numbers of the specified precision.
Use column parameters¶
You can use column
to pass along column names (of a defined type) as parameters, like:
Using column dynamic parameters
% SELECT * FROM TR ORDER BY {{column(order_by, 'timestamp')}} LIMIT {{Int32(lim, 10)}}
It's highly recommended to always define the column
function's second argument (the one for the default value). The alternative for not defining it is to validate that the first argument is defined, but this will only have a real effect on the execution of the API Endpoint, a placeholder will be used in the development of the Pipes.
Validate the column parameter when not defining a default value
% SELECT * FROM TR {% if defined(order_by) %} ORDER BY {{column(order_by)}} {% end %}
Pass arrays¶
It is also possible to pass along a list of values with the Array
function for parameters, like so:
Passing arrays as dynamic parameters
% SELECT * FROM TR WHERE access_type IN {{Array(access_numbers, 'Int32', default='101,102,110')}}
Pagination¶
It is possible to paginate results by adding LIMIT
and OFFSET
clauses to your query. You can parameterize the values of these clauses, allowing you to pass pagination values as query parameters to your API Endpoint.
The LIMIT
clause allows you to select only the first n
rows of a query result. The OFFSET
clause allows you to skip n
rows from the beginning of a query result. Together, you can dynamically chunk the results of a query up into pages.
For example, the following query introduces two dynamic parameters page_size
and page
which lets you control the pagination of a query result using query parameters on the URL of an API Endpoint.
Paging results using dynamic parameters
% SELECT * FROM TR LIMIT {{Int32(page_size, 100)}} OFFSET {{Int32(page, 0) * Int32(page_size, 100)}}
Advanced templating using dynamic parameters¶
Tinybird's templating system is based on the Tornado Python framework, and uses Python syntax.
In order to perform more complex queries, use flow control operators like if
, else
and elif
in combination with the defined()
function, which will allow you to check if a parameter has been received or not and act accordingly.
Those control statements need to be enclosed in curly brackets with percentages {%..%}
as in the following example:
Advanced templating using dynamic parameters
% SELECT toDate(start_datetime) as day, countIf(status_code < 400) requests, countIf(status_code >= 400) errors, avg(duration) avg_duration FROM log_events WHERE endsWith(user_email, {{String(email, 'gmail.com')}}) AND start_datetime >= {{DateTime(start_date, '2019-09-20 00:00:00')}} AND start_datetime <= {{DateTime(end_date, '2019-10-10 00:00:00')}} {% if method != 'All' %} AND method = {{String(method,'POST')}} {% end %} GROUP BY day ORDER BY day DESC
Validate presence of a parameter¶
Validate if a param is in the query
% select * from table {% if defined(my_filter) %} where attr > {{Int32(my_filter)}} {% end %}
When you call the API Endpoint with /v0/pipes/:PIPE.json?my_filter=20
it will apply the filter
Default parameter values and placeholders¶
Following best practices, you should set default parameter values as follows:
Default parameter values
% SELECT * FROM table WHERE attr > {{Int32(my_filter, 10)}}
When you call the API Endpoint with /v0/pipes/:PIPE.json
without setting any value to my_filter
, it automatically applies the default value of 10.
If you don't set a default value for a parameter, you should validate that the parameter is defined before using it in the query as explained above.
If you don't validate the parameter and it's not defined, the query will probably fail. Tinybird populates the parameter with a placeholder value based on the data type. For instance, numerical data types are populated with 0, strings with __placeholder__
, and date and timestamps with 2019-01-01
and 2019-01-01 00:00:00
respectively. You could try yourself with a query like this:
Get placeholder values
% SELECT {{String(param)}} as placeholder_string, {{Int32(param)}} as placeholder_num, {{Boolean(param)}} as placeholder_bool, {{Float32(param)}} as placeholder_float, {{Date(param)}} as placeholder_date, {{DateTime(param)}} as placeholder_ts, {{Array(param)}} as placeholder_array
This returns the following values:
{ "placeholder_string": "__placeholder__", "placeholder_num": 0, "placeholder_bool": 0, "placeholder_float": 0, "placeholder_date": "2019-01-01", "placeholder_ts": "2019-01-01 00:00:00", "placeholder_array": ["__placeholder__0","__placeholder__1"] }
Test dynamic parameters¶
Any dynamic parameters you create are displayed in the UI above the Pipe, along with a "Test new values" button. Selecting this button opens a Test popup, populated with the default value of your parameters.
The Test popup is a convenient way to quickly test different Pipe values than the default ones without impacting production. Use the View API page to see API Endpoint metrics resulting from that specific combination of parameters. Close the popup to bring the Pipe back to its default production state.
Throw errors¶
Validate if a param is defined and throw an error if it's not defined
% {% if not defined(my_filter) %} {{ error('my_filter (int32) query param is required') }} {% end %} select * from table where attr > {{Int32(my_filter)}}
It will stop the API Endpoint processing and will return a 400 error.
custom_error
function is an advanced version of error
where you can customize the response, for example. It gets an object as the first argument which will be sent as JSON and the status_code as a second argument (defaults to 400)
Validate if a param is defined and throw an error if it's not defined
% {% if not defined(my_filter) %} {{ custom_error({'error_id': 10001, 'error': 'my_filter (int32) query param is required'}) }} {% end %} select * from table where attr > {{Int32(my_filter)}}
Next steps¶
Thanks to the magic of dynamic parameters, you can create flexible API Endpoints with ease, so you don't need to manage or test dozens of Pipes. Be sure you're familiar with the 5 rules for faster SQL queries.