Earlier quoted context omitted.
As a counterpoint I find that nutting out the schema upfront is an incredibly helpful process to define the functionality of the app. Once you have a strict schema that models the application well nearly everything else just falls into place. Strong foundation
Nutting?
SQLite 2022 Recap
31–40 of 41 posts
Re: SQLite 2022 Recap
#32Full outer join is my fave update. I kinda wish sqlite has more functions though.
You can add as many functions as you like: https://www.sqlite.org/appfunc.html
Unfortunately I don't think that can be done natively (like defining a function before using it in a query via the CLI). Only by writing and compiling some C
Re: SQLite 2022 Recap
#33Full outer join is my fave update. I kinda wish sqlite has more functions though.
You can add as many functions as you like: https://www.sqlite.org/appfunc.html
There is something like this: https://news.ycombinator.com/item?id=26683832
But I would prefer using something Sqlite officially maintains and not to maintain anything myself. I also don't prefer using libraries from random people unfortunately. Working at a fintech makes me paranoid...
Re: SQLite 2022 Recap
#34Earlier quoted context omitted.
I don't understand the use case? From the docs [ https://sqlite.org/wasm/doc/trunk/demo-123.md ], the database disappears on page reload on top of requiring using workers for longer running processes.
Even without persistence it's absurdly useful. In 2023 loading even a 50MB+ database file into a browser is feasible (that's only 10 heavy React webpage loads) and now you can run SQL queries directly against it in the browser. Plenty of interesting databases fit into less than a MB even. I've been using SQLite in WebAssembly for my Datasette Lite project - a Python server-side web app running entirely in the browser…
Additionally, SQLite in WebAssembly with a HTTP-Range-request based virtual file system greatly simplifies interactive visualisation of large datasets https://observablehq.com/@mjbo/sqljs-httpvfs (my own demo)
Re: SQLite 2022 Recap
#35Earlier quoted context omitted.
Even without persistence it's absurdly useful. In 2023 loading even a 50MB+ database file into a browser is feasible (that's only 10 heavy React webpage loads) and now you can run SQL queries directly against it in the browser. Plenty of interesting databases fit into less than a MB even. I've been using SQLite in WebAssembly for my Datasette Lite project - a Python server-side web app running entirely in the browser…
Additionally, SQLite in WebAssembly with a HTTP-Range-request based virtual file system greatly simplifies interactive visualisation of large datasets https://observablehq.com/@mjbo/sqljs-httpvfs (my own demo)
That's a really cool demo notebook.
Re: SQLite 2022 Recap
#36Earlier quoted context omitted.
You can add as many functions as you like: https://www.sqlite.org/appfunc.html
Unfortunately I don't think that can be done natively (like defining a function before using it in a query via the CLI). Only by writing and compiling some C
> Only by writing and compiling some C
Or Rust - https://ricardoanderegg.com/posts/extending-sqlite-with-rust...
Re: SQLite 2022 Recap
#37Re: SQLite 2022 Recap
#38Earlier quoted context omitted.
With JSON blobs in simple tables a very large part of designing a database schema becomes designing a JSON schema, with different opportunities to make mistakes but (potentially) the same strictness.
How do you enforce the JSON schema?
We are talking about the internal database of some application: its schema is something that should be designed well, not enforced defensively like a schema for validating undependable inputs.
Re: SQLite 2022 Recap
#39Earlier quoted context omitted.
It would be nice if OPFS (Origin Private File System) allowed reading/writing to an actual SQLite file on the users disk. At the moment, as I understand it, the OPFS virtual disk is completely isolated from the users disk. This means you cannot just lightly query a 1GB file without first copying the 1GB from the users filesystem to the OPFS. Any writes mean you must then copy the 1GB SQLite db file from OPFS to the u…
The original plan from Google for the file system access API was to allow read/write of real files, but both Mozilla and Apple said that was too dangerous, and the OPFS was created as a compromise. Yeah, it's going to be confusing for users when they want to actually want to use one of these files outside of the application that created it. But it's better than nothing!
This does seem strange, as you can read and write whole files to/from the user's file system once they give your app permission.
Adding those API's to read/write parts of the file is the next logical step. But it looks like it is limited to the isolated OPFS only.
Re: SQLite 2022 Recap
#40A pretty boring and stable year in SQLite land, which is just how I like it. The JSON -> and ->> operators introduced in 3.38 are my personal favorites, as these really help in implementing my "ship early, then maybe iterate as you're starting to understand what you're doing" philosophy: 1. In the initial app.sqlite database, have a few tables, all with just a `TEXT` field containing serialized JSON, mapping 1:1 to i…
Similarly one of my most important projects reads a lot of api calls returning serialized json. Those calls are expensive so I have, over time, tried many complicated cacheing mechanism.
These days though it's _so_much_simpler_and_cleaner_ to just wrap the call in a decorator that caches the request to sqlite3 and only makes the call if the cache is stale.
I don't worry about parsing the results or doing any of the heavy lifting right away - just cache the json.
Sqlite is so good at querying those blobs and is so fast it's just not worth munging them. Nice.
And using something like datasette to prototype queries for some of the more complicated tree structures is a breeze.