alasql

- _AlaSQL is an unfunded open source project installed 650k+ times each month. [Please donate your time](https://github.com/AlaSQL/alasql/issues?q=is%3Aopen+label%3A%22Help+wanted%22+sort%3Aupdated-desc). We appreciate any and all contributions we can get._ - _Have a question? [Ask The AlaSQL Bot](https://chatgpt.com/g/g-XcBL24WTe-alasql-bot) or post on [Stack Overflow](http://stackoverflow.com/questions/ask?tags=AlaSQL)._ # AlaSQL

AlaSQL - _( [à la](http://en.wiktionary.org/wiki/%C3%A0_la) [SQL](http://en.wikipedia.org/wiki/SQL) ) [ælæ ɛskju:ɛl]_ - is an open source SQL database for JavaScript with a strong focus on query speed and data source flexibility for both relational data and schemaless data. It works in the web browser, Node.js, and mobile apps. This library is perfect for: - Fast in-memory SQL data processing for BI and ERP applications on fat clients - Easy ETL and options for persistence by data import / manipulation / export of several formats - All major browsers, Node.js, and mobile applications We focus on [speed](https://github.com/alasql/alasql/wiki/Speed) by taking advantage of the dynamic nature of JavaScript when building up queries. Real-world solutions demand flexibility regarding where data comes from and where it is to be stored. We focus on flexibility by making sure you can [import/export](https://github.com/alasql/alasql/wiki/Import-export) and query directly on data stored in Excel (both `.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, really - it's working towards a full database engine complying with [most of the SQL-99 language](https://github.com/alasql/alasql/wiki/Supported-SQL-statements), spiced up with additional syntax for NoSQL (schema-less) data and graph networks. #### Traditional SQL Table ```js /* create SQL Table and add data */ alasql('CREATE TABLE cities (city string, pop number)'); alasql("INSERT INTO cities VALUES ('Paris',2249975),('Berlin',3517424),('Madrid',3041579)"); /* execute query */ var res = alasql('SELECT * FROM cities WHERE pop < 3500000 ORDER BY pop DESC'); // res = [ { "city": "Madrid", "pop": 3041579 }, { "city": "Paris", "pop": 2249975 } ] ``` [Live Demo](https://jsfiddle.net/jqk80ard/) #### Array of Objects ```js 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](https://jsfiddle.net/8brvex4f/) #### Spreadsheet ```js // 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 ```js 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 be no surprise that proper use of indexes on your tables is essential for good performance.** #### Options AlaSQL has several [configuration options](https://github.com/AlaSQL/alasql/wiki/AlaSQL-Options) which change the behavior. It can be set via SQL statements or via the options object before using `alasql`. If you're using `NOW()` in queries often, setting `alasql.options.dateAsString` to `false` speed things up. It will just return a JS Date object instead of a string representation of a date. ## Installation ```bash 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](https://cdn.jsdelivr.net/npm/alasql) ```html ``` ## Getting started See the ["Getting started" section of the wiki](https://github.com/alasql/alasql/wiki/Getting%20started) More advanced topics are covered in other wiki sections like ["Data manipulation"](https://github.com/alasql/alasql/wiki/Data-manipulation) and in questions on [Stack Overflow](http://stackoverflow.com/questions/tagged/alasql) Other links: - Documentation: [Github wiki](https://github.com/alasql/alasql/wiki) - Library CDN: [jsDelivr.com](http://www.jsdelivr.com/#!alasql) - Feedback: [Open an issue](https://github.com/alasql/alasql/issues/new) - Try online: Playground - Website: [alasql.org](http://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](https://github.com/alasql/alasql/blob/develop/CONTRIBUTING.md) The AlaSQL project depends on your contribution of code and may have [bugs](https://github.com/alasql/alasql/labels/%21%20Bug). So please, submit any bugs and suggestions [as an issue](https://github.com/alasql/alasql/issues/new). Please check out the [limitations of the library](https://github.com/alasql/alasql#limitations). ## 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](https://github.com/alasql/alasql/wiki/Speed) ## 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](https://github.com/alasql/alasql/wiki/SQL%20keywords). ### 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: ```js 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](https://github.com/alasql/alasql/wiki/User-Defined-Functions) ### Compiled statements and functions ```js var ins = alasql.compile('INSERT INTO one VALUES (?,?)'); ins(1, 10); ins(2, 20); ``` See more [in the wiki](https://github.com/alasql/alasql/wiki/Compile) ### SELECT against your JavaScript data Group your JavaScript array of objects by field and count number of records in each group: ```js 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]); ``` See more ideas for creative data manipulation [in the wiki](https://github.com/alasql/alasql/wiki/Getting-started) ### 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 elements - `obj->(a*1)` - Access JavaScript functions - `obj->valueOf()` - Format query output with `SELECT VALUE, ROW, COLUMN, MATRIX` - Output nested objects with `INTO OBJECT()` - converts arrow notation columns back to nested structure - ES5 multiline SQL with `var SQL = function(){/*SELECT 'MY MULTILINE SQL'*/}` and pass instead of SQL string (will not work if you compress your code) #### Extracting Nested Properties with INTO OBJECT() When selecting nested properties using arrow notation (`->`), results are normally flattened with the arrow path as the key. Use `INTO OBJECT()` to restore the nested structure: ```js var data = [{name: 'Oslo', info: {country: 'Norway', population: 700000}}]; // Standard output (flattened) alasql('SELECT name, info->country FROM ?', [data]); // [{ "name": "Oslo", "info->country": "Norway" }] // With INTO OBJECT() (nested) alasql('SELECT name, info->country INTO OBJECT() FROM ?', [data]); // [{ "name": "Oslo", "info": { "country": "Norway" } }] ``` ### Read and write Excel and raw data files You can import from and export to CSV, TAB, TXT, and JSON files. File extensions can be omitted. Calls to files will always be asynchronous so multi-file queries should be chained: ```js var tabFile = 'mydata.tab'; alasql .promise([ "SELECT * FROM txt('MyFile.log') WHERE [0] LIKE 'M%'", // parameter-less query ['SELECT * FROM tab(?) ORDER BY [1]', [tabFile]], // [query, array of params] "SELECT [3] AS city,[4] AS population FROM csv('./data/cities')", "SELECT * FROM json('../config/myJsonfile')", ]) .then(function (results) { console.log(results); }) .catch(console.error); ``` ### Read SQLite database files AlaSQL can read (but not write) SQLite data files using [SQL.js](https://github.com/sql-js/sql.js) library: ```html ``` `sql.js` calls will always be asynchronous. ### AlaSQL works in the console - CLI The node module ships with an `alasql` command-line tool: ```bash $ npm install -g alasql ## install the module globally $ alasql -h ## shows usage information $ alasql "SET @data = @[{a:'1',b:?},{a:'2',b:?}]; SELECT a, b FROM @data;" 10 20 [ 1, [ { a: 1, b: 10 }, { a: 2, b: 20 } ] ] $ alasql "VALUE OF SELECT COUNT(*) AS abc FROM TXT('README.md') WHERE LENGTH([0]) > ?" 140 // Number of lines with more than 140 characters in README.md ``` [More examples are included in the wiki](https://github.com/alasql/alasql/wiki/AlaSQL-CLI) ## Features you might love ### AlaSQL ♥ D3.js AlaSQL plays nice with d3.js and gives you a convenient way to integrate a specific subset of your data with the visual powers of D3. See more about [D3.js and AlaSQL in the wiki](https://github.com/alasql/alasql/wiki/d3.js) ### AlaSQL ♥ Excel AlaSQL can export data to both [Excel 2003 (.xls)](https://github.com/alasql/alasql/wiki/XLS) and [Excel 2007 (.xlsx)](https://github.com/alasql/alasql/wiki/XLSX) formats with coloring of cells and other Excel formatting functions. ### AlaSQL ♥ Meteor Meteor is amazing. You can query directly on your Meteor collections with SQL - simple and easy. See more about [Meteor and AlaSQL in the wiki](https://github.com/alasql/alasql/wiki/Meteor) ### AlaSQL ♥ Angular.js Angular is great. In addition to normal data manipulation, AlaSQL works like a charm for exporting your present scope to Excel. See more about [Angular and AlaSQL in the wiki](https://github.com/alasql/alasql/wiki/Angular.js) ### 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 it one unit of work for fetching and identifying what's relevant. See more about [Google Maps and AlaSQL in the wiki](https://github.com/alasql/alasql/wiki/Google-maps) ### AlaSQL ♥ Google Spreadsheets AlaSQL can query data directly from a Google sprea