SQLite TEXT Affinity Does Not Enforce Text Storage
A SQLModel field declared as String becomes a SQLite TEXT-affinity column, but SQLite can still store a BLOB there. In a direct-write reproduction, UPDATE book SET title = X'FF' passed CHECKs based on trim(), length(), and NUL detection; a SQLModel read returned bytes and BookRead.model_validate() failed because the response expects str. Add typeof(title) = 'text' checks for every user-facing text column (including notes), and remember create_all() will not migrate an existing table to add them. Reproduced with Python 3.10.6, SQLModel 0.0.49, SQLAlchemy 2.0.57.
SQLite cannot add a CHECK to an existing table with
ALTER TABLE. Find non-text rows withWHERE typeof(title) <> 'text'and rebuild the table to add the constraint. If you have hit a variant of this, share it below.