Embedded Browser SQLite: SQL.js

Introduction

Sometimes your application will provide so little data, that it may be overkill to use an API to access it. In such case you might want to embedded the database within your application. Actually, this technique is used for a while in mobile applications. It can be useful to speed up low bandwidth devices, boost application launch and more. Thing is, I'm a web developer and I wanted to be able to do the same in one of my web application.

Standard

The recommended and compatible way to store data in Browser is by using the IndexedDB. Thing is, it uses a different approach than pure SQL, as explained in documentation. The API is also tedious to use, that is why MDN recommend to use more programmer-friendly library for simple usages. And to finish, IndexedDB format being younger it is not as supported as SQLite.

Based on that, I wanted to try to load a SQLite database into my browser and request it. The database contains some 13 thousand entries about file extension information. The application will provide the company and software associated to the file extension searched by users.

Web Assembly

MDN describes Web Assembly as a way to run code written in multiple languages on the web at near native speed, with client apps running on the web that previously couldn’t have done. It is a low-level assembly-like language with a compact binary format that runs with near-native performance and provides languages such as C/C++ and Rust with a compilation target so that they can run on the web. It works by compiling code to LLVM byte-code and loading it using Emscripten.




Usages can be :
    • Porting a C/C++ application with Emscripten.
    • Writing or generating Web Assembly directly at the assembly level.
    • Writing a Rust application and targeting Web Assembly as its output.
    • Using AssemblyScript which looks similar to Type Script and compiles to Web Assembly binary.

SQL.js

A while ago I’ve heard about a project to load and parse PDF files directly in the browser and learned that it uses a port of PDFium. So I begun to search for a port of SQLite client and by chance, found the SQL.js awesome project done by Alon Zakai. It provides a port to Web Assembly and a JavaScript loader.

To install, it was a breeze as there is a first release and an npmjs package. You can check the documentation for different usages.

npm install --save sql.js

Webpack

So I decided to start working on this, because I didn’t find any API nor database listing file extensions, something I needed for my Torrent crawler project. I made a SQLite database and created a React application to showcase it.

While in the process of implementing the solution I found some blockers that I wanted to share for those having the same issues.

To run SQL.js the module need to load the WASM file in order to parse the SQLite database. Thing is, because I used Webpack 4, I always got the following error when trying to load it from the bundle :

`Web Assembly module is included in initial chunk. This is not allowed, because Web Assembly download and compilation must happen asynchronous.`

Making some researches learned me that Webpack is not fully compatible with Web Assembly for the moment, so I couldn’t include it with my bundle. So I hacked my way by loading directly the wasm file from SQL.js repository (not a big fan of that but it worked) :

let config = {
    locateFile: filename => >`https://cdn.rawgit.com/kripken/sql.js/v1.0.1/dist/${filename}`
} 

This way you can pass the `config` object to SQL.js initialization function and it will load the was from the specified location.

Pack the database

I also needed to embedded the database with my application, this was more straightforward as Webpack provides a file-loader to include any binary file to your project. To use it you just have to add this to your Webpack configuration file.

{
    test: /\.(sqlite)$/i,
    use: [
      {
        loader: 'file-loader',
        options: {
          name: '[name].[ext]'
        }
      }
    ]
},

After that, you just import your database to get the URL on your server. I did it this way :

import database from '../assets/database.sqlite';

Load the database

Now that we have our SQL.js initialized and our database packed with our application we need to load it as SQL.js do not provide database loading by URL. The documentation shows you how to do it, I used the same code and specified the database path.

var xhr = new XMLHttpRequest();
xhr.open('GET', database, true);
xhr.responseType = 'arraybuffer';
xhr.onload = () => {
    var uInt8Array = new Uint8Array(xhr.response);
    var db = new SQL.Database(uInt8Array);
};
xhr.send();

Request the database

To finish, I added a simple query in the application to allow users to search by file extension using the `db.exec` function provided by SQL.js.

You can check the relevant code in the `Search.js` React component in the project. It will search based on requested extension and update the Redux store afterwards.

See the demo

You can check the project source code and demo at https://github.com/FlyersWeb/file-extension-api.

Hoping that it will be useful for those who wants to embedded some light databases with their browser applications. Moreover I was thinking that by not accessing an API to request the database on a server, this removes server security breach through SQL injection.

I also wanted to thanks Cory House for his coryhouse/react-slingshot starter kit with React + React-redux which I used to make this application.