You can run SQLlite in the client via webassembly therefore open a SQLite file in the browser to query it yes, you just can't write anything in it and expect it to persist somehow on the static hosting service itself.
> The best I came up with: let's say you have a toy project, and you call the Github API and replace the file upon every write. Implementing a read is easier as you know where the file is located. This hack shows that you somehow need write access to get any form of performance out of it, because this hack is super slow.
No you'll need a server for that, you can't make random HTTP requests to any server in the browser, because of CORS/SAME ORIGIN policies.
"SQLite is serverless" is meaningless buzzword. It just means that SQLite is equivalent a flat file where you'd shove some data, just that you can use SQL to query that file instead of having to index data in it yourself.