3 ms·
SQLite is not a toy database
- dcchambers 2y agoI 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.
- acuozzo 2y ago> That, and the name ("lite"). It's SQLite like a mineral. Pyrite, for instance. SQL-ite, not SQ-lite.
- zamadatix 2y agoIt "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 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").
- erik_seaberg 2y agoProduction-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.
- dcchambers 2y agoFor 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 https://www.sqlite.org/mostdeployed.html
- a96 2y agoAlthough the common technical meaning of "light" is low resource usage, small footprint and overhead. Opposite of massive or convoluted in the sense of heavy.
- Arcuru 2y agoPrevious: 2021 - https://news.ycombinator.com/item?id=26580614 https://news.ycombinator.com/item?id=26580614 2022 - https://news.ycombinator.com/item?id=32478907 https://news.ycombinator.com/item?id=32478907
- neverartful 2y agoIt'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 https://www.sqlite.org/quirks.html
- sgarland 2y agoThis 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.
- strken 2y agoIt'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.
- bad_username 2y agoMoreover, 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.
- drooby 2y agoSQLite is being used in production. Right now. In your pocket. Almost everyone's pocket.
- a96 2y agoYes. My first thought was that SQLite might actually be the most used and useful "sql" production database ever.
- dzonga 2y agosqlite 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.
- mergisi 2y ago[flagged]
- masfoobar 2y agoI 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!
- nojvek 2y agoIf 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