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.
Prefer strict tables in SQLite
71–80 of 188 posts
Re: Prefer strict tables in SQLite
#72It's good advice to "Prefer strict X in Y" for almost any value of X and Y. Lax DWIM stuff always comes back and bites you in the end.
Re: Prefer strict tables in SQLite
#73Coming 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 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.
Re: Prefer strict tables in SQLite
#74thanks! i have seen the sqlite quirks page before but just added this to my queries thanks to you
Re: Prefer strict tables in SQLite
#75Coming 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…
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.
Re: Prefer strict tables in SQLite
#76It's good advice to "Prefer strict X in Y" for almost any value of X and Y. Lax DWIM stuff always comes back and bites you in the end.
"Everything should be built top down, except for the first time", as the saying goes %) The problem is often that new things are built with tools that allow for flexibility, because the builder hasn't decided on the shape of what needs to be built. In 1990s it was Perl, in 2020s it's vibe-coding, but in either case it's lax and "dwim".
As the developer attains a much better understanding of the product, strictness becomes more and more beneficial, but the spectre of backwards compatibility haunts the interfaces and defaults.
Re: Prefer strict tables in SQLite
#77Coming 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…
> 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.
Re: Prefer strict tables in SQLite
#78Yeah my DB is the one place I want strict types. Well also RPCs. But SQLite is a somewhat different set of use cases, so maybe I'd understand https://sqlite.org/flextypegood.html more if I were using it. Like there's a point about random scripts not made for SQLite happening to work with it, which isn't normally a consideration for other DBMSes.
> https://sqlite.org/flextypegood.html
Both the advantages and disadvantages section is missing key arguments.
Key argument in favor of flexible typing: Easy to evolve schema. When you are using SQLite to store data in your embedded database and your requirements change, do you want to create a new database and migrate data each time? Evolving the schema in-place is much easier, and if your application is the only one reading/writing data into the database you will not be surprised by the fact the column that previously stored integers now contains strings as well.
Key argument against flexible typing: The schema is a contract, and when multiple applications read and write to a single database, and these applications are updated on their own schedules, storing the wrong type of data in a column will break the other applications. Strict adherence to the contract is necessary for applications to collaboratively read and write data. When a table is created by one application and used by another, the data types must be what you agreed to.
Re: Prefer strict tables in SQLite
#79https://sqlite.org/flextypegood.html explains why this isn't the default (and probably will never be the default).
> rigid type enforcement can successfully prevent the customer name (text) from being inserted into the integer Customer.creditScore column. On the other hand, if that mistake occurs, it is very easy to spot the problem and find all affected rows.
That doesn't line up with my experience. (In particular, it may not be easy to fix those corrupted rows; the data may be entirely lost.)
> By suppressing easy-to-detect errors and passing through only the hard-to-detect errors, rigid type enforcement can actually make it more difficult to find and fix bugs.
This doesn't line up with my experience at all.
Re: Prefer strict tables in SQLite
#80Coming 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…
Even foreign keys weren't available before 2009, and are still not enabled by default: