alasql

AlaSQL.js – JavaScript SQL database for browser and Node.js. Handles both traditional relational tables and nested JSON data (NoSQL). Export, store, and import data from localStorage, IndexedDB, or Excel. http://alasql.org

AlaSQL is an open source project used on more than two million page views per month – and we appreciate any and all contributions we can get. Please help out.

Have a question? Ask on Stack Overflow using the “alasql” tag.

AlaSQL

AlaSQL – à la SQL ) [ælæ ɛskju:ɛl] – 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 designed 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

We focus on speed by taking advantage of the dynamic nature of JavaScript when building up queries. Real-world solutions demand flexibility regarding where data comes from and where it is to be stored. We focus on flexibility by making sure you can import/export and query directly on data stored in Excel (both .xls and .xlsx), CSV, JSON, TAB, IndexedDB, LocalStorage, and SQLite files.

The library adds the comfort of a full database engine to your JavaScript app. No, really – it’s working towards a full database engine complying with most of the SQL-99 language, spiced up with additional syntax for NoSQL (schema-less) data and graph networks.

Traditional SQL Table

/* create SQL Table and add data */
alasql("CREATE TABLE cities (city string, pop number)");

alasql("INSERT INTO cities VALUES ('Paris',2249975),('Berlin',3517424),('Madrid',3041579)");

/* execute query */
var res = alasql("SELECT * FROM cities WHERE pop < 3500000 ORDER BY pop DESC");

// res = [ { "city": "Madrid", "pop": 3041579 }, { "city": "Paris", "pop": 2249975 } ]

Live Demo

Array of Objects

var data = [ {a: 1, b: 10}, {a: 2, b: 20}, {a: 1, b: 30} ];

var res = alasql('SELECT a, SUM(b) AS b FROM ? GROUP BY a',[data]);

// res = [ { "a": 1, "b": 40},{ "a": 2, "b": 20 } ]

Live Demo

Spreadsheet

// file is read asynchronously (Promise returned when SQL given as array)
alasql(['SELECT * FROM XLS("./data/mydata") WHERE lastname LIKE "A%" and city = "London" GROUP BY name '])
    .then(function(res){
        console.log(res); // output depends on mydata.xls
    }).catch(function(err){
        console.log('Does the file exist? There was an error:', err);
    });

Bulk Data Load

alasql("CREATE TABLE example1 (a INT, b INT)");

// alasql's data store for a table can be assigned directly
alasql.tables.example1.data = [
    {a:2,b:6},
    {a:3,b:4}
];

// ... or manipulated with normal SQL
alasql("INSERT INTO example1 VALUES (1,5)");

var res = alasql("SELECT * FROM example1 ORDER BY b DESC");

console.log(res); // [{a:2,b:6},{a:1,b:5},{a:3,b:4}

If you are familiar with SQL it should come as no surprise that proper use of indexes on your tables is essential to get good performance.

Installation

npm install --save alasql      # node
bower install --save alasql    # bower
npm install -g alasql          # global installation for command line tools

For the browser: include alasql.min.js

<script src="https://cdn.jsdelivr.net/npm/alasql@0.4"></script>

Subscribe to the Newsletter

Get our latest news,tutorials,guides,tips & deals delivered to your inbox.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

shares