AlaSQL

JavaScript SQL database for browser and Node.js. Handles both traditional r...

DatabaseSQLBrowser storageData analysis
AlaSQL/alasqlalasql.orgalasql

README

Please use version 1.x as prior versions has a security flaw if you use user generated data to concat your SQL strings instead of providing them as a parameters to AlaSQL.


_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._

CI-test NPM downloads OPEN open source software Release Stars Average time to resolve an issue Coverage CII Best Practices undefined code style: prettier FOSSA Status




AlaSQL


AlaSQL logo




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


  1. ``` js
  2. /* create SQL Table and add data */
  3. alasql("CREATE TABLE cities (city string, pop number)");

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

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

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

Live Demo

Array of Objects


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

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

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

Live Demo

Spreadsheet


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


Bulk Data Load


  1. ``` js
  2. alasql("CREATE TABLE example1 (a INT, b INT)");

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

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

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

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


__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



  1. ``` sh
  2. yarn add alasql                # yarn

  4. npm install alasql             # npm

  6. npm install -g alasql          # global installation for command line tools
  7. ```

For the browser: include alasql.min.js


  1. ``` html
  2. <script src="https://cdn.jsdelivr.net/npm/alasql@1.7"></script>
  3. ```


Getting started


See the "Getting started" section of the wiki

More advanced topics are covered in other wiki sections like "Data manipulation" and in questions on Stack Overflow

Other links:

Documentation: Github wiki

Library CDN: jsDelivr.com

Feedback: Open an issue

* Try online: Playground

Website: alasql.org


Please note


All contributions are extremely welcome and greatly appreciated(!) -
The project has never received any funding and is based on unpaid voluntary work: We really (really) love pull requests

