([à la] (http://en.wiktionary.org/wiki/%C3%A0_la) SQL) [ælæ ɛskju:ɛl] - AlaSQL is a versatil javascript SQL database library for both relational data, schemaless data, and graph data with a strong foucus on query speed and flexibillity for datasources. It works in your browser, Node.js, IO.js and Apache Cordova.

We focus on speed by taking advantage of the dynamic nature of javascript when building up queries. Real world solutions demands 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 your own JSON object, Excel files, localStorage, IndexedDB, and SQLite.

The library brings you the comfort of a full database engine to your javascript app. No, really - its working towards a full database engine complying with most of SQL-99 spiced up with additional syntax for handling noSQL (schemaless) data and graph networks. To help porting from native databases you can specify the flavor for the SQL behavior as either AlaSQL, T-SQL, MySQL, Postgres, ORACLE, SQLite, OrientDB. MSSQL is on its way.

Website: alasql.org

Try online: Playground

Feedback: Open an issue

Documentation: Github wiki

Get the file (CDN): jsDelivr.com

AlaSQL project is very young and still in active development phase, therefore it may have bugs. Please, wait a little bit before using it in production. Please, submit any bugs and suggestions as an issue. AlaSQL uses Semantic Versioning so please note that major version is zero (0.y.z) so the API can not be considered 100% stable.

All contributions are much welcome and greatly appreciated(!) so just open an issue and lets talk about your idea.

For the browser: Include alasql.min.js and call alasql() with your SQL statements:

<script src="//cdn.jsdelivr.net/alasql/0.1/alasql.min.js"></script> 

<script>
    
    alasql("CREATE TABLE cities (city string, population number)");
        
    alasql("INSERT INTO cities VALUES ('Rome',2863223), ('Paris',2249975), ('Berlin',3517424),  ('Madrid',3041579)");
        
    var res = alasql("SELECT * FROM cities WHERE population < 3500000 ORDER BY population DESC");
   
   // res now contains this array of object:
   // [{"city":"Madrid","population":3041579},{"city":"Rome","population":2863223},{"city":"Paris","population":2249975}]   
   
</script>

Play with this example in jsFiddle

To use AlaSQL via bower install as normal

bower install alasql

To use AlaSQL with Meteor install as normal

meteor install agershun:alasql

For node install with npm

npm install alasql

Require alasql and create a new database to start executing your SQL.

var alasql = require('alasql');

var db = new alasql.Database();

db.exec("CREATE TABLE example (a INT, b INT)");

// You can insert data directly from javascript object...
db.tables.example1.data = [ 
    {a:5,b:6},
    {a:3,b:4}
];

// ...or you can insert data with normal SQL 
db.exec("INSERT INTO example1 VALUES (1,3)");

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

// res now contains this array of objects:
// [{a:1,b:3},{a:3,b:4},{a:3,b:4}]

You can access AlaSQL from the comandline by installing from npm globally

npm install alasql -g

Now you can access alasql via the commandline

> alasql "SELECT * INTO json('my.json') from xlsx('cities.xlsx',{headers:true}) WHERE population > 20000000"

To get get value instead of a JSON you can prepend VALUE to the SELECT

? will be replaced with the corresponding n'th argument.

alacon "VALUE SELECT 20-?+?" 5 100

See more examples at the wiki

The wiki has a great section on how to getting started

When you feel you got the grip you can check out the wiki section about data manipulation or getting inspired by the list of Q&As

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 quick 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:

• 2x speed compared to SQL.js selecting with SUM, JOIN, and GROUP BY.

• 2x speed compared to WebSQL selecting with SUM, JOIN, and GROUP BY (in-memory opperations for WebSQL - see this discussion)

• 1.5x speed compared to Linq for GROUP BY on 1,048,576 rows

See more speed 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 to compare supported features with SQL standarts.

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

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

    // Who loves lovers of Alice?
    alasql('SEARCH / ANY(>> >> #Alice) name');
    // ['Olga','Helen']

See more at the wiki

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

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

    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}];
    var res = alasql('SELECT a, COUNT(*) AS b FROM ? GROUP BY a',[data]);
    console.log(res);

