AlaSQL: A Real Database for Web Browsers (and Node.js)
Tags: alasql, sql, showdev, javascript
Published: 2019-01-30 16:08:40

---
title: AlaSQL: A Real Database for Web Browsers (and Node.js)
published: true
description: A quick introduction to AlaSQL
tags: alasql,sql,showdev,javascript
cover_image: https://s3-us-west-2.amazonaws.com/devcodepro/media/blog/que-es-sql.png
---

"Wait, what?!"

![](https://media.giphy.com/media/3o7abBP0nMjrdIvaCY/giphy.gif)

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 agershun/alasql %}

## Installation

Using NPM:
`npm install --save alasql`

Using CDN:

```html
<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:

```javascript
// CommonJS style
var alasql = require('alasql');

// ES6 style
import * as alasql from 'alasql';

// Global variable style
window.alasql
```

And then, start writing SQL:

```javascript
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:

```javascript
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?

![](https://media.giphy.com/media/HT4IFvKvQwhCE/giphy.gif)

## Example

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

```jsx
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:

![](https://media.giphy.com/media/3gNpPsV0uinCadzIqd/giphy.gif)

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:

```jsx
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.

```jsx
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:


```jsx
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:

```jsx
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:

![](https://media.giphy.com/media/RkHKubMsAu6e7hLxAD/giphy.gif)

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

![](https://media.giphy.com/media/l2JhtKtDWYNKdRpoA/giphy.gif)

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.

```javascript
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:

![](https://media.giphy.com/media/d2Ng0iJqNKePt8cUin/giphy.gif)

It works as expected 👌.

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

{% github jorgeramon/alasql-react-example %}