- Notifications
You must be signed in to change notification settings - Fork689
Getting started
Afterincluding/requiring/installing AlaSQL the objectalasql is the main variable of the module. You can use it immediately as a default database
In browser:
<scriptsrc="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:
varalasql=require('alasql');alasql('CREATE TABLE one (two INT)');
Another approach is to create new database:
varmybase=newalasql.Database();mybase.exec('CREATE TABLE one (two INT)');
You can give a name to database and then access it from alasql:
varmybase=newalasql.Database('mybase');console.log(alasql.databases.mybase);
Each database can be used with the following methods:
vardb=newalasql.Database()-createnewalasql-databasevarres=db.exec("SELECT * FROM one")-executesSELECTqueryandreturnsarrayofobjects
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()
alasql.promise('SELECT * FROM test').then(function(res){// Process data}).catch(function(err){// Process errors});
You can use compile statements:
varinsert=db.compile('INSERT INTO one (1,2)');insert();
You can use parameters in compiled and interpreted statements:
varinsert1=db.compile('INSERT INTO one (?,?)');varinsert2=db.compile('INSERT INTO one ($a,$b)');varinsert3=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:
varyears=[{yearid:2012},{yearid:2013},{yearid:2014},{yearid:2015},{yearid:2016},];varres=alasql.queryArray('SELECT * FROM ? AS years '+'WHERE yearid > ?',[years,2014]);// res == [2015,2016]
Work directly on JSON data and group JavaScript array 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);
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):
vardata=[[2014,1,1],[2015,2,1],[2016,3,1],[2017,4,2],[2018,5,3],[2019,6,3]];varres=alasql('SELECT SUM([1]) FROM ? d WHERE [0]>2016',[data]);
Use alasql.queryArrayOfArrays() function to return array of arrays. In this caseyou can specify array position of selected column with number or number in brackets:
varres=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:
// Same filtervarres1=alasql.queryArrayOfArrays('SELECT * FROM ? a WHERE [0]>2016',[data]);varres2=data.filter(function(a){returna[0]>2016});// Complex filter with aggregating, grouping and sortingvarres=alasql.queryArrayOfArrays('SELECT [2] AS 0, SUM([1]) AS 1 FROM ? a WHERE a[0]>? GROUP BY [0] ORDER BY [1]',[data,2016]);
Attach IndexedDB database, and then complex query on two joined tables and filtering:
alasql(’ATTACHINDEXEDDBDATABASEMyBase; \USEMyBase; \SELECTCity.* \FROMCity \JOINCountryUSINGCountryCode \WHERECountry.Continent=”Asia”’,[],function(res){console.log(res.pop());});
<scriptsrc="http://alasql.org/console/alasql.min.js"></script><divid="res"></div><scripttype="text/sql"id="sql">CREATETABLEpeople(IdINTPRIMARYKEY,FirstNameSTRING,LastNameSTRING);INSERTINTOpeopleVALUES(1,"Peter","Peterson"),(2,"Eric","Ericson"),(3,"John","Johnson");IFEXISTS(SELECT*FROMpeopleWHEREId=2)UPDATEpeopleSETFirstName="Roll",LastName="Rolson"WHEREId=2ELSEINSERTINTOpeopleVALUES(2,"Eric","Rollson");IFEXISTS(SELECT*FROMpeopleWHEREId=4)UPDATEpeopleSETFirstName="Roll",LastName="Rolson"WHEREId=4ELSEINSERTINTOpeopleVALUES(4,"Smith","Smithson");SELECT*INTOHTML("#res",{headers:true})FROMpeople;</script><script>alasql('SOURCE "#res"');</script>
Try this examplein jsFiddle
See alsothis slide for more inspiration
© 2014-2026,Andrey Gershun &Mathias Rangel Wulff
Please help improve the documentation by opening a PR on thewiki repo