In this article, I will develop a Node.js Express application that imports data from a CSV file into a SQLite database and render a pie chart using Google Charts. The purpose of this post is to familiarize the reader with Node Express, importing a CSV file into SQLite and, subsequently, rendering a pie chart with the data.
SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers. Even though it is not customary to use SQLite with MongoDB apps, it is worth exploring as this database engine is self-contained and does not need a database server.
Source code: https://github.com/medhatelmasry/xpress-sqlite-chart
The following is assumed about your environment:
- Node.js & NPM are installed on your computer
- You have installed nodemon
- You already have some familiarity with Node.js
Initializing our node express app
Let us first create a skeleton template of a simple Node.js Express application. This application will focus of APIs, so there will be no need to use any view engines.
In a working directory, create a subdirectory named xpress-sqlite-chart for our new Node.js application with:
mkdir xpress-sqlite-chart
cd xpress-sqlite-chart
Let us initialize a new node app with:
npm init --yes
This creates a package.json that looks like this:
{"name": "xpress-sqlite-chart","version": "1.0.0","description": "","main": "index.js","scripts": {"test": "echo \"Error: no test specified\" && exit 1"},"keywords": [],"author": "","license": "ISC"}
Edit the package.json as follows:
1) Change the value for main: from index.js to server.js:
2) add another script >> "start": "node server.js"
The resulting package.json will look like this, with changes highlighted:
{"name": "xpress-sqlite-chart","version": "1.0.0","description": "","main": "server.js","scripts": {"start": "node server.js","test": "echo \"Error: no test specified\" && exit 1"},"keywords": [],"author": "","license": "ISC"}
We need to add some packages with the following npm command:
npm install express csvtojson sqlite3
The three above packages are self-explanatory for those familiar with Node.js.
Add folders config, data, database, public, and routes as shown below:
Sample data
Inside the data folder, create a text file named students.csv with the following content:
FirstName,LastName,School
Ann,Fay,MiningJoe,Sun,NursingBen,Ray,MiningZoe,Cox,BusinessSam,Ray,MiningDan,Ash,MedicinePat,Lee,ComputingKim,Day,NursingTim,Rex,ComputingRob,Ram,BusinessJan,Fry,MiningJim,Tex,NursingBen,Kid,BusinessMia,Chu,MedicineTed,Tao,ComputingAmy,Day,BusinessIan,Roy,NursingLiz,Kit,NursingMat,Tan,MedicineDeb,Roy,MedicineAna,Ray,MedicineLyn,Poe,ComputingAmy,Raj,NursingKim,Ash,MiningBec,Kid,NursingEva,Fry,ComputingEli,Lap,BusinessSam,Yim,NursingJoe,Hui,MiningLiz,Jin,NursingRic,Kuo,MedicinePam,Mak,ComputingStu,Day,BusinessTom,Gad,MedicineBob,Bee,ComputingJim,Ots,ComputingTom,Mag,MedicineHal,Doe,MiningRoy,Kim,MedicineVis,Cox,NursingKay,Aga,NursingReo,Hui,NursingBob,Roe,Mining
Building a basic Node.js Express application
In the config folder, add a file named index.js with the following content:
module.exports = {'database_name': 'school.db',// Setting port for server'port': process.env.PORT || 3000};
The above file serves as a configuration file that specifies the SQLite database name and the port number that will be used. As regards the port number, it takes the PORT environment variable, otherwise it defaults to 3000.
Add two files to the routes folder named index.js and students.js respectively.
The content of routes/index.js is as follows:
const express = require("express");const router = express.Router();router.get("/", (req, res, next) => {res.json({"message":"Ok"})});module.exports = router;
The above will display a JSON object {"message":"Ok"} when the use points his browser to endpoint /.
The content of routes/student.js is as follows:
const express = require("express");const router = express.Router();router.get("/students", (req, res, next) => {res.json([{"id":1,"FirstName":"Ann","LastName":"Fay","School":"Mining"},{"id":2,"FirstName":"Ben","LastName":"Ray","School":"Nursing"}])});module.exports = router;
The above code sets up an endpoint that displays a JSON array of some fixed sample data of students.
In the root folder of your application, create a file named server.js and add to it the following code:
const express = require("express")const app = express()const path = require('path');const config = require('./config');var bodyParser = require("body-parser");app.use(bodyParser.urlencoded({ extended: false }));app.use(bodyParser.json());var index = require("./routes/");var students = require("./routes/students");app.use(express.static(path.join(__dirname, 'public')));app.use("/", index);app.use("/api", students);// Start serverapp.listen(config.port, () => {console.log(`Server running on port ${config.port}`);});
What does the above code do?
- The first four lines load express, path and config objects.
- Next we load bodyParser so that we can extract json objects from the body of the response object
- objects index & students that point to /routes/index.js and /routes/students.js respectively.
- app.use(express.static(path.join(__dirname, 'public'))); sets a folder named public that will contain static files like *.css, *.js, *.html, images files, etc...
- We then set / to go to object index and route /api to go to object students
- Finally, we start our web server which listens on the port numbers specified in file config/index.js.
Start the node app by issuing the following command in a terminal window at the root of your application:
nodemon
If you point your browser http://localhost:3000, you will see the following:
Pointing your browser to http://localhost:3000/api/students will reveal the following:
We now have a skeleton web app that we can build out towards our final product.
Importing CSV data into SQLite
We will use the csvtojson package to import CSV data that resides in the data/students.csv file into SQLite.
Add a file named index.js to the database folder. Then, add the following code to database/index.js:
const sqlite3 = require('sqlite3').verbose()const path = require('path');const csv2json = require('csvtojson');const config = require('../config');const CSV_FILE = path.join(__dirname, "../data/students.csv")let db = new sqlite3.Database(config.database_name, (err) => {if (err) {// Cannot open databaseconsole.error(err.message)throw err} else {console.log('Connected to the SQLite database.')db.run(`CREATE TABLE students (id INTEGER PRIMARY KEY AUTOINCREMENT,FirstName VARCHAR(80),LastName VARCHAR(80),School VARCHAR(50))`,(err) => {if (err) {// Table already created} else {csv2json().fromFile(CSV_FILE).then(data => {var insert = 'INSERT INTO students (FirstName, LastName, School) VALUES (?,?,?)';for(const item of data) {db.run(insert, [item.FirstName,item.LastName,item.School]);}}).catch(err => {// log error if anyconsole.log(err);});// Table just created, creating some rows}});}});module.exports = db
What does the above code do?
- Packages for sqlite3 and csv2json are loaded to use SQLite and conversion from CSV to JSON libraries
- We also load the path library so that we can use the path.join() function
- The configuration file in config/index.js is referenced enabling us to access the database name
- The location of our CSV file is defined in variable CSV_FILE
- the command 'new sqlite3.Database()' creates a database file named school.db, if it does not already exist
- Database table named students is created if it does not already exist
- Function csv2json().fromFile() loads contents of the CSV file into a variable named data. We then iterate through the data collection to execute a SQL INSERT statement for each item in the collection
Reading data from students table in school.db SQLite database
In our routes/students.js API file, let's read data from our students database table. Add the following to the top of the routes/students.js file:
const db = require("../database");
The above allows us to access the db database object defined in database/index.js.
Instead of hardcoding JSON data in the "/students" GET route, we can read data from the database, Therefore, replace the the router.get("/students .... code in router/students.js with the following:
router.get("/students", (req, res, next) => {
var sql = "select * from students"var params = []db.all(sql, params, (err, rows) => {if (err) {res.status(400).json({"error":err.message});return;}res.json({"message":"success","data":rows})});});
We execute the SQL statement "SELECT * FROM students" to read contents of students table and return the data as an array of JSON objects.
Testing our API
Let's test our application. When you point your browser to http://localhost:3000/api/students, you will see a list of all the students in the database:
Also note the creation of a SQLite database file named school.db in the root of your web app.
Creating a chart of students by school
We will use the freely available Google Charts library to render a pie-chart illustrating number of students in every school. The starting point is to create a aggregation of the data. This will be done by adding the following additional /chart route to routes/students.js just under the existing /students route:
router.get("/chart", (req, res, next) => {sql = "SELECT School as school, COUNT(*) as count";sql += " FROM students GROUP BY School";var params = []db.all(sql, params, (err, rows) => {if (err) {res.status(400).json({"error":err.message});return;}res.json(rows)});});
Point your browser to http://localhost:3000/api/chart and you will see a list of aggregate data as follows:
We can render this aggregate data into a pie chart. In the public folder, create a simple HTML file named chart.html with the following content:
<!DOCTYPE html><html lang="en"><head><title>Pie chart of students by school</title><script src="https://code.jquery.com/jquery-3.6.0.min.js"></script><script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script></head><body><div id="chart_div"></div><script type="text/javascript">google.charts.load('current', {packages: ['corechart', 'bar']});google.charts.setOnLoadCallback(LoadData);function LoadData() {$.ajax({url: '/api/chart',dataType: "json",type: "GET",error: function (xhr, status, error) {toastr.error(xhr.responseText);},success: function (data) {PopulationChart(data);return false;}});return false;}function PopulationChart(data) {var dataArray = [['school', 'count']];$.each(data, function (i, item) {dataArray.push([item.school, item.count]);});var data = google.visualization.arrayToDataTable(dataArray);var options = {title: 'Students by School',width: 700,height: 800};var divElement = document.getElementById('chart_div');var chart = new google.visualization.PieChart(divElement);chart.draw(data, options);return false;}</script></body></html>
Note that we are making a ajax get call to endpoint /api/chart in the $.ajax() jQuery call. Also, note that new google.visualization.PieChart() generates a pie chart. With Google Charts, you can generate a multitude of chart types including bar, geo, column, area, etc...
When you point your browser to http://localhost:3000/chart.html you will see the following pie-chart.
No surprise, 27.9% of students are doing nursing during this age of covid.
CORS
In public/chart.html, change the URL in "url: '/api/chart', to "http://localhost:3000/api/chart" so that we can run the chart.html page outside of the current web server.
From within the file system of desktop computer, double click on file public/chart.html. The page loads in a browser but appears blank. Visit the browser developer tools console tab. You will see an error that resembles the following:
This suggests that we have a CORS issue. What is CORS?
Cross-origin resource sharing (CORS) is a browser mechanism which enables controlled access to resources located outside of a given domain.
We need to enable CORS in our server app. This is done by adding the following code to server.js just before app.use("/", index); around line 15:
// enable CORSapp.use(function(req, res, next) {res.setHeader("Access-Control-Allow-Origin", "*");res.setHeader('Access-Control-Allow-Methods', 'GET, POST, OPTIONS, PUT, PATCH, DELETE');res.setHeader("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");next();});
Now try double-clicking on public/chart.html (or refreshing the page) and you should see that it is working fine.
What have we learned in this tutorial?
- initializing a node.js Express web app
- building a basic Node.js Express web app
- import a CSV file into SQLite database
- work with SQLite in a Node.js application
- creating a simple pie chart from aggregate data with Google Charts
- enabling CORS in a Node.js Express application
Thanks for coming this far in this article. In my next article I will show you how you can take this app and turn it into a Docker image that can be pushed to Docker-Hub and, subsequently, hosted on Microsoft Azure.
No comments:
Post a Comment