Manipulate file with SQL

AlaSQL is an open source SQL database for JavaScript with a strong focus on query speed and data source flexibility for both relational data and schemaless data. It works in the web browser, Node.js, and mobile apps.

This library is perfect for:

• Fast in-memory SQL data processing for BI and ERP applications on fat clients
• Easy ETL and options for persistence by data import / manipulation / export of several formats
• All major browsers, Node.js, and mobile applications

Installation

npm i -S alasql

Functions used in this tutorial

npm i -S tablify

async function printTable(json) {
  const tablify = require('tablify').tablify
  print(tablify(json));
}

Manipulate Data with SQL

In the example below, we load and convert a CSV file to JSON. Then we can start working with it.

Tip: We cast the pop_est String field into Number

const alasql = require("alasql");

// Load Data
const r = await fetch('https://cdn.jsdelivr.net/npm/world-atlas@1/world/110m.tsv')
const tsv = await r.text();
const json = csvJSON(tsv, '\t'); // convert CSV to JSON

// Compute total of population by continent
const data = alasql(`SELECT continent, SUM(CAST(pop_est as NUMBER)) AS total_pop_est FROM ? GROUP BY continent order by total_pop_est desc`, [json]);
printTable(data)

Making Chart

Once the data is ready, all that remains is to convert it to the "series" format expected by the Graph lib

const alasql = require("alasql");
const r = await fetch('https://cdn.jsdelivr.net/npm/world-atlas@1/world/110m.tsv')
const tsv = await r.text();
const json = csvJSON(tsv, '\t'); // convert CSV to JSON

const data = alasql("SELECT continent, SUM(CAST(pop_est as NUMBER)) AS total_pop_est FROM ? GROUP BY continent order by total_pop_est desc", [json]);

const continents = Array.from(new Set(data.map(e=>e.continent)));
const series = toSeries({
  data:data, 
  x:"continent", 
  y:"total_pop_est", 
  category:"continent"
})

barChart({
  horizontal:true,
  series: series,
  categories: continents
});

Go further with AlaSQL

👉 Ala SQL Docs