"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.
WHEREexpressions 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…
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…
Top comments (9)
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).
Do you think this can work in react native?
I think it can work since SQLite can be your database engine. Why don't you give it a try?
I will give a try thx!
Does this work with indexed db?
Yes
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/
To quote the author:
Some comments may only be visible to logged-in visitors. Sign in to view all comments.