The limited type system and loose data validation always prevented me of using SQLite.
It's a nice data store but a poor database in my opinion.
It's a nice data store but a poor database in my opinion.
This.
"limited type system" is actually an exaggeration in my opinion. The "data types" seem to only serve documentation purposes. You can always store any value in any column regardless of the "data type"
You don't actually need to specify column types at all. You can just leave them out. Or make up entirely new types.
Foreign key constraints are also not enforced by default but there is at least an option to turn that on.
Yes
https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed...
You don't actually need to specify column types at all. You can just leave them out. Or make up entirely new types like `BLIB` or whatever.
Foreign key constraints are also not enforced by default but there is at least an option to turn that on.
I'm keeping a close eye on DuckDB which looks like a modern saner version of SQLite.
Sqlite is very unusual in that it uses static type declarations, but it doesn't actually enforce them. That can be quite unexpected.
See also: "Why wouldn't you check the type of your variable before passing it to a function?"
You have to write tons of wrappers and boilerplate to handle those insertion failures instead of cleaning up your data during acquisition.
>See also: "Why wouldn't you check the type of your variable before passing it to a function?"
Completely different workflow. The types of data is known during acquisition.
Also, there is much more boilerplate in man-in-the-middle-ing each database query to validate data rather than have a single entry point which validate the data types which is the database.
Also, using any in-database feature like functions or triggers should also have this security baked in but won't due to SQLite's missing validation.
No...because they can be boiled down to the sqlite types and again it doesn't matter if you properly validate the data on insertion which you should be doing.
>Also, there is much more boilerplate in man-in-the-middle-ing each database query to validate data rather than have a single entry point which validate the data types which is the database.
No...you don't validate queries..you validate insertion
>Also, using any in-database feature like functions or triggers should also have this security baked in but won't due to SQLite's missing validation.
You don't need to depend on it...because you validated on insertion.