Comment by chuckadams
16 days ago
No network, no write concurrency, no types to speak of... Where those things aren't needed, sqlite is the de facto standard. It's everywhere.
16 days ago
No network, no write concurrency, no types to speak of... Where those things aren't needed, sqlite is the de facto standard. It's everywhere.
Perfect summary. I'll add: insane defaults that'll catch you unaware if you're not careful! Like foreign keys being opt-in; sure, it'll create 'em, but it won't enforce them by default!
Is it possible to fix some of these limitations by building DBMSes on top of SQLite, which might fix the sloppiness around types and foreign keys?
Using the API with discipline goes a long way.
Always send "pragma foreign_keys=on" first thing after opening the db.
Some of the types sloppiness can be worked around by declaring tables to be STRICT. You can also add CHECK constraints that a column value is consistent with the underlying representation of the type -- for instance, if you're storing ip addresses in a column of type BLOB, you can add a CHECK that the blob is either 4 or 16 bytes.
SQLite did add 'STRICT' tables for type enforcement.
Still doesn't have a huge variety of types though.
The fact that they didn’t make STRICT default is really a shame.
I understand maintaining backwards compatibility, but the non-strict behavior is just so insane I have a hard time imagine it doesn’t bite most developers who use SQLite at some point.
> The fact that they didn’t make STRICT default is really a shame.
SQLite makes strong backwards-compatibility guarantees. How many apps would be broken if an Android update suddenly defaulted its internal copy of SQLite to STRICT? Or if it decided to turn on foreign keys by default?
Those are rhetorical questions. Any non-0 percentage of affected applications adds up to a big number for software with SQLite's footprint.
Software pulling the proverbial rug out from under downstream developers by making incompatible changes is one of the unfortunate evils of software development, but the SQLite project makes every effort to ensure that SQLite doesn't do any rug-tugging.
Nearly every default setting in sqlite is "wrong" from the outset, for typical use cases. I'm surprised packages that offer a sane configuration out of the box aren't more popular.
I mean it has blob types. Which basically means you can implement any type you want. You can also trivially implement custom application functions to work on these blob types in your queries. [1]
- [1] https://sqlite.org/appfunc.html