> 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").
It's not a toy database, but it is a good idea to be familiar with its Quirks, Gotchas, and Caveats:
https://www.sqlite.org/quirks.html
This part never fails to make me twitch:
> 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.
Moreover, later in the page:
> 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.
SQLite is being used in production. Right now. In your pocket. Almost everyone's pocket.
Yes. My first thought was that SQLite might actually be the most used and useful "sql" production database ever.
Previous:
2021 - https://news.ycombinator.com/item?id=26580614
2022 - https://news.ycombinator.com/item?id=32478907
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.
Good little product that packs a punch!
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.
https://www.sqlite.org/mostdeployed.html
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.
> That, and the name ("lite").
It's SQLite like a mineral. Pyrite, for instance.
SQL-ite, not SQ-lite.
It "is" (that's how the creator originally thought about it) but it also isn't really prescribed as the only interpretation https://www.youtube.com/watch?v=Jib2AmRb_rk&t=50s
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").
If you say in schema that a field is int, SQLite will ingest text happily. Even blobs on the field.
No ability to rename columns. Compared to PG DML, it’s quite restrictive.
Not a toy, but plenty of gotchas
sqlite has 99% of the features most people use on a day to day basis.
for the 1% or if you don't wanna deal with the well-documented quirks - then yeah reach for the big guns.
[flagged]