--- title: Endpoints meta: description: Endpoints make it easy to use the results of your queries in applications. --- # {% icon name="endpoint" /%} Endpoints Endpoints are a type of pipe that you can call from other applications. For example, you can ingest your data, build SQL logic inside a pipe, and then publish the result of your query as a REST API endpoint. ## Create an endpoint Endpoints are defined in a .pipe file using the `TYPE ENDPOINT` directive. See [Endpoint pipes](/forward/dev-reference/datafiles/pipe-files#endpoint-pipes). You can list your endpoints and their URLs and tokens using the `tb endpoint` command. See [tb endpoint](/forward/dev-reference/commands/tb-endpoint). ## Query parameters Query parameters are great for any value of the query that you might want control dynamically from your applications. For example, you can get your endpoint to answer different questions by passing a different value as query parameter. Use dynamic parameters means to 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` or `GROUP BY` clauses. ### Define dynamic parameters To make a query dynamic, start the query with a `%` character. That signals the engine that it needs to parse potential parameters. After you have created a dynamic query, you can define parameters by using the following pattern `{{([,, description=<"This is a description">, required=])}}`. For example: ```sql {% title="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 previous query returns 10 results by default, or however many are specified on the `lim` parameter when requesting data from that API endpoint. {% callout type="caution" %} Boolean data type does not support the `description` or `required` arguments. {% /callout %} ### Call an endpoint with dynamic parameters When using an endpoint that uses parameters, pass in the desired parameters. Using the previous example where `lim` sets the amount of maximum rows you want to get, the request would look like this: ```shell {% title="Using an endpoint containing dynamic parameters" %} curl -d https://api.europe-west2.gcp.tinybird.co/v0/pipes/tr_pipe?lim=20&token= ``` ### Available data types for dynamic parameters You can use the following data types for dynamic parameters: - `Boolean`: Accepts `True` and `False` as values, as well as strings like `'TRUE'`, `'FALSE'`, `'true'`, `'false'`, `'1'`, or `'0'`, or the integers `1` and `0`. - `String`: For any string values. - `DateTime64`, `DateTime` and `Date`: Accepts values like `YYYY-MM-DD HH:MM:SS.MMM`, `YYYY-MM-DD HH:MM:SS` and `YYYYMMDD` respectively. - `Float32` and `Float64`: Accepts floating point numbers of either 32 or 64 bit precision. - `Int` or `Integer`: Accepts integer numbers of any precision. - `Int8`, `Int16`, `Int32`, `Int64`, `Int128`, `Int256` and `UInt8`, `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: ```sql {% title="Using column dynamic parameters" %} % SELECT * FROM TR ORDER BY {{column(order_by, 'timestamp')}} LIMIT {{Int32(lim, 10)}} ``` Always define the `column` function's second argument, the one for the default value. The alternative for not defining the argument is to validate that the first argument is defined, but this only has an effect on the execution of the endpoint. A placeholder is used in the development of the pipes. ```sql {% title="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 You can pass along a list of values with the `Array` function for parameters, like so: ```sql {% title="Passing arrays as dynamic parameters" %} % SELECT * FROM TR WHERE access_type IN {{Array(access_numbers, 'Int32', default='101,102,110')}} ``` ### Send stringified JSON as parameter Consider the following stringified JSON: ```json "filters": [ { "operand": "date", "operator": "equals", "value": "2018-01-02" }, { "operand": "high", "operator": "greater_than", "value": "100" }, { "operand": "symbol", "operator": "in_list", "value": "AAPL,AMZN" } ] ``` You can use the `JSON()` function to use `filters` as a query parameter. The following example shows to use the `filters` field from the JSON snippet with the stock_prices_1m sample dataset. ```sql % SELECT symbol, date, high FROM stock_prices_1m WHERE 1 {\% if defined(filters) %} {\% for item in JSON(filters, '[]') %} {\% if item.get('operator', '') == 'equals' %} AND {{ column(item.get('operand', '')) }} == {{ item.get('value', '') }} {\% elif item.get('operator') == 'greater_than' %} AND {{ column(item.get('operand', '')) }} > {{ item.get('value', '') }} {\% elif item.get('operator') == 'in_list' %} AND {{ column(item.get('operand', '')) }} IN splitByChar(',',{{ item.get('value', '') }}) {\% end %} {\% end %} {\% end %} ``` When accessing the fields in a JSON object, use the following syntax: ``` item.get('Field', 'Default value to avoid SQL errors'). ``` ## Pagination You can 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 endpoint. Use the `LIMIT` clause to select only the first `n` rows of a query result. Use the `OFFSET` clause 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 endpoint. ```sql {% title="Paging results using dynamic parameters" %} % SELECT * FROM TR LIMIT {{Int32(page_size, 100)}} OFFSET {{Int32(page, 0) * Int32(page_size, 100)}} ``` You can also use pages to perform calculations such as `count()`. The following example counts the total number of pages: ```sql {% title="Operation on a paginated endpoint" %} % SELECT count() as total_rows, ceil(total_rows/{{Int32(page_size, 100)}}) pages FROM endpoint_to_paginate ``` The addition of a `LIMIT` clause to a query also adds the `rows_before_limit_at_least` field to the response metadata. `rows_before_limit_at_least` is the lower bound on the number of rows returned by the query after transformations but before the limit was applied, and can be useful for response handling calculations. **Example:** ```sql SELECT * FROM users WHERE active = true LIMIT 10 ``` Response might include: - `rows`: 10 (actual rows returned) - `rows_before_limit_at_least`: 1,000 (at least this many active users exist) **Why "at least"?** ClickHouse can stop counting once it knows the answer exceeds the `LIMIT` value. This makes it a lower bound, not an exact count. **Useful for:** - Pagination logic ("showing 10 of at least 1,247 results") - Determining if more results exist without running a separate `COUNT()` query This metadata is returned automatically with any `LIMIT` query, saving you from running an additional `COUNT(*)` query to check if more results are available. {% callout type="tip" %} To get consistent pagination results, add an `ORDER BY` clause to your paginated queries. {% /callout %} ## Advanced templating To build more complex queries, use flow control operators like `if`, `else` and `elif` in combination with the `defined()` function, which helps you to check if a parameter whether a parameter has been received and act accordingly. Tinybird's templating system is based on the [Tornado Python framework](https://github.com/tornadoweb/tornado), and uses Python syntax. You must enclose control statements in curly brackets with percentages `{%..%}` as in the following example: ```sql {% title="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 To validate if a parameter is present in the query, use the `defined()` function. For example: ```sql {% title="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 endpoint with `/v0/pipes/:PIPE.json?my_filter=20` it applies the filter. ### Default parameter values and placeholders Following best practices, you should set default parameter values as follows: ```sql {% title="Default parameter values" %} % SELECT * FROM table WHERE attr > {{Int32(my_filter, 10)}} ``` When you call the 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 previously. If you don't validate the parameter and it's not defined, the query might 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 `__no_value__`, 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: ```sql {% title="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: ```json { "placeholder_string": "__no_value__", "placeholder_num": 0, "placeholder_bool": 0, "placeholder_float": 0, "placeholder_date": "2019-01-01", "placeholder_ts": "2019-01-01 00:00:00", "placeholder_array": ["__no_value__0","__no_value__1"] } ``` When using `defined()` functions in the `WHERE` clause, make sure to add `1` and use the `AND` operator to avoid SQL syntax errors like: ```sql SELECT * FROM table WHERE 1 {\% if defined(my_filter) %} AND attr > {{Int32(my_filter)}} {\% end %} ``` ### Cascade parameters Parameters with the same name in different pipes are cascaded down the dependency chain. For example, if you publish pipe A with the parameter `foo`, and then pipe B which uses pipe A as a data source also with the parameter `foo`, then when you call the endpoint of pipe B with `foo=bar`, the value of `foo` will be `bar` in both pipes. ## Throw custom errors The following example stops the endpoint processing and returns a 400 error: ```sql {% title="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)}} ``` The `custom_error` function is an advanced version of `error` where you can customize the response and other aspects. The function gets an object as the first argument, which is sent as JSON, and the status_code as a second argument, which defaults to 400. ```sql {% title="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)}} ``` ## Errors and retries Endpoints return standard HTTP success or error codes. For errors, the response also includes extra information about what went wrong, encoded in the response as JSON. ### Error codes Endpoints might return the following HTTP error codes: {% table %} * Code * Description --- * 400 * Bad request. A ``HTTP400`` can be returned in several scenarios and typically represents a malformed request such as errors in your SQL queries or missing query parameters. --- * 403 * Forbidden. The auth Token doesn't have the correct scopes. --- * 404 * Not found. This usually occurs when the name of the endpoint is wrong or hasn't been published. --- * 405 * HTTP Method not allowed. Requests to endpoints must use the ``GET`` method. --- * 408 * Request timeout. This occurs when the query takes too long to complete by default this is 10 seconds. --- * 414 * Request-URI Too Large. Not all APIs have the same limit but it's usually 2KB for GET requests. Reduce the URI length or use a POST request to avoid the limit. --- * 429 * Too many requests. Usually occurs when an endpoint is hitting into rate limits. --- * 499 * Connection closed. This occurs if the client closes the connection after 1 second, if this is unexpected increase the connection timeout on your end. --- * 500 * Internal Server Error. Usually an unexpected transient service error. {% /table %} Errors when running a query are usually reported as 400 Bad request or 500 Internal Server Error, depending on whether the error can be fixed by the caller or not. In those cases the API response has an additional HTTP header, `X-DB-Exception-Code` where you can check the internal database error, reported as a stringified number. ### Retries When implementing an API Gateway, make sure to handle potential errors and implement retry strategies where appropriate. Implement automatic retries for the following errors: - HTTP 429: Too many requests - HTTP 500: Internal Server Error Follow an exponential backoff when retrying requests that produce the previous errors.