SQLite is not a toy database
antonz.org
antonz.org
> 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.
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.
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.
Good little product that packs a punch!
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.
It's SQLite like a mineral. Pyrite, for instance.
SQL-ite, not SQ-lite.
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").
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.
No ability to rename columns. Compared to PG DML, it’s quite restrictive.
Not a toy, but plenty of gotchas
for the 1% or if you don't wanna deal with the well-documented quirks - then yeah reach for the big guns.