To the limits of SQL... and beyond

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 SELECT FROM 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 DateTime query parameter used to filter an audit log of user behavior:

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

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.

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 GROUP BY 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 if defined() expression:

SELECT variable columns

Want to give the frontend the power to SELECT and/or GROUP BY variable columns from a data source? Use the columns() 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, for statements, throwing errors, sql_and, elif…

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