Auto-generate charts from any SQL database: patterns that actually work

Auto-charting (turning an arbitrary SQL result into a sensible visualisation) is one of those problems that looks easy and turns out to have a hundred small decisions. Get them right and the chart “just appears”. Get them wrong and you get pie charts with 47 slices.

Here is the rule set that works, learned from running this on real customer data.

Start from the result shape, not the question

The user’s question is interesting, but the result tells you what is actually plottable. Inspect the result columns:

• n_rows: how many rows came back?
• For each column: type (numeric / text / boolean / date / timestamp / interval) and cardinality (distinct values).

From those, a decision tree picks the chart type.

The default-picker algorithm

A pragmatic version, in pseudocode:

if n_rows == 1 and n_cols == 1 and is_numeric(col[0]):
    return "kpi"                              # single big number

if n_rows == 1:
    return "table"                            # row card

if n_cols == 2 and is_date(col[0]) and is_numeric(col[1]):
    return "line" if n_rows >= 6 else "bar"   # time series

if n_cols == 2 and is_text(col[0]) and is_numeric(col[1]):
    if n_rows <= 12: return "bar"             # categorical
    if n_rows <= 30: return "bar (horizontal)"
    return "table"                            # too many categories

if n_cols == 3 and is_date(col[0]) and is_text(col[1]) and is_numeric(col[2]):
    if distinct(col[1]) <= 6: return "stacked_bar" or "multi_line"
    return "table"

if n_cols == 3 and all_numeric(col[1:]):
    return "scatter"

if n_cols >= 4:
    return "table"                            # too wide to chart well

You can build a more elaborate version, but this defaults right ~85% of the time and is debuggable.

Pie chart is almost never the answer

A working rule: never pick pie unless explicitly requested. Bar > pie in almost every case for human comparison, even with three categories. Save pie for the edge case where the user types “show as pie”.

Time series: get the date binning right

A common bug: the user asks “monthly revenue”, the SQL returns daily totals, the chart shows 365 line points. You need to either:

• Bin server-side (good): date_trunc('month', ...) and group.
• Bin client-side before plotting (acceptable for small results).
• Refuse and ask the user to clarify granularity (best when ambiguous).

Date granularity is the single most common source of “wrong chart” complaints. Be explicit.

Sorting and ordering

Categorical bars should be sorted by value descending by default, except when the categories have natural order (months, weekdays, quarters, ageing buckets, status enums). Detect by name:

if column_name in {"month", "quarter", "weekday", "ageing_bucket", "status"}:
    use_natural_order(column_name)
else:
    sort_desc_by_value()

Truncate at top N (8–12 visible) and group the tail into “Other”, visible only when the user asks.

Colours: pick once

Five colour rules that prevent the chart from looking amateurish:

1. One series: use a single brand-aligned colour. Don’t randomise.
2. Categorical: a fixed palette with at most 8 distinct colours. After 8, switch to bars sorted by value (more colours just confuse).
3. Sequential (heatmap, ageing buckets): a single-hue ramp, light → dark by severity.
4. Diverging (positive/negative): red/green, but only if the column is signed. For non-signed numerics, never use red/green.
5. Dark theme rules: light axes, dark grid lines, no pure white.

Annotations matter

For most numeric charts:

• Always show gridlines on the value axis, dashed.
• Always show the total or average as a footer line.
• Always include the time range (e.g., “Apr–Sep FY 25-26”) in the chart subtitle.

These three additions add maybe 30 lines of code per chart type and save dashboards from looking like Excel defaults.

Letting the user override

The auto-picker is the default. Users will want to switch chart type 5–10% of the time. Expose three controls:

• Chart type (bar / line / area / table / kpi / scatter).
• Axis swap (for 2D charts).
• Sort by value vs natural order.

That is enough. More controls add complexity without adding correctness.

Stacking the model on top

You can use an LLM to pick the chart, but in practice the rule-based picker above outperforms an LLM call because it is deterministic, fast, and explainable. Use the LLM for:

• Picking a good title from the question.
• Picking axis labels with units (₹, %, count).
• Picking chart subtitle (“Top 10 customers by overdue, sorted descending”).

Determinism for the chart type, LLM for the labels. Best of both.

The traps

Three traps we have seen repeatedly:

• Empty result → empty chart. Always show “No data for this query” with the SQL collapsed below, never a blank box.
• NULLs in category column → “null” slice. Replace nulls with (Unspecified) and call it out.
• Two columns of similar magnitude on one Y axis (say sales and orders, where sales is ₹ lakh and orders is a count). Use a dual axis and explicitly label both.

Doing this with AnalytAI

The chart picker and renderer in AnalytAI follows these rules. Connect any SQL database, ask a question, and the chart appears with the right defaults, overridable from a single toolbar.

Book a 20-minute demo and bring any SQL connection string. We will run questions against it on the call.

Related reads:

• Talk to PostgreSQL in plain English
• Writing SQL with AI in 2026