Dashboard SQL Reference

Dashboards are expressed as SQL queries. Each query defines an UI element. Queries are separated by ;.

All SQL is valid DuckDB SQL. For more look at the DuckDB SQL docs.

Shaper creates custom SQL types. You can cast your data to these types to render the data differently. The simplest way to cast a column’s type is by appending the type with ::, for example SELECT 'hello'::LABEL.

This can be used to control the visualization of the data, add filter options and control the dashboard layout.

Tables

Data is rendered as table by default. Column names are used as table headers.

PERCENT

SELECT mynumber::PERCENT AS percent;

Renders a DOUBLE as percentage value. Your numbers should probably be between 0 and 1.

TREND

Show a trend indicator as a table column:

SELECT
  10 AS "this week",
  5 AS "last week",
  (10 / 5)::TREND AS "change";

The change column will show “100%↑”.

Single Value

SELECT 'hello world';

If a query returns a single row and a single column, it is rendered as big single card.

PERCENT

SELECT 0.33::PERCENT AS percent;

Renders a DOUBLE as percentage value. Your numbers should probably be between 0 and 1.

COMPARE

Show a comparison value below the value and a trend indicator:

SELECT
  10 AS "this week",
  5::COMPARE AS "last week";

This will show last week: 5 [100%↑] below the value.

Bar Charts

To render a bar chart you need specify along which axis to render the chart and how to plot the values. Additionally you can group the data by a category.

Any additional columns will only be visible in the tooltip on hover.

Example:

SELECT time::XAXIS, value::BARCHART, category::CATEGORY FROM my_table

XAXIS, YAXIS

You can render a bar chart along the x-axis or y-axis.

Often you will be using a timestamp as the axis column. The axis column must be a VARCHAR, TIMESTAMP, TIME or numer type.

BARCHART

Mark the column as BARCHART that you like to display as value of the bars in the chart. This must be a numeric value or INTERVAL.

CATEGORY

Mark a VARCHAR column as category to render multiple bars for each point on the axis.

This will also display a legend for the categories in the chart.

When CATEGORY is set to NULL or an empty VARCHAR then the category will not show up in the legend.

This is useful to to only change bar colors or to only label specific bars in a chart.

For example:

SQL

SELECT 'Would you recommend the product to a friend?'::LABEL;
SELECT col0::XAXIS, col1::BARCHART_STACKED_PERCENT, col2::CATEGORY
FROM (
  VALUES
  (5, 0.7,   'Fully recommend'),
  (4, 0.025, NULL),
  (3, 0.1,   NULL),
  (2, 0.025, NULL),
  (1, 0.15,  'Would not recommend'),
);

Result

BARCHART_STACKED

When using CATEGORY you can change BARCHART to BARCHART_STACKED to render categories as a single, stacked bar instead of rendering multiple bars.

BARCHART_PERCENT

Use BARCHART_PERCENT or BARCHART_STACKED_PERCENT to show the value of the chart as percentage. This sets the max value of the chart to 100%. The values should most likely be between 0 and 1.

COLOR

Specify a color for each category:

SQL

SELECT
  count()::BARCHART_STACKED,
  date_trunc('quarter',  created_at)::XAXIS AS "Quarter",
  category::CATEGORY,
  color::COLOR
FROM sessions LEFT JOIN cat_colors USING (category)
GROUP BY ALL ORDER BY ALL DESC;

Result

The last color value for each category is used. And if no color is specified, the default color is used.

We recommend storing colors as table to be able to reuse them across dashboards.

For the above example you could add this to your init.sql file:

CREATE OR REPLACE TABLE cat_colors AS (
  SELECT col0 AS category, col1 AS color FROM (
    VALUES
    ('Consultation', '#19b2ee'),
    ('Support', '#18c413'),
    ('Trial', '#f7af26'),
  )
);

If you only want to reuse colors in a single dashboard, use a temporary table instead.

You can also specify a custom color for a chart without categories:

SQL

SELECT
  count()::BARCHART,
  date_trunc('quarter',  created_at)::XAXIS AS "Quarter",
  '#f7af26'::COLOR
FROM sessions GROUP BY ALL ORDER BY ALL DESC;

Result

Line Charts

Line charts work almost identical to bar charts, but they don’t have the YAXIS and _STACKED options.

XAXIS

Specify the column to use as x-axis. Most likely this will be a time dimension.

LINECHART

Specify which column to show as value (numeric or INTERVAL)

CATEGORY

Optionally mark add a column category to render multiple lines in the line chart

LINECHART_PERCENT

Render the value of the chart as percentage. This sets the max value of the chart to 100%. The values should most likely be between 0 and 1.

COLOR

Specify a color for each category. Works the same as for bar charts (see above).

Gauges

Use a gauge to visualize a single value as progress towards a goal or to indicate a status across categories.

GAUGE

A range query must return a single with a column marked as GAUGE. Use RANGE to specify the range of the gauge:

SELECT 4::GAUGE, [0, 10]::RANGE;