See more ideas of creative datamanipulation in the wiki

Take charge and add your idea or vote on your favorite feature to be implemented:

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

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

Angular is great. Besides using AlaSQL for normal data manipulation it works like a charm for exporting you present scope to Excel. See more about Angular 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 a one unif of work for fetching and identifying whats relevant. See more about Google 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 about Google Spreadsheets and AlaSQL in the wiki

#What is new?

AlaSQL can work as a webworker. Include alasql-worker.js and that is it: AlaSQL will work as a webworker.

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

Try the example at jsFiddle.

Another option - run alasql.worker() function:

    <script src="alasql.min.js"></script>
    <script>
         alasql.worker();
         var res = alasql('select value 10',[],function(res){
              console.log(res);
         });
    </script>

Try this example in jsFiddle.

Also you can use AlaSQL in webworker just simply load it as a script:

    importScripts('alasql.min.js');

Now AlaSQL can work with files in XLS, XSLX, CSV, TAB, TXT, and JSON format

    alasql('select * into one from csv("mydata.csv")');
    alasql('select Country, Name from xlsx("cities.xlsx",{headers:true, range:"B1:E10"})\
        where Population > 100000',
        [],function(data){
        console.log(data);
    });

See test168 - test169

AlaSQL can work with SQLite data files on the top of with SQL.js library:

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

See more detailed the example.

Upload CSV file with headers to IndexedDB database, and then save only asian countries to Excel file:

    alasql('ATTACH INDEXEDDB DATABASE geo; \
            CREATE TABLE IF NOT EXISTS geo.country; \
            SELECT * INTO geo.country FROM CSV("country.csv",{headers:true}); \
            SELECT * INTO XLSX("asia.xlsx") FROM geo.country WHERE continent_name = "Asia"');

See the example.

See more examples in the wiki

Most of SQL-99. Please see the wiki for more info

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

• Json objects - ``` {a:1,b:@[1,2,3]}
• Object propertires - obj->property->subproperty
• Object and arrays elements - obj->(a*1)
• JavaScript functions - obj->valueOf()
• SELECT VALUE, ROW, COLUMN, MATRIX to format results of query

You can use browser localStorage and DOM-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}]]);
    var res = alasql('SELECT * FROM MyAtlas.City');
    console.log(res);

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.

You can use files in these formats directly from AlaSQL (in sync and async modes):

    var res1 = alasq("select * from txt('mytext.txt') where [0] like 'M%'");
    var res2 = alasq("select * from tab('mydata.tab') order by [1]");
    var res3 = alasq("select [3] as city,[4] as population from csv('cities.csv')");
    
    alasq("select * from json('array.json')",[],function(res4){
        console.log(res4)
    });

See test157.js as an example.

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}

true

alasql> SELECT VALUE @{a:1,b:2}->b

2

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

4

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)

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-thread, not concurent, and not secured.

AlaSQL uses mocha for tests. Install mocha and run

    > npm test

or run test/index.html for tests in browser.

Now you can use AlaSQL ASSERT operator to test results of previous operation:

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

AlaSQL uses SQLLOGICTEST to test it compatibility with SQL-99. The tests include about 2.000.000 queries and statements.

The testruns can be found in the testlog.

0. It is Ok with select for 1000000 records or to join two tables by 10000 records in each. Now you can use streamming functions to work with longer datasources (see test/test143.js).

1. ORDER BY clause on three or more UNIONS ( [See example in AlaSQL console](http://alasql.org/console?select 10 as a union all select 20 as a union all select 30 as a order by a desc) )

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

Probably, there are many of others. Please, give us a chance to fix them. Thank you!

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

MIT - see MIT licence information

• Andrey Gershun
• Mathias Rangel Wulff
• Aubert Grégoire

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

• OLAP.js - embeddable extensible JavaScript OLAP server
• WebSQLShim - WebSQL shim over IndexedDB (work in progress)
• AlaMDX - JavaScript MDX OLAP library (work in progresss)
• Other similar projects - list of databases on JavaScript

© 2014-2015, Andrey Gershun (agershun@gmail.com) & Mathias Rangel Wulff (mathiasrw@gmail.com)