DEV Community

Cover image for AlaSQL: A Real Database for Web Browsers (and Node.js)
Jorge Ramón
Jorge Ramón

Posted on

AlaSQL: A Real Database for Web Browsers (and Node.js)

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

GitHub logo 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.

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…

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:

GitHub logo 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…

Discussion (8)

Collapse
rokkoo profile image
Alfonso

Do you think this can work in react native?

Collapse
jorge_rockr profile image
Jorge Ramón Author

I think it can work since SQLite can be your database engine. Why don't you give it a try?

Collapse
rokkoo profile image
Alfonso

I will give a try thx!

Collapse
mrdionjr profile image
Salomon Dion

Thanks for creating this library. I came across AlaSQL about 2 months ago. I use it to load CSV and Excel files and perform queries on them. It helps me stay consistent and use the same language with different data sources (database, spreadsheet).

Collapse
margo_hdb profile image
Margo McCabe

Great post. We're actually hosting the creators of AlaSQL for a Q&A and showcase in a couple weeks, feel free to tune in and ask them anything! harperdb.io/rsvp-alasql-showcase/

Collapse
johhansantana profile image
Johhan Santana

Does this work with indexed db?

Collapse
qm3ster profile image
Mihail Malo

To quote the author:

What is AlaQSL?

Collapse
keshidaffo profile image
keshi-daffo

How to manage if column name contains space in it's name?