Loading SQLite databases from a remote URL

[psychemedia]

The jupyterlite/xeus-sqlite-kernel currently seems to require users to create a database as part of a script before they can query it, rather than connect to a sqlite file shipped as part of a JuptyerLite distribution or uploaded to it, or connect to a remote database file.

The phiresky/sql.js-httpvfs Javascript package wraps a SQLite emscripten distribution and also provides a virtual filesystem that allows the SQLite database to access very large remote SQLite databases (if configured appropriately) in an efficient way.

I wonder if this approach could be used to support remote connections from the xeus-sqlite-kernel? Users could then use a simple web server to publish both the JupyterLite environment, and even a large database, and then query the database efficiently from their browser?

The SQLime browser based SQlite playground (nalgeon/sqlime) also offers some interesting "web integrations", as described on its About SQLime page. For example, SQL files can be hosted Github gists, the gist id passed via a URL parameter to SQLime, then loaded and run by the playground when the page is loaded. (Databases files can also be uploaded from the desktop to the playground environment.)

The Basthon in-browser notebook [repo] & SQLite kernel loads a database from a file path specified via a URL parameter:

(I also note here examples of using custom javascript functions to create custom sqlite functions.)

[DerThorsten]

@psychemedia there is a PR here jupyter-xeus/xeus-sqlite#119
with that one can fetch databases with
%FETCH <URL> <FILENAME>
and load them with
%LOAD <FILENAME> RW
one can then persist the virtual filesystem into the browsers indexed db, that way one only needs to do the fetching once

[psychemedia]

Interesting, thanks..:-)

I take it that downloads the whole db, eg compared to the sql.js-httpvfs approach where the idea is you can connect to large remote databases and then just pull what you need chunks at a time?

Still v. useful though :-)

[jamietanna]

Is it worth closing this, now jupyter-xeus/xeus-sqlite#119 has been released? 👀

[westurner]

• "Loading SQLite databases from a remote URL" Loading SQLite databases from a remote URL #6 (comment)

xeus-sqlite-interpreter > void interpreter::parse_SQLite_magic > %FETCH <url> <filename> https://github.com/jupyter-xeus/xeus-sqlite/blob/ce5a598bdab29eb6aab78a78d0caee93c292ec74/src/xeus_sqlite_interpreter.cpp#L191

xlite.cpp >
void fetch(const std::string url, const std::string filename)
https://github.com/jupyter-xeus/xeus-sqlite/blob/ce5a598bdab29eb6aab78a78d0caee93c292ec74/src/xlite.cpp#L38

[westurner]

• Loading partial SQLite databases over HTTP
  • sql.js-httpvfs https://github.com/phiresky/sql.js-httpvfs
  • sqlite-wasm-http: https://github.com/mmomtchev/sqlite-wasm-http

    This project is inspired from @phiresky/sql.js-httpvfs but uses the new official SQLite WASM distribution.

  • "Using SQLite as storage for web server static content" https://news.ycombinator.com/item?id=42010323

[DerThorsten]

• Loading partial SQLite databases over HTTP

  • sql.js-httpvfs https://github.com/phiresky/sql.js-httpvfs
  • sqlite-wasm-http: https://github.com/mmomtchev/sqlite-wasm-http

    This project is inspired from @phiresky/sql.js-httpvfs but uses the new official SQLite WASM distribution.

  • "Using SQLite as storage for web server static content" https://news.ycombinator.com/item?id=42010323

PRs are welcome