[zzzeek]

the elephant in the room with SQLite is that they refuse to support most forms of ALTER TABLE, that is, to be able to change the structure of a schema.

Their rationalization for this is that the way SQLite stores data, this is more efficiently performed by simply creating a new table and copying all the rows from the old table into the new one, and that one would want to batch all the table changes together rather than emitting individual ALTER statemnts.

from the POV of people who make tools like my own Alembic Migrations, this is an annoyingly insufficient answer because the logistics of recreating table schemas and copying data over is much more complicated than just emitting an ALTER directive. I'd like if SQLite had at least the ability to be extended to support a third party "ALTER" plugin that would under the hood run the intricacies of copying the tables around, rather than pushing this out to the tool creators. It doesn't really matter in most cases, for the use cases used by SQLite, that ALTER would be inefficient.

instead, my users bugged me for years to solve this problem and I have to maintain this thing https://alembic.sqlalchemy.org/en/latest/batch.html which I mostly hate completely.

SQLite's typing model is also very idiosyncratic and is based on a naming convention approach, which I think most users of SQLite don't understand very well, because it works in a completely strange way based on looking for substrings inside of the completely arbitrary names you can assign to types. I can create a column with the datatype ELEPHINT and that is a legitimate datatype which will store integers. there's also the "INTEGER PRIMARY KEY " / "INTEGER PRIMARY KEY AUTOINCREMENT" silliness but that's a relatively mild poor API compared to things MySQL does all over the place, I suppose.