6 ms·

Coming from the enterprise SQL world, I never took SQLite seriously for the very reason that field types were not enforced by default. (Yes, I was agog when it

Coming from the enterprise SQL world, I never took SQLite seriously for the very reason that field types were not enforced by default. (Yes, I was agog when it became the backbone for app metadata on smartphones.) Anyway, reading this reminds me of the old chestnut from networking about choosing UDP over TCP for its low-latency and simplicity and then eventually adding nearly all the reliability facilities of TCP to the app (automatic retry, etc) by hand.

nine_k 2mo ago
The difference is that when you add all these mechanisms yourself, you can do it differently than TCP does, sometimes to a great effect: see QUIC and HTTP/3. OTOH I don't see a similar superpower arising from handcrafted data type enforcement over (non-strict) SQLite.
- astrobe_ 2mo ago
  Yep. More generally the correct reason to prefer UDP over TCP is the fine-grained control you gain. When you want that and have to use TCP, you're in for a deadly fight against the OS and its TCP/IP stack. 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]. [1] https://deepplum.com/post-b/ https://deepplum.com/post-b/ [2] https://en.wikipedia.org/wiki/Mecanum_wheel https://en.wikipedia.org/wiki/Mecanum_wheel
762236 2mo ago
If I'm interested in a Jeep or Bronco, I don't go to a car reviewer. They say it is noisy and handles poorly. They act like their use case is what matters for something obviously targeting a different use case.
Rohansi 2mo ago
> I never took SQLite seriously for the very reason that field types were not enforced by default. 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.
- lacunary 2mo ago
  tuning defaults for performance feels much more acceptable than tuning defaults for correctness
  - Rohansi 2mo ago
    It's for backwards compatibility, as always. You also need to enable foreign keys or they aren't enforced. You also need to tune for performance (enable WAL, adjust cache size, etc.). 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. [1] https://sqlite.org/quirks.html https://sqlite.org/quirks.html
- pavon 2mo ago
  It wasn't option for the first 20 years of SQLite's existence.
Rendello 2mo ago
Even foreign keys weren't available before 2009, and are still not enabled by default: https://sqlite.org/foreignkeys.html https://sqlite.org/foreignkeys.html
- akoboldfrying 2mo ago
  Thanks, I've been using SQLite for a while and I did not realise that FKs are disabled by default! Fortunately not on anything critical, still... Yikes.
  - Rendello 2mo ago
    The SQLite docs have a great "quirks" page [1], which contains this telling quote: > 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. 1. https://sqlite.org/quirks.html https://sqlite.org/quirks.html
noisy_boy 2mo ago
> Anyway, reading this reminds me of the old chestnut from networking about choosing UDP over TCP for its low-latency and simplicity and then eventually adding nearly all the reliability facilities of TCP to the app (automatic retry Sometimes you can build a successful business out of doing so: https://aeron.io/ https://aeron.io/
- ezekiel68 2mo ago
  For sure -- but these are the 'exceptions that prove the rule'. Someone else mentioned HTTP3 and QUIC. For all of these, there are thousands of projects (including multiplayer games and even enterprises) that didn't quite get it right and suffered for it. Or, at least, so says the lore.

nine_k 2mo ago

The difference is that when you add all these mechanisms yourself, you can do it differently than TCP does, sometimes to a great effect: see QUIC and HTTP/3. OTOH I don't see a similar superpower arising from handcrafted data type enforcement over (non-strict) SQLite.

astrobe_ 2mo ago
Yep. More generally the correct reason to prefer UDP over TCP is the fine-grained control you gain. When you want that and have to use TCP, you're in for a deadly fight against the OS and its TCP/IP stack. 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]. [1] https://deepplum.com/post-b/ https://deepplum.com/post-b/ [2] https://en.wikipedia.org/wiki/Mecanum_wheel https://en.wikipedia.org/wiki/Mecanum_wheel

762236 2mo ago

If I'm interested in a Jeep or Bronco, I don't go to a car reviewer. They say it is noisy and handles poorly. They act like their use case is what matters for something obviously targeting a different use case.

Rohansi 2mo ago

> I never took SQLite seriously for the very reason that field types were not enforced by default. 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.

lacunary 2mo ago
tuning defaults for performance feels much more acceptable than tuning defaults for correctness
- Rohansi 2mo ago
  It's for backwards compatibility, as always. You also need to enable foreign keys or they aren't enforced. You also need to tune for performance (enable WAL, adjust cache size, etc.). 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. [1] https://sqlite.org/quirks.html https://sqlite.org/quirks.html
pavon 2mo ago
It wasn't option for the first 20 years of SQLite's existence.

Rendello 2mo ago

Even foreign keys weren't available before 2009, and are still not enabled by default: https://sqlite.org/foreignkeys.html https://sqlite.org/foreignkeys.html

akoboldfrying 2mo ago
Thanks, I've been using SQLite for a while and I did not realise that FKs are disabled by default! Fortunately not on anything critical, still... Yikes.
- Rendello 2mo ago
  The SQLite docs have a great "quirks" page [1], which contains this telling quote: > 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. 1. https://sqlite.org/quirks.html https://sqlite.org/quirks.html

noisy_boy 2mo ago

> Anyway, reading this reminds me of the old chestnut from networking about choosing UDP over TCP for its low-latency and simplicity and then eventually adding nearly all the reliability facilities of TCP to the app (automatic retry Sometimes you can build a successful business out of doing so: https://aeron.io/ https://aeron.io/

ezekiel68 2mo ago
For sure -- but these are the 'exceptions that prove the rule'. Someone else mentioned HTTP3 and QUIC. For all of these, there are thousands of projects (including multiplayer games and even enterprises) that didn't quite get it right and suffered for it. Or, at least, so says the lore.