Anton Zhiyanov

If you're going to use SQLite as an application file format, you should:

1. Enable the secure_delete pragma <https://antonz.org/sqlite-secure-delete/> so that when your user deletes something, the data is actually erased. Otherwise, when a user shares one of your application's files with someone else, the recipient could recover information that the sender thought they had deleted.

2. Enable the options described at <https://www.sqlite.org/security.html#untrusted_sqlite_database_files> under "Untrusted SQLite Database Files" to make it safer to open files from untrusted sources. No one wants to get pwned when they open an email attachment.

3. Be aware that when it comes to handling security vulnerabilities, the SQLite developers consider this use case to be niche ("few real-world applications" open SQLite database files from untrusted sources, they say) and they seem to get annoyed that people run fuzzers against SQLite, even though application file formats should definitely be fuzzed. https://www.sqlite.org/cves.html

They fail to mention any of this on their marketing pages about how you should use SQLite as an application file format.

I implemented this in pure Python a few days ago in case anyone finds it helpful, here it is: https://gist.github.com/pirate/7e44387c12f434a77072d50c52a3d18e

My implementation supports graceful degradation between nanosecond scale resolution, microsecond, and millisecond, by using 12 bits for each and filling up the leftmost bits of rand_a and rand_b. Not all environments provide high resolution system clocks with no drift, so it's is important to maintain monotonicity when generating IDs with a low-res timestamp as input. You still want the bits that would've held the nanosecond value to be monotonic.

Neither of the existing uuid_utils and uuid7 python libs that can generate UUID7s support this monotonicity property.

Am planning on using this for ArchiveBox append-only "snapshot" records, which are intrinsically linked to time, so it's a good use-case imo.

There's another great resource here that I think is one of the best explainers of UUIDv7: https://antonz.org/uuidv7/

Whatever you do, don't implement the cursed 36-bit whole-second based time UUIDv7 variant that you occasionally see on StackOverflow / blog posts, stick to 48!

Integrating live code editors within docs and tutorials is great.

Another example of this I saw recently using SQLite (compiled to Wasm) in the browser: https://docs.sqlitecloud.io/docs/sqlite

And if you ever want something similar for more general backend APIs (without relying on Wasm or the browser to run the software), https://codapi.org/ looks very slick e.g. as demonstrated in https://antonz.org/sql-upsert/ - discussed on HN previously [0]

Inspired by https://www.db-fiddle.com/ my colleagues ended up building a fairly bespoke setup for XTDB's docs (XTDB doesn't yet compile to Wasm) shortly before I came across Codapi, although our requirements were even more particular, e.g. see https://docs.xtdb.com/tutorials/financial-usecase/time-in-finance.html - the backend here is https://github.com/xtdb/xt-fiddle which runs purely on top of Lambda Snapstart, and embedded within docs based on Astro's Starlight [1] and Web Components

[0] https://news.ycombinator.com/item?id=38663717

[1] https://starlight.astro.build/

I wish sqlite was performance focused. Perf is pretty much always worse than a db server, even after tuning the pragmas (the way it locks tends to limit single threaded perf and concurrency). Main benefit of sqlite is just its portability since you can easily embed it. It's nice for slapping a db backend on a client installed app, where trying to spin up a db server would be problematic.

There's probably an extension for permissions in sqlite but out of the box the db files are plaintext (you need an extension for that).

Good starting point for extensions: https://antonz.org/sqlean/

I think you are thinking "hashmaps", not nosql. There are some ways of persisting hash maps to disk [1], but the workhorse of most ACID DBs (including popular nosql DBs like mongo) are B-Trees, which have O(log n) complexity for all operations.

In any case when working with JSON columns in SQL it is useful to index the documents internal fields, which the OP doesn't talk about. Found a nice example in this blog post [2].

--

1: https://en.wikipedia.org/wiki/Linear_hashing

2: https://antonz.org/json-virtual-columns/

Here are my favorites:

- Jacob Kaplan-Moss (https://jacobian.org/) [Engineering leadership, OSS]

- Anton Zhiyanov (https://antonz.org/) [SQL, Go, Python]

- Julia Evans (https://jvns.ca/) [SQL, Linux, Python, Go, Web]

- Brandur Leach (https://brandur.org/) [Postgres, Go, Ruby, Web]

- Brandon Rhodes (https://rhodesmill.org/brandon/) [Python, Astronomy, Linux]

- Nathanial J Smith (https://vorpus.org/blog/) [Python, Async, Linguistics]

I also write occasionally at https://rednafi.com.