OTOH I don't see a similar superpower arising from handcrafted data type enforcement over (non-strict) SQLite.
Datagram is also quite often more fit for applications than streams, because many applications are message oriented. The Websocket protocol acknowledges that even though over TCP. But that's more a bonus point than a strong reason to choose UDP over TCP, one can always recreate packets/messages on top of TCP. It's a bit goofy though, because TCP uses IP packets.
A lot of online games with significant real-time constrains and many-to-many connections gladly use UDP - and similarly, video conference services also use it. Smaller protocols like DNS and NTP as well.
There are other arguments beside real-time streaming with acceptable data loss, see [1] and the "end-to-end argument" paper it links in particular.
Choosing UDP and ending up recreating some of its reliability and flow control features is not a "Uh, Oh..." moment. It's normally a deliberate choice. Sometimes you do need custom wheels [2].
> The original implementation of SQLite sought to follow Postel's Law which states in part "Be liberal in what you accept". This used to be considered good design - that a system would accept dodgy inputs and try to do the best it could without complaining too much. More recently, people have come to prefer software that is strict in what it accepts, so as to more easily find errors.
> There are now millions of applications that take advantage of SQLite's flexible and forgiving design choices. We cannot change SQLite to follow the current preference toward strict and dogmatic behavior without breaking those legacy applications.
Sometimes you can build a successful business out of doing so: https://aeron.io/
Or, at least, so says the lore.
I can understand not taking it seriously if it was completely unsupported but I'm pretty sure most databases don't have perfect default configs. Even PostgreSQL needs configuration for optimal performance because the defaults are for low (minimum) spec systems.
> then eventually adding nearly all the reliability facilities of TCP to the app (automatic retry, etc) by hand.
Depending on what you're doing you're still probably doing better than TCP after all that work. TCP is a stream based protocol which is not ideal for many applications due to head of line blocking. If you built your own reliability layer over UDP you likely avoid that issue entirely.
The SQLite documentation is very up front about these things [1] and more. It should only be an issue if you're the type of person who never reads any documentation.