"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:

AlaSQL / 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 unfunded open source project installed 200k+ times each month. Please donate your time. We appreciate any and all contributions we can get.
Have a question? Ask the AlaSQL bot or post on Stack Overflow.

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 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 by taking advantage of the dynamic nature of JavaScript when building up queries. Real-world solutions demand flexibility regarding where…

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:

jorgeramon / alasql-react-example

An example of how to use AlaSQL with React

This project was bootstrapped with Create React App.

Available Scripts

In the project directory, you can run:

`npm start`

Runs the app in the development mode.
Open http://localhost:3000 to view it in the browser.

The page will reload if you make edits.
You will also see any lint errors in the console.

`npm test`

Launches the test runner in the interactive watch mode.
See the section about running tests for more information.

`npm run build`

Builds the app for production to the build folder.
It correctly bundles React in production mode and optimizes the build for the best performance.

The build is minified and the filenames include the hashes.
Your app is ready to be deployed!

See the section about deployment for more information.

`npm run eject`

Note: this is a one-way operation. Once you eject, you can’t go back!

If you aren’t satisfied with the build tool…