What's the best way to read Sqlite3 directly in Browser using Javascript?

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

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/