Data Visualization

Learn how to create beautiful data visualizations in SQLPage using charts, tables, and interactive components. Transform your SQL query results into compelling visual displays.

What You’ll Build

Interactive data visualizations including:

Bar, line, and area charts
Interactive sortable tables
Dynamic calculations and computations
Multi-series charts with custom styling
Color-coded cards for data categorization

Creating Charts

The chart component turns your SQL data into beautiful visualizations.

Basic Bar Chart

select 'chart' as component,
    'Revenue per country' as title,
    'bar' as type,
    'time' as xtitle,
    'price' as ytitle,
    true as stacked;

select 'Russia' as series,
    '2022-01' as label,
    2 as value
union all
select 'Russia', '2022-02', 4
union all
select 'Russia', '2022-03', 2;

select 'Brasil' as series,
    '2022-01' as label,
    4 as value
union all
select 'Brasil', '2022-03', 1
union all
select 'Brasil', '2022-04', 1;

Component Properties

type: Chart type - bar, line, area, scatter, pie, etc.
xtitle: X-axis label
ytitle: Y-axis label
stacked: Stack multiple series on top of each other
title: Chart title displayed above the chart

Row Properties

Each row in your query represents one data point:

series: Name of the data series (for multi-series charts)
label: X-axis value or category name
value: Y-axis numeric value

Area Chart with Computed Data

select 'chart' as component,
    'Collatz conjecture' as title,
    'area' as type;

SELECT 'syracuse' as series, x, y
FROM (
    SELECT 0 AS x, 15 AS y 
    UNION SELECT 1, 46 
    UNION SELECT 2, 23 
    UNION SELECT 3, 70 
    UNION SELECT 4, 35 
    UNION SELECT 5, 106 
    UNION SELECT 6, 53 
    UNION SELECT 7, 160 
    UNION SELECT 8, 80 
    UNION SELECT 9, 40 
    UNION SELECT 10, 20 
    UNION SELECT 11, 10 
    UNION SELECT 12, 5
) AS syracuse 
ORDER BY x;

Use subqueries and CTEs to perform calculations before charting. This example shows the Collatz conjecture sequence calculated in SQL.

Chart Types and Use Cases

Bar Chart
Line Chart
Area Chart
Pie Chart

Best for: Comparing values across categories

select 'chart' as component,
    'Sales by Product' as title,
    'bar' as type;
    
select 
    product_name as label,
    sum(sales) as value
from sales
group by product_name;

Best for: Showing trends over time

select 'chart' as component,
    'User Growth' as title,
    'line' as type,
    true as marker;
    
select 
    date as label,
    count(*) as value
from users
group by date
order by date;

Best for: Showing volume or cumulative totals

select 'chart' as component,
    'Cumulative Revenue' as title,
    'area' as type,
    true as fill;
    
select 
    month as label,
    sum(sum(revenue)) over (order by month) as value
from transactions
group by month;

Best for: Showing parts of a whole

select 'chart' as component,
    'Market Share' as title,
    'pie' as type;
    
select 
    company_name as label,
    market_share as value
from companies
where market_share > 0;

Interactive Tables

The table component creates feature-rich data tables.

select 'table' as component,
    true as sort,
    true as search,
    true as hover,
    true as striped_rows;

select 
    'John' as "First Name",
    'Doe' as "Last Name",
    1994 as "Birth Date"
union all
select 'Jane', 'Smith', 1989;

Table Features

Sorting

Set sort: true to enable column sorting. Users can click headers to sort ascending or descending.

Search

Set search: true to add a search box that filters rows across all columns.

Pagination

Large result sets automatically paginate. Control with the page_size property.

Custom Styling

Use hover, striped_rows, striped_columns, and border for visual customization.

Advanced Table with Markdown

select 'table' as component,
    true as sort,
    true as search,
    'actions' as markdown;  -- Render the 'actions' column as markdown

select
    id,
    name,
    email,
    '[Edit](edit.sql?id=' || id || ') | [Delete](delete.sql?id=' || id || ')' as actions
from users
order by id;

The markdown property specifies which columns should be rendered as Markdown, allowing you to embed links, images, and formatted text in table cells.

Dynamic Cards with Calculations

Create interactive learning tools with computed values.

select 'card' as component,
    5 as columns;

