AlaSQL.js - JavaScript SQL database for browser and Node.js. Handles both traditional relational tables and nested JSON data (NoSQL). Export, store, and import data from localStorage, IndexedDB, or Excel.

Related tags

Database alasql
Overview

Please use version 1.x as prior versions has a security flaw if you use user generated data to concat your SQL strings instead of providing them as a parameters to AlaSQL.

AlaSQL is an open source project used on more than two million page views per month - and we appreciate any and all contributions we can get. Please help out.

Have a question? Ask on Stack Overflow using the "alasql" tag.

CI-test NPM downloads OPEN open source software Release Stars Average time to resolve an issue Coverage CII Best Practices code style: prettier FOSSA Status

AlaSQL

AlaSQL logo

AlaSQL - ( à la 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 designed 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 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 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, spiced up with additional syntax for NoSQL (schema-less) data and graph networks.

Traditional SQL Table

/* 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

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 installation for command line tools

For the browser: include alasql.min.js

<script src="https://cdn.jsdelivr.net/npm/[email protected]"></script>

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:

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(*) 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 elements - obj->(a*1)

  • Access JavaScript functions - obj->valueOf()

  • Format query output with SELECT VALUE, ROW, COLUMN, MATRIX

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

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:

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 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'
    ]).then(function(res){
        console.log("Genres:",res.pop());
    });
</script>

sql.js calls will always be asynchronous.

AlaSQL works in the console - CLI

The node module ships with an alasql command-line tool:

$ 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

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

AlaSQL ♥ Excel

AlaSQL can export data to both Excel 2003 (.xls) and Excel 2007 (.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

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

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

AlaSQL ♥ Google Spreadsheets

AlaSQL can query data directly from a Google spreadsheet. A good "partnership" for easy editing and powerful data manipulation. See more about Google Spreadsheets and AlaSQL in the wiki

Miss a feature?

Take charge and add your idea or vote for your favorite feature to be implemented:

Feature Requests

Limitations

Please be aware that AlaSQL has bugs. Beside having some bugs, there are a number of limitations:

  1. AlaSQL has a (long) list of keywords that must be escaped if used for column names. When selecting a field named key please write SELECT `key` FROM ... instead. This is also the case for words like `value`, `read`, `count`, `by`, `top`, `path`, `deleted`, `work` and `offset`. Please consult the full list of keywords.

  2. It is OK to SELECT 1000000 records or to JOIN two tables with 10000 records in each (You can use streaming functions to work with longer datasources - see test/test143.js) but be aware that the workload is multiplied so SELECTing from more than 8 tables with just 100 rows in each will show bad performance. This is one of our top priorities to make better.

  3. Limited functionality for transactions (supports only for localStorage) - Sorry, transactions are limited, because AlaSQL switched to more complex approach for handling PRIMARY KEYs / FOREIGN KEYs. Transactions will be fully turned on again in a future version.

  4. A (FULL) OUTER JOIN and RIGHT JOIN of more than 2 tables will not produce expected results. INNER JOIN and LEFT JOIN are OK.

  5. Please use aliases when you want fields with the same name from different tables (SELECT a.id AS a_id, b.id AS b_id FROM ?).

  6. At the moment AlaSQL does not work with JSZip 3.0.0 - please use version 2.x.

  7. JOINing a sub-SELECT does not work. Please use a with structure (Example here) or fetch the sub-SELECT to a variable and pass it as an argument (Example here).

  8. AlaSQL uses the FileSaver.js library for saving files locally from the browser. Please be aware that it does not save files in Safari 8.0.

There are probably many others. Please help us fix them by submitting an issue. Thank you!

How To

Use AlaSQL to convert data from CSV to Excel

ETL example:

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(function(res){
    // results from the file asia.xlsx
});

Use AlaSQL as a Web Worker

AlaSQL can run in a Web Worker. Please be aware that all interaction with AlaSQL when running must be async.

From the browser thread, the browser build alasql-worker.min.js automagically uses Web Workers:

<script src="alasql-worker.min.js"></script>
<script>
var arr = [{a:1},{a:2},{a:1}];

alasql([['SELECT * FROM ?',[arr]]]).then(function(data){
    console.log(data);
});
</script>

Live Demo.

The standard build alasql.min.js will use Web Workers if alasql.worker() is called:

<script src="alasql.min.js"></script>
<script>
alasql.worker();
alasql(['SELECT VALUE 10']).then(function(res){
    console.log(res);
}).catch(console.error);
</script>

Live Demo.

From a Web Worker, you can import alasql.min.js with importScripts:

importScripts('alasql.min.js');

Webpack, Browserify, Vue and React (Native)

When targeting the browser, several code bundlers like Webpack and Browserify will pick up modules you might not want.

Here's a list of modules that AlaSQL may require in certain environments or for certain features:

  • Node.js
    • fs
    • net
    • tls
    • request
    • path
  • React Native
    • react-native
    • react-native-fs
    • react-native-fetch-blob
  • Vertx
    • vertx
  • Agonostic
    • XLSX/XLS support
      • cptable
      • jszip
      • xlsx
      • cpexcel
    • es6-promise

Webpack

There are several ways to handle AlaSQL with Webpack:

IgnorePlugin

Ideal when you want to control which modules you want to import.

var IgnorePlugin =  require("webpack").IgnorePlugin;

module.exports = {
  ...
  // Will ignore the modules fs, path, xlsx, request, 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 can simply tell Webpack not to parse AlaSQL, which avoids all the dynamic require warnings and avoids using eval/clashing with CSP with script-loader. Read the Webpack docs about noParse

...
//Don't parse alasql
{module:noParse:[/alasql/]}
script-loader

If both of the solutions above fail to meet your requirements, you can load AlaSQL with script-loader.

//Load alasql in the global scope with script-loader
import "script!alasql"

This can cause issues if you have a CSP that doesn't allow eval.

Browserify

Read up on excluding, ignoring, and shimming

Example (using excluding)

var browserify = require("browserify");
var b = browserify("./main.js").bundle();
//Will ignore the modules fs, path, xlsx
["fs","path","xlsx",  ... ].forEach(ignore => { b.ignore(ignore) });

Vue

For some frameworks (lige Vue) alasql cant access XLSX by it self. We recommend handeling it by including AlaSQL the following way:

import XLSX from 'xlsx';
alasql.utils.isBrowserify = false;
alasql.utils.global.XLSX = XLSX;

jQuery

Please remember to send the original event, and not the jQuery event, for elements. (Use event.originalEvent instead of myEvent)

JSON-object

You can use JSON objects in your databases (do not forget use == and !== operators for deep comparison 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)

Experimental

Useful stuff, but there might be dragons

Graphs

AlaSQL is a multi-paradigm database with support for graphs that can be searched or manipulated.

// Who loves lovers of Alice?
var res = alasql('SEARCH / ANY(>> >> #Alice) name');
console.log(res) // ['Olga','Helen']

See more in the wiki

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

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.

Plugins

AlaSQL supports plugins. To install a plugin you need to use the REQUIRE statement. See more in the wiki

Alaserver - simple database server

Yes, you can even use AlaSQL as a very simple server for tests.

To run enter the command:

$ alaserver

then open http://127.0.0.1:1337/?SELECT%20VALUE%20(2*2) in your browser

Warning: Alaserver is not multi-threaded, not concurrent, and not secured.

Tests

Regression tests

AlaSQL currently has over 1200 regression tests, but they only cover Coverage of the codebase.

AlaSQL uses mocha for regression tests. Install mocha and run

$ npm test

or open test/index.html for in-browser tests (Please serve via localhost with, for example, http-server).

Tests with AlaSQL ASSERT from SQL

You can use AlaSQL's ASSERT operator to test the 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 its compatibility with SQL-99. The tests include about 2 million queries and statements.

The testruns can be found in the testlog.

Bleeding Edge

If you want to try the most recent development version of the library please download this file or visit the testbench to play around in the browser console.

License

MIT - see MIT licence information

Main contributors

AlaSQL is an OPEN Open Source Project. This means that:

Individuals making significant and valuable contributions are given commit-access to the project to contribute as they see fit. This project is more like an open wiki than a standard guarded open source project.

We appreciate any and all contributions we can get. If you feel like contributing, have a look at CONTRIBUTING.md

Credits

Many thanks to:

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

AlaSQL logo © 2014-2018, Andrey Gershun ([email protected]) & Mathias Rangel Wulff ([email protected])

Comments
  • Bithound.com and Travis-Ci

    Bithound.com and Travis-Ci

    @mathiasrw You just added a the special config file for http://bithound.com.

    Do we need something similar for https://travis-ci.org/ ?

    Unfortunately, I have never worked with these services. Will learn it.

    ! For discussion Related to: Code quality 
    opened by agershun 60
  • sqllogictest

    sqllogictest

    alaSQL src should incooperate test cases from sqllogictest [1] to identify areas where alaSQL does not comply with expectations to traditional SQL statements.

    The goal (for now) is not to pass all the tests, but to help us describe (in the documentation) what alaSQL does not support - and by that identify areas to improve for the alaSQL library.

    [1] https://github.com/oriherrnstadt/SQLlogictest/tree/master/test

    ! Bug ! Feature request Solved: Please test Related to: Tests 
    opened by mathiasrw 50
  • Use in meteor

    Use in meteor

    You might want to include in your documentation something like this for Meteor users:

    "Use SQL in Meteor client-side querys.

    Let's face it, Mongo sucks on common data operations, so having the ability to use SQL to query data (with JOINS, GRUP BY and so on) would relief a lot of pain.With alaSQL you can do something like this in your helper:

    productsSold:function(){
 var customerSalesHistory=salesHistory.find({customerId:Session.get('currentCustomer')}).fetch();
   var items=products.find().fetch();
   return alasql("select items.name, sales.ordered as quantity from ? sales, ? items
    where items.Id=sales.itemId",[customerSalesHistory,items]);
 }

    " Also, you might want to create the json to make alaSql a meteor package. Meteor users can only use minimongo, so there is a real need of a reactive way to make reactive querys on teh client. Also, minimongo is not persistent, while I see that alaSql is. So for Meteor users, alaSql is the best thing ever.

    ! Feature request ! For discussion Solved: Please test 
    opened by vchigne 49
  • ATTACH SQLITE DATABASE: Create a SQLite database which alaSQL can read

    ATTACH SQLITE DATABASE: Create a SQLite database which alaSQL can read

    Hello alaSQL team,

    The main issue is that we cannot create a working example for the "ATTACH SQLITE DATABASE" method. We would appreciate a working example.

    Among others we have tried code below. It uses sql.js but that is not a real requirement for us. Plain javascript + alaSQL would also do. The main purpose is to have the code working in iOS (iPhone) and Android.

    //Create the database
var db = new SQL.Database(sqlTest01.sqlite);
// Run a query without reading the results
db.run("CREATE TABLE test (col1, col2);");
// Insert two rows: (1,111) and (2,222)
db.run("INSERT INTO test VALUES (?,?), (?,?)", [1,111,2,222]);


alasql('ATTACH SQLITE DATABASE sqlTest01("sqlTest01.sqlite");\
    USE sqlTest01; \
    SELECT * FROM test',[],function(res){
        console.log("result:",res.pop());
});

    Kind regards, Mario

    ! Question 
    opened by MarioVanDenEijnde 45
  • Is alaSql work in Angular2?

    Is alaSql work in Angular2?

    Hi, I want to use alasql in my angular2 project, i am using angular-cli and install alasql through npm. When i am trying to add alasql in my component it is giving following exception "node_module/alasql/dist/alasql.d.ts' is not a module. Please contact the package author to update the package definition".

    Please let me know if you have any suggestion/work around to work in angular2.
    Thanks in Advance.

    ! Question 
    opened by NitinTiwari1010 41
  • Alasql in browserify setup in the browser

    Alasql in browserify setup in the browser

    When doing importing or exporting of files (e.g. Excel) there is always an error like Uncaught TypeError: fs.writeFileSync is not a function

    I use alasql with browserify in the browser.

    I think it can be fixed by changing the checks like if (typeof exports === 'object') to if (typeof exports === 'object' && process.browser !== true)

    Maybe there is a better solution?

    ! Feature request Solved: Please test 
    opened by nowzig 34
  • FileStorage Issue on createTable

    FileStorage Issue on createTable

    The FS.createTable method is returning the error: alasql.js:16591 Uncaught TypeError: Cannot read property 'products' of undefined

    My code:

    alasql('CREATE FILESTORAGE DATABASE test1("./test1.json")');
alasql('ATTACH FILESTORAGE DATABASE test1("./test1.json")');
alasql('USE test1');
alasql("CREATE TABLE IF NOT EXISTS products (id INT, category_id INT, name string, created_at DATE)");
alasql("INSERT INTO products (category_id, name, created_at) VALUES (?,?,?,?)", [1, 2, 'XYZ', new Date()]);

    Line that throws the error: (var tb = db.data[tableid];). Is db.data[tableid]; not defined for some reason?

    This also occurs without the "IF NOT EXISTS".

    Is this a bug on your end, or is my code incorrect? (It happens with https://github.com/agershun/alasql/blob/324a8c07ee5739477779e7bae222f3f9ef17ee21/test/cordova/test1.js aswell).

    ! Bug Solved: Please test Bug: Code provided to reproduced 
    opened by nbdamian 31
  • alasql query to parse a nested json array value and exporting it to excel

    alasql query to parse a nested json array value and exporting it to excel

    below is the snippet to replicate... Need a query in alasql to query the nested values in the array to export in xls.

    0:{reservationId: 61, reservationName: "reseravtion name", startDate: "02/05/2014", endDate: "07/08/2014", PromoStrCount: 8, …}
	PromoStrCount:8
	endDate:"07/08/2014"
	promos:(3) [{…}, {…}, {…}]
		0:{promoId: 73525, promoName: "reservaton name", promostartDate: "02/05/2014", promoendDate: "02/09/2014", StrCount: 4, …}
			0:
			StrCount:4
			locations:(4) [{…}, {…}, {…}, {…}]
				0:{fixtureId: 95580, storeNbr: 3, storeName: "store name", reservationId: 61, reservationName: "reservation name", …}
					0:
					crossAdjacencyDesc:"FLOORING"
					first_date_Available:null
					fixtureEffDate:null
					fixtureEndDate:null
					fixtureId:95580
					height:60
					storeNbr:3
					t1573FixtureId:0
				1:{fixtureId: 96209, storeNbr: 56, storeName: "LOWE'S OF NORWALK, CA  ", reservationId: 61, reservationName: "reservation name", …}
				2:{fixtureId: 99410, storeNbr: 598, storeName: "LOWE'S OF GREENVILLE, NC ", reservationId: 61, reservationName: "reservation name", …}
				3:{fixtureId: 89888, storeNbr: 1700, storeName: "LOWE'S OF N. FONTANA, CA ", reservationId: 61, reservationName: "reservation name", …}
			promoId:73525
			promoName:"promo name"
			promoendDate:"02/09/2014"
			promostartDate:"02/05/2014"
		1:{promoId: 78414, promoName: "promo name ", promostartDate: "02/10/2014", promoendDate: "07/08/2014", StrCount: 2, …}
		2:{promoId: 78415, promoName: "promo name ", promostartDate: "02/10/2014", promoendDate: "07/08/2014", StrCount: 2, …}
	reservationId:61
	reservationName:"reservation name "
	startDate:"02/05/2014"

    I need to fetch the below data

    ! Question 
    opened by itzmearun 26
  • Group/merge inclusive nested

    Group/merge inclusive nested

    Hello ALASQL team,

    Can you help me with another challenge?

    I have the following arrays:

    res1: [{"Title":"Normen","Id":5},{"Title":"Agenda","Id":1},{"Title":"Lijsten","Id":3}]
res2: [{"Title":"Lijst Constructies Beheer","ParentId":3},{"Title":"MateriaalTechnische Normen","ParentId":5},{"Title":"Lijst Constructies","ParentId":3},{"Title":"MateriaalTechnische Normen Beheer","ParentId":5}]

    And I need the following result:

    res3: [{"Title":"Normen","Id":5,"children":[{"Title":"MateriaalTechnische Normen","ParentId":5},{"Title":"MateriaalTechnische Normen Beheer","ParentId":5}]},{"Title":"Agenda","Id":1},{"Title":"Lijsten","Id":3,"children":[{"Title":"Lijst Constructies Beheer","ParentId":3},{"Title":"Lijst Constructies","ParentId":3}]}]

    Kind regards, Mario

    ! Question Solved: Please test 
    opened by MarioVanDenEijnde 26
  • Querying JSON that looks like an array

    Querying JSON that looks like an array

    Hi,

    I'm trying to built a query that will match against an array value. I think I have my quoting wrong.

    I generate alasql_including_query.js to become:

    //var alasql = require('/usr/local/lib/node_modules/alasql/alasql.min.js');
if(typeof exports === 'object') {
        var assert = require("assert");
        var alasql = require('/usr/local/lib/node_modules/alasql/alasql.min.js');   // technically a straight require("alasql") should work
} else {
        __dirname = '.';
};

alasql.fn.myDuration = function(secs) {
    return Math.floor(secs/60);
}

function stringify_alasql(query) {
        alasql(query,[],function(res){
                  console.log(JSON.stringify(res));
        })
}
var result ="SELECT projects, path, application, myDuration([duration]) as minutes             FROM json(\"\")             WHERE startDate = \"2015-10-07, 10:00 AM\"             AND projects = \"[\\\"4. Client Work/4. Client: XX [MCAB-9]\\\"]\"             GROUP BY projects, minutes, path, application, duration             ;"
stringify_alasql(result)

    Where timing_output looks like:

    [
  {
    "path" : "https:\/\/...\/display\/ATL\/Architecture+Approach+1",
    "projects" : [
      "4. Client Work\/4. Client: XX [MCAB-9]"
    ],
    "endDate" : "2015-10-07, 10:00 AM",
    "startDate" : "2015-10-07, 9:00 AM",
    "application" : "Safari",
    "duration" : 89.99672394990921
  },
  {
    "path" : "https:\/\/...\/display\/ATL\/Atlassian+Enterprise+Architecture",
    "projects" : [
      "4. Client Work\/4. Client: XX [MCAB-9]"
    ],
    "endDate" : "2015-10-07, 10:00 AM",
    "startDate" : "2015-10-07, 9:00 AM",
    "application" : "Safari",
    "duration" : 67.80467706918716
  },
  {
    "path" : "https:\/\/...\/pages\/editpage.action\/?pageId=41625335",
    "projects" : [
      "4. Client Work\/4. Client: XX [MCAB-9]"
    ],
    "endDate" : "2015-10-07, 10:00 AM",
    "startDate" : "2015-10-07, 9:00 AM",
    "application" : "Safari",
    "duration" : 81.00990098714828
  },
  {
    "path" : "\/Volumes\/Storage\/martincleaver\/SoftwareDevelopment\/newchoir_sets\/playlist_builder.rb",
    "projects" : [
      "0. Ambiguous\/X. Ambiguous: Software Development",
      "D. Timesuck: Newchoir"
    ],
    "endDate" : "2015-10-07, 9:00 PM",
    "startDate" : "2015-10-07, 8:00 PM",
    "application" : "RubyMine",
    "duration" : 269.991591989994
  },
  {
    "path" : "http:\/\/...\/display\/AR\/2015\/10\/07\/Playlist+Builder+run+for+2015-10-07-20-43-24",
    "projects" : [
      "D. Timesuck: Newchoir"
    ],
    "endDate" : "2015-10-07, 9:00 PM",
    "startDate" : "2015-10-07, 8:00 PM",
    "application" : "Safari",
    "duration" : 60.54301196336746
  }
]

    When I run:

    $ cat "/tmp/timing_output" | node '/tmp/alasql_including_query.js'

    I get: [{"minutes":null}]

    If I omit my where clause I do get results, so I can only conclude that I am quoting the WHERE clause incorrectly.

    I would appreciate any help!

    ! Question 
    opened by mrjcleaver 26
  • AUTO_INCREMENT and LocalStorage

    AUTO_INCREMENT and LocalStorage

    When using localstorage, AUTO_INCREMENT doesn't seem to function, as though without specifying a storage method it does work, is this intended behaviour or am I implementing it incorrect?

    my implementation of A_I with localstorage: http://jsfiddle.net/gym7L42r/

    ! Bug Help wanted Related to: SQL compliance 
    opened by error39 25
  • Timezone challenge for test408.js

    Timezone challenge for test408.js

    Not sure of how to solve this. haven't dig into this one so much yet. maybe anyone else know of any solution. or maybe it should just be skipped all together in browsers?

    it seems to currently be dependent on setting https://github.com/AlaSQL/alasql/blob/484a4dbae28c2418f0581eca160ad149ac5fb0ba/test/test408.js#L6-L8 but only in NodeJS where process exist. it seems to do some magic that i did not know about was a thing that you could do

    either way... i live in a timezone with +1 so the result was incorrect with an 1hour offset from the expected result maybe there is some js solution to encode things to utc if that's the intended format?

    anyway. it blocks #1586 for now. don't know if it should be put in as something NodeJS specific.

    Help wanted Related to: Code quality Good first issue 
    opened by jimmywarting 1
  • Get CI/CD to run browser test

    Get CI/CD to run browser test

    with #1585 in mind we should be able to cook something up that automatically runs all universal test + browser specifics in a real browser. I don't know how yet. puppeteer, airtap, karma is just a few examples of things that can execute stuff in a browser.

    opened by jimmywarting 4
  • divide test into env. specific folders

    divide test into env. specific folders

    ...Makes it easier to run test file that are NodeJS or Browser specific and can't run in any other env.

    /test
|-- nodejs           <-- NodeJS specific 
|   |-- test001.js   <-- fs test
|-- browsers         <-- browser specific 
|   |-- test002.js   <-- indexeddb test 
|   |-- test006.js   <-- localstorage test
|-- test003.js       <-- universal
|-- test004.js       <-- universal

    After fixing this then we could perhaps remove the DOM-storage (aka local/session-storage) dependency and only have that running inside browser with proper CI/CD pipeline.

    opened by jimmywarting 0
  • Examples and Courses doesn't work on a Website

    Examples and Courses doesn't work on a Website

    It seems that the main menu "Examples" and "Courses" is something left from an old website or something that got deleted. Because they don't work.

    Another issue is that:

    https://mc.yandex.ru/metrika/tag.js

    file doesn't load because of an invalid Cert.

    opened by jcubic 0
  • query from indexeddb that has no `keyPath` (columns) returns weird data

    query from indexeddb that has no `keyPath` (columns) returns weird data

    tried executing this:

    await alasql.promise(`SELECT * FROM [table_name]`)

    ...on a existing indexeddb table that have no keyPaths. the result looked kind of like:

    {0: '1', 1: '0', 2: '4', 3: '4', 4: '4', 5: '1', 6: '1', 7: '0', 8: '1' ... }

    just looking at the table in chrome devtool shows that it is just a key value pair without an object image

    Help wanted ! Question Regarding: IndexedDB Related to: Code quality Good first issue 
    opened by jimmywarting 2
Releases(v2.5.1)
Owner
Andrey Gershun
Andrey Gershun
GitHub http://alasql.org
Azure Data Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux.

Azure Data Studio is a data management tool that enables working with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux.

Microsoft 7k Dec 31, 2022
Fast and advanced, document based and key-value based NoSQL database that able to work as it is installed.

About Fast and advanced, document based and key-value based NoSQL database that able to work as it is installed. Features NoSQL database Can be run as

null 6 Dec 7, 2022
Fast and advanced, document-based and key-value-based NoSQL database.

Contents About Features Installation Links About Fast and advanced, document-based and key-value-based NoSQL database. Features NoSQL database Can be

null 6 Dec 7, 2022
A simple UI client for most SQL Engines written in Electron. It is compatible with Windows, Mac, Ubuntu / Debian and Redhat. It supports most dialects of RMBDs like MySQL, Microsoft SQL Server, Postgres, SQLite and has limited supports for Cassandra, MongoDB and Redis.

sqlui-native sqlui-native is a simple UI client for most SQL Engines written in Electron. It is compatible with most desktop OS's and support most dia

Sy Le 49 Jan 1, 2023
Connect to private Google Cloud SQL instance through Cloud SQL Auth Proxy running in Kubernetes.

⛅ google-cloud-sql A CLI app which establishes a connection to a private Google Cloud SQL instance and port-forwards it to a local machine. Connection

Dinko Osrecki 10 Oct 16, 2022
A JSON Database that saves your Json data in a file and makes it easy for you to perform CRUD operations.

What is dbcopycat A JSON Database that saves your Json data in a file and makes it easy for you to perform CRUD operations. ⚡️ Abilities Creates the f

İsmail Can Karataş 13 Jan 8, 2023
Object Relational Mapping

Object Relational Mapping This package is not actively maintained If you're starting a new project, please consider using one of the following instead

Diogo Resende 3.1k Dec 14, 2022
⚡️ lowdb is a small local JSON database powered by Lodash (supports Node, Electron and the browser)

Lowdb Small JSON database for Node, Electron and the browser. Powered by Lodash. ⚡ db.get('posts') .push({ id: 1, title: 'lowdb is awesome'}) .wri

null 18.9k Dec 30, 2022
Database manager for MySQL, PostgreSQL, SQL Server, MongoDB, SQLite and others. Runs under Windows, Linux, Mac or as web application

Database manager for MySQL, PostgreSQL, SQL Server, MongoDB, SQLite and others. Runs under Windows, Linux, Mac or as web application

DbGate 2k Dec 30, 2022
Run SPARQL/SQL queries directly on Virtuoso database with connection pool support.

?? virtuoso-connector Package that allows you to create a direct connection to the Virtuoso database and run queries on it. Connection can be used to

Tomáš Dvořák 6 Nov 15, 2022
Database driven code generation for ts-sql-query

ts-sql-codegen This utility generates table mapper classes for ts-sql-query by inspecting a database through tbls. While it is possible to use ts-sql-

Lorefnon 12 Dec 12, 2022
A typesafe database ORM that exposes the full power of handwritten sql statements to the developer.

TORM A typesafe database ORM that exposes the full power of handwritten sql statements to the developer. import { torm, z } from 'https://deno.land/x/

Andrew Kaiser 15 Dec 22, 2022
ORM for TypeScript and JavaScript (ES7, ES6, ES5). Supports MySQL, PostgreSQL, MariaDB, SQLite, MS SQL Server, Oracle, SAP Hana, WebSQL databases. Works in NodeJS, Browser, Ionic, Cordova and Electron platforms.

TypeORM is an ORM that can run in NodeJS, Browser, Cordova, PhoneGap, Ionic, React Native, NativeScript, Expo, and Electron platforms and can be used

null 30.1k Jan 3, 2023
Bluzelle is a smart, in-memory data store. It can be used as a cache or as a database.

SwarmDB ABOUT SWARMDB Bluzelle brings together the sharing economy and token economy. Bluzelle enables people to rent out their computer storage space

Bluzelle 225 Dec 31, 2022
a Node.JS script to auto-import USB drives that are attached to a computer. Use it to turn your NAS into a smart photo / file importer.

File Vacuum 5000 ⚠️ ⚠️ ⚠️ ⚠️ ⚠️ ⚠️ ⚠️ ⚠️ ⚠️ ⚠️ ⚠️ ⚠️ WARNING: This script is designed to manipulate files on both an external drive and another specif

null 46 Jan 10, 2022
Microsoft-store - Microsoft Store package for LTSC.

Microsoft Store Microsoft Store package for Windows LTSC. Usage Just download the release and double click the exe file. Can be used in Windows LTSC 2

fernvenue 7 Jan 2, 2023
DolphinDB JavaScript API is a JavaScript library that encapsulates the ability to operate the DolphinDB database, such as: connecting to the database, executing scripts, calling functions, uploading variables, etc.

DolphinDB JavaScript API English | 中文 Overview DolphinDB JavaScript API is a JavaScript library that encapsulates the ability to operate the DolphinDB

DolphinDB 6 Dec 12, 2022
sqlite3 in ur indexeddb

This is an absurd project. It implements a backend for sql.js (sqlite3 compiled for the web) that treats IndexedDB like a disk and stores data in bloc

James Long 3.6k Jan 4, 2023
sqlite3 in ur indexeddb

This is an absurd project. It implements a backend for sql.js (sqlite3 compiled for the web) that treats IndexedDB like a disk and stores data in bloc

James Long 3.6k Dec 30, 2022