30

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!

Shreeni
  • 3,222
  • 7
  • 27
  • 39
  • 1
    This can't be done by JavaScript directly - however, WebSQL/IndexedDB *might* use SQLite underneath; but that's an implementation detail. (SQLite *requires* in-process access to a [local] filesystem in most cases.) –  Feb 08 '13 at 02:35
  • 1
    .. and I can't imagine a *complete rewrite* of a database engine that can read SQLite data in JavaScript (e.g. in memory data only) would be very practical, but [I've been surprised before](http://bellard.org/jslinux/). –  Feb 08 '13 at 02:37
  • @pst regarding you first comment, yes I know they might be using SQLite underneath, but from our side, having to bring it from server and sync it with a WebSQL/IndexedDB seems too much of a hassle and tugh to maintain in the long run. – Shreeni Feb 08 '13 at 02:42
  • 1
    I think that the best bet [currently] on the browser is WebSQL/IndexedDB, depending upon the target browser(s). It shouldn't take long to transfer the initial scema/data. Alternatively, could ship back everything as appropriately [de]normalized JSON - JSON might initially be simpler, but minimizes the ability to perform "ad-hoc" queries, much like a document database. –  Feb 08 '13 at 02:44
  • Unless you are modifying sql on client side, there is no need to use sqlite, you can simply use caching on browser with headers like if modified or ETag. All you want to do is good caching, and you can do caching with more time and just tag url with some version tag. – Akash Kava Feb 08 '13 at 05:49
  • @AkashKava my actual source is in Sqlite3 and I don't want a server side layer to process it - I just want to pass it to browser is as is and let the JS layer open, read and render the contents. Caching (or the problem of not re-reading it from server) isn't really my objective. My objective is to move this operation from having to use an app-Server to one using only web-server. – Shreeni Feb 08 '13 at 06:51
  • @AkashKava The primary motivation is that scaling a web server like s3 is something that I don't have to deal with, but scaling an app-server, with or without auto-scaling is still a non-trivial effort. – Shreeni Feb 08 '13 at 06:52
  • 1
    Unless you create a plugin or extension for different browser, you wont be able to do this, sqlite is offered as web sql, but soon it will be deprecated as it is replaced by IndexedDB, a pure JSON based database. Based on my experience, using device or client to process data will have adverse affect, none of this is supported in IE, on mobile devices there are bugs which are hard to detect and they cant be upgraded easily. I have been there, we eventually put everything on app-server, divide app server as api server, scalling single component is easy rather then debugging and supporting client – Akash Kava Feb 08 '13 at 10:36

2 Answers2

35

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

const SQL = await initSqlJs(options);
const fetched = await fetch("/path/to/database.sqlite");
const buf = await fetched.arrayBuffer();
const db = new SQL.Database(new Uint8Array(buf));
const contents = db.exec("SELECT * FROM my_table");
// contents is now [{columns:['col1','col2',...], values:[[first row], [second row], ...]}]

See the documentation on sql-js.github.io/sql.js/documentation/

HairyFotr
  • 1,453
  • 1
  • 15
  • 28
lovasoa
  • 6,419
  • 1
  • 35
  • 45
  • 4
    +10. This is the solution. lovasoa's commits have been merged into the original sql.js, get it at https://github.com/kripken/sql.js. The above example needs to be modified in just one way: add `var SQL = window.SQL;` at the top (after including the sql.js file and changing `my_table` of course). Works great (I used Chrome). – Ahmed Fasih Aug 20 '14 at 13:40
  • same way how to insert new records or update the records in table ? – batMan007 Sep 03 '19 at 07:15
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.

Marcel
  • 15,039
  • 20
  • 92
  • 150
  • I am accepting @Marcel's answer since there seems to be no better answer at the moment (for client side JS). – Shreeni Jun 05 '13 at 03:19
  • @Shreeni Have you looked into the sql.js proposed by @Prinzhorn? This seems to be very promising. – Marcel Jun 05 '13 at 05:55
  • 2
    I have looked at the sql.js library but documentation seems scarce. Can anyone point to any good practical example? In particular, I'd like to know how if one can just fetch an sqlite file from server with an ajax call and query it with sql.js – malber Nov 21 '13 at 09:45
  • 1
    This is no longer the right answer. See lovasoa's answer regarding the now-well-documented sql.js. – Ahmed Fasih Aug 20 '14 at 13:43