An article recommends using STRICT tables in SQLite, introduced in version 3.37.0 (2021-11-27), to enforce data type constraints and improve database reliability, especially when multiple applications share the same database. This tip addresses a common criticism of SQLite's dynamic typing, making it more robust for multi-application scenarios and encouraging safer database practices. It could drive adoption of STRICT tables and potentially influence future defaults. STRICT tables enforce exact data types and disallow type coercion, but they do not support some types like DATE. Each table must be explicitly declared as STRICT.
Background
SQLite traditionally uses dynamic typing (flexible typing), where columns can store any data type regardless of the declared type, which can lead to accidental type mismatches. STRICT tables, available since SQLite 3.37.0, change this by enforcing strict type checking, ensuring that inserted data matches the declared column type. This is particularly beneficial when a database is shared across multiple applications or when stronger type safety is desired.
References
Discussion
Comments generally welcome the feature, with some users wishing STRICT were the default. Concerns include missing data types like DATE and debates about SQLite's use case as an embedded database versus shared databases. The discussion reflects a desire for more type safety while acknowledging SQLite's flexibility.