The AlaSQL project depends on your contribution of code and may have [bugs](https://github.com/agershun/alasql/labels/%21%20Bug). So please, submit any bugs and suggestions [as an issue](https://github.com/agershun/alasql/issues/new).

Please check out the limitations of the library.

Performance


AlaSQL is designed for speed and includes some of the classic SQL engine optimizations:

Queries are cached as compiled functions
Joined tables are pre-indexed
WHERE expressions are pre-filtered for joins

See more performance-related info on the wiki

Features you might like



Traditional SQL


Use "good old" SQL on your data with multiple levels of: JOIN, VIEW, GROUP BY, UNION, PRIMARY KEY, ANY, ALL, IN, ROLLUP(), CUBE(), GROUPING SETS(), CROSS APPLY, OUTER APPLY, WITH SELECT, and subqueries. The wiki lists supported SQL statements and keywords.



User-Defined Functions in your SQL


You can use all benefits of SQL and JavaScript together by defining your own custom functions. Just add new functions to the alasql.fn object:


  1. ``` js
  2. alasql.fn.myfn = function(a,b) {
  3.     return a*b+1;
  4. };
  5. var res = alasql('SELECT myfn(a,b) FROM one');
  6. ```

You can also define your own aggregator functions (like your own SUM(...)). See more in the wiki


Compiled statements and functions


  1. ``` js
  2. var ins = alasql.compile('INSERT INTO one VALUES (?,?)');
  3. ins(1,10);
  4. ins(2,20);
  5. ```

See more in the wiki


SELECT against your JavaScript data


Group your JavaScript array of objects by field and count number of records in each group:

  1. ``` js
  2. var data = [{a:1,b:1,c:1},{a:1,b:2,c:1},{a:1,b:3,c:1}, {a:2,b:1,c:1}];
  3. var res = alasql('SELECT a, COUNT(*) AS b FROM ? GROUP BY a', [data] );
  4. ```

See more ideas for creative data manipulation in the wiki



JavaScript Sugar


AlaSQL extends "good old" SQL to make it closer to JavaScript. The "sugar" includes:

Write Json objects - {a:'1',b:@['1','2','3']}

Access object properties - obj->property->subproperty
Access object and arrays elements - obj->(a*1)
Access JavaScript functions - obj->valueOf()
Format query output with SELECT VALUE, ROW, COLUMN, MATRIX
ES5 multiline SQL with `var SQL = function(){/SELECT 'MY MULTILINE SQL'/}` and pass instead of SQL string (will not work if you compress your code)


Read and write Excel and raw data files


You can import from and export to CSV, TAB, TXT, and JSON files. File extensions can be omitted. Calls to files will always be asynchronous so multi-file queries should be chained:

  1. ``` js
  2. var tabFile = 'mydata.tab';

  4. alasql.promise([
  5.     "SELECT * FROM txt('MyFile.log') WHERE [0] LIKE 'M%'", // parameter-less query
  6.     [ "SELECT * FROM tab(?) ORDER BY [1]", [tabFile] ],    // [query, array of params]
  7.     "SELECT [3] AS city,[4] AS population FROM csv('./data/cities')",
  8.     "SELECT * FROM json('../config/myJsonfile')"
  9. ]).then(function(results){
  10.     console.log(results);
  11. }).catch(console.error);
  12. ```


Read SQLite database files


AlaSQL can read (but not write) SQLite data files using SQL.js library:

  1. ``` html
  2. <script src="alasql.js"></script>
  3. <script src="sql.js"></script>
  4. <script>
  5.     alasql([
  6.         'ATTACH SQLITE DATABASE Chinook("Chinook_Sqlite.sqlite")',
  7.         'USE Chinook',
  8.         'SELECT * FROM Genre'
  9.     ]).then(function(res){
  10.         console.log("Genres:",res.pop());
  11.     });
  12. </script>
  13. ```

sql.js calls will always be asynchronous.


AlaSQL works in the console - CLI


The node module ships with an alasql command-line tool:

  1. ``` sh
  2. $ npm install -g alasql ## install the module globally

  4. $ alasql -h ## shows usage information

  6. $ alasql "SET @data = @[{a:'1',b:?},{a:'2',b:?}]; SELECT a, b FROM @data;" 10 20
  7. [ 1, [ { a: 1, b: 10 }, { a: 2, b: 20 } ] ]

  9. $ alasql "VALUE OF SELECT COUNT(*) AS abc FROM TXT('README.md') WHERE LENGTH([0]) > ?" 140
  10. // Number of lines with more than 140 characters in README.md
  11. ```

More examples are included in the wiki


Features you might love


AlaSQL ♥ D3.js


AlaSQL plays nice with d3.js and gives you a convenient way to integrate a specific subset of your data with the visual powers of D3. See more about D3.js and AlaSQL in the wiki

AlaSQL ♥ Excel


AlaSQL can export data to both Excel 2003 (.xls) and Excel 2007 (.xlsx) formats with coloring of cells and other Excel formatting functions.

AlaSQL ♥ Meteor


Meteor is amazing. You can query directly on your Meteor collections with SQL - simple and easy. See more about Meteor and AlaSQL in the wiki

AlaSQL ♥ Angular.js


Angular is great. In addition to normal data manipulation, AlaSQL works like a charm for exporting your present scope to Excel. See more about Angular and AlaSQL in the wiki

AlaSQL ♥ Google Maps


Pinpointing data on a map should be easy. AlaSQL is great to prepare source data for Google Maps from, for example, Excel or CSV, making it one unit of work for fetching and identifying what's relevant. See more about Google Maps and AlaSQL in the wiki

AlaSQL ♥ Google Spreadsheets


AlaSQL can query data directly from a Google spreadsheet. A good "partnership" for easy editing and powerful data manipulation. See more about Google Spreadsheets and AlaSQL in the wiki

Miss a feature?

Take charge and add your idea or vote for your favorite feature to be implemented:
Feature Requests


Limitations


Please be aware that AlaSQL has bugs. Beside having some bugs, there are a number of limitations:

0. AlaSQL has a (long) list of keywords that must be escaped if used for column names. When selecting a field named key please write SELECT key FROM ... instead. This is also the case for words like value , read , count , by , top , path , deleted , work and offset . Please consult the full list of keywords.

0. It is OK to SELECT 1000000 records or to JOIN two tables with 10000 records in each (You can use streaming functions to work with longer datasources - see test/test143.js) but be aware that the workload is multiplied soSELECTing from more than 8 tables with just 100 rows in each will show bad performance. This is one of our top priorities to make better.

0. Limited functionality for transactions (supports only for localStorage) - Sorry, transactions are limited, because AlaSQL switched to more complex approach for handling PRIMARY KEYs / FOREIGN KEYs. Transactions will be fully turned on again in a future version.

0. A (FULL) OUTER JOIN and RIGHT JOIN of more than 2 tables will not produce expected results. INNER JOIN and LEFT JOIN are OK.

0. Please use aliases when you want fields with the same name from different tables (SELECT a.id AS a_id, b.id AS b_id FROM ?).

0. At the moment AlaSQL does not work with JSZip 3.0.0 - please use version 2.x.

0. JOINing a sub-SELECT does not work. Please use a with structure (Example here) or fetch the sub-SELECT to a variable and pass it as an argument (Example here).

0. AlaSQL uses the FileSaver.js library for saving files locally from the browser. Please be aware that it does not save files in Safari 8.0.

There are probably many others. Please help us fix them by submitting an issue. Thank you!


How To


Use AlaSQL to convert data from CSV to Excel


ETL example:

  1. ``` js
  2. alasql([
  3.     'CREATE TABLE IF NOT EXISTS geo.country',
  4.     'SELECT * INTO geo.country FROM CSV("country.csv",{headers:true})',
  5.     'SELECT * INTO XLSX("asia") FROM geo.country WHERE continent_name = "Asia"'
  6. ]).then(function(res){
  7.     // results from the file asia.xlsx
  8. });
  9. ```

Use AlaSQL as a Web Worker


AlaSQL can run in a Web Worker. Please be aware that all interaction with AlaSQL when running must be async.

From the browser thread, the browser build alasql-worker.min.js automagically uses Web Workers:

  1. ``` html
  2. <script src="alasql-worker.min.js"></script>
  3. <script>
  4. var arr = [{a:1},{a:2},{a:1}];
  6. alasql([['SELECT * FROM ?',[arr]]]).then(function(data){
  7.     console.log(data);
  8. });
  9. </script>
  10. ```

Live Demo.

The standard build alasql.min.js will use Web Workers if alasql.worker() is called:

  1. ``` html
  2. <script src="alasql.min.js"></script>
  3. <script>
  4. alasql.worker();
  5. alasql(['SELECT VALUE 10']).then(function(res){
  6.     console.log(res);
  7. }).catch(console.error);
  8. </script>
  9. ```

Live Demo.

From a Web Worker, you can import alasql.min.js with importScripts:

  1. ``` js
  2. importScripts('alasql.min.js');
  3. ```

Webpack, Browserify, Vue and React (Native)


When targeting the browser, several code bundlers like Webpack and Browserify will pick up modules you might not want.

Here's a list of modules that AlaSQL may require in certain environments or for certain features:

Node.js
  fs
  net
  tls
  request
  path
React Native
  react-native
  react-native-fs
  react-native-fetch-blob
Vertx
  vertx
Agonostic
  XLSX/XLS support
    cptable
    jszip
    xlsx
    cpexcel
  es6-promise

Webpack


There are several ways to handle AlaSQL with Webpack:

IgnorePlugin

Ideal when you want to control which modules you want to import.

  1. ``` js
  2. var IgnorePlugin =  require("webpack").IgnorePlugin;

  4. module.exports = {
  5.   ...
  6.   // Will ignore the modules fs, path, xlsx, request, vertx, and react-native modules
  7.   plugins:[new IgnorePlugin(/(^fs$|cptable|jszip|xlsx|^es6-promise$|^net$|^tls$|^forever-agent$|^tough-cookie$|cpexcel|^path$|^request$|react-native|^vertx$)/)]
  8. };
  9. ```

module.noParse

As of AlaSQL 0.3.5, you can simply tell Webpack not to parse AlaSQL, which avoids all the dynamic require warnings and avoids using eval/clashing with CSP with script-loader.
Read the Webpack docs about noParse

  1. ``` js
  2. ...
  3. //Don't parse alasql
  4. {module:noParse:[/alasql/]}
  5. ```


script-loader

If both of the solutions above fail to meet your requirements, you can load AlaSQL with script-loader.

  1. ``` js
  2. //Load alasql in the global scope with script-loader
  3. import "script!alasql"
  4. ```

This can cause issues if you have a CSP that doesn't allow eval.

Browserify


Read up on excluding, ignoring, and shimming

Example (using excluding)

  1. ``` js
  2. var browserify = require("browserify");
  3. var b = browserify("./main.js").bundle();
  4. //Will ignore the modules fs, path, xlsx
  5. ["fs","path","xlsx",  ... ].forEach(ignore => { b.ignore(ignore) });
  6. ```

Vue


For some frameworks (lige Vue) alasql cant access XLSX by it self. We recommend handeling it by including AlaSQL the following way:

  1. ```import alasql from 'alasql';
  2. import XLSX from 'xlsx';
  3. alasql.utils.isBrowserify = false;
  4. alasql.utils.global.XLSX = XLSX;
  5. ```

jQuery


Please remember to send the original event, and not the jQuery event, for elements. (Use event.originalEvent instead of myEvent)

JSON-object


You can use JSON objects in your databases (do not forget use == and !== operators for deep comparison of objects):

  1. ```sql

  3. alasql> SELECT VALUE {a:'1',b:'2'}

  5. {a:1,b:2}

  7. alasql> SELECT VALUE {a:'1',b:'2'} == {a:'1',b:'2'}

  9. true

  11. alasql> SELECT VALUE {a:'1',b:'2'}->b

  13. 2

  15. alasql> SELECT VALUE {a:'1',b:(2*2)}->b

  17. 4

  19. ```

Try AlaSQL JSON objects in Console sample


Experimental


_Useful stuff, but there might be dragons_

Graphs


AlaSQL is a multi-paradigm database with support for graphs that can be searched or manipulated.

  1. ``` js
  2. // Who loves lovers of Alice?
  3. var res = alasql('SEARCH / ANY(>> >> #Alice) name');
  4. console.log(res) // ['Olga','Helen']
  5. ```

See more in the wiki

localStorage and DOM-storage


You can use browser localStorage and DOM-storage as a data storage. Here is a sample:

  1. ``` js
  2. alasql('CREATE localStorage DATABASE IF NOT EXISTS Atlas');
  3. alasql('ATTACH localStorage DATABASE Atlas AS MyAtlas');
  4. alasql('CREATE TABLE IF NOT EXISTS MyAtlas.City (city string, population number)');
  5. alasql('SELECT * INTO MyAtlas.City FROM ?',[ [
  6.         {city:'Vienna', population:1731000},
  7.         {city:'Budapest', population:1728000}
  8. ] ]);
  9. var res = alasql('SELECT * FROM MyAtlas.City');
  10. ```

Try this sample in jsFiddle. Run this sample
two or three times, and AlaSQL store more and more data in localStorage. Here, "Atlas" is
the name of localStorage database, where "MyAtlas" is a memory AlaSQL database.

You can use localStorage in two modes: SET AUTOCOMMIT ON to immediate save data
to localStorage after each statement or SET AUTOCOMMIT OFF. In this case, you need
to use COMMIT statement to save all data from in-memory mirror to localStorage.

Plugins


AlaSQL supports plugins. To install a plugin you need to use the REQUIRE statement. See more in the wiki

Alaserver - simple database server


Yes, you can even use AlaSQL as a very simple server for tests.

To run enter the command:

  1. ``` sh
  2. $ alaserver
  3. ```

then open in your browser

Warning: Alaserver is not multi-threaded, not concurrent, and not secured.


Tests


Regression tests


AlaSQL currently has over 1200 regression tests, but they only cover Coverage
of the codebase.

AlaSQL uses mocha for regression tests. Install mocha and run

  1. ``` sh
  2. $ npm test
  3. ```

or open test/index.html for in-browser tests (Please serve via localhost with, for example,http-server).

Tests with AlaSQL ASSERT from SQL


You can use AlaSQL's ASSERT operator to test the results of previous operation:

  1. ```sql
  2. CREATE TABLE one (a INT);             ASSERT 1;
  3. INSERT INTO one VALUES (1),(2),(3);   ASSERT 3;
  4. SELECT * FROM one ORDER BY a DESC;    ASSERT [{a:3},{a:2},{a:1}];
  5. ```

SQLLOGICTEST


AlaSQL uses SQLLOGICTEST to test its compatibility with SQL-99. The tests include about 2 million queries and statements.

The testruns can be found in the testlog.



Bleeding Edge


If you want to try the most recent development version of the library please download this file or visit the testbench to play around in the browser console.


License


MIT - see MIT licence information


Main contributors


Andrey Gershun
Mathias Rangel Wulff

AlaSQL is an OPEN Open Source Project. This means that:

Individuals making significant and valuable contributions are given commit-access to the project to contribute as they see fit. This project is more like an open wiki than a standard guarded open source project.


We appreciate any and all contributions we can get. If you feel like contributing, have a look at CONTRIBUTING.md


Credits


Many thanks to:

Zach Carter for Jison parser-generator
Andrew Kent for JS SQL Parser
Eli Grey for FileSaver.js
SheetJS for JS XLSX Library

and other people for useful tools, which make our work much easier.

Related projects that have inspired us


AlaX - Export to Excel with colors and formats
WebSQLShim - WebSQL shim over IndexedDB (work in progress)
AlaMDX - JavaScript MDX OLAP library (work in progress)
Other similar projects - list of databases on JavaScript



AlaSQL logo
© 2014-2018, Andrey Gershun (agershun@gmail.com) & Mathias Rangel Wulff (m@rawu.dk)