sqlite-plus VS datasette-lite

I definitely plan to! I have a much larger list of SQLite extensions I've built here: https://github.com/asg017/sqlite-ecosystem

Here's a few other references you may enjoy if you wanna learn more about SQLite extensions:

- The single source file for sqlite-vec: https://github.com/asg017/sqlite-vec/blob/main/sqlite-vec.c

- sqlean, a project from Anton Zhiyanov which is good base of great SQLite extensions: https://github.com/nalgeon/sqlean

- The official SQLite docs: https://www.sqlite.org/loadext.html

- The "hello world" SQLite extension example: https://www.sqlite.org/src/file/ext/misc/rot13.c

People should check out Nalgeon's work on SQLite extensions: https://github.com/nalgeon/sqlean.

Extra - Not sure what you are doing with the database but I came across this repo of extensions that seem pretty solid - https://github.com/nalgeon/sqlean

The simplicity of tree-navigation in "closure" is pretty amazing for how simple it is: https://github.com/nalgeon/sqlean/issues/27

Given that SQLite supports huge sizes for text, you could almost use the above as a way to create an outliner, with everything stored in the database itself ...

https://github.com/nalgeon/sqlean/blob/main/docs/define.md

I've been considering using that. Anyone have any experience with it?

Author here, happy to answer questions!

Simon already gave a great intro to my SQLite extensions, and I just want to point folks to Anton Zhiyanov's sqlean project, for pure-C extensions: https://github.com/nalgeon/sqlean

Also, some of these extensions are also packaged as Datasette plugins, so you can also run `datasette install datasette-sqlite-regex` or `datasette install datasette-sqlite-ulid` to add these extension to your Datasette instances! https://docs.datasette.io/en/stable/plugins.html

Also see: https://github.com/simonw/datasette-lite

https://github.com/simonw/datasette-lite :

> You can use this tool to open any SQLite database file that is hosted online and served with a `access-control-allow-origin: ` CORS header. Files served by GitHub Pages automatically include this header, as do database files that have been published online using `datasette publish`.*

> [...] You can paste in the "raw" URL to a file, but Datasette Lite also has a shortcut: if you paste in the URL to a page on GitHub or a Gist it will automatically convert it to the "raw" URL for you

> To load a Parquet file, pass a URL to `?parquet=`

> [...] https://lite.datasette.io/?parquet=https://github.com/Terada...*

There are various *-to-sqlite utilities that load data into a SQLite database for use with e.g. datasette. E.g. Pandas with `dtype_backend='arrow'` saves to Parquet.

datasette plugins are written in Python and/or JS w/ pluggy:

Datasette Lite might be even better for this - you can construct URLs that link directly to examples: https://github.com/simonw/datasette-lite

There are some amazing things for SQLite in the browser especially if you're looking for ways to host queryable data for cheap.

I have a hacked up POC experimental version of datasette-lite to be able to look at multi-GB databases at https://github.com/simonw/datasette-lite/pull/49. It uses a hacked up chunk'd lazyFile implementation from emscripten and others to grap pages from Cloudflare R2.

It's a test with california's unclaimed property records (https://www.sco.ca.gov/upd_download_property_records.html) of a 28GB searching up that guy who owns Twitter: https://datasette-lite-lab.mindflakes.com/index.html?url=htt...

I think there may be a space for super-large multi-GB files served from static storage being accessible from SQlite as well. Another one would be this full-text search of a 43GB SQLite database of Wikipedia's full text search: http://static.wiki/ . Hearing there's official support for this is awesome and I hope they also might add some provisions for those sticking with POSIX/Emscripten as well.

I grafted the enhanced lazyFile implementation of this to datasette-lite relatively recently. Threw in a 18GB CSV from

https://www.sco.ca.gov/upd_download_property_records.html

into a FTS5 Sqlite Database which came out to about 28GB after processing:

POC, non-merging Draft PR for the hack:

https://github.com/simonw/datasette-lite/pull/49

You can run queries through it if you URL hack into it and just get to the query dialog, browsing is kind of a dud at the moment since datasette runs a count(*) which downloads everything.

Oh wow! I wonder how hard it would be to load that module into https://github.com/simonw/datasette-lite

datasette-lite – Datasette running in your browser using WebAssembly and Pyodide

I have an open issue for that here: https://github.com/simonw/datasette-lite/issues/28

My initial hunch is that this will be really difficult - probably require a fork of something like https://github.com/coleifer/pysqlite3 then compiled for WebAssembly.

I'm confident it's feasible, but I don't have the skills to figure it out myself.