Queries are a type of page that allow you to leverage SQL to get the data you want from your connections.
It’s possible to create new queries from within teams.
SELECT data from your connection and the result will appear in a table or chart layout. By default, a table layout is used.
SELECTstatements. We want to avoid users executing non-read-only operations, such as
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
SELECTstatements 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 data source 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
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.