🚀
Introducing Versions: Develop data products using Git. Join the waitlist

To the limits of SQL... and beyond

The Tinybird templating language lets you do more than define query parameters for your endpoints.
David Margulies
Sales Engineer
Jul 15, 2022
 ・ 
  min read

Tinybird’s special sauce is the one-click API endpoint. You write some SQL, click a button, and - boom - you’ve got a REST API endpoint fully loaded with OpenAI compatible docs.

Why did we focus on the API endpoint? Simple: our mission is to make it delightful for developers to build things with data. What better way to do this than to make it simple to build a customizable API on top of a real-time data source?

Of course, an API isn’t an API without parameterization, which is why query parameters are a fundamental part of our architecture. We support parameters out of the box, defined within SQL queries in Tinybird Pipes using a templating language.

This post explains a bit more about our approach to parameterization, and some cool examples putting a few of the more complex use cases into action in Tinybird. 

Anatomy of a Tinybird endpoint parameter

The basic post-ingestion flow of data in Tinybird starts with a Pipe, which is a set of chained SQL queries where subsequent nodes can {%code-line%}SELECT FROM{%code-line-end%} prior nodes. Typically, the final node in the Pipe is then published as an API endpoint.

Using our templating language, you can add an API parameter to any node in the chain. Here’s a very simple example of a {%code-line%}DateTime{%code-line-end%} query parameter used to filter an audit log of user behavior:

{% tip-box title="The % Character" %}When adding a parameter to a Tinybird Pipe, you have to add the '%' character to the beginning of the query. This happens automatically in the UI, but if you're writing Pipes in the CLI, make sure to add the '%'.{%tip-box-end%}

After you publish this node as an endpoint, you can see the new parameter on the API Endpoint page.

A parameter for a Tinybird endpoint in the Tinybird UI
The new parameter in the Tinybird UI

You can also share the documentation with your team directly from Tinybird, or you can peek over at the Swagger docs and see the new parameter.

A parameter for a Tinybird endpoint in Swagger documentation
The new parameter in the automatically generated Swagger docs.

Of course, simple query parameters like the one above are just par for the course. We couldn’t not have them in our product from day one.

But we wanted to take things a step further and extend the functionality beyond just simple parameterization. We often tell customers that what you can do in Tinybird is constrained only by the limits of SQL. But in reality, you can take it even further.

Let me explain:

Powerful query parameters

Inspired by the possibilities that other template engines offer, we implemented a similar approach in our Pipes. When you write SQL in Tinybird, you can not only add typed parameters using a templating language but also extend the logical use of those parameters beyond the limits of SQL with more complex expressions.

Rather than try to explain how those work, I figured I could just offer some of my favorite examples of Tinybird's templating engine in action. So here are some cool examples of complex template expressions you can add to Tinybird Pipes to extend the functionality of your query parameters.

Examples of complex parameterization in Tinybird

Use filter arrays

Want to filter results by more than one parameter value? Pass an array:

Perform dynamic aggregations

Want to change the granularity of a {%code-line%}GROUP BY{%code-line-end%} based on a selected date range? Create a dynamic aggregation:

SELECT if a parameter is defined

Want to change your data source or filters based on if a parameter is defined? Use an {%code-line%}if defined(){%code-line-end%} expression:

SELECT variable columns

Want to give the frontend the power to {%code-line%}SELECT{%code-line-end%} and/or {%code-line%}GROUP BY{%code-line-end%} variable columns from a data source? Use the {%code-line%}columns(){%code-line-end%} function:

And much, much more.

These are just 4 examples, and there are even more powerful things you can do with templates, including setting private variables, {%code-line%}for{%code-line-end%} statements, throwing errors, {%code-line%}sql_and{%code-line-end%}, {%code-line%}elif{%code-line-end%}…

You can find a more complete list in our advanced templating guide or in the advanced templates API docs.