"Wait, what?!"

Yes, that was my reaction too when I read it on Twitter.

In this post I will explain you what is AlaSQL and test if it works as expected.

What is AlaQSL?

AlaSQL is a lightweight client-side in-memory SQL database. It was written in pure Javascript, supports JOIN, GROUP, UNION, IN, ALL and many more operations.

Is it fast? Of course it is! It takes advantage of the dynamic nature of Javascript and

uses optimization methods. According to the author:

Queries are cached as compiled functions.

Joined tables are pre-indexed.

WHERE expressions are pre-filtered for joins

Does it work in most of Web Browsers? Sure! It works in all modern versions of Chrome, Mozilla, Safari and even IE. You can use it on Node.js too.

Does it support NoSQL databases? Yes!! You can create JSON tables and work with JSON objects.

You can get more information in AlaSQL github repository:

agershun / alasql 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. 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. AlaSQL 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… View on GitHub

Installation

Using NPM:

npm install --save alasql

Using CDN:



<script src= "https://cdn.jsdelivr.net/npm/alasql@0.4" ></script>

And that's all. Very simple, huh?

Usage

First, import alasql into your code:



// CommonJS style var alasql = require ( ' alasql ' ); // ES6 style import * as alasql from ' alasql ' ; // Global variable style window . alasql

And then, start writing SQL:



alasql ( " CREATE TABLE test (language INT, hello STRING) " ); alasql ( " INSERT INTO test VALUES (1, 'Hello!') " ); alasql ( " INSERT INTO test VALUES (2, 'Aloha!') " ); alasql ( " INSERT INTO test VALUES (3, 'Bonjour!') " ); const results = alasql ( " SELECT * FROM test WHERE language > 1 " ); console . log ( results ); // Output: // [{ "language":2, "hello":"Aloha!" },{ "language":3,"hello":"Bonjour!" }]

You can even run queries over an array of objects:



const data = [{ id : 1 , amount : 10 }, { id : 2 , amount : 20 }, { id : 1 , amount : 30 }]; const results = alasql ( ' SELECT id, SUM(amount) AS total FROM ? GROUP BY id ' , [ data ]); console . log ( results ); // Output: // [{"id":1,"total":40},{"id":2,"total":20}]

Awesome, right?

Example

Let's create a new React application using npx create-react-app tool and implement a basic TODO list application:



import React from ' react ' ; class App extends React . Component { constructor ( props ) { super ( props ); this . state = { todo : [] }; } addTodo () { const { todo } = this . state ; const { inputTodo } = this . refs ; todo . push ( inputTodo . value ); inputTodo . value = "" ; this . setState ({ todo }); } removeTodo ( index ) { const { todo } = this . state ; todo . splice ( index , 1 ); this . setState ({ todo }); } render () { const { todo } = this . state ; return ( < main className = "container" > < h1 className = "mt-4" > TODO List </ h1 > < div className = "row mt-4" > < form className = "form-inline" > < div className = "form-group mx-sm-3 mb-2" > < label for = "inputTodo" className = "sr-only" > Todo </ label > < input type = "text" ref = "inputTodo" className = "form-control" id = "inputTodo" placeholder = "Todo" /> </ div > < button type = "button" className = "btn btn-primary mb-2" onClick = { e => this . addTodo () } > Add </ button > </ form > </ div > < div className = "row" > < table className = "table table-bordered" > < thead > < tr > < th > TODO </ th > < th ></ th > </ tr > </ thead > < tbody > { ! todo . length && < tr > < td colspan = "2" className = "text-center" > No data available </ td > </ tr > } { todo . length > 0 && todo . map (( x , i ) => ( < tr > < td > { x } </ td > < td > < button className = "btn btn-danger" onClick = { e => this . removeTodo ( i ) } > x </ button > </ td > </ tr > )) } </ tbody > </ table > </ div > </ main > ); } } export default App ;

The result is:

It works like a charm, but if I reload the page, I lost all my TODO list.

Let's use AlaSQL to persist those TODOs.

First, let's import AlaSQL and use componentWillMount hook to create the table:



import React from ' react ' ; import * as alasql from ' alasql ' ; class App extends React . Component { // Constructor ... componentWillMount () { alasql ( ' CREATE TABLE todo (id INT AUTOINCREMENT PRIMARY KEY, text STRING) ' ); } // Lines of code ... } export default App ;

Everytime the component is loaded, AlaSQL will create the table.

Now, we need to implement a method to get all TODOs from database, a method to insert new TODOs and a method to delete them.



import React from ' react ' ; import * as alasql from ' alasql ' ; class App extends React . Component { // Lines of code ... fetchTodos () { const result = alasql ( ' SELECT * FROM todo ' ); this . setState ({ todo : result }); } insertTodo ( text ) { alasql ( ' INSERT INTO todo VALUES ? ' , [{ id : alasql . autoval ( ' todo ' , ' id ' , true ), text }]); } deleteTodo ( id ) { alasql ( ' DELETE FROM todo WHERE id = ? ' , id ); } // Lines of code ... } export default App ;

As you can see, using traditional SQL SELECT, INSERT and DELETE do the job. alasql.autoval gets the next ID to be inserted since our table ID is autoincrementable.

Next, let's refactor the addTodo and removeTodo methods and add componentDidMount hook to fetch TODOs from database:



import React from ' react ' ; import * as alasql from ' alasql ' ; class App extends React . Component { // Lines of code... componentDidMount () { this . fetchTodos (); } addTodo () { const { inputTodo } = this . refs ; if ( ! inputTodo . value ) return ; this . insertTodo ( inputTodo . value ); this . fetchTodos (); inputTodo . value = "" ; } removeTodo ( id ) { this . deleteTodo ( id ); this . fetchTodos (); } // Lines of code ... } export default App ;

For last, let's update the render method adding a new ID column and using TODO object instead of plain text:



import React from ' react ' ; import * as alasql from ' alasql ' ; class App extends React . Component { // Lines of code ... render () { const { todo } = this . state ; return ( < main className = "container" > < h1 className = "mt-4" > TODO List </ h1 > < div className = "row mt-4" > < form className = "form-inline" > < div className = "form-group mx-sm-3 mb-2" > < label for = "inputTodo" className = "sr-only" > Todo </ label > < input type = "text" ref = "inputTodo" className = "form-control" id = "inputTodo" placeholder = "Todo" /> </ div > < button type = "button" className = "btn btn-primary mb-2" onClick = { e => this . addTodo () } > Add </ button > </ form > </ div > < div className = "row" > < table className = "table table-bordered" > < thead > < tr > < th > ID </ th > < th > TODO </ th > < th ></ th > </ tr > </ thead > < tbody > { ! todo . length && < tr > < td colspan = "3" className = "text-center" > No data available </ td > </ tr > } { todo . length > 0 && todo . map ( x => ( < tr > < td > { x . id } </ td > < td > { x . text } </ td > < td > < button className = "btn btn-danger" onClick = { e => this . removeTodo ( x . id ) } > x </ button > </ td > </ tr > )) } </ tbody > </ table > </ div > </ main > ); } } export default App ;

The result using AlaSQL is:

Damn, if I reload page again, I lost all my TODOs again... why?!

Well, in fact we are using AlaSQL and in fact we are inserting data in a table BUT we haven't created a database to persist the data.

So, let's modify the componentWillMount hook and create a new database named todo_db if it doesn't exist.

In this case, localStorage will be used as database engine.



componentWillMount () { alasql ( ` CREATE LOCALSTORAGE DATABASE IF NOT EXISTS todo_db; ATTACH LOCALSTORAGE DATABASE todo_db; USE todo_db; ` ); alasql ( ' CREATE TABLE IF NOT EXISTS todo (id INT AUTOINCREMENT PRIMARY KEY, text STRING) ' ); }

Everytime the component is loaded, AlaSQL will create the database if it doesn't exist.

Here is the final result:

It works as expected 👌.

Thanks for reading! You can find the source code from this post in the next repository: