AlaSQL

![NPM downloads](http://img.shields.io/npm/dm/alasql.svg?style=flat&label=npm downloads) ![Release](https://img.shields.io/github/release/agershun/alasql.svg?label=Last release)

AlaSQL

([à 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 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

How to use

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

Bower

To use AlaSQL via bower install as normal

bower install alasql

Node.js or IO.js

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}]

Please checkout Alacon if you intent to use alasql for commandline interface

Peformance

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

Fetures 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. See the wiki to compare supported features with SQL standarts.

AlaSQL ♥ your JSON data

see why

###Miss a feature? Take charge and add your idea or vote on your favorite feature to be implemented:

Cases where AlaSQL is useful

AlaSQL ♥ D3.js

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

AlaSQL ♥ Meteor

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

AlaSQL ♥ Angular.js

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

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 a one unif of work for fetching and identifying whats 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 powerfull data manipulation. See more about Google Spreadsheets and AlaSQL in the wiki

Documentation

#What is new?

AlaSQL now supports plug-ins

Now AlaSQL supports plugins system. To install the plugin you need to use REQUIRE statement, like:

    alasql('REQUIRE ECHO');
    var res = alasql('ECHO 123');  // Returns simply 123

You can use the same command in browser or include plugins directly:

    <script src="alasql.min.js"></script>
    <script src="alasql-echo.js"></script>

The list of exsisting packages and how to build your own is on its way...

AlaSQL and Meteor

Install AlaSQL for Meteor from the official package:

    meteor add agershun:alasql

AlaSQL supports Meteor collections. Sounds awesome but what is it? Dj Walker-Morgan from compose.io puts it this way:

This lets you turn...

return Robots.find({}, { sort: { introduced: 1 }} );

into

return alasql('SELECT * FROM ? ORDER BY introduced',[Robots]);

which doesn't look like a huge jump, until you realise that this works in both the browser and the server and opens up a way to do JOIN, GROUP BY, UNION, DISTINCT and others.

It works on the client and the server side.

Now you can use Meteor Collections as arguments. To do it simply store alasql.min.js to the client/lib directory and then apply SQL to Meteor Collections:

    Template.body.helpers({
       tasks: function () {
         return alasql('SELECT * FROM ?',[Tasks]);
       }
    });

Or you can use with find() options with special METEOR() from-function:

    return alasql('SELECT * FROM ?',[Tasks]);
    return alasql('SELECT * FROM METEOR(?)',[Tasks]);
    return alasql('SELECT * FROM METEOR(?,?)',[Tasks,{text:"Hello world!"}]);
    return alasql('SELECT * FROM METEOR(?,{text:"Hello world!"})',[Tasks]);

Search paths in graph

Now you can search graphs with SEARCH operator:

    var res = alasql('CREATE GRAPH Pablo, Maxim, Alex, Napoleon, \
      Josephine,  Kate, Julia  {age:27}, Paloma, \
      #Pablo >loves> #Julia, #Maxim >> #Julia, #Alex >> #Kate, \
      #Kate >> #Julia, #Alex >> #Paloma, #Napoleon > "loves" > #Josephine, \
      #Josephine >"knows"> #Pablo');

    var res = alasql('SEARCH PATH(#Pablo) name FROM #Napoleon ');
    // returns ['loves','Josephine','knows','Pablo']

You can play with grpahs in AlaSQL in this jsFiddle example.

Documents and graphs paradigms

AlaSQL now is multi-paradigm database with support documents and graphs. Below you can find an example how to create graph:

    alasql('CREATE GRAPH #Olga, #Helen, #Pablo, #Andrey, #Alice, \
        #Olga >> #Pablo, #Helen >> #Andrey, \
        #Pablo >> #Alice, #Andrey >> #Alice');

and search over it with SEARCH operator:

    // Whom loves Olga?
    alasql('SEARCH / #Olga >> name');
    // ['Pablo']

    // Whom loves Olga's love objects?
    alasql('SEARCH / #Olga >> >> name');
    // ['Alice']

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

You also make searches over JSON object with SEARCH operator:

    var data = {a:{a:{a:{a:{b:10}}}},b:20};
    var res = alasql('SEARCH a b FROM ?',[data]);
    var res = alasql('SEARCH (a)+ b FROM ?',[data]);
    var res = alasql('SEARCH (a a)+ b FROM ?',[data]);
    var res = alasql('SEARCH (a a a)+ b FROM ?',[data]);
    var res = alasql('SEARCH (/)+ b FROM ?',[data]);
    var res = alasql('SEARCH /+b FROM ?',[data]);
    var res = alasql('SEARCH a* b FROM ?',[data]);
    var res = alasql('SEARCH a+ b FROM ?',[data]);
    var res = alasql('SEARCH a? b WHERE(b>20) FROM ?',[data]);

Please see more examples in test300-test309.js. All these features will be documented soon.

Version upgrade from 0.0.51 to 0.1.0

AlaSQL now is 6 month old and it can change minor release number from 0.0 to 0.1.

As we decided earlier, we will change version number to 0.1 after cleaning some known bugs and problems (including UNION CORRESPONDING issue and IE9 file saving).

The plans and priorities for development from version 0.1 to 0.2 will be:

Wiki documentation
Fixing bugs and clean the code
Add graph- and document- database paradigm functionality
Prepare to convert AlaSQL to modular structure (AlaSQL2)

Other areas of future improvements (please add and vote for new features on Feathub):

Better Apache Cordova and other mobile frameworks support
Improve export to XLS and XLSX (adding colors and LibreOffice support)
Support key-value external storages and NoSQL databases
Add some missing SQL functionality (constraints, foreign keys, etc.)

AlaSQL export to Excel with colors (2)

Now AlaSQL can save files to Excel and LibreOffice formats with colors and other Excel formatting functions. This function also work in IE9. This is a code sample:

    var mystyle = {
      headers:true, 
      column: {style:{Font:{Bold:"1"}}},
      rows: {1:{style:{Font:{Color:"#FF0077"}}}},
      cells: {1:{1:{
        style: {Font:{Color:"#00FFFF"}}
      }}}
    };
    alasql('SELECT * INTO XLSXML("restest280b.xls",?) FROM ?',[mystyle,data]);

See the working example in jsFiddle

AlaSQL export to Excel with colors (1)

Now AlaSQL can export data to XLS format with coloring cells

    var opts = {
      headers: true,
      sheetid: 'My Birds',
      style:"background:#00ff00",
      columns: [
        {columnid:'a',title:'Albatroses',
          style:'background:red;font-size:20px',
          cell:{style:'background:blue'}
        },
        {columnid:'b',title:'Bird',cell:{
          style:function(value,sheet,row,column,rowidx,columnidx){
            return 'background'+(value==10?'brown':'white')
        }}},
        { 
          columnid: 'b', cell:{value:function(value){ return value * value}}
        }
      ]
    };

    var res = alasql('SELECT * INTO XLS("restest257a.xls",?) FROM ?',[opts,data]);

Please, see the example with advanced color syntax in jsFiddle.

AlaSQL and Google Spreadsheets (with Tabletop)

Now AlaSQL can read data (and make other manipulations like grouping, filtering, or saving to XLSX) directly from Google Spreadsheets. It uses wonderful Tabletop library.

<script src='../../console/tabletop.js'></script>
<script src='../../console/alasql.min.js'></script>
<div id="res"></div>
<script>
    var url = 'https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0AmYzu_s7QHsmdDNZUzRlYldnWTZCLXdrMXlYQzVxSFE&output=html';

    alasql('SELECT * INTO HTML("#res",{headers:true}) FROM TABLETOP(?) WHERE name < "D" ORDER BY category',[url]);
</script>

You can try this example in jsFiddle.

AlaSQL as a WebWorker

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');

Read and write Excel, CSV, TAB, JSON, and text files to/from database

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

d3.js + AlaSQL

AlaSQL can be used with d3.js to create diagrams, like the example below. Here AlaSQL read Excel file with Olympic Prizes, group and sort rows by one of the axes, and then take top 10 records and create a list with axe values:

    function graph(axe) {
        alasql('SELECT TOP 10 '+axe+', SUM([Gold Medals]) AS Gold, \
            SUM([Silver Medals]) AS Silver, SUM([Bronze Medals]) AS Bronze \
            FROM XLSX("medals.xlsx",{headers:true}) \
            GROUP BY '+axe+' ORDER BY '+axe,[],function(data){

            d3.select("body")
            .append('ul')
            .data(data)
            .entry()
            .append('li')
            .text(function(d){return d[axe]});
        });
    }

    graph('Axe');

Try the simple example of AlaSQL + d3.js integration.

Google Maps + AlaSQL

    alasql("SELECT * FROM CSV("https://cdn.rawgit.com/albertyw/avenews/master/old/data/average-latitude-longitude-countries.csv",{headers:true})', [], function(country){
        var mapOptions = { zoom : 3, center : new google.maps.LatLng(40, 0),
            mapTypeId : google.maps.MapTypeId.ROADMAP
        };
        var map = new google.maps.Map(document.getElementById('map-canvas'), mapOptions);
        for (i = 0; i < country.length; i++) {
            var opts = {
                strokeColor : '#000000',
                fillColor : ["red","green","blue","brown"][i%4],
                fillOpacity : 0.35,
                map : map,
                center : new google.maps.LatLng(country[i].Latitude,country[i].Longitude),
                radius : 100000
            };
            new google.maps.Circle(opts);
    });

Check this example and this example of integrations AlaSQL and Google Maps.

ETL sample: CSV and IndexedDB database

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.

Read SQLite database files

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.

Angular.js and AlaSQL.js

You can use AlaSQL together with Angular.js framework:

    $scope.exportData = function () {
        alasql('SELECT * INTO XLSX("john.xlsx",{headers:true}) FROM ?',[$scope.items]);
    };

See simple example in jsFiddle.

Other examples of AlaSQL and Angular.js integration:

Calculating average of array

Examples

Try AlaSQL in console:

AlaSQL Console http://alasql.org/console
Console source code
Alacon - command-line AlaSQL console (to run: alacon "sql-statement" params...)
Alaserver - very simple AlaSQL-server (to run: alaserver, then type in browser "http://127.0.0.1:1337/?SELECT VALUE 2*2" )

Try AlaSQL in Fiddle:

Simple examples: sample 1, sample 2, sample 3, sample 4, sample 5
IndexedDB example
AlaSQL Sandbox - source
Sieve of Eratosthenes - source

Other examples:

[World database](http://alasql.org/console?source 'world.sql';select top 10 * from City) - source
Mini-ETL: upload CSV data to IndexedDB database
More than 200 of AlaSQL tests

What People Say about AlaSQL?

What people around the world say about AlaSQL

Presentations

Installation

In browser

Include file: alasql.js to the page.

  <script src="alasql.js"></script>  
  <script>
    alasql("CREATE TABLE test (language INT, hello STRING)");
    alasql("INSERT INTO test VALUES (1,'Hello!')");
    alasql("INSERT INTO test VALUES (2,'Aloha!')");
    alasql("INSERT INTO test VALUES (3,'Bonjour!')");
    console.table(alasql("SELECT * FROM test WHERE language > 1"));
  </script>

Try this sample in [AlaSQL console](http://alasql.org/console?CREATE TABLE test (language INT, hello STRING);INSERT INTO test VALUES (1,'Hello!');INSERT INTO test VALUES (2,'Aloha!');INSERT INTO test VALUES (3,'Bonjour!');SELECT * FROM test WHERE language > 1)

You can use alasql.js with define()/require() functions in browser as well, because it supports AMD and UMD:

    require(['../../alasql.js'], function(alasql) {
        var test1 = [{a:1,b:2,c:3},{a:4,b:5,c:6},{a:7,b:8,c:9}];
        console.table(alasql('SELECT a, b*c AS bc FROM ? AS t',[test1]));
    });

Like in this sample you do not need to CREATE TABLE and INSERTS if you do not need constraints functionality.

Supported SQL statements

SELECT TOP number columns INTO table FROM tableid1 JOIN tableid2 ON oncond WHERE cond GROUP BY v1,v2 HAVING cond ORDER BY a,b, LIMIT number OFFSET number
INSERT INTO table [ (field1, field2) ] VALUES (value1, value2), (value3, value4), ...
INSERT INTO table SELECT subquery
UPDATE table SET field = value1, field = value2 WHERE condition
DELETE FROM table WHERE condition
CREATE TABLE [IF NOT EXISTS] table (columns type PRIMARY KEY, constraints)
ALTER TABLE ADD COLUMN / DROP COLUMN
DROP TABLE [IF EXISTS] table
CREATE DATABASE, USE DATABASE, DROP DATABASE
SHOW DATABASES / SHOW TABLES / SHOW COLUMNS / SHOW CREATE TABLE
SOURCE 'url-file.sql'
ASSERT json-object
Expression (like SELECT expression)

Try all these statements in AlaSQL console

SELECT statement

AlaSQL.js supports following subset of SELECT syntax:

SELECT column1, column2 AS alias3, FUNCTION(field4+field5) AS alias6, SUM(expression7) AS alias8, , table2.
TOP number
FROM table1, table2, (SELECT * FROM table3) alias
LEFT / RIGHT / INNER / OUTER / ANTI / SEMI / CROSS / NATURAL JOIN table2 ON condition / USING columns
WHERE condition
GROUP BY column1, column2, ROLLUP(a,b), CUBE(c,d,e), GROUPING SETS(g,h)
HAVING condition
ORDER BY column1, column2 DESC,
LIMIT number [OFFSET number]
UNION / UNION ALL select / INTERSECT / EXCEPT

Operators:

+, -, *, /, %, AND, OR, NOT, BETWEEN, NOT BETWEEN, EXISTS (Subquery), > ALL (subquery/array), > ANY/SOME (subquery / array), [NOT] IN (subquery / array), LIKE
CAST (expression AS type)

Aggregators:

SUM(), COUNT(), MIN(), MAX(), FIRST(), LAST(), AVG(), AGGR(), ARRAY(), REDUCE()

GROUP BY Grouping functions:

ROLLUP(), CUBE(), GROUPING SETS()

Functions:

ABS(), IIF(), IFNULL(), INSTR(), LOWER(), UPPER(), LCASE(), UCASE(), LEN(), LENGTH()
GREATEST(), LEAST()

SELECT modifiers (non-standard SQL):

SELECT VALUE - get single value
SELECT ROW - get first row as an array
SELECT COLUMN - get first column as an array
SELECT MATRIX - get all results as an array of arrays

User-defined JavaScript functions

You can use all benefits of SQL and JavaScript togeather by defining user functions. Just add new functions to alasql.fn object:

        alasql.fn.double = function(x){return x*2};        
        alasql.fn.sum10 = function(x,y) { return x+y*10; }
        db.exec('SELECT a, double(a) AS b, sum10(a,b) FROM test1');

User-defined functions are related to current database. You can define different functions in different databases.

alasql

alasql is a main variable of module. You can use it immediatly as default database

In browser:

    <script src="alasql.js"></script>
    <script>
        alasql('CREATE TABLE one (two INT)');
    </script>

Try this sample in [AlaSQL console](http://alasql.org/console?CREATE TABLE one (two INT))

or in Node.js:

    var alasql = require('alasql');
    alasql('CREATE TABLE one (two INT)');

Another approach is to create new database:

    var mybase = new alasql.Database();
    mybase.exec('CREATE TABLE one (two INT)');

You can give a name to database and then access it from alasql:

    var mybase = new alasql.Database('mybase');
    console.log(alasql.databases.mybase);

Each database can be used with the following methods:

    var db = new alasql.Database() - create new alasql-database
    var res = db.exec("SELECT * FROM one") - executes SELECT query and returns array of objects

Usually, alasql.js works synchronously, but you can use callback.

    db.exec('SELECT * FROM test', [], function(res){
        console.log(res);
    });

or you can use promise() - promised version of exec (in this case you need to install es6-promise module for Node.js) (this feature is experimental and may be removed in a future to reduce dependices):

    alasql.promise('SELECT * FROM test')
    }).then(function(res){
        // Process data
    }).catch(function(err){
        // Process errors
    });

You can use compile statements:

    var insert = db.compile('INSERT INTO one (1,2)');
    insert();

You can use parameters in compiled and interpreted statements:

    var insert1 = db.compile('INSERT INTO one (?,?)');
    var insert2 = db.compile('INSERT INTO one ($a,$b)');
    var insert3 = db.compile('INSERT INTO one (:a,:b)');

    insert1([1,2]);
    insert2({a:1,b:2});
    insert3({a:3,b:4});

    db.exec('INSERT INTO one (?,?)',[5,6]);

You even can use param in FROM clause:

        var years = [
            {yearid: 2012}, {yearid: 2013},
            {yearid: 2014}, {yearid: 2015},
            {yearid: 2016},
        ];

        var res = alasql.queryArray('SELECT * FROM ? AS years ' +
            'WHERE yearid > ?', [years,2014]);

        // res == [2015,2016]

You can use array of arrays to make a query. In this case use square brackets for column name, like [1] or table[2] (remember, all arrays in JavaScript start with 0):

        var data = [
            [2014, 1, 1], [2015, 2, 1],
            [2016, 3, 1], [2017, 4, 2],
            [2018, 5, 3], [2019, 6, 3]
        ];
        var res = alasql('SELECT SUM([1]) FROM ? d WHERE [0]>2016', [data]);

Use alasql.queryArrayOfArrays() function to return array of arrays. In this case you can specify array position of selected column with number or number in brackets:

        var res = alasql.queryArrayOfArrays(
            'SELECT [1] AS 0,[1]+[2] AS [1] FROM ? d WHERE [0]>2016', [data]);

This feature can be used as filter for arrays. Compare:

        // Same filter
        var res1 = alasql.queryArrayOfArrays('SELECT * FROM ? a WHERE [0]>2016', [data]);
        var res2 = data.filter(function(a){return a[0]>2016});

        // Complex filter with aggregating, grouping and sorting
        var res = alasql.queryArrayOfArrays(
            'SELECT [2] AS 0, SUM([1]) AS 1 FROM ? a WHERE a[0]>? GROUP BY [0] ORDER BY [1]', 
            [data, 2016]);

Lower and Upper Case

By default, AlaSQL is case-insensitive to all standard keywords (like SELECT) and standard functions (like ABS()). All database names, table names, column names, and user-defined functions are case sensitive.

JavaScript is case-sensitive language, so use the same CaSe for AlaSQL and JavaScript data.

Now you can use databases, tables, and columns with spaces inside square brackets:

    CREATE DATABASE [My Database];  -- BTW You can use comments in the SQL statements
    USE [My Database];              /* or comments like this */   
    CREATE TABLE [A.table] ([the-column] INT);
    INSERT INTO [A.table] VALUES (1),(2);
    SELECT [the-column] AS [AS] FROM [My Database];

Try this sample in [AlaSQL console](http://alasql.org/console?CREATE DATABASE [My Database];USE [My Database];CREATE TABLE [A.table] ([the-column] INT);INSERT INTO [A.table] VALUES (1),(2);SELECT [the-column] AS [AS] FROM [My Database])

JavaScript Sugar

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

Transactions

There is a limited support of transactions (with tx.rollback() and tx.commit() functions):

    db = new alasql.Database("mydb");
    db.transaction(function(tx) {
        tx.exec('SELECT COUNT(*) FROM students');
        tx.rollback();
    });

Warning: Sorry, transactions now are temporary unavailable due some complexities with realization.

SQL to JavaScript parser and compiler

You can use AlaSQL to parse to AST and compile SQL statements:

    // Parse to AST
    var ast = alasql.parse("SELECT * FROM one");
    console.log(ast.toString()); // Print restored SQL statement

    // Compile to JavaScript function with or without parameters
    var statement = alasql.compile("SELECT * FROM one WHERE a > ? AND b < ?");
    statement([1,5]);

AlaSQL uses wonderful Jison parser to produce AST-tree.

localStorage and DOM-storage

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.

Work with CSV, TAB, TXT, and JSON files

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.

JSON-object

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)

Alacon - command-line utility

You can use AlaSQL from the command-line:

    > alacon "SELECT VALUE 1+1"
    2
    > alacon "SELECT VALUE 1+?" 2
    3
    > alacon "@{a:(1+?)}" 3
    4

Alaserver - simple database server

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.

Performance

According the preliminary performance tests alasql.js is faster than sql.js in 5 to 10 times on more than 1000 records tables, and 2 to 3 times to WebSQL on different queries.

AlaSQL has four different optimization algorithm:

Caching of compiled queries
Joins: Preindexation of joined table
Joins: Prefiltering of WHERE expressions

Now optimization algorithm has some limitations and therefore "table1 JOIN table2 USING column1, column2" is faster than "table1 JOIN table2 ON table1.column1 = table2.column1 AND table1.column2 = table2.column2 ", but with one column it is ok.

Limitations

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

Tests

Tests with Mocha

AlaSQL uses mocha for tests. Run mocha from command line in directory with tests:

    > cd test
    > mocha .

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

Tests with AlaSQL ASSERT from SQL

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}];

