> Some commentators say that SQLite is "weakly typed" and that other SQL databases are "strongly typed". We consider these terms to be inaccurate and even pejorative. We prefer to say that SQLite is "flexibly typed" and that other SQL database engines are "rigidly typed".
A database, relational or otherwise, should have strict typing, full stop. It may have transparent casting on retrieval, but if you try to store an INT in a TEXT column, it should throw an error. To do otherwise is to have questionable data integrity.
I like SQLite for small projects that don’t need a remote DB, and I like that Python has a native library for it. I do not, however, like its many gotchas, like the above, or how you have to explicitly enable foreign keys.
It's hard to know what to do about SQLite's dangerous defaults. Changing them breaks backwards compatibility, while leaving them means some users are unintentionally using their database without referential integrity or strict tables.
Maybe you wait until v4? But v3 was 20 years ago and there's no obvious reason to break backwards compatibility or change the API yet, so that's just kicking the can down the road.
> SQLite says that the following query returns false:
> SELECT 1='1';
> It does this because an integer is not a string. Every other major SQL database engine says this is true, for reasons that the creator of SQLite does not understand.
The mind that allowed strings be accepted into integer columns should not find it hard to understand.
I have become fond of sqlite in the last few years.
Originally my goto for databases has been SQL Server or MySQL (depending on what I need to do or use) even on my development machine. Now, I jump straight in with sqlite.
I wrote a program which was storing data in files. I moved it over to sqlite for the query abilities. It was sooo much faster and easier to maintain.
I can't help but wonder if devs feel this way because several full stack web frameworks default to using SQLite in their dev environment. That, and the name ("lite").
Of course that's not at all true, SQlite is an incredibly robust production-grade tool that is deployed to billions of devices, but it sure feels like that caused damage to the brand.
That said I think this is actually becoming less of an issue these days. Certainly feels like there has been a resurgence in SQLite appreciation and usage over the past few years.
Production-grade data needs to be redundant across regions to outlive a disaster. I was glad to hear that SQLite replication exists, but it's an add-on (Litestream) that isn't strongly consistent nor multi-master.
For a SaaS product? Maybe - but even that's debatable. I imagine snapshots/backups stored off-site/in other regions are more than fine for the majority of SaaS businesses - certainly almost all businesses under a certain size. Sure you risk some minor data loss in a true disaster (like, meteor hits a datacenter disaster), but generally I think your odds are pretty good.
But that's just a subset of what databases are useful for. So much production data lives on-device with no replication or redundancy at all. It's still production-grade data.
Although the common technical meaning of "light" is low resource usage, small footprint and overhead. Opposite of massive or convoluted in the sense of heavy.
Though I'll also note the "lite" crowd often interpret the "L" as double duty/shared (like the "m" in "dramedy" isn't necessarily interpreted as solely part of "drama" or "comedy").
https://www.sqlite.org/quirks.html