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.

Got a question? Ask onStack Overflow and tag with "alasql".

AlaSQL -(à laSQL ) [æ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 your browser, Node.js, and Cordova.

This library is designed for:

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

We focus onspeed 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 canimport/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 withmost of the SQL-99 spiced up with an additional syntax for handling NoSQL (schema-less) data and graph networks.

// A) Traditional SQLalasql("CREATE TABLE cities (city string, population number)");alasql("INSERT INTO cities VALUES ('Rome',2863223),('Paris',2249975),('Berlin',3517424),('Madrid',3041579)");varres=alasql("SELECT * FROM cities WHERE population < 3500000 ORDER BY population DESC");console.log(res);/*[  {    "city": "Madrid",    "population": 3041579  },  {    "city": "Rome",    "population": 2863223  },  {    "city": "Paris",    "population": 2249975  }]*/

// B) Select from array of objectsvardata=[{a:1,b:10},{a:2,b:20},{a:1,b:30}];varres=alasql('SELECT a, SUM(b) AS b FROM ? GROUP BY a',[data]);console.log(res);// [{"a":1,"b":40},{"a":2,"b":20}]

// C) Read from file must be async (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);});

// D) Cheat and load your data directlyalasql("CREATE TABLE example1 (a INT, b INT)");alasql.tables.example1.data=[// Insert data directly from JavaScript object...{a:2,b:6},{a:3,b:4}];alasql("INSERT INTO example1 VALUES (1,5)");// ...or insert data with normal SQLvarres=alasql("SELECT * FROM example1 ORDER BY b DESC");console.log(res);// [{a:2,b:6},{a:1,b:5},{a:3,b:4}]

jsFiddle withexample A) andexample B)

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.

npm install --save alasql# nodebower install --save alasql# bowerimport alasql from'alasql';# meteornpm install -g alasql# command line

For the browser: includealasql.min.js

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

The wiki has a great section onhow to get started

When you feel you've gotten a grip, you can check out the wiki section aboutdata manipulation or get inspired by thelist of Q&As

• Documentation:Github wiki

• Library CDN:jsDelivr.com

• Feedback:Open an issue

• Try online:Playground

• Website:alasql.org

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

AlaSQL project is very young and still in an active development phase, therefore itmay havebugs.Please, submit any bugs and suggestionsas an issue.

AlaSQL usesSemantic Versioning so please note that the major version is zero (0.y.z) and the API can not be considered 100% stable. Consider this before using the library in production and please check out thelimitations of the library

AlaSQL is very focused on speed, and we make sure to use all the tricks we can find to make JavaScript spit out your results as quickly as possible. For example:

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

The results are good. Check out AlaSQL vs. other JavaScript SQL databases:

• 3x speedcompared to SQL.js selecting withSUM,JOIN, andGROUP BY.

• 1x speedcompared to WebSQL selecting withSUM,JOIN, andGROUP BY (in-memory operations for WebSQL - seethis discussion)

• 2x speedcompared to Linq forGROUP BY on 1,048,576 rows

Please remember to set indexes on your tables to speed up your queries.Have a look here andSQL Index page if you are unfamiliar with this concept (this is a part ofSQL Tutorial).

See morespeed related info on the wiki

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. See the wiki tocompare supported features with SQL standards.

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:

alasql.fn.myfn=function(a,b){returna*b+1;}varres=alasql('SELECT myfn(a,b) FROM one');

You can also define your own aggregator functions (like your ownSUM(...)). See morein the wiki

varins=alasql.compile('INSERT INTO one VALUES (?,?)');ins(1,10);ins(2,20);

See morein the wiki

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

vardata=[{a:1,b:1,c:1},{a:1,b:2,c:1},{a:1,b:3,c:1},{a:2,b:1,c:1}];varres=alasql('SELECT a, COUNT(*) AS b FROM ? GROUP BY a',[data]);console.log(res);

See more ideas for creative data manipulationin the wiki

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

• Write Json objects -{a:'1',b:@['1','2','3']}
• Acesss object propertires -obj->property->subproperty
• Access Ooject and arrays elements -obj->(a*1)
• Access JavaScript functions -obj->valueOf()
• Format query output withSELECT VALUE, ROW, COLUMN, MATRIX
• ES5 multiline SQL withvar SQL = function(){/*select 'MY MULTILINE SQL'*/} and pass instead of SQL string (will not work if you compress your code)

You can import from and export to CSV, TAB, TXT, and JSON files. File extensions can be omitted. Calls to files will always beasync so the approach is to chain the queries if you have more than one:

vartabFile='mydata.tab'alasql.promise(["select * from txt('MyFile.log') where [0] like 'M%'",["select * from tab(?) order by [1]",[tabFile]],// note how to pass parameter when promises are chained"select [3] as city,[4] as population from csv('./data/cities')","select * from json('../config/myJsonfile')"]).then(function(results){console.log(results)}).catch(console.error)

AlaSQL can read (but not write) SQLite data files if you include theSQL.js library:

<scriptsrc="alasql.js"></script><scriptsrc="sql.js"></script><script>alasql(['ATTACH SQLITE DATABASE Chinook("Chinook_Sqlite.sqlite")','USE Chinook','SELECT * FROM Genre']).then(function(res){console.log("Genres:",res.pop());});</script>

sql.js calls will always be async.

After globally installing AlaSQLnpm install alasql -g you can access AlaSQL via the commandline

> alasql"SET @data = @[{a:'1',b:?},{a:'2',b:?}]; SELECT a, b FROM @data;" 10 20[ 1, [ { a: 1, b: 10 }, { a: 2, b: 20 } ] ]> alasql"VALUE OF SELECT COUNT(*) as abc FROM TXT('README.md') WHERE LENGTH([0]) > ?" 140// Number of lines with more than 140 charactersin README.md

See morein the wiki

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 aboutD3.js and AlaSQL in the wiki

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

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

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

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 aboutGoogle Maps and AlaSQL in the wiki

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

Take charge andadd your idea orvote for your favorite feature to be implemented:

Please be aware that AlaSQL hasbugs. 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 namedkey please writeSELECT `key` FROM ... instead. This is also the case for words like`value`,`read`,`count`,`by`,`top`,`path`,`deleted`,`work` and`offset`. Please consult thefull list of keywords.

1. It is OK toSELECT 1000000 records or toJOIN two tables with 10000 records in each (You can use streaming functions to work with longer datasources - seetest/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.

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

3. A(FULL) OUTER JOIN andRIGHT JOIN of more than 2 tables will not produce expected results.INNER JOIN andLEFT JOIN are OK.

4. 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 ?).

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

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

7. AlaSQL uses theFileSaver.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 bysubmitting an issue. Thank you!

ETL example:

alasql(['CREATE TABLE IF NOT EXISTS geo.country','SELECT * INTO geo.country FROM CSV("country.csv",{headers:true})','SELECT * INTO XLSX("asia") FROM geo.country WHERE continent_name = "Asia"').then(function(res){          ...// results from the file asia.xlsx});

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

In the browser you can includealasql-worker.min.js instead ofalasql.min.js and AlaSQL will figure out the rest:

<scriptsrc="alasql-worker.min.js"></script><script>vararr=[{a:1},{a:2},{a:1}];alasql([['SELECT * FROM ?',[arr]]]).then(function(data){console.log(data);});</script>

Try thejsFiddle example.

Another option is to includealasql.min.js as usual but callalasql.worker() as the first thing yourself:

<scriptsrc="alasql.min.js"></script><script>alasql.worker();varres=alasql(['select value 10']).then(function(res){console.log(res);}).catch(console.error);</script>

Try thisjsFiddle example.

If using AlaSQL as Web Worker, you can import it traditionally as a script:

importScripts('alasql.min.js');

To use AlaSQL within acreate-react-app (CRA) setupwithout ejecting it is: Pleasehave a look at this comment.

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 enviroments 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

There are several ways to handle AlaSQL with Webpack:

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

varIgnorePlugin=require("webpack").IgnorePlugin;module.exports={  ...//Will ignore the modules fs, path, xlsx, request, vertx, and react-native modulesplugins:[newIgnorePlugin(/(^fs$|cptable|jszip|xlsx|^es6-promise$|^net$|^tls$|^forever-agent$|^tough-cookie$|cpexcel|^path$|^request$|react-native|^vertx$)/)]};

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

...//Don't parse alasql{module:noParse:[/alasql/]}

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

//Load alasql in the global scope with script-loaderimport"script!alasql"

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

Read up onexcluding,ignoring, andshimming

Example (using excluding)

varbrowserify=require("browserify");varb=browserify("./main.js").bundle();//Will ignore the modules fs, path, xlsx["fs","path","xlsx",  ...].map(ignore=>b.ignore(ignore));

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

import XLSX from 'xlsx';alasql.utils.isBrowserify = false;alasql.utils.global.XLSX = XLSX;

Please remember to send the original event, and not the jQuery event, for elements. (Useevent.originalEvent instead ofmyEvent)

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

alasql>SELECT VALUE {a:'1',b:'2'}{a:1,b:2}alasql>SELECT VALUE {a:'1',b:'2'}== {a:'1',b:'2'}truealasql>SELECT VALUE {a:'1',b:'2'}->b2alasql>SELECT VALUE {a:'1',b:(2*2)}->b4

Try AlaSQL JSON objects in Console [sample](http://alasql.org/console?drop table if exists one;create table one;insert into one values {a:@[1,2,3],c:{e:23}}, {a:@[{b:@[1,2,3]}]};select * from one)

Useful stuff, but there might be dragons

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

// Who loves lovers of Alice?varres=alasql('SEARCH / ANY(>> >> #Alice) name');console.log(res)// ['Olga','Helen']

See moreat the wiki

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

alasql('CREATE localStorage DATABASE IF NOT EXISTS Atlas');alasql('ATTACH localStorage DATABASE Atlas AS MyAtlas');alasql('CREATE TABLE IF NOT EXISTS MyAtlas.City (city string, population number)');alasql('SELECT * INTO MyAtlas.City FROM ?',[[{city:'Vienna',population:1731000},{city:'Budapest',population:1728000}]]);varres=alasql('SELECT * FROM MyAtlas.City');console.log(res);

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

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

AlaSQL supports plugins. To install a plugin you need to use theREQUIRE statement. See moreat the wiki

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

To run enter the command:

    alaserver [port]

then type in browser something like "http://127.0.0.1:1337/?SELECT VALUE 2*2"

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

AlaSQL currently has over 1200 regression tests, but they only coverof the codebase.

AlaSQL usesmocha for regression tests. Installmocha and run

    > npm test

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

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

CREATETABLEone (aINT);    ASSERT1;INSERT INTO oneVALUES (1),(2),(3);    ASSERT3;SELECT*FROM oneORDER BY aDESC;    ASSERT [{a:3},{a:2},{a:1}];

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

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

MIT - seeMIT licence information

• Andrey Gershun
• Mathias Rangel Wulff

AlaSQL is anOPEN 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 atCONTRIBUTING.md._

Many thanks to Zach Carter forJison parser generator, to the author of FileSaver.js, Andrew Kent for hisSQL Parser,authors ofXLSX library,and other people for useful tools, which make our work much easier.

• 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

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