SQLLOGICTEST

AlaSQL uses SQLLOGICTEST to test it compatibility aith SQL-92. The tests include about 140000 different queries and statements.

Result for all test files without select4.test and select5.test in total on May 19, 2015 15:45

tests - 138365
parsed ok - 135205 (98%)
run ok - 86330 (62%) <----- this is the most important number in the test
time - 452s

See detailed information at TESTLOG.

Warnings

AlaSQL project is very young and still in active development phase, therefore it may have some bugs. Please, wait a little bit before start to use it in production. I am going to add more tests and clean the code before relaese more stable version 0.1.0. Please, submit any bugs and suggestions in Issues page.

You can check next version branches for new experimental features.

Known Bugs and Limitations

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) )
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 one of the future version.

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

FileSaver

AlaSQL includess FileSaver.js library inside for saving files locally from browser. Pleae be aware that it does not save files in Safari 8.0.

##Contributing

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

License

MIT - see MIT licence information

Main contributors

Credits

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.

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 progresss)
Other similar projects - list of databases on JavaScript

Name		Name	Last commit message	Last commit date
Latest commit History 781 Commits
bin		bin
console		console
dist		dist
examples		examples
lib		lib
node_modules		node_modules
partners		partners
src		src
stuff		stuff
test-sql		test-sql
test		test
utils		utils
.bithoundrc		.bithoundrc
.bower.json		.bower.json
.codeclimate.yml		.codeclimate.yml
.gitattributes		.gitattributes
.gitignore		.gitignore
.jshintrc		.jshintrc
CHANGELOG.md		CHANGELOG.md
LICENSE		LICENSE
README.md		README.md
RELEASES.md		RELEASES.md
TESTLOG.md		TESTLOG.md
bower.json		bower.json
gulpfile.js		gulpfile.js
inch.json		inch.json
package.json		package.json

