Querying data with Tinybird.js

The best way to query your data in Tinybird is through the use of Pipes. You can create Pipes manually through the Web UI, manage them via javascript or directly through the API.

In order to query data through Pipes using Tinybird.js, you just need the name of the Pipe and an Auth token to query the data.

json(sql), csv(sql)

There are two ways to extract data from a Pipe.

  • .json(sql): it will provide the result in JSON format, including meta information.

  • .csv(sql): it just returns the data in CSV format with headers.

You can add any specific sql query in order to further filter the results of your Pipe for both methods.

json(sql) · limiting response data with sql
// tinybird.js constructor function requires an Auth token.
const tinyb = tinybird('<token>')
const pipe = tinyb.pipe('<pipe>')
const res = await pipe.json('select * from _ limit 3')
console.log(res.data)

You may have noticed the underscore (_) in the SQL query. With that you are essentially telling the Pipe to apply the query to the default results of the Pipe, so that you don’t have to write the name of the pipe again in the query.

Apart from the resulting data itself, the response object contains also metadata of the resulting columns of your pipe in the .metadata object.

json(sql) · showing response metadata
const tinyb = tinybird('<token>')
const pipe = tinyb.pipe('<pipe>')
const res = await pipe.json('select * from _ limit 5')
console.log(res.meta)

The response also contains some statistics about the query in the .statistics object.

json(sql) · showing query statistics
const tinyb = tinybird('<token>')
const pipe = tinyb.pipe('<pipe>')
const res = await pipe.json('select * from _ limit 2')
console.log(res.statistics, res.statistics.elapsed)

And finally, you can also get the data in CSV format.

json(sql) · showing query statistics
const tinyb = tinybird('<token>')
const pipe = tinyb.pipe('<pipe>')
const res = await pipe.csv('select * from _ limit 10')
console.log(res)

As you can see, accessing the results of your Pipe data directly from your javascript application is really easy and fast. You just need a valid Auth token and a valid Pipe name.

Additionally, there are a bunch of handy methods to help you query your data when developing your applications. Most of them can be replaced by a regular SQL query but, we think they are pretty cool and convenient ;-)

pipe(name)

This will save you some keystrokes, the pipe() method helps you setting the default pipe for your queries, so you can refer to it by typing _:

pipe(pipe_name)
const pipe = tinybird('<token>').pipe('<pipe>')
const res = await pipe.json('select count() from _')
console.log(res.data)

where(sql_filter)

Another handy tool to avoid hand-made SQL is the where() method. It enables filtering the results of your Pipe, which can be useful when creating dashboards that contain custom filters.

where(filter)
//norun
const pipe = tinybird(your_auth_token).pipe(pipe_name)
pipe.where('attr > 10')
const res = await pipe.data('select count() from _^')
console.log(res.data)

This prints the number of records with attr > 10. Note the _^ shortcut, this tells the Tinybird.js library to use the default Pipe with all the filters applied.

count()

The count() method is just a shortcut for select count() from pipe.

count()
const pipe = tinybird('<token>').pipe('<pipe>')
console.log(await pipe.count())

first()

first() returns the first row of the results.

first()
const pipe = tinybird('<token>').pipe('<pipe>');
const res = await pipe.first('select uniq(<column>) as differents from _')

console.log(`Different rows -> ${res.differents}`)

single()

single() returns the first element of the first row, useful when you just want one value, like when counting rows.

single()
const pipe = tinybird('<token>').pipe('<pipe>')
console.log(await pipe.single('select uniq(<column>) from _'))

facets()

With facets() you can get all the facets for the Pipe:

facets()
const pipe = tinybird('<token>').pipe('<pipe>')
console.log(await pipe.facets())

countPerValue()

By using countPerValue(), you can directly get the number of occurrences of each different value (for a particular column).

countPerValue()
const pipe = tinybird('<token>').pipe('<pipe>')
console.log(await pipe.countPerValue('<column>'))