Generic SQL driver with db0

[pi0]

For supporting more SQL-based providers, we shall make a generic db0 based driver that works for all supported ones.

What's the point of using SQL as KV? I personally believe only to reuse same already setup service for another purpose that requires a simple KV interface. Otherwise, SQL is interface to interact with database and DB0 can be used standalone.

---

Today, only we have planetscale which is SQL based and I think we can move it too (and handle differences like "cache boost" mode here or could be there too.)

[Celibioux]

What About SurrealDB?

[pi0]

@Celibioux I have listed it for db0 tracker list: unjs/db0#32

[danielwaltz]

Love seeing this! One extra perk I'll throw out there to having this feature (that admittedly I got from watching a recent interview with DHH by Aaron Francis - relevant part here) is the ability to potentially use separate SQLite databases for things like a caching layer or queues.

I know DHH is kind of a controversial voice in the web dev space right now, and while I don't agree with all his viewpoints, I quite like the stance on using SQLite as a default for storage without needing to set up and connect to external services. I know this is the case with Nitro + db0 which I very much align with. Plus I find the argument that the cost for memory is significantly higher than the cost for storage without as large a performance benefit as it used to be thanks to SSDs very compelling.

After watching that part of interview, I immediately thought of unstorage and went looking to see if SQLite was supported! The rabbit hole landed me here. Just wanted to share this perspective and that I'm looking forward to seeing this land!

[pi0]

Thanks for the feedback dear @danielwaltz. SQLite and other SQL dialects will for sure be supported in unstorage so you can leverage the same DB-File for the Nitro caching layer as well if you wish.

---

I don't want to share any judgments based on theories only before first properly experimenting and perhaps we can use a dedicated discussion in Nitro for the rest of it but to give just more context, converting Nitro's default storage from fs-backed storage to SQLite was in the plan and I specifically put a hold on it because there is more to it (apart from a fact that a manual setup of sqlite seems easier). Modern filesystems (ZFS, BTRRFS, APFS, etc) are already powerful KV storage backends with tons of optimizations and kernels do LOTS of caching stuff themselves (and we can do more with multi-layer storages coming for cache purposes). Adding a file+mem backed DB layer (at least in theory) adds to CPU cycles and data overhead and disables fs optimizations since data is opaque. Without multi-layer caching, SQLite has an advantage of using more in-memory benefits so for a fair compare we need to do more checks done. Whatever method is winner on ordinary setups will be new default for nitro cache backend.