License

SongKiDoo/alasql

Folders and files

Latest commit

History

Repository files navigation

AlaSQL

How to use

Bower

Node.js or IO.js

Peformance

Fetures you might like

Traditional SQL

AlaSQL ♥ your JSON data

Cases where AlaSQL is useful

AlaSQL ♥ D3.js

AlaSQL ♥ Meteor

AlaSQL ♥ Angular.js

AlaSQL ♥ Google Maps

AlaSQL ♥ Google Spreadsheets

Documentation

AlaSQL now supports plug-ins

AlaSQL and Meteor

Search paths in graph

Documents and graphs paradigms

Version upgrade from 0.0.51 to 0.1.0

AlaSQL export to Excel with colors (2)

AlaSQL export to Excel with colors (1)

AlaSQL and Google Spreadsheets (with Tabletop)

AlaSQL as a WebWorker

Read and write Excel, CSV, TAB, JSON, and text files to/from database

d3.js + AlaSQL

Google Maps + AlaSQL

ETL sample: CSV and IndexedDB database

Read SQLite database files

Angular.js and AlaSQL.js

Examples

What People Say about AlaSQL?

Presentations

Installation

In browser

Supported SQL statements

SELECT statement

User-defined JavaScript functions

alasql

Lower and Upper Case

JavaScript Sugar

Transactions

SQL to JavaScript parser and compiler

localStorage and DOM-storage

Work with CSV, TAB, TXT, and JSON files

JSON-object

Alacon - command-line utility

Alaserver - simple database server

Performance

Limitations

Tests

Tests with Mocha

Tests with AlaSQL ASSERT from SQL

SQLLOGICTEST

Warnings

Known Bugs and Limitations

FileSaver

License

Main contributors

Credits

Related projects that have inspired us

About

Resources

License

Stars

Watchers

Forks

Releases

Packages 0

Languages

Packages