1. Can not rename column, in a fast iteration project, renaming schema is quite common
2. No easy way to upsert data. There should be an easy equivalent to MySQL's "INSERT ... ON DUPLICATE KEY UPDATE". If you use REPLACE you got PK changed.
Just today I encountered an "insert or update" situation and handled it like this:
INSERT OR IGNORE INTO table_name (item_key, item_count) VALUES (?, 0);
UPDATE table_name SET item_count = item_count+1 WHERE item_key = ?;
I'm not familiar with "upsert" so maybe there is a difference, but this worked well for my use case (which was "increment or insert then increment"). You could even reverse the operations (by adding OR IGNORE to the UPDATE statement), if you wanted to not do two operations if the pk didn't exist yet.
Postgres still doesn't have upsert either. Neither does sql server or Oracle. I hardly see that as a knock against SQLite. All of the above have various levels of hackey workaround so for this though.
1. Can not rename column, in a fast iteration project, renaming schema is quite common
2. No easy way to upsert data. There should be an easy equivalent to MySQL's "INSERT ... ON DUPLICATE KEY UPDATE". If you use REPLACE you got PK changed.