I created a schema and accidentally put “test” instead of “text” in a column type. To fix it, I tried copying it to a new column, since you can’t alter a column in sqlite, then copying it back, but that lost the NOT NULL constraint.
Here’s what I did:
ALTER TABLE deploy_cmds ADD COLUMN x TEXT; UPDATE deploy_cmds SET x = command;
ALTER TABLE deploy_cmds DROP COLUMN command;
ALTER TABLE deploy_cmds ADD COLUMN command TEXT; UPDATE deploy_cmds SET command=x;
ALTER TABLE deploy_cmds DROP COLUMN x;
PRAGMA writable_schema = 1;
UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE deploy_cmds(id INTEGER PRIMARY KEY ASC, command TEXT NOT NULL)' WHERE NAME = 'deploy_cmds';
PRAGMA writable_schema = 0;
[Disconnect and reconnect to refresh schema]
Another less dangerous option would have been to create a new table with the correct schema, “INSERT INTO” it, drop the old table, and then “ALTER TABLE x RENAME TO y”.