WITH nums(x) AS (
    SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 
    UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
)
SELECT 
    a.x || ' times ' || b.x as title,
    CASE a.x % 4
        WHEN 0 THEN 'red'
        WHEN 1 THEN 'green'
        WHEN 3 THEN 'yellow'
        ELSE 'blue'
    END as color,
    a.x || ' x ' || b.x || ' = ' || (a.x * b.x) as description,
    'This is basic math' as footer,
    '?x=' || a.x as link
FROM nums as a, nums as b
WHERE 
    $x IS NULL OR
    b.x = CAST($x AS DECIMAL)
ORDER BY a.x, b.x;

How It Works

Generate Number Grid

The CTE creates numbers 1-10, then the cartesian join creates all combinations (multiplication table).

Color Coding

Each card gets a color based on the first number modulo 4, making patterns visible.

Interactive Filtering

When you click a card, it sets ?x= in the URL. The WHERE clause then filters to show only that multiplication table.

URL Parameters

$x reads the URL parameter. If null, show all tables. If set, filter to that specific number.

Forms for Data Input

Combine forms with data visualization for complete applications.

select 'form' as component,
    'Create' as validate,
    'New User' as title;
    
select 'number' as type,
    'age' as name,
    'Age' as label;
    
select 
    'First Name' as name,
    true as autocomplete,
    true as required,
    'We need your name for legal reasons.' as description;
    
select 'Last name' as name,
    true as autocomplete;
    
select 
    'radio' as type,
    'favorite_food' as name,
    'banana' as value,
    'I like bananas the most' as label;
    
select 
    'radio' as type,
    'favorite_food' as name,
    'cake' as value,
    'I like cake more' as label,
    'Bananas are okay, but I prefer cake' as description;
    
select 
    'checkbox' as type,
    'checks[]' as name,
    1 as value,
    'Accept the terms and conditions' as label;

Accessing Form Data

Text Fields
Radio Buttons
Checkboxes

INSERT INTO users (first_name, last_name) 
VALUES (:"First Name", :"Last name")
WHERE :"First Name" IS NOT NULL;

SELECT 
    'You chose: ' || :favorite_food as message
WHERE :favorite_food IS NOT NULL;

SELECT 'debug' as component;
SELECT :checks as checkbox_values;
-- Checkboxes return JSON array of selected values

Real-World Dashboard Example

-- Page Header
select 'shell' as component,
    'Sales Dashboard' as title,
    'chart-bar' as icon;

-- Key Metrics
select 'card' as component, 4 as columns;

select 
    'Total Revenue' as title,
    '$' || sum(amount) as description,
    'green' as color,
    'currency-dollar' as icon
from sales;

select 
    'Orders Today' as title,
    count(*) as description,
    'blue' as color,
    'shopping-cart' as icon
from orders
where date(created_at) = date('now');

select 
    'Active Users' as title,
    count(distinct user_id) as description,
    'orange' as color,
    'users' as icon
from sessions
where last_active > datetime('now', '-1 hour');

select 
    'Conversion Rate' as title,
    round(100.0 * count(*) / (select count(*) from visits), 2) || '%' as description,
    'purple' as color,
    'percentage' as icon
from conversions;

-- Sales Trend Chart
select 'chart' as component,
    'Sales Trend (Last 30 Days)' as title,
    'area' as type,
    'Date' as xtitle,
    'Revenue ($)' as ytitle;

select 
    date(created_at) as label,
    sum(amount) as value
from sales
where created_at > date('now', '-30 days')
group by date(created_at)
order by date(created_at);

-- Top Products Table
select 'table' as component,
    'Top Selling Products' as title,
    true as sort,
    true as hover;

select 
    product_name as "Product",
    count(*) as "Orders",
    '$' || sum(amount) as "Revenue",
    round(avg(amount), 2) as "Avg Price"
from sales
join products on sales.product_id = products.id
group by product_name
order by sum(amount) desc
limit 10;

Advanced Visualization Techniques

Multiple Series Charts

Compare multiple data series on the same chart:

select 'chart' as component,
    'Sales by Region' as title,
    'line' as type;
    
select 
    region as series,
    month as label,
    sum(sales) as value
from sales_data
group by region, month
order by month, region;

Computed Window Functions

Use SQL window functions for running totals and moving averages:

select 'chart' as component,
    'Running Total' as title,
    'area' as type;
    
select 
    date as label,
    sum(sum(amount)) over (order by date) as value
from transactions
group by date;

Conditional Formatting

Apply colors based on data values:

select 'card' as component;

select 
    product_name as title,
    stock_level as description,
    case 
        when stock_level < 10 then 'red'
        when stock_level < 50 then 'orange'
        else 'green'
    end as color
from inventory;

Dynamic Chart Types

Let users choose chart type via URL parameter:

select 'chart' as component,
    'Flexible Visualization' as title,
    coalesce($chart_type, 'bar') as type;
    
select 
    category as label,
    sum(value) as value
from data
group by category;

Users can then append ?chart_type=line or ?chart_type=pie to change visualization.

Debugging and Data Inspection

Use the debug component during development:

select 'debug' as component;
select 
    $x as x_parameter,
    :"First Name" as firstName,
    :checks as checkboxes;

This displays all query results in a raw format, perfect for:

Inspecting URL parameters
Checking form submission data
Debugging complex queries
Understanding data structure

Chart Customization Options

type

string

default:"bar"

Chart type: bar, line, area, scatter, pie, donut, radar, bubble

stacked

boolean

default:"false"

Stack multiple series on top of each other

marker

boolean

default:"false"

Show markers on line/area charts at each data point

fill

boolean

default:"true"

Fill the area under line charts

xtitle

string

Label for the X-axis

ytitle

string

Label for the Y-axis

labels

boolean

default:"true"

Show data labels on the chart

legend

boolean

default:"true"

Show the chart legend

Performance Tips

Limit Data Points

Keep chart data points under 1000 for optimal performance. Use aggregation:

-- Bad: 100,000 individual points
select timestamp as label, value from metrics;

-- Good: Aggregated to hourly
select 
    strftime('%Y-%m-%d %H:00', timestamp) as label,
    avg(value) as value
from metrics
group by strftime('%Y-%m-%d %H', timestamp);

Use Indexes

Add indexes on columns used in WHERE, GROUP BY, and ORDER BY:

CREATE INDEX idx_sales_date ON sales(created_at);
CREATE INDEX idx_sales_product ON sales(product_id);

Materialize Complex Views

For expensive calculations, create materialized views or summary tables that update periodically.

Complete Example

The full demonstration including all these techniques is available in the SQLPage repository.

Further Resources

Chart Component Docs

Complete reference for the chart component

Table Component Docs

Full documentation for table features

Form Component Docs

Learn about all form field types

Card Component Docs

Explore card layouts and options

Examples

Data Visualization

What You’ll Build

Creating Charts

Basic Bar Chart

Area Chart with Computed Data

Chart Types and Use Cases

Interactive Tables

Table Features

Sorting

Search

Pagination

Custom Styling

Advanced Table with Markdown

Dynamic Cards with Calculations

How It Works

Forms for Data Input

Accessing Form Data

Real-World Dashboard Example

Advanced Visualization Techniques

Debugging and Data Inspection

Chart Customization Options

Performance Tips

Complete Example

Further Resources

Chart Component Docs

Table Component Docs

Form Component Docs

Card Component Docs

Build docs developers (and LLMs) love

Examples

​What You’ll Build

​Creating Charts

​Basic Bar Chart

​Area Chart with Computed Data

​Chart Types and Use Cases

​Interactive Tables

​Table Features

Sorting

Search

Pagination

Custom Styling

​Advanced Table with Markdown

​Dynamic Cards with Calculations

​How It Works

​Forms for Data Input

​Accessing Form Data

​Real-World Dashboard Example

​Advanced Visualization Techniques

​Debugging and Data Inspection

​Chart Customization Options

​Performance Tips

​Complete Example

​Further Resources

Chart Component Docs

Table Component Docs

Form Component Docs

Card Component Docs

Build docs developers (and LLMs) love

What You’ll Build

Creating Charts

Basic Bar Chart

Area Chart with Computed Data

Chart Types and Use Cases

Interactive Tables

Table Features

Advanced Table with Markdown

Dynamic Cards with Calculations

How It Works

Forms for Data Input

Accessing Form Data

Real-World Dashboard Example

Advanced Visualization Techniques

Debugging and Data Inspection

Chart Customization Options

Performance Tips

Complete Example

Further Resources