( [à la] (http://en.wiktionary.org/wiki/%C3%A0_la) SQL ) [ælæ ɛskju:ɛl] - AlaSQL is an open source SQL database for Javascript with a strong foucus on query speed and datasource flexibillity for relational data, schemaless data, and graph data. It works in your browser, Node.js, IO.js and Cordova.
The library is designed for:
- Fast SQL based in-memory data processing for BI and ERP applications on fat clients
- Easy ETL and option for persistency by data import / manipulation / export for 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 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 Excel (both xls
and .xlsx
), CSV, JSON, TAB, IndexedDB, LocalStorage, and SQLite files.
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 (schema less) data and graph networks. To help porting from native databases you can specify the flavour for the SQL behaviour as either AlaSQL, T-SQL, MySQL, Postgres, ORACLE, SQLite, OrientDB. MSSQL is on its way.
// A) Traditional SQL
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");
console.log(res);
/*
[
{
"city": "Madrid",
"population": 3041579
},
{
"city": "Rome",
"population": 2863223
},
{
"city": "Paris",
"population": 2249975
}
]
*/
// B) SQL on 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]);
console.log(res); // [{"a":1,"b":40},{"a":2,"b":20}]
// C) Read from file
alasql.promise('SELECT * FROM XLS("mydata.xls") 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 exists? there was an error:', err);
});
jsFiddle with example A) and example B)
npm install alasql --save # npm
meteor add agershun:alasql # meteor
bower install alasql --save # bower
npm install -g alasql # command line interface
For the browser: include alasql.min.js
<script src="http://cdn.jsdelivr.net/alasql/0.2/alasql.min.js"></script>
The wiki has a great section on how to get 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
-
Documentation: Github wiki
-
Library CDN: jsDelivr.com
-
Feedback: Open an issue
-
Try online: Playground
-
Website: alasql.org
AlaSQL project is very young and still in active development phase, therefore it may have bugs. Please, submit any bugs and suggestions as an issue. AlaSQL uses Semantic Versioning so please note that major version is zero (0.y.z) and the API can not be considered 100% stable. Consider this before using the library in production.
All contributions are much welcome and greatly appreciated(!) so just open an issue and lets talk about your idea.
Also: we really (really) love pull requests
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:
-
2.5x speed compared to SQL.js selecting with
SUM
,JOIN
, andGROUP BY
. -
3x speed compared to WebSQL selecting with
SUM
,JOIN
, andGROUP BY
(in-memory opperations for WebSQL - see this discussion) -
2x 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.
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');
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
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
To use AlaSQL with Node or IO.js install with npm
npm install alasql --save
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.
alasql "VALUE SELECT 20-?+?" 5 100
See more examples at the wiki
AlaSQL can work as a webworker. Include alasql-worker.js and thats's 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 and 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.
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.
Take charge and add your idea or vote on your favorite feature to be implemented:
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.
-
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).
-
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 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
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.
- 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
© 2014-2015, Andrey Gershun ([email protected]) & M. Rangel Wulff ([email protected])