sqlite3vfshttp

There are implementations for that: For example https://github.com/psanford/sqlite3vfshttp or https://github.com/phiresky/sql.js-httpvfs

FUSE does local filesystems, and there are also HTTP remote VFS for sqlite. sqlite-wasm-http is based on phiresky/sql.js-httpvfs, "a read-only HTTP-Range-request based virtual file system for SQLite. It allows hosting an SQLite database on a static file hoster and querying that database from the browser without fully downloading it." "but uses the new official SQLite WASM distribution." [1][2] [2] sql.js-httpvfs: https://github.com/phiresky/sql.js-httpvfs [1] sqlite-wasm-http: https://www.npmjs.com/package/sqlite-wasm-http [3] sqlite3vfshttp: https://github.com/psanford/sqlite3vfshttp HTTP Range requests: https://developer.mozilla.org/en-US/docs/Web/HTTP/Range_requ...

You could use a sqlite database and use range queries using something like this: https://github.com/psanford/sqlite3vfshttp https://github.com/phiresky/sql.js-httpvfs Simon Willison wrote about it: https://simonwillison.net/2022/Aug/10/sqlite-http/

I often use (my[0]) sqlite http vfs to query databases stored in s3. You can simply make a presigned url and then give that to the vfs and then it all just works. [0]: https://github.com/psanford/sqlite3vfshttp

I'm a big fan of FTS5 as a cheap/easy way of adding full text search to datasets. Recently I've been storing these datasets in S3 and been querying them directly via a VFS[0]. Its a simple way to have FTS available to lambda functions without the cost of using an online database. [0]: https://github.com/psanford/sqlite3vfshttp

I love sqlite's FTS. I have a number of personal projects where I have an archive of data and want to occasionally be able to do ad-hoc searches over and get reasonable ranking and results. Throwing sqlite fts at the problem gets me that in just a few lines of code. You can even combine it with a sqlite vfs[0] to run full text searches against a sqlite db stored in s3 relatively efficiently. [0]: https://github.com/psanford/sqlite3vfshttp

The sqlite cli supports loading modules at runtime, so you can do things like load and use a db via a custom vfs. My Go implementation of this vfs supports running in loadable module mode[1]. [1]: https://github.com/psanford/sqlite3vfshttp#demo

I made a similar sqlite vfs in Go[1]. I really love the VFS api. HTTP range queries is about the most mild thing you can do with it. The VFS implementations that use non-traditional backing stores are more fun. Like say, using DynamoDB as a read/write backing store for your sqlite database[2]. [1]: https://github.com/psanford/sqlite3vfshttp [2]: https://github.com/psanford/donutdb

I really like the idea of using FUSE as SQLite backend, rather than injecting into the process I love the use case of querying SQLite from a CDN with range requests, because it allows for real “serverless” querying. For example, this: https://github.com/psanford/sqlite3vfshttp

You can also query sqlite databases directly from an http server that supports range requests (like s3). There are a bunch of implementations of this in different languages including Go[0] and Javascript[1]. [0]: https://github.com/psanford/sqlite3vfshttp [1]: https://github.com/phiresky/sql.js-httpvfs

There's a bunch of projects that have implemented this. I wrote a SQLite VFS in Go that lets you query a read-only SQLite db over http (including from s3) [0]. The VFS API offers the possibility for weirder storage solutions, if thats the type of thing you're into. Recently I've been moving some of my personal websites hosted on AWS Lambda over to use a read/write sqlite db backed by DynamoDB[1]. There are a bunch of limitations to this type of thing (like it uses a global write lock), but it works nicely for DBs that have low write frequency. [0]: https://github.com/psanford/sqlite3vfshttp [1]: https://github.com/psanford/donutdb

For accessing the database I use sqlite3vfshttp[0] with an s3 presigned url for authentication. [0]: https://github.com/psanford/sqlite3vfshttp

I just implemented something similar in Go[0]. Just yesterday I added the ability to compile it as a loadable sqlite3 extension, so you can use the normal sqlite3 cli tool to query databases hosted on an http(s) connection. [0]: https://github.com/psanford/sqlite3vfshttp