3 ms·

No foreign keys by default. Using them means that every connecting session must toggle it on. Forget one and it will be free to violate referential integrity.

by chasil 3y ago

No foreign keys by default. Using them means that every connecting session must toggle it on. Forget one and it will be free to violate referential integrity. One writer. Any session that issues BEGIN TRANSACTION and then hangs halts all dml. WAL mode confusion. WAL cannot safely be used on network filesystems, and it breaks ACID on ATTACHed databases, among other problems. Date and time types don't really exist. There are functions to assemble your own, but it does require some thought. The ODBC driver for SQLite does have options to "emulate" this. Length specifications on a column are ignored. CHAR(2) will allow the insertion of a blob. I think that check constraints could be used to enforce this. Type affinity means that any data type can be inserted into columns declared as any other data type. Rigid type enforcement can be done, but it is not the default. Those are the major eye-openers.

anitil 3y ago
I believe there is a recent (optional) strict mode that will enforce types. I haven't used it so I don't know how full-featured it is. Your other points are of course correct
- chrismorgan 3y ago
  Unfortunately, strict mode is incompatible with name-based type mapping: if you have a column that you want to be an RFC 3339 date-time (e.g. "2023-06-06T23:25:27+11:00"), for example, the only spelling permitted for the column type is “TEXT”, whereas without strict mode you can name it “DATETIME” or similar and popular libraries in various languages will automatically map that to the language’s datetime type (according to local conventions, not anything universally defined—e.g. https://docs.rs/sqlx/latest/sqlx/sqlite/types/index.html https://docs.rs/sqlx/latest/sqlx/sqlite/types/index.html shows the built-in mappings the Rust sqlx library has decided to support). This destroys the ergonomics of strict mode for me, rendering strict mode quite undesirable, since it’s basically fighting with a more useful sort of strictness. (I wrote a little more about this, with examples, at https://news.ycombinator.com/item?id=33282830 https://news.ycombinator.com/item?id=33282830.)
norir 3y ago
These are all fair points though they mostly can be addressed at the application layer. One could, for example, use a wrapper that opens the sqlite library and immediately turns foreign keys on. One does this once per language in your project and never think about it again. Similarly, type constraints can be enforced at the application layer. I agree that it would be nice if some of these things were built in but at least sqlite is reliable when configured and used in particular ways.

by chasil 3y ago

anitil 3y ago

I believe there is a recent (optional) strict mode that will enforce types. I haven't used it so I don't know how full-featured it is. Your other points are of course correct

chrismorgan 3y ago
Unfortunately, strict mode is incompatible with name-based type mapping: if you have a column that you want to be an RFC 3339 date-time (e.g. "2023-06-06T23:25:27+11:00"), for example, the only spelling permitted for the column type is “TEXT”, whereas without strict mode you can name it “DATETIME” or similar and popular libraries in various languages will automatically map that to the language’s datetime type (according to local conventions, not anything universally defined—e.g. https://docs.rs/sqlx/latest/sqlx/sqlite/types/index.html https://docs.rs/sqlx/latest/sqlx/sqlite/types/index.html shows the built-in mappings the Rust sqlx library has decided to support). This destroys the ergonomics of strict mode for me, rendering strict mode quite undesirable, since it’s basically fighting with a more useful sort of strictness. (I wrote a little more about this, with examples, at https://news.ycombinator.com/item?id=33282830 https://news.ycombinator.com/item?id=33282830.)

norir 3y ago

These are all fair points though they mostly can be addressed at the application layer. One could, for example, use a wrapper that opens the sqlite library and immediately turns foreign keys on. One does this once per language in your project and never think about it again. Similarly, type constraints can be enforced at the application layer. I agree that it would be nice if some of these things were built in but at least sqlite is reliable when configured and used in particular ways.