4 ms·
Hey, author here, happy to answer any questions! Also checkout this notebook for a deeper dive into sqlite-lines, along with a slick WASM demonstration and more
by alexgarcia-xyz 4y ago
Hey, author here, happy to answer any questions! Also checkout this notebook for a deeper dive into sqlite-lines, along with a slick WASM demonstration and more thoughts on the codebase itself https://observablehq.com/@asg017/introducing-sqlite-lines https://observablehq.com/@asg017/introducing-sqlite-lines
I really dig SQLite, and I believe SQLite extensions will push it to another level. I rarely reach for Pandas or other "traditional" tools and query languages, and instead opt for plain ol' SQLite and other extensions. As a shameless plug, I recently started a blog series on SQLite and related tools and extensions if you want to learn more! Next week I'll be publishing more SQLite extensions for parsing HTML + making HTTP requests https://observablehq.com/@asg017/a-new-sqlite-blog-series https://observablehq.com/@asg017/a-new-sqlite-blog-series
A few other SQLite extensions:
- xlite, for reading Excel files, in Rust https://github.com/x2bool/xlite https://github.com/x2bool/xlite
- sqlean, several small SQLite extensions in C https://github.com/nalgeon/sqlean https://github.com/nalgeon/sqlean
- mergestat, several SQLite extensions for developers (mainly Github's API) in Go https://github.com/mergestat/mergestat https://github.com/mergestat/mergestat
- simonw 4y agoI'm so interested to hear about the HTTP requests stuff. I've played a tiny bit with SQLite Python extensions that perform HTTP requests, but I'm very unsure of the implications of this kind of thing. What happens to the rest of the SQLite query engine if your custom function hits a page like https://httpbin.org/delay/10 https://httpbin.org/delay/10 with a deliberate 10 second delay before the request returns? Maybe this is fine - your transaction takes 10 seconds longer but everything else continues to work as normal. I've not done the work to figure that out.
- alexgarcia-xyz 4y agoYup, if it hits https://httpbin.org/delay/10 https://httpbin.org/delay/10 then the entire script will delay for 10 seconds (unless you have a custom lower timeout setting). Which means you can only make HTTP requests in serial requests, which sucks, but much simpler than coordinating with other tools It'll come out in a week, but lmk if you want to play with it beforehand!
- simonw 4y agoHow will it affect other SQLite connections against the same database? My guess is they'll be OK, but presumably any transactions will lock tables for the duration?
- alexgarcia-xyz 4y agoI think you're right, tho not an expert with multiple connections. I think if the DB is in WAL mode and you're only issuing read-only requests, then there will be no lock and long HTTP requests won't lock anything up. But if you issue a write-request that locks tables, then other write-requests will be blocked with long requests. But if you're in journal mode then there's not much you can do. Please correct me if I'm wrong!
- simonw 4y agoHow difficult do you think it would be compile SpatiaLite (a big hairy C codebase) to WASM and load it as a dynamic module, like you've done with sqlite-lines?
- alexgarcia-xyz 4y agoThis has been done by other people already! - https://github.com/jvail/spl.js https://github.com/jvail/spl.js - https://observablehq.com/@visionscarto/hello-spl-js https://observablehq.com/@visionscarto/hello-spl-js
- simonw 4y agoOh wow! I wonder how hard it would be to load that module into https://github.com/simonw/datasette-lite https://github.com/simonw/datasette-lite Hah, turns out I have an issue for that already, which I'd forgotten about: https://github.com/simonw/datasette-lite/issues/30 https://github.com/simonw/datasette-lite/issues/30