What's the best way to read Sqlite3 directly i

Question

For one of our Insights platform, we plan to generate summary SQLite3 databases in the background and let it be rendered on the browser as charts. Currently, we are intending to a server-side endpoint that will service the data requirement.

We are looking to optimize this further by eliminating the server-side endpoint altogether. We are fine (from a security perspective) to expose the SQLite3 directly on S3 and have a javascript module read and generate the charts.

The SQLite3 files are expected to fairly small - perhaps 4-6 columns and perhaps 10-500 rows of data, and all of them containing one table only. Test runs indicate file sizes of less than 15KB. We don't intend to write or manipulate the SQLite3 on the browser. We don't need to cache it on the browser as a WebSQL or an IndexedDB form, but we are ok with using them if that is what is needed.

From my web searches, We are unable to find a Javascript library that can read a SQLite3 file and query it for results. If you know of any javascript libraries that can do this, then please let us know.

On the other hand, if you think that we shouldn't be doing this for whatever reason, then please throw them as comments/answers too, because this is something we are trying for the first time and seems a little out-of-the-box, so feedback welcome!

Answer 1

There is a javascript library called sql.js that can do exactly what you want. In your case, you would use it like that

var xhr = new XMLHttpRequest();
xhr.open('GET', '/path/to/database.sqlite', true);
xhr.responseType = 'arraybuffer';

xhr.onload = function(e) {
  var uInt8Array = new Uint8Array(this.response);
  var db = new SQL.Databse(uInt8Array);
  var contents = db.exec("SELECT * FROM my_table");
  // contents is now [{columns:['col1','col2',...], values:[[first row], [second row], ...]}]
};
xhr.send();

See the documentation at http://lovasoa.github.io/sql.js/documentation

Answer 2

I can not tell the best, but one: Write a JavaScript SQLite reader library yourself. This will be a tedious task, but I am sure it can be done. Some cool folks have done pdf.js, which is a JavaScript renderer for PDF files, which are also binary BLOB's like SQLite files are.

You will most probably start with the FileReader API to walk thru the SQLite file, then create some in-memory representation of the content, which your chart tool can use.

Disclaimer: You probably want to solve your initial problem with another solution, as proposed by others, but this answers your question.