GAUGE_PERCENT

To render a gauge that shows a percentage, use GAUGE_PERCENT.

If the value falls within 0 and 1 (0% to 100%), you can skip specifying the RANGE.

SQL

SELECT 'CPU Usage'::LABEL;
SELECT 0.22::GAUGE_PERCENT;

Result

RANGE, LABELS, COLORS

To show a status across multiple categories, you can specify more than two range values.

Once you do that you most likely also want to set specific colors for each category using COLORS.

And you can display a label for each category using LABELS.

Note that the LABELS and COLORS lists contain one less element than the RANGE list.

SQL

SELECT 'Score'::LABEL;
SELECT
    75::GAUGE,
    [0,     33,        66,       100]::RANGE,
    ['#f55d5d', '#fbd953', '#48af45']::COLORS,
    ['Bad',        'Okay',    'Good']::LABELS;

Result

Filtering

Make dashboards interactive by adding filter options to them.

The resulting filter value will be available in all following queries on the dashboard via DuckDB’s built-in getvariable('varname') function.

varname is the column name you specified via AS varname in the filter query.

DATEPICKER

Create a date picker:

SELECT today()::DATEPICKER AS date;

The value (above today()) is the date that is selected by default. You can use the resulting variable in the following queries with getvariable('date'). The variable is of type DATE.

DATEPICKER_FROM, DATEPICKER_TO

Create a date range picker:

SELECT
  (today() - INTERVAL '7 days')::DATEPICKER_FROM AS "from",
  (today())::DATEPICKER_TO AS "to";

The query specifies the default date range and generates two variables from and to;

Use the variables in following queries:

SELECT *
FROM mytable
WHERE created_at BETWEEN getvariable('from') AND getvariable('TO');

DROPDOWN

Define a drop-down select menu:

SELECT type::DROPDOWN AS type
FROM my_table
GROUP BY type
ORDER BY type;

The first value is selected by default. You can use the resulting variable in the following queries with getvariable('type'). The variable is of type VARCHAR.

To add a custom default value, return it as first result of the query. Use UNION ALL for this:

SELECT 'default'::DROPDOWN AS type
UNION ALL
(
  SELECT type
  FROM my_table
  GROUP BY type
  ORDER BY type
);

LABEL

Specify separate label and value for a dropdown:

SELECT
    EXTRACT(MONTH FROM range)::TEXT::DROPDOWN AS month,
    strftime(range, '%B')::LABEL
FROM range(DATE '2024-01-01', DATE '2025-01-01', INTERVAL 1 MONTH)
ORDER BY range;

This uses a number as value for the dropdown and shows the month name as label.

DROPDOWN_MULTI

Same as DROPDOWN, but allows selecting multiple values:

SELECT type::DROPDOWN_MULTI AS type
FROM my_table
GROUP BY type
ORDER BY type;

By default all values are selected. The resulting variable is a list of VARCHAR values. You can use it with an IN clause:

... WHERE type IN getvariable('type')

LABEL

Labels for multi-select dropdowns work the same as for single-select dropdowns. See the example above.

HINT

For multi-select dropdowns you can add a hint in addition to the value and the label:

SELECT
    type::DROPDOWN_MULTI AS type,
    count(*)::HINT
FROM my_table
GROUP BY type
ORDER BY type;

A common use case is to show the count of items for each category in a dropdown.

Download Buttons

DOWNLOAD_CSV

Download the result of a query as CSV file:

SELECT concat('mydownload-', today())::DOWNLOAD_CSV AS Download;
SELECT *
FROM my_table
WHERE created_at BETWEEN getvariable('from') AND getvariable('to')
ORDER BY created_at;

To render a download button return a single row with a single column with the DOWNLOAD_CSV type.

The value (here concat('mydownload-', today())) is the name of the file that will be downloaded.

And the column name (here Download) is the label of the button.

The query that follows the download button query is the data that will be downloaded.

You can use variables from previous queries to customize the download and create flexible download interfaces with this.

DOWNLOAD_XLSX

Download the result of a query as Excel file:

SELECT concat('mydownload-', today())::DOWNLOAD_XLSX AS Download;
SELECT *
FROM my_table
WHERE created_at BETWEEN getvariable('from') AND getvariable('to')
ORDER BY created_at;

Excel files (.xlsx) mostly work the same as CSV files (above).

Use Excel files if you like the data to be formatted nicely for Excel. But note that Excel files cannot be streamed to the client. They must fit fully into the server’s memory. For large datasets use CSV downloads instead.

Layout

Shaper tries to automatically layout the dashboard for you and use all available screen space.

Filters and download buttons are at the top in the header of a section. Other queries are rendered as content below.

Depending on the screen size, up to 5 content queries are rendered next to each other.

SECTION

Use SECTION to create a new section in the dashboard:

SELECT 'My Section'::SECTION;

This will create a new section with the title “My Section”.

Create a section without title to only affect the layout:

SELECT ''::SECTION;

If a section header query returns no rows, the whole section is hidden:

SELECT ''::SECTION WHERE FALSE;
SELECT 'this query result is never displayed';

This is useful to conditionally hide a section depending on other query results or filters.

Adding filters or download buttons creates a new section automatically.

LABEL

Add labels to any element on the page with a LABEL query right before it. Return a single row with a single column:

SELECT 'My Label'::LABEL;
SELECT 10 AS "My Value";

For content queries the label is shown as title at the top of the card.

For filters and download buttons the label is shown before the button. DROPDOWN_MULTI is an exception and shows the label on the button itself.

PLACEHOLDER

Leave an empty space in the content grid:

SELECT ''::PLACEHOLDER;

Placeholders are useful to align content vertically if you have no other content to fill the space:

SELECT 200 AS "Total Overall";
SELECT 150 AS "Total This Week";
SELECT ''::SECTION;
SELECT ''::PLACEHOLDER;
SELECT 50 AS "Total Last Week";

This shows four cards on the screen. “Total This Week” and “Total Last Week” are aligned vertically. And the space below “Total Overall” is empty.

Auto-Reload

Configure dashboards to automatically reload after a certain time interval.

RELOAD

Specify one RELOAD query per dashboard to configure the dashboard to reload automatically:

SELECT (INTERVAL '1 minute')::RELOAD;

The query must return a single row with a single RELOAD column.

Add this query to the bottom or the top of the dashboard.

The value can be either a TIMESTAMP or an INTERVAL.

If the value is in the past, the dashboard won’t reload.

Non-SELECT Statements

While most queries in a dashboard will be SELECT statements, there are other DuckDB SQL statements that are useful for building dashboards.

Data Exploration

The following statements return data the same way SELECT statements do.

They are particularly useful to explore the data in the system before actually building a dashboard.

You can also use them as sub-queries. For example, you use SUMMARIZE and then use SELECT to only show some of the info:

SELECT column_name, avg, count FROM (SUMMARIZE sessions);

DESCRIBE

DESCRIBE or short DESC returns the schema of a table:

DESC sessions;

Result:

column_name	column_type	null	key	default	extra
session_id	VARCHAR	YES
created_at	TIMESTAMP	YES
user_id	VARCHAR	YES
project_id	VARCHAR	YES
duration	BIGINT	YES
category	VARCHAR	YES

SUMMARIZE

SUMMARIZE; returns varies statistics for the data in each column of a table.

SHOW TABLES

SHOW TABLES; returns a list of tables in the current database schema. Combine it with USE to switch to a specific database+schema first. Or use SHOW ALL TABLES and then filter the results to the database and schema you are interested in.

SHOW ALL TABLES

SHOW ALL TABLES; returns a list of all tables in databases and all schemas. It also includes column_names and column_types which means the output can be a lot of data. You might want to skip the column info and maybe filter the result to a specific database or schema:

SELECT database, schema, name FROM (SHOW ALL TABLES);

Code Reuse

The following statements don’t display any data themselves, but they can make the dashboard SQL code easier to read and maintain.

CREATE TEMPORARY TABLE

Use CREATE TEMPORARY TABLE or short CREATE TEMP TABLE to create a temporary table that is only available in the current dashboard.

This is useful to pre-compute a dataset that is used across multiple queries. It can speed up loading times, reduce the amount of code needed and avoid errors when adjusting dashboards later on.

For example, use this to filter data based on embedding and user-selected variables:

CREATE TEMP TABLE dataset AS (
  SELECT * FROM sessions
    WHERE user_id = getvariable('user_id')
      AND category IN getvariable('category')
      AND created_at BETWEEN getvariable('start_date')
                         AND getvariable('end_date')
);

Keep in mind that the whole table is computed and stored in memory.

CREATE TEMPORARY VIEW

CREATE TEMPORARY VIEW or short CREATE TEMP VIEW creates a temporary view that is only available in the current dashboard.

Use temporary views instead of tables for in-between states that are too big to store in memory and you plan to filter or aggregate further in later queries.

Choose between temporary tables and views depending on the performance characteristics of your queries and the size of the data.

CREATE TEMP VIEW user_sessions AS (
  SELECT * FROM sessions WHERE user_id = getvariable('user_id')
);

SET VARIABLE

SET VARIABLE allows you to set a variable that can be used in queries via getvariable('varname').

Use variables instead of temporary tables and views if you only need to store a single value instead of tabular data.

For example, format the company name:

SET VARIABLE company_name = (
  SELECT name.upper().trim().replace(' ', '_').regexp_replace('[^\w]', '', 'g')
  FROM companies WHERE id = getvariable('company_id')
);

Note that the example uses the DuckDB-specific function chaining syntax to keep the code easier to read.

Shaper also uses this mechanism internally to provide variables when embedding dashboards or defining filters such as date pickers or dropdowns. Beware that SET VARIABLE overwrites any existing variable with the same name.

The DuckDB metadata function duckdb_variables() lists all variables that are currently set, which is useful for debugging. Try this after defining some variables or filters:

FROM duckdb_variables();