Being a straight-up Python developer with no experience in HTML, JavaScript, etc., is no longer an excuse, as many Python libraries, such as Streamlit and Gradio, have emerged over the last few years.
This article is not about them, though, because I am one of those straight-up Python developers, and I’ve already done the Streamlit and Gradio thing. So it was time to roll up my sleeves and see if I could learn new skills and create a dashboard with those old front-end development stalwarts: HTML, JavaScript, and CSS.
The data for our dashboard will come from a local SQLite database. I created a sales_data table in SQLite containing dummy sales data. Here is the data in tabular form.
Below is some code that you can use to follow along and create your own SQLite database and table with the data as shown.
In case you’re wondering why I’m only inserting a handful of records into my database, it’s not because I don’t think the code can handle large data volumes. It’s just that I wanted to concentrate on the dashboard functionality rather than being distracted by the data. Feel free to use the script I provide below to add additional records to the input data set if you like.
So, we stay in the Python world for just a bit longer as we set up a SQLite DB programmatically.
import sqlite3
# Define the database name
DATABASE_NAME = "C:\\Users\\thoma\\projects\\my-dashboard\\sales_data.db"
# Connect to SQLite database
conn = sqlite3.connect(DATABASE_NAME)
# Create a cursor object
cursor = conn.cursor()
# SQL to create the 'sales' table
create_table_query = '''
CREATE TABLE IF NOT EXISTS sales (
order_id INTEGER PRIMARY KEY,
order_date TEXT,
customer_id INTEGER,
customer_name TEXT,
product_id INTEGER,
product_names TEXT,
categories TEXT,
quantity INTEGER,
price REAL,
total REAL
);
'''
# Execute the query to create the table
cursor.execute(create_table_query)
# Sample data to insert into the 'sales' table
sample_data = [
(1, "2022-08-01", 245, "Customer_884", 201, "Smartphone", "Electronics", 3, 90.02, 270.06),
(2, "2022-02-19", 701, "Customer_1672", 205, "Printer", "Electronics", 6, 12.74, 76.44),
(3, "2017-01-01", 184, "Customer_21720", 208, "Notebook", "Stationery", 8, 48.35, 386.80),
(4, "2013-03-09", 275, "Customer_23770", 200, "Laptop", "Electronics", 3, 74.85, 224.55),
(5, "2022-04-23", 960, "Customer_23790", 210, "Cabinet", "Office", 6, 53.77, 322.62),
(6, "2019-07-10", 197, "Customer_25587", 202, "Desk", "Office", 3, 47.17, 141.51),
(7, "2014-11-12", 510, "Customer_6912", 204, "Monitor", "Electronics", 5, 22.5, 112.5),
(8, "2016-07-12", 150, "Customer_17761", 200, "Laptop", "Electronics", 9, 49.33, 443.97)
]
# SQL to insert data into the 'sales' table
insert_data_query = '''
INSERT INTO sales (order_id, order_date, customer_id, customer_name, product_id, product_names, categories, quantity, price, total)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
'''
# Insert the sample data
cursor.executemany(insert_data_query, sample_data)
# Commit the transaction
conn.commit()
# Close the connection
conn.close()
print(f"Database '{DATABASE_NAME}' has been created and populated successfully.")Dashboard Functionality
Our dashboard will have the following functionality.
- Key Metrics. Total revenue, total orders, average order value, top category
- Different Chart Types. Revenue Over Time (line chart), Revenue by Category (bar chart), Top Products by Revenue (horizontal bar chart)
- Filtering. By date and category
- Data Table. Display our data records in a paginated and searchable grid format.
Setting up our Environment
Next, we have a series of steps to follow to set up our environment.
1/ Install Node.js.
Node.js is a runtime environment that enables you to run JavaScript outside the browser, allowing you to use JavaScript to build fast and scalable server-side applications.
So, ensure Node.js is installed on your system to enable you to run a local server and manage packages. You can download it from the Node.js official website.
2/ Create a main project folder and subfolders
Open your command terminal and run the following commands. I’m using Ubuntu on my Windows box for this, but you can change it to suit your preferred command-line utility and system.
$ mkdir my-dashboard
$ cd my-dashboard
$ mkdir client
% mkdir server3/ Initialise a Node project
$ npm init -yThis command automatically creates a default package.json file in your project directory without requiring user input.
The -y flag answers “yes” to all prompts, using the default values for fields like:
- name
- version
- description
- main
- scripts
- author
- license
Here is what my package file looked like.
{
"name": "my-dashboard",
"version": "1.0.0",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC",
"description": "",
"dependencies": {
"express": "^4.21.2",
"sqlite3": "^5.1.7"
}
}4/ Install Express and SQLite
SQLite is a lightweight, file-based relational database engine that stores all your data in a single, portable file, eliminating the need for a separate server.
Express is a minimal, flexible web application framework for Node.js that simplifies the building of APIs and web servers through routing and middleware.
We can install both using the command below.
$ npm install express sqlite3Now, we can start developing our code. For this project, we’ll need four code files: an index.html file, a server.js file, a client.js file, and a script.js file.
Let’s go through each of them step by step.
1) client/index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">
<link rel="stylesheet" href="style.css">
<title>Sales Performance Dashboard</title>
</head>
<body>
<div class="container">
<!-- Centered Heading -->
<h1 class="text-center">Sales Performance Dashboard</h1>
<!-- Filter Section -->
<div class="filters row my-4">
<div class="col-md-4">
<label for="start-date">Start Date</label>
<input type="text" id="start-date" class="form-control" placeholder="Start Date">
</div>
<div class="col-md-4">
<label for="end-date">End Date</label>
<input type="text" id="end-date" class="form-control" placeholder="End Date">
</div>
<div class="col-md-4">
<label for="category-filter">Category</label>
<select id="category-filter" class="form-control">
<option value="all">All Categories</option>
<!-- Options will be populated dynamically -->
</select>
</div>
</div>
<!-- Key Metrics Section -->
<h2 class="mt-5">Key Metrics</h2> <!-- Added heading for Key Metrics -->
<div id="key-metrics" class="row text-center my-4">
<div class="col-md-3">
<h4>Total Revenue</h4>
<p id="total-revenue">$0</p>
</div>
<div class="col-md-3">
<h4>Total Orders</h4>
<p id="total-orders">0</p>
</div>
<div class="col-md-3">
<h4>Average Order Value</h4>
<p id="average-order-value">$0</p>
</div>
<div class="col-md-3">
<h4>Top Category</h4>
<p id="top-category">None</p>
</div>
</div>
<!-- Chart Section -->
<div class="chart-section my-4">
<label for="chart-type-selector">Select Chart:</label>
<select id="chart-type-selector" class="form-control mb-3">
<option value="revenueOverTime">Revenue Over Time</option>
<option value="revenueByCategory">Revenue By Category</option>
<option value="topProducts">Top Products by Revenue</option>
</select>
<canvas id="chart-canvas"></canvas>
</div>
<!-- Raw Data Table Section -->
<div id="raw-data" class="my-4">
<h3>Raw Data</h3>
<table id="data-table" class="table table-striped table-bordered"></table>
</div>
</div>
<!-- Required JS Libraries -->
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>
<script src="script.js"></script>
</body>
</html>This HTML file establishes the basic visual elements of our Sales Performance Dashboard, including interactive filters for date and category, a section displaying key sales metrics, a dropdown menu to select chart types, and a table for raw data.
Bootstrap is used for styling. Flatpickr is used for date inputs. Chart.js is used for visualisations, and DataTables is used for tabular display. Interactivity is handled by an external script.js file, which we’ll examine shortly.
Bootstrap is a popular front-end framework, initially developed by Twitter, that helps you build responsive and visually consistent web interfaces more easily and quickly.
DataTables is a jQuery-based plugin that enhances standard HTML <table> elements, transforming them into fully interactive, feature-rich tables.
Flatpickr is a lightweight, customizable JavaScript date and time picker. It lets users select dates (and optionally times) from a sleek pop-up calendar instead of typing them manually.
Chart.js is a simple yet powerful JavaScript library for creating interactive, animated charts in web applications using the <canvas> element.
2) client/style.css
/* client/style.css */
body {
background-color: #f8f9fa;
font-family: 'Arial', sans-serif;
}
h1 {
text-align: center; /* Center the heading */
margin-top: 20px; /* Add spacing above the heading */
margin-bottom: 40px; /* Add spacing below the heading */
}
.container .filters {
margin-top: 20px;
margin-bottom: 60px !important; /* Ensure larger spacing between filters and Key Metrics */
}
.container #key-metrics {
margin-top: 40px !important; /* Additional spacing above the Key Metrics section */
margin-bottom: 20px; /* Optional spacing below */
}
.key-metrics div {
margin: 10px 0;
padding: 10px;
background-color: #f4f4f4;
border: 1px solid #ccc;
border-radius: 4px;
}
/* Fix for DataTables Pagination Spacing */
.dataTables_wrapper .dataTables_paginate {
text-align: center;
margin-top: 10px;
}
.dataTables_wrapper .dataTables_paginate .paginate_button {
margin: 0 12px;
padding: 5px 10px;
border: 1px solid #ddd;
border-radius: 4px;
background-color: #f9f9f9;
color: #007bff;
text-decoration: none;
display: inline-block;
}
.dataTables_wrapper .dataTables_paginate .paginate_button:hover {
background-color: #007bff;
color: #fff;
border: 1px solid #007bff;
}
.dataTables_wrapper .dataTables_paginate .paginate_button.current {
font-weight: bold;
color: #fff;
background-color: #007bff;
border-color: #007bff;
}We use a cascading style sheet (CSS) to style the basic visual components of our dashboard, for example, button and text colours, spacing between elements, etc.
The style.css file gives the dashboard its appearance and overall look. It’s a clean, light theme with ample spacing and layout adjustments for clarity and readability. The style.css file also customises the appearance of DataTables’ pagination buttons, making them more user-friendly and visually consistent with Bootstrap’s design.
3) server/server.js
const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const app = express();
const PORT = 3000;
// Full path to your SQLite database
const DB_PATH = "C:\\Users\\thoma\\projects\\my-dashboard\\sales_data.db";
// Serve static files from the client directory
app.use(express.static(path.join(__dirname, '..', 'client')));
// Route to fetch data from SQLite database
app.get('/data', (req, res) => {
const db = new sqlite3.Database(DB_PATH, sqlite3.OPEN_READONLY, (err) => {
if (err) {
console.error("Error connecting to database:", err.message);
res.status(500).json({ error: "Database connection failed" });
return;
}
});
// Query the database
const query = "SELECT * FROM sales;"; // Replace 'sales' with your table name
db.all(query, [], (err, rows) => {
if (err) {
console.error("Error running query:", err.message);
res.status(500).json({ error: "Query failed" });
} else {
res.json(rows); // Send the query result as JSON
}
});
db.close((err) => {
if (err) {
console.error("Error closing database:", err.message);
}
});
});
// Catch-all route to serve the main HTML file
app.get('*', (req, res) => {
res.sendFile(path.join(__dirname, '..', 'client', 'index.html'));
});
// Start the server
app.listen(PORT, () => {
console.log(`Server running at http://localhost:${PORT}`);
});This Node.js script contains the JavaScript code that sets up a basic Express server that powers the Sales Performance Dashboard. It does two main things:
- Serves static files (like HTML, CSS, and JS) from the client subfolder so the frontend loads in the browser.
- Provides a /data endpoint that reads from a local SQLite database (sales_data.db) and returns the entire sales table as JSON, enabling dynamic data visualisations and tables on the frontend.
NB After publication, a reader noted the following error when running the above code.
PS D:\repo\my-dashboard\server> node server.js
D:\repo\my-dashboard\node_modules\path-to-regexp\dist\index.js:96
throw new PathError(`Missing parameter name at index ${index}`, str);
^
PathError [TypeError]: Missing parameter name at index 1: *; visit https://git.new/pathToRegexpError for info
at name (D:\repo\my-dashboard\node_modules\path-to-regexp\dist\index.js:96:19)
at parse (D:\repo\my-dashboard\node_modules\path-to-regexp\dist\index.js:113:68)
at pathToRegexp (D:\repo\my-dashboard\node_modules\path-to-regexp\dist\index.js:267:58)
at Object.match (D:\repo\my-dashboard\node_modules\path-to-regexp\dist\index.js:237:30)
at matcher (D:\repo\my-dashboard\node_modules\router\lib\layer.js:86:23)
at new Layer (D:\repo\my-dashboard\node_modules\router\lib\layer.js:93:62)
at router.route (D:\repo\my-dashboard\node_modules\router\index.js:428:17)
at app.route (D:\repo\my-dashboard\node_modules\express\lib\application.js:257:22)
at app.<computed> [as get] (D:\repo\my-dashboard\node_modules\express\lib\application.js:478:22)
at Object.<anonymous> (D:\repo\my-dashboard\server\server.js:42:5) {
originalPath: '*'
}The issue seems to have been caused by the version of the Express library they were using. I suggested changing this line,
app.get(‘*’, (req, res) => {
to
app.get(‘/*’, (req, res) => {
In the end, after some experimentation, they found that changing the line to the one shown below worked for them.
app.get(‘/’, (req, res) => {
4) client/script.js
let chartInstance = null; // Global variable to store the current Chart.js instance
// Wait until the DOM is fully loaded
document.addEventListener('DOMContentLoaded', function () {
// Fetch sales data from the backend API
fetch('/data')
.then((response) => response.json())
.then((data) => {
// Handle case where no data is returned
if (!data || data.length === 0) {
const app = document.getElementById('app');
if (app) {
app.innerHTML = "<p>No data available.</p>";
}
return;
}
// Initialize filters and dashboard content
setupFilters(data);
initializeDashboard(data);
// Re-render charts when chart type changes
document.getElementById('chart-type-selector').onchange = () => filterAndRenderData(data);
})
.catch((error) => {
// Handle fetch error
console.error('Error fetching data:', error);
const app = document.getElementById('app');
if (app) {
app.innerHTML = "<p>Failed to fetch data.</p>";
}
});
});
// Initialize Flatpickr date pickers and category filter
function setupFilters(data) {
// Convert date strings to JS Date objects
const dates = data.map((item) => new Date(item.order_date.split('/').reverse().join('-')));
const minDate = new Date(Math.min(...dates));
const maxDate = new Date(Math.max(...dates));
// Configure start date picker
flatpickr("#start-date", {
defaultDate: minDate.toISOString().slice(0, 10),
dateFormat: "Y-m-d",
altInput: true,
altFormat: "F j, Y",
onChange: function () {
filterAndRenderData(data);
},
});
// Configure end date picker
flatpickr("#end-date", {
defaultDate: maxDate.toISOString().slice(0, 10),
dateFormat: "Y-m-d",
altInput: true,
altFormat: "F j, Y",
onChange: function () {
filterAndRenderData(data);
},
});
// Set up category dropdown change listener
const categoryFilter = document.getElementById('category-filter');
if (categoryFilter) {
categoryFilter.onchange = () => filterAndRenderData(data);
}
}
// Initialize dashboard after filters are set
function initializeDashboard(data) {
populateCategoryFilter(data); // Populate category dropdown
filterAndRenderData(data); // Initial render with all data
}
// Apply filters and update key metrics, chart, and table
function filterAndRenderData(data) {
const chartType = document.getElementById('chart-type-selector').value;
const startDate = document.getElementById('start-date')._flatpickr.selectedDates[0];
const endDate = document.getElementById('end-date')._flatpickr.selectedDates[0];
const selectedCategory = document.getElementById('category-filter').value;
// Filter data by date and category
const filteredData = data.filter((item) => {
const itemDate = new Date(item.order_date.split('/').reverse().join('-'));
return (
itemDate >= startDate &&
itemDate <= endDate &&
(selectedCategory === 'all' || item.categories === selectedCategory)
);
});
updateKeyMetrics(filteredData); // Update metrics like revenue and orders
drawChart(filteredData, 'chart-canvas', chartType); // Render chart
populateDataTable(filteredData); // Update table
}
// Update dashboard metrics (total revenue, order count, etc.)
function updateKeyMetrics(data) {
const totalRevenue = data.reduce((acc, item) => acc + parseFloat(item.total), 0);
const totalOrders = data.length;
const averageOrderValue = totalOrders > 0 ? totalRevenue / totalOrders : 0;
// Calculate total revenue per category to find top category
const revenueByCategory = data.reduce((acc, item) => {
const category = item.categories || "Uncategorized";
acc[category] = (acc[category] || 0) + parseFloat(item.total);
return acc;
}, {});
// Determine category with highest total revenue
const topCategory = Object.keys(revenueByCategory).reduce(
(a, b) => (revenueByCategory[a] > revenueByCategory[b] ? a : b),
"None"
);
// Display metrics in the DOM
document.getElementById('total-revenue').textContent = `$${totalRevenue.toFixed(2)}`;
document.getElementById('total-orders').textContent = `${totalOrders}`;
document.getElementById('average-order-value').textContent = `$${averageOrderValue.toFixed(2)}`;
document.getElementById('top-category').textContent = topCategory || 'None';
}
// Draw the selected chart type using Chart.js
function drawChart(data, elementId, chartType) {
const ctx = document.getElementById(elementId).getContext('2d');
// Destroy previous chart if one exists
if (chartInstance) {
chartInstance.destroy();
}
switch (chartType) {
case 'revenueOverTime':
// Line chart showing revenue by order date
chartInstance = new Chart(ctx, {
type: 'line',
data: {
labels: data.map((item) => item.order_date),
datasets: [{
label: 'Revenue Over Time',
data: data.map((item) => parseFloat(item.total)),
fill: false,
borderColor: 'rgb(75, 192, 192)',
tension: 0.1,
}],
},
options: {
scales: {
y: { beginAtZero: true },
},
},
});
break;
case 'revenueByCategory':
// Bar chart showing total revenue per category
const categories = [...new Set(data.map((item) => item.categories))];
const revenueByCategory = categories.map((category) => {
return {
category,
revenue: data
.filter((item) => item.categories === category)
.reduce((acc, item) => acc + parseFloat(item.total), 0),
};
});
chartInstance = new Chart(ctx, {
type: 'bar',
data: {
labels: revenueByCategory.map((item) => item.category),
datasets: [{
label: 'Revenue by Category',
data: revenueByCategory.map((item) => item.revenue),
backgroundColor: 'rgba(255, 99, 132, 0.2)',
borderColor: 'rgba(255, 99, 132, 1)',
borderWidth: 1,
}],
},
options: {
scales: {
y: { beginAtZero: true },
},
},
});
break;
case 'topProducts':
// Horizontal bar chart showing top 10 products by revenue
const productRevenue = data.reduce((acc, item) => {
const productName = item.product_names || 'Unknown Product';
acc[productName] = (acc[productName] || 0) + parseFloat(item.total);
return acc;
}, {});
const topProducts = Object.entries(productRevenue)
.sort((a, b) => b[1] - a[1])
.slice(0, 10);
chartInstance = new Chart(ctx, {
type: 'bar',
data: {
labels: topProducts.map((item) => item[0]), // Product names
datasets: [{
label: 'Top Products by Revenue',
data: topProducts.map((item) => item[1]), // Revenue
backgroundColor: 'rgba(54, 162, 235, 0.8)',
borderColor: 'rgba(54, 162, 235, 1)',
borderWidth: 1,
}],
},
options: {
indexAxis: 'y', // Horizontal bars
scales: {
x: { beginAtZero: true },
},
},
});
break;
}
}
// Display filtered data in a DataTable
function populateDataTable(data) {
const tableElement = $('#data-table');
// Destroy existing table if it exists
if ($.fn.DataTable.isDataTable(tableElement)) {
tableElement.DataTable().clear().destroy();
}
// Create a new DataTable with relevant columns
tableElement.DataTable({
data: data.map((item) => [
item.order_id,
item.order_date,
item.customer_id,
item.product_names,
item.categories,
`$${parseFloat(item.total).toFixed(2)}`,
]),
columns: [
{ title: "Order ID" },
{ title: "Order Date" },
{ title: "Customer ID" },
{ title: "Product" },
{ title: "Category" },
{ title: "Total" },
],
});
}
// Populate the category filter dropdown with available categories
function populateCategoryFilter(data) {
const categoryFilter = document.getElementById('category-filter');
categoryFilter.innerHTML = '';
categoryFilter.appendChild(new Option('All Categories', 'all', true, true));
// Extract unique categories
const categories = new Set(data.map((item) => item.categories));
categories.forEach((category) => {
categoryFilter.appendChild(new Option(category, category));
});
}It’s our most complicated code file, but it has to do a lot. This JavaScript file powers the interactivity and data visualisation for the Sales Performance Dashboard. In short, it …
1/ Fetches sales data
- When the page loads (
DOMContentLoaded), it calls a backend API at the /data endpoint. - If no data is returned, a “No data available” message is displayed.
2/ Sets up filters
- Uses Flatpickr date pickers to choose a start and end date based on the dataset’s min/max order dates.
- Adds a category dropdown, allowing users to filter by product category.
- Adds a chart type selector to switch between different chart visualisations.
3/ Initialises the dashboard
- Populates the category filter with available categories.
- Runs the first render with the full dataset.
4/ Applies filters and re-renders
- Each time the user changes a filter (date range, category, or chart type), it:
- Filters the dataset by date range and category.
- Updates key metrics: total revenue, number of orders, average order value, and top revenue category.
- Redraws the selected Chart.js chart.
- Refreshes the data table.
5/ Draws charts with Chart.js
- Revenue Over Time → Line chart showing revenue trends by date.
- Revenue by Category → Bar chart aggregating total revenue per category.
- Top Products → Horizontal bar chart showing the top 10 products by revenue.
6/ Displays tabular data
- Uses DataTables (a jQuery plugin) to render a table of filtered orders, with columns for order ID, date, customer ID, product, category, and total.
7/ Keeps the UI in sync
- Destroys and recreates charts/tables when filters change to avoid duplicates.
- Keeps metrics, charts, and tables consistent with the active filters.
Running our dashboard
Now that we have all our code sorted, it’s time to run the dashboard, so go to the server subfolder and type in the following command.
$ node server.jsYou’ll get a response to the above command, something like,
Server running at http://localhost:3000Open a web browser and visit http://localhost:3000. You should see your dashboard populated with data from the SQLite database, as shown in the image below.
All the filters, chart selection, etc, should work as advertised.
Summary
In this article, I’ve walked you through creating a fully functional, interactive sales performance dashboard using core web technologies—HTML, CSS, JavaScript, Node.js, Express, and a local SQLite database.
We discussed the tech stack & setup. i.e.
- Backend: Node.js, Express, SQLite
- Frontend: HTML, Bootstrap (for layout), Chart.js (for charts), Flatpickr (date pickers), DataTables (for tabular data)
- Folder structure as shown below.
my-dashboard/
├── client/
│ ├── index.html
│ ├── style.css
│ └── script.js
└── server/
└── server.jsI showed you how to create and populate a SQLite database in code that we could use as the source data for our dashboard. We also discussed the environment setup and both the front-end and back-end development processes, and briefly touched on our data dashboard functionality.
Finally, I walked you through and explained in detail the four code files we needed to create, and then showed you how to run the dashboard in a browser.
