Overview

Easy JavaScript based charts in any web application

ChartSQL.js is currently in development. Documentation is for feedback purposes.

ChartSQL.js is an open source library to chart SQL (or any table like data) seamlessly within any web application.

We have designed ChartSQL.js to be the easiest to use JavaScript charting library available.

Key Benefits

Simple .js integration
Supports any SQL table-like or NoSQL source of data
Full rendering control with intuitive 'directives'
What You Query is What You Chart (Whikee-Whic) - Simple and declarative charting.

Minimal Example

The following is all that is required to create a basic column chart

// Sample data, typically retrieved from an executed SQL query or API call
var data = [
  {category: 'shoes', total_sales: 5000},
  {category: 'pants', total_sales: 10000},
  {category: 'shirts', total_sales: 8000},
  {category: 'socks', total_sales: 4000}
];

// Render the data into a chart
chartsql.createChart({
  // Target HTML element id to place the chart, or one 
  // will be created and appended to the body
  target: 'auto-column',
  // The data for the chart
  data: data
});

Quick Start

Add chartsql.js to Your Project and setup a basic column chart

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <script src="../chartsql.js"></script>
</head>
<body>

  <!-- Container for Chart -->
  <div id="my-chart"></div>
  
  <script>      
    // Sample data, typically retrieved from executing the SQL query,
    // but chart data can be from any source.
    var data = [
	{category: 'shoes', total_sales: 5000},
	{category: 'pants', total_sales: 10000},
	{category: 'shirts', total_sales: 8000},
	{category: 'socks', total_sales: 4000}
    ]

    // Create and render the chart
    chartsql.createChart({
      target: 'my-chart',
      data: data
    });
  </script>
</body>
</html>

createChart({})

createChart() allows you to render data into chart visuals at the target HTML element

The typical flow is:

Setup ChartSQL
Fetch data
call createChart() to render the data into a chart.

Parameters

Parameter

Required?

Description

target

The DOM element ID where the chart will be rendered.

data

yes

An object containing the data to be used for rendering the chart. See Working with Data

directives

yes

SQL string containing directives, or object of directives.

Auto Charts

ChartSQL.js will try to automatically choose a basic chart type depending on the fields in your data.

The auto charts helps you quickly visualize and explore data and then you can Customize Charts for more control.

Auto Column Chart

When there is one string and one numeric field in the data

{category: 'shoes', total_sales: 5000}

// Sample data, typically retrieved from an executed SQL query or API call
var data = [
  {category: 'shoes', total_sales: 5000},
  {category: 'pants', total_sales: 10000},
  {category: 'shirts', total_sales: 8000},
  {category: 'socks', total_sales: 4000}
];

// Render the data into a chart
chartsql.createChart({
  // Target HTML element id to place the chart, or one 
  // will be created and appended to the body
  target: 'auto-column',
  // The data for the chart
  data: data
});

Auto Grouped Column Chart

When there is one string and 2 or more numeric fields the numeric fields will be grouped

{category: 'shoes', total_sales: 5000, total_profit: 2000}

var data = {
	columns: ['category', 'total_sales', 'total_profit'],
	rows: [
		['shoes', 5000, 2000],
		['pants', 10000, 4000],
		['shirts', 8000, 3000],
		['socks', 4000, 1000]
	]
}

chartsql.createChart({
	target: 'auto-grouped-column',
	data: data,
	directives: {
	}
});

Auto Date Line Chart

When there is a date field and a numeric field

{month: '2022-01-01', total_sales: 5000}

// Sample data, typically retrieved from an executed SQL query
var data = [
	{month: '2022-01-01', total_sales: 5000},
	{month: '2022-02-01', total_sales: 7000},
	{month: '2022-03-01', total_sales: 4900},
	{month: '2022-04-01', total_sales: 7528},
	{month: '2022-05-01', total_sales: 7762},
	{month: '2022-06-01', total_sales: 8000},
	{month: '2022-07-01', total_sales: 9500},
	{month: '2022-08-01', total_sales: 8250},
	{month: '2022-09-01', total_sales: 9000},
	{month: '2022-10-01', total_sales: 8500},
	{month: '2022-11-01', total_sales: 9500},
	{month: '2022-12-01', total_sales: 10000}
]

// Render a chart at the target element with the data provided
chartsql.createChart({
	target: 'auto-dateline',
	data: data
});

Customizing Charts

You can specify exactly how to render the chart with directives (aka @directives).

Directives allow you to fully control the type of chart and the selection of the categories, series formats and other ChartSQL.js features.

Basic Directives Example

createChart() takes a 'directives' object which contains the directives for controlling rendering of the chart.

// Sample data, typically retrieved from an executed SQL query
var data = [
	{category: 'shoes', total_sales: 5000},
	{category: 'pants', total_sales: 10000},
	{category: 'shirts', total_sales: 8000},
	{category: 'socks', total_sales: 4000}
]

// Render a chart at the target element with the data provided
chartsql.createChart({
	target: 'basic-bar',
	data: data,
	directives: {
		chart: 'bar'
	}
});

Common Directives

chart - The type of chart: bar, column, line etc
category - The column to use for the category (primary axis) of the chart
series - The column/s to use for the data (secondary axis) of the chart
formats - The data formats to render for the series

You can See All Directives for all the ways in which you can customize your charts.

ChartSQL.js is currently in development and does not support all directives. See the Supported Directives section for current support

Directives in SQL

ChartSQL's namesake comes from the fact that you can define your chart directives within SQL source code. The same capability is provided to you in ChartSQL.js.

If your data is already coming from an executed SQL script, you can also choose to define your directives in your SQL.

You pass the full SQL script to createChart directives and it will get parsed

var sql = `
	-- @chart: bar
	SELECT category, total_sales
	FROM sales
`;

// Sample data as if retrieved from an executed SQL query
var data = [
	{category: 'shoes', total_sales: 5000},
	{category: 'pants', total_sales: 10000},
	{category: 'shirts', total_sales: 8000},
	{category: 'socks', total_sales: 4000}
]

// Render a chart at the target element with the data provided
chartsql.createChart({
	target: 'basic-sql',
	data: data,
	directives: sql
});

Working with Data

ChartSQL.js is designed to work with table like data of columns and rows. Typically this data will come from executing a SQL query, but data from any source can be charted.

The createChart() method liberally accepts table data in a few different formats.

Array of Objects

var data = [
	{ category: 'shoes', total_sales: 5000 },
	{ category: 'pants', total_sales: 10000 },
	{ category: 'shirts', total_sales: 8000 },
	{ category: 'socks', total_sales: 4000 }
];

Array of Arrays

The first row in the array should be the column names

var data = [
	['category', 'total_sales'],
	['shoes', 5000],
	['pants', 10000],
	['shirts', 8000],
	['socks', 4000]
];

Table Object

var data = {
	columns: ['category', 'total_sales'],
	rows: [
		['shoes', 5000],
		['pants', 10000],
		['shirts', 8000],
		['socks', 4000]
	]
};

Data Class

If desired, you can instantiate your own instance of the Data.js class that ChartSQL creates internally.

var data = new ChartSQLjs.Data({
	columns: ['category', 'total_sales'],
	rows: [
		['shoes', 5000],
		['pants', 10000],
		['shirts', 8000],
		['socks', 4000]
	]
});

Relationship to ChartSQL Studio, Datasources, Dashboards & Extensions

ChartSQL.js is the parsing and rendering layer. It does not handle managing and executing SQL against datasources, creating dashboards, or editing SQL Charts.

Supported Features

ChartSQL.js is a subset of ChartSQL Studio and Dashboard Features. See the tables below of supported features

✔️ = fully supported
❌ = no support planned
☐ = support planned
〰️ = Partial support

Auto Charts

List of Auto Charts charts that are currently supported

Auto Chart

ChartSQL.js

ChartSQL Studio

Column

✔️

Grouped Column

✔️

Date-based Line

✔️

Datetime based line

✔️

Stacked-Grouped Column

☐

✔️

Scatter

✔️

Bubble (scatter with size)

☐

✔️

Heatmap

☐

✔️

Chart Types

List of Chart Types that are currently supported

Type

ChartSQL.js

ChartSQL Studio

Area

☐

✔️

Bar

☐

✔️

Bubble

☐

✔️

Column

☐

✔️

Combo

☐

✔️

Gauge

☐

✔️

Heatmap

☐

✔️

Line

☐

✔️

Pie

☐

✔️

Radar

☐

✔️

Scatter

☐

✔️

Directives

Directive

ChartSQL.js

ChartSQL Studio

Note

@baselines

☐

✔️

@baseline-types

☐

✔️

@chart

✔️

@cateogry

✔️

@formats

☐

✔️

@secondary-series

☐

✔️

@series

✔️

@title

☐

✔️

@subtitle

☐

✔️

@groups

☐

✔️

@series-types

☐

✔️

@series-labels

☐

✔️

@stacking-mode

☐

✔️

@dash-id

❌

✔️

Only used for publishing to ChartSQL cloud. No use for ChartSQL.js

@overlay-series

☐

✔️

@tags

❌

✔️

Used for searching inside ChartSQL Studio and dashboards. Not used by ChartSQL.js

@mongodb-query

❌

✔️

Only used by ChartSQL Studio

Other Capabilities

Capability

ChartSQL.js

ChartSQL Studio

Note

Series Assist

✔️

Assists when selecting series and no @series is defined

Category Assist

✔️

Assists in selecting categories when no @category is defined

PreviousChartSQL.js Next@threshold

Last updated 1 year ago

hashtagKey Benefits

hashtagMinimal Example

hashtagQuick Start

hashtagcreateChart({})

hashtagParameters

hashtagAuto Charts

hashtagAuto Column Chart

hashtagAuto Grouped Column Chart

hashtagAuto Date Line Chart

hashtagCustomizing Charts

hashtagBasic Directives Example

hashtagCommon Directives

hashtagDirectives in SQL

hashtagWorking with Data

hashtagArray of Objects

hashtagArray of Arrays

hashtagTable Object

hashtagData Class

hashtagRelationship to ChartSQL Studio, Datasources, Dashboards & Extensions

hashtagSupported Features

hashtagAuto Charts

hashtagChart Types

hashtagDirectives

hashtagOther Capabilities

Key Benefits

Minimal Example

Quick Start

createChart({})

Parameters

Auto Charts

Auto Column Chart

Auto Grouped Column Chart

Auto Date Line Chart

Customizing Charts

Basic Directives Example

Common Directives

Directives in SQL

Working with Data

Array of Objects

Array of Arrays

Table Object

Data Class

Relationship to ChartSQL Studio, Datasources, Dashboards & Extensions

Supported Features

Auto Charts

Chart Types

Directives

Other Capabilities