JS meets SQL. Say hi to AlaSQL!

At Geoblink, we often find ourselves moving a lot of data from our database to the frontend, to display in useful charts and graphs for our users. Although restructuring data from the backend is usually necessary, it is not especially challenging, as the queries to the database are crafted to return the data how we need it for each purpose. For these cases, Lodash is more than enough to filter and fit the data to needs of the front-end.  But, what happens when we do not query the data ourselves? Sometimes, when using third-party APIs, the data may not be structured how we want it. We need a way to organize this data quickly and easily.

Cue in AlaSQL. AlaSQL gives us the power of SQL queries in Javascript, in a fast, in-memory SQL database. With this library, we can store records in a relational table format, and query the data using SQL syntax. This is extremely useful for counting or grouping a large amount of records in a flash, without having to overthink how to manipulate the JSONs to achieve the required structure.

AlaSQL is a very flexible solution, allowing multiple options when it comes to both the source and the destination of the data. You can load data to and from CSV, XSLX, TXT, Javascript objects and more right out of the box, and it works on all major browsers, Node, and even iOS and Android devices.

There are several ways you can work with data in AlaSQL. You can always work as you would expect with a normal SQL database. Keep in mind this is a full-fledged SQL database for Javascript, so be sure to use proper indexes to get good performance, and to be careful with database inputs to avoid SQL injection.

alasql("CREATE TABLE stores (name string, sales number)");
alasql("INSERT INTO stores VALUES('Sol', 10000), ('Gran Vía', 221345), ('Preciados', 234562), (‘Callao', 123098)");
var res = alasql("SELECT * FROM store WHERE sales < 150000 ORDER BY sales DESC");

But you can also run queries directly against Javascript data.

var stores = [
  {
    name: ‘Sol’,
    sales: 100000
  }, 
  {
    name: ‘Gran Vía’,
    sales: 221345
  },
  {
    name: ‘Preciados’,
    sales: 234562
  },
  {
    name, ‘Callao’,
    sales: 123098
  }
]
var res = alasql("SELECT * FROM ? WHERE sales < 150000 ORDER BY sales DESC", [stores]);

Let’s take a look at how AlaSQL can make data manipulation easy, fast, and maintainable. In the following scenario, we want to structure an array of objects containing stores, each belonging to a different category, and having different amount of sales.

const data = [
  { 
    id: 1,
    category: 'Sports',
    sales: 10000
  },
  {
    id: 2,
    category: 'Sports',
    sales: 15000
  },
  {
    id: 3,
    category: 'Toys',
    sales: 1000
  },
  {
    id: 1,
    category: 'Fashion',
    sales: 20000
  },
  {
    id: 1,
    category: 'Unknown',
    sales: 20000
  }
]

We want to group these stores by category, ignoring ‘Unknown’, and obtain the total sales per category, as well as the percentage of those sales with respect to the global total. Performing this task manipulating the original object can be challenging. Even with libraries such as Underscore.js or Lodash, we would need to perform at least one loop to compute the total, and then a more complex reduction with if statements to group stores and compute sales. This kind of operation however, is relatively easy with an SQL query, so we can take advantage of this with AlaSQL.

 

const query = `
  WITH total_sales AS (
    SELECT
      sum(sales) as total
    FROM ?
  )
  SELECT
    d.category,
    SUM(d.sales / t.total) as sales_percentage,
    SUM(d.sales) as sales
  FROM
    ? d,
    total_sales t
  WHERE
    d.category != ‘Unknown’
  GROUP BY
    d.category, t.total`

const res = alasql(query, [data, data])

 

The result is a piece of code that is fast to come up with, and easily understandable.

This of course is just scratching the surface of what you can do with the library. Beyond this basic introduction, it is possible to create you own functions, create compiled statements and functions and much more. Also particularly interesting is how well it integrates with popular libraries and services such as d3.js and Google Maps.

And last but not least: AlaSQL is an open source tool backed by a great community and fantastic documentation. Be sure to check out their page here  for a thorough guide on getting started and some great ideas on how to manipulate your data with AlaSQL.

 

 By Ignacio 'Guli' Moreno