SQLite Forum

I'm looking for a robust way to solve this. I appear to be using 3.29.1.

I have an app that I distribute, which uses various SQLite databases. From time to time, as I add new features, I need to add columns here and there to some tables. So far I've been doing that by checking a value stored as a data-version-number in a globals table in a database, and when necessary I bump that number and execute a number of ALTER TABLE ADD COLUMN statements as required.

This is all done in the app startup, and normally works without issue. However, recently a user had an unrelated problem and I gave him an unreleased version to test. Unfortunately it seems I broke my update procedure for his set of databases and now he can't update to the next released version. I expect that some column I want to add is, now, already in a table.

Once I get hold of him it should be easy enough to diagnose and fix as he's comfortable with Terminal and the sqlite3 CLI. But I want to fix this for the future by being able to detect this specific error (duplicate column) if it happens and ignore it.

1) There is unfortunately no IF NOT EXISTS for ADD COLUMN. Would this be difficult to add, as a feature request? Then I could simply do such as ALTER TABLE MYTABLE ADD COLUMN IF NOT EXISTS NEWCOL INTEGER. This would be the most robust method.

2) As things stand, with "duplicate column" I get error numner 1 (generic error, not too useful) and a message such as "duplicate column name: newcol". I could parse this, but don't want to depend on error message content.

3) I could parse the sqlite_master table but I'd rather not do that either.

Have I overlooked a better approach?