How to use charts

Published: 2025-07-15

This tutorial shows, how to use DbGate for creating nice and information-rich charts. I assume, that you are already connected to database with DbGate and you are able to run SQL query or use query designer for obtain data from SQL tables.

You could also use sample databases in DbGate Online Demo, I will use table Invoice from sample database Chinook for ilustration purposes.

Timeline chart

One of basic charts is timeline chart. It’s line chart with time on X-axis.

If you run following query:

SELECT InvoiceDate, Total from Invoice

DbGate detects 2 charts:

Tip: try to choose only columns, which you need for the chart. The auto-detection looks for all combinations of charts, some of them doesn’t have sense. These charts are offered, if you run simple query, with all columns included:

As you could see, DbGate detects more charts, but only 2 of them you could probably use.

Chart customization

After clicking on one of detected chart, you could use “Customize” button for changing chart. These are the most important customization options:

• Chart type
  • Bar
  • Line
  • Timeline - it’s special case of line chart, with time on X axis
  • Pie
  • Polar area
• X axis field
• Grouping field. Grouping is powerful charting feature, I will explain it below
• Y axis field. You could have more fields on Y axis, then more charts will be shown
  • There is special field available for each data set, “Count”, which counts rows
• Aggregate function - Sum, Minimum, Maximum, Average, First Value, Last Value

The chart configuration is stored to the query, in the beginning of the SQL file. So if you execute the same query again, the same chart will be shown. You could also directly edit configuration in YAML format, if you are comfortable with this.

Grouping

Grouping could be used, if you need to display more charts from one data set. I would like to display total invoicing by year, grouped by country

So we have to add Country column to result set:

SELECT InvoiceDate, Total, BillingCountry from Invoice where BillingCountry in ('USA', 'Canada', 'Brazil', 'France', 'Germany')

Auto-detect algorithm now detects 6 charts, click on chart “Total by Invoice Date”, grouped by country, the click to “Customize”.

You will see something like this, which doesn’t look very usable:

But when you change “Chart type” to “Bar” and “X axis transform” to “Date (Year)”, the chart looks much better now.

As you can see, grouping by date could be done in charting engine, it is not neccessary to do this in SQL. However, for bigger results, of course grouping made natively by SQL will be more efective, because this will be done directly on database server.

Group number is limited to 32, it doesn’t sense to have bigger number of lines/bars in one chart. If this limit is exceeded, the chart is not displayed.

Conclusion

I showed basic usage of DbGate charting feature. You could let us comments on this topic on X tweet. You could find more info about charts in documentation