the hackernews 
AlaSQL is an unfunded open offer project place apart in 100k+ events every month. Please donate your time. We like any and all contributions we can obtain.
Possess a ask? Question on Stack Overflow the utilization of the “alasql” stamp.
Wondering whilst you must quiet beef as much as v3? You in all likelihood should always quiet, until you’re the utilization of WebSQL. Study the the breaking changes here
AlaSQL – ( à la SQL ) [ælæ ɛskju:ɛl] – is an open offer SQL database for JavaScript with a stable point of curiosity on ask tempo and files offer flexibility for every relational files and schemaless files. It in actuality works within the secure browser, Node.js, and cell apps.
This library is designed for:
Hasty in-memory SQL files processing for BI and ERP applications on full purchasers
Simple ETL and alternate choices for persistence by files import / manipulation / export of a variety of codecs
All predominant browsers, Node.js, and cell applications
We point of curiosity on tempo by taking beneficial thing about the dynamic nature of JavaScript when lift queries. Staunch-world alternate choices query flexibility concerning where files comes from and where it is to be kept. We point of curiosity on flexibility by making clear you may per chance well import/export and ask right away on files kept in Excel (each .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, in actuality – it is training a full database engine complying with most of the SQL-99 language, spiced up with extra syntax for NoSQL (schema-less) files and graph networks.
Oldschool SQL Desk
/produce SQL Desk and add files */
alasql(“CREATE TABLE cities (city string, pop number)”);
alasql(“INSERT INTO cities VALUES (‘Paris’,2249975),(‘Berlin’,3517424),(‘Madrid’,3041579)”);
/cease ask */
var res=alasql(“SELECT FROM cities WHERE pop <3500000 ORDER BY pop DESC”);
// res=[ { “city”: “Madrid”, “pop”: 3041579 }, { “city”: “Paris”, “pop”: 2249975 } ]
Live Demo
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]);
// res=[ { “a”: 1, “b”: 40},{ “a”: 2, “b”: 20 } ]
Live Demo
Spreadsheet
// file is read asynchronously (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);
});
Bulk Data Load
alasql(“CREATE TABLE example1 (a INT, b INT)”);
// alasql’s data store for a table can be assigned directly
alasql.tables.example1.data=[
{a:2,b:6},
{a:3,b:4}
];
// … or manipulated with normal SQL
alasql(“INSERT INTO example1 VALUES (1,5)”);
var res=alasql(“SELECT FROM example1 ORDER BY b DESC”);
console.log(res); // [{a:2,b:6},{a:1,b:5},{a:3,b:4}]
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.
Installation
yarn add alasql # yarn
npm install alasql # npm
npm install -g alasql # global install of command line tool
For the browsers: include alasql.min.js
Getting started
See the “Getting started” section of the wiki
More advanced topics are covered in other wiki sections like “Data manipulation” and in questions on Stack Overflow
Other links:
Documentation: Github wiki
Library CDN: jsDelivr.com
Feedback: Open an issue
Try online: Playground
Website: alasql.org
Please note
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
The AlaSQL project depends on your contribution of code and may have bugs. So please, submit any bugs and suggestions as an issue.
Please check out the limitations of the library.
Performance
AlaSQL is designed for speed and includes some of the classic SQL engine optimizations:
Queries are cached as compiled functions
Joined tables are pre-indexed
WHERE expressions are pre-filtered for joins
See more performance-related info on the wiki
Features 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. The wiki lists supported SQL statements and keywords.
User-Defined Functions in your SQL
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) {
return a*b+1;
};
var res=alasql(‘SELECT myfn(a,b) FROM one’);
You can also define your own aggregator functions (like your own SUM(…)). See more in the wiki
Compiled statements and functions
var ins=alasql.compile(‘INSERT INTO one VALUES (?,?)’);
ins(1,10);
ins(2,20);
See more in the wiki
SELECT against your JavaScript data
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[ælæ ɛskju:ɛl] AS b FROM ? GROUP BY a’, [data] );
See more ideas for creative data manipulation in the wiki
JavaScript Sugar
AlaSQL extends “good old” SQL to make it closer to JavaScript. The “sugar” includes:
Write Json objects – {a:’1′,b:@[‘1′,’2′,’3’]}
Access object properties – obj->property->subproperty
Access object and arrays intention – obj->(a*1)
Access JavaScript functions – obj->valueOf()
Layout ask output with SELECT VALUE, ROW, COLUMN, MATRIX
ES5 multiline SQL with var SQL=neutral(){/*SELECT ‘MY MULTILINE SQL’*/} and cross as a replacement of SQL string (is no longer going to work whilst you compress your code)
Read and write Excel and raw files files
You may per chance import from and export to CSV, TAB, TXT, and JSON files. File extensions may per chance moreover moreover be left out. Calls to files will frequently be asynchronous so multi-file queries should always be chained:
var tabFile=’mydata.tab’;
alasql.promise([
“SELECT FROM txt(‘MyFile.log’) WHERE [0] LIKE ‘M%'”, // parameter-less ask
[ “SELECT FROM tab(?) ORDER BY [1]”, [tabFile] ], // [query, array of params]
“SELECT [3] AS city,[4] AS population FROM csv(‘./files/cities’)”,
“SELECT FROM json(‘../config/myJsonfile’)”
]).then(neutral(results){
console.log(results);
}).protect(console.error);
Read SQLite database files
AlaSQL can read (but no longer write) SQLite files files the utilization of SQL.js library:
alasql([
‘ATTACH SQLITE DATABASE Chinook(“Chinook_Sqlite.sqlite”)’,
‘USE Chinook’,
‘SELECT FROM Genre’
]).then(neutral(res){
console.log(“Genres:”,res.pop());
});
sql.js calls will frequently be asynchronous.
AlaSQL works within the console – CLI
The node module ships with an alasql expose-line utility:
?” 140
// Preference of strains with better than 140 characters in README.md” dir=”auto”>$ npm set up -g alasql ## set up the module globally
$ alasql -h ## presentations utilization files
$ alasql “SET @files=@[{a:’1′,b:?},{a:’2′,b:?}]; SELECT a, b FROM @files;” 10 20
[ 1, [ { a: 1, b: 10 }, { a: 2, b: 20 } ] ]
$ alasql “VALUE OF SELECT COUNT[ælæ ɛskju:ɛl] AS abc FROM TXT(‘README.md’) WHERE LENGTH([0])> ?” 140
// Preference of strains with better than 140 characters in README.md
More examples are incorporated within the wiki
Choices you may per chance well cherish
AlaSQL ♥ D3.js
AlaSQL performs nice with d3.js and offers you a handy come to combine a roar subset of your files with the visible powers of D3. Watch extra about D3.js and AlaSQL within the wiki
AlaSQL ♥ Excel
AlaSQL can export files to each Excel 2003 (.xls) and Excel 2007 (.xlsx) codecs with coloring of cells and various Excel formatting functions.
AlaSQL ♥ Meteor
Meteor is unbelievable. You may per chance ask right away in your Meteor collections with SQL – easy and simple. Watch extra about Meteor and AlaSQL within the wiki
AlaSQL ♥ Angular.js
Angular is nice. As well to identical outdated files manipulation, AlaSQL works love a charm for exporting your present scope to Excel. Watch extra about Angular and AlaSQL within the wiki
AlaSQL ♥ Google Maps
Pinpointing files on a map should always be easy. AlaSQL is nice to prepare offer files for Google Maps from, as an illustration, Excel or CSV, making it one unit of labor for fetching and identifying what’s relevant. Watch extra about Google Maps and AlaSQL within the wiki
AlaSQL ♥ Google Spreadsheets
AlaSQL can ask files right away from a Google spreadsheet. A pleasant “partnership” for simple bettering and highly effective files manipulation. Watch extra about Google Spreadsheets and AlaSQL within the wiki
Cross over a feature?
Purchase charge and add your conception or vote for your accepted feature to be implemented:
Obstacles
Please undergo in mind that AlaSQL has bugs. Beside having some bugs, there are a different of boundaries:
AlaSQL has a (prolonged) checklist of keywords that wants to be escaped if feeble for column names. When selecting a area named key please write SELECT `key` FROM … as a replacement. Right here’s moreover the case for words love `price`, `read`, `depend`, `by`, `top`, `course`, `deleted`, `work` and `offset`. Please search the recommendation of the total checklist of keywords.
It’s OK to SELECT 1000000 data or to JOIN two tables with 10000 data in every (You may per chance notify streaming functions to work with longer datasources – stare take a look at/take a look at143.js) but undergo in mind that the workload is multiplied so SELECTing from better than 8 tables with magnificent 100 rows in every will existing infamous efficiency. Right here’s thought to be one of our top priorities to acquire better.
Puny efficiency for transactions (supports magnificent for localStorage) – Sorry, transactions are small, attributable to AlaSQL switched to extra complex come for handling PRIMARY KEYs / FOREIGN KEYs. Transactions may per chance be fully grew to change into on all over again in a future model.
A (FULL) OUTER JOIN and RIGHT JOIN of better than 2 tables is no longer going to develop anticipated results. INNER JOIN and LEFT JOIN are OK.
Please notify aliases in case you need fields with the identical title from various tables (SELECT a.id AS a_id, b.id AS b_id FROM ?).
In the intervening time AlaSQL would no longer work with JSZip 3.0.0 – please notify model 2.x.
JOINing a sub-SELECT would no longer work. Please notify a with structure (Example here) or obtain the sub-SELECT to a variable and cross it as an argument (Example here).
AlaSQL uses the FileSaver.js library for saving files within the community from the browser. Please undergo in mind that it would no longer place files in Safari 8.0.
There are potentially many others. Please befriend us fix them by submitting a local. Thanks!
How To
Exhaust AlaSQL to convert files from CSV to Excel
ETL instance:
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(neutral(res){
// results from the file asia.xlsx
});
Exhaust AlaSQL as a Net Worker
AlaSQL can bustle in a Net Worker. Please undergo in mind that all interaction with AlaSQL when running wants to be async.
From the browser thread, the browser obtain alasql-worker.min.js automagically uses Net Employees:
var arr=[{a:1},{a:2},{a:1}];
alasql([[‘SELECT FROM ?’,[arr]]]).then(neutral(files){
console.log(files);
});
Dwell Demo.
The everyday obtain alasql.min.js will notify Net Employees if alasql.worker() is known as:
alasql.worker();
alasql([‘SELECT VALUE 10’]).then(neutral(res){
console.log(res);
}).protect(console.error);
Dwell Demo.
From a Net Worker, you may per chance well import alasql.min.js with importScripts:
importScripts(‘alasql.min.js’);
Webpack, Browserify, Vue and React (Native)
When concentrating on the browser, a variety of code bundlers love Webpack and Browserify will prefer up modules you may per chance well no longer need.
Right here’s a checklist of modules that AlaSQL may per chance moreover require in certain environments or for certain parts:
Node.js
fs
secure
tls
place apart a query to
course
React Native
react-native
react-native-fs
react-native-obtain-blob
Vertx
vertx
Agonostic
XLSX/XLS beef up
cptable
jszip
xlsx
cpexcel
es6-promise
Webpack
There are a variety of the way to contend with AlaSQL with Webpack:
IgnorePlugin
Excellent in case you wish protect watch over which modules you wish import.
var IgnorePlugin= require(“webpack”).IgnorePlugin;
module.exports={
…
// Will ignore the modules fs, course, xlsx, place apart a query to, vertx, and react-native modules
plugins: [new IgnorePlugin(/(^fs$|cptable|jszip|xlsx|^es6-promise$|^net$|^tls$|^forever-agent$|^tough-cookie$|cpexcel|^path$|^request$|react-native|^vertx$)/)]
};
module.noParse
As of AlaSQL 0.3.5, you may per chance well simply give an explanation for Webpack no longer to parse AlaSQL, which avoids all of the dynamic require warnings and avoids the utilization of eval/clashing with CSP with script-loader.
Read the Webpack doctors about noParse
…
//Don’t parse alasql
{module:noParse: [/alasql/]}
script-loader
If each of the alternate choices above fail to meet your requirements, you may per chance well load AlaSQL with script-loader.
//Load alasql within the realm scope with script-loader
import “script!alasql”
It goes to predicament off concerns whilst you may per chance well moreover bear a CSP that doesn’t allow eval.
Browserify
Read up on with the exception of, ignoring, and shimming
Example (the utilization of with the exception of)
var browserify=require(“browserify”);
var b=browserify(“./predominant.js”).bundle();
//Will ignore the modules fs, course, xlsx
[“fs”,”path”,”xlsx”, … ].forEach(ignore=> { b.ignore(ignore) });
Vue
For some frameworks (lige Vue) alasql cant obtain right of entry to XLSX by it self. We propose handling it by including AlaSQL the next come:
import XLSX from ‘xlsx’;
alasql.utils.isBrowserify=spurious;
alasql.utils.world.XLSX=XLSX;
jQuery
Please undergo in mind to send the fashioned tournament, and no longer the jQuery tournament, for intention. (Exhaust tournament.originalEvent as a replacement of myEvent)
JSON-object
You may per chance notify JSON objects in your databases (cease no longer neglect notify==and !==operators for deep comparability of objects):
alasql> SELECT VALUE {a:’1′,b:’2′}
{a:1,b:2}
alasql> SELECT VALUE {a:’1′,b:’2′}=={a:’1′,b:’2′}
correct
alasql> SELECT VALUE {a:’1′,b:’2′}->b
2
alasql> SELECT VALUE {a:’1′,b:(2*2)}->b
4
Strive AlaSQL JSON objects in Console [sample](http://alasql.org/console?fall table if exists one;produce table one;insert into one values {a:@[1,2,3],c:{e: 23}}, {a:@[{b:@[1,2,3]}]};utilize from one)
Experimental
Worthwhile stuff, but there may per chance be seemingly to be dragons
Graphs
AlaSQL is a multi-paradigm database with beef up for graphs that may per chance moreover moreover be searched or manipulated.
// Who loves lovers of Alice?
var res=alasql(‘SEARCH / ANY(>>>> #Alice) title’);
console.log(res) // [‘Olga’,’Helen’]
Watch extra within the wiki
localStorage and DOM-storage
You may per chance notify browser localStorage and DOM-storage as a data storage. Right 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.Metropolis (city string, population number)’);
alasql(‘SELECT INTO MyAtlas.Metropolis FROM ?’,[ [
{city:’Vienna’, population:1731000},
{city:’Budapest’, population:1728000}
] ]);
var res=alasql(‘SELECT FROM MyAtlas.Metropolis’);
Strive this sample in jsFiddle. Flee this sample
two or three events, and AlaSQL retailer extra and extra files in localStorage. Right here, “Atlas” is
the title of localStorage database, where “MyAtlas” is a memory AlaSQL database.
You may per chance notify localStorage in two modes: SET AUTOCOMMIT ON to immediate place files
to localStorage after every assertion or SET AUTOCOMMIT OFF. On this case, you need
to make notify of COMMIT assertion to place all files from in-memory replicate to localStorage.
Plugins
AlaSQL supports plugins. To set up a plugin it goes to be essential to make notify of the REQUIRE assertion. Watch extra within the wiki
Alaserver – easy database server
Yes, you may per chance well even notify AlaSQL as a in actuality easy server for assessments.
To bustle enter the expose:
then open http://127.0.0.1: 1337/?SELECT%20VALUE%20(2*2) in your browser
Warning: Alaserver is no longer multi-threaded, no longer concurrent, and no longer secured.
Tests
Regression assessments
AlaSQL for the time being has over 1200 regression assessments, but they only duvet
of the codebase.
AlaSQL uses mocha for regression assessments. Set up mocha and bustle
or open take a look at/index.html for in-browser assessments (Please back thru localhost with, as an illustration, http-server).
Tests with AlaSQL ASSERT from SQL
You may per chance notify AlaSQL’s ASSERT operator to test the outcomes of outdated 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 its compatibility with SQL-99. The assessments encompass about 2 million queries and statements.
The testruns may per chance moreover moreover be chanced on within the testlog.
Contributing
Watch Contributing for little print.
Ensuing from all of the oldsters that already contributed!
License
MIT – stare MIT licence files
Most predominant contributors
Andrey Gershun
Mathias Rangel Wulff
AlaSQL is an OPEN Initiating Source Project. This come that:
Folk making essential and treasured contributions are given commit-obtain right of entry to to the project to make a contribution as they stare fit. This project is extra love an open wiki than a dilapidated guarded open offer project.
We like any and all contributions we can obtain. Whenever you feel love contributing, bear a stare at CONTRIBUTING.md
Credit
Many thanks to:
Zach Carter for Jison parser-generator
Andrew Kent for JS SQL Parser
Eli Grey for FileSaver.js
SheetJS for JS XLSX Library
and various folks for functional instruments, which obtain our work considerable more straightforward.
Linked initiatives that bear inspired us
AlaX – Export to Excel with colours and codecs
AlaMDX – JavaScript MDX OLAP library (work in progress)
Other identical initiatives – checklist of databases on JavaScript
© 2014-2023, Andrey Gershun (agershun@gmail.com) & Mathias Rangel Wulff (m@rawu.dk)
