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)

#alasql #sql #showdev #javascript

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

CI-test NPM downloads OPEN open source software Release Average time to resolve an issue Coverage OpenSSF Scorecard OpenSSF Best Practices Stars

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 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>
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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!" }]
Enter fullscreen mode Exit fullscreen mode

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}]
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)');
}
Enter fullscreen mode Exit fullscreen mode

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…

View on GitHub

Top comments (9)

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
 
rokkoo profile image
Alfonso •

Do you think this can work in react native?

Collapse
 
jorge_rockr profile image
Jorge Ramón •

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
 
johhansantana profile image
Johhan Santana •

Does this work with indexed db?

Collapse
 
satra2806 profile image
satra2806 •

Yes

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
 
qm3ster profile image
Mihail Malo •

To quote the author:

What is AlaQSL?

Some comments may only be visible to logged-in visitors. Sign in to view all comments.

Read next

zanepearton profile image

Rust + WASI: Application Monitoring

Zane -

willowjr profile image

What I’ve Learned from Building a Calculator with Vue.js

William Kwadwo Owusu -

grahamthedev profile image

Alphanumeric / Natural sort in MySQL - 30 years and we still can't do this? 😤

GrahamTheDev -

skylerdev profile image

How I Created a Hover Reveal Text Animation with TailwindCSS and React

Skyler Dev -