Queries

Queries

Queries

Queries are a type of page that allow you to leverage SQL to get the data you want from your connections.
image

Using queries

Queries can SELECT data from your connection and the result will appear in a table or chart layout. By default, a table layout is used.

⚠️
Basedash provides some basic validation to try to ensure your SQL only contains SELECT statements. We want to avoid users executing non-read-only operations, such as UPDATE, DELETE, and DROP TABLE. If you try and run a query which contains non-read-only operations, then Basedash should give you an error explaining that only SELECT statements are allowed (and not execute the query). Keep in mind, that we can’t guarantee that our validation would prevent all non-read-only operations from being executed across all SQL dialects. We attempt to provide a reasonable guardrail.

Queries are useful in the following scenarios:

  • When you need to do deep joins of your connection tables (e.g. joining on a foreign value from an already joined table)
  • Visualize data in a line or bar chart
  • Leverage SQL to make calculations on your data

Charts

Using queries, you can create simple charts to visualize your data. You can do this by selecting the “Chart” layout on a query.

Charts require a specific format of data from the query you write. The first column specified in the SQL query will be used as the chart’s x-axis. The second column defines the values used in the chart.

For example, the following query will display a line chart showing user count every month of the past year:

select dates.date, COUNT(*) as "User count"
from generate_series(
    date_trunc('month', CURRENT_DATE - interval '12 months'),
    date_trunc('month', CURRENT_DATE),
    interval '1 month'
) as dates
left outer join "User"
    on "User"."createdAt" <= dates.date
group by dates.date
order by dates.date asc;

We’ll automatically determine which chart type to use based on the data you query—a line chart will be used if one of the data points is a date, otherwise a bar chart will be used.

Write and modify queries using AI

When creating a new query, you can use natural language to describe the data you’d like to see and we will using AI to generate the SQL required to fetch that data. You can also use AI to describe modifications that you’d like to make to your SQL query.

image