How to do SQL-like queries in client side browser? How to do SQL-like queries in client side browser? sql sql

How to do SQL-like queries in client side browser?


Take a look at http://linqjs.codeplex.com/

It easily meets all your requirements.


As long as the data can fit in memory as an array of objects, you can just use sort and filter. For example, say you want to filter products. You want to find all products either below $5 or above $100 and you want to sort by price (ascending), and if there are two products with the same price, sort by manufacturer (descending). You could do that like this:

var results = products.filter(function(product) {    // price is in cents    return product.price < 500 || product.price > 10000;});results.sort(function(a, b) {    var order = a.price - b.price;    if(order == 0) {        order = b.manufacturer.localeCompare(a.manufacturer);    }    return order;});

For cross-browser compatibility, just shim filter.


Try Alasql.js. This is a javascript client-side SQL database.

You can do complex queries with joins and grouping, even optimization of joins and where parts. It does not use WebSQL.

Your requirements support:

  • Wide browser compatibility - all modern versions of browsers, including mobiles.
  • Can sort on more than one column.- Alasql does it with ORDER BY clause.
  • Can filter results. - with WHERE clause.
  • Must work completely client side so the user only needs to download a large set of data once and can cut the data however they want without constantly fetching data from the server and would in fact be able to do all queries offline after the initial pull. - you can use pure JavaScript (Array.push(), etc.) operations to modify data (do not forget to set table.dirty flag).

Here is a simple example ( play with it in jsFiddle ):

// Fill table with datavar person = [     { name: 'bill' , sex:'M', income:50000 },    { name: 'sara' , sex:'F', income:100000 },    { name: 'larry' , sex:'M', income:90000 },    { name: 'olga' , sex:'F', income:85000 },];// Do the queryvar res = alasql("SELECT * FROM ? person WHERE sex='F' AND income > 60000", [person]);