ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

I have a 99% pure SQL method. The idea is to version your schema. You can do this in two ways:

Use the 'user_version' pragma command (PRAGMA user_version) to store an incremental number for your database schema version.
Store your version number in your own defined table.

In this way, when the software is started, it can check the database schema and, if needed, run your ALTER TABLE query, then increment the stored version. This is by far better than attempting various updates "blind", especially if your database grows and changes a few times over the years.

One workaround is to just create the columns and catch the exception/error that arise if the column already exist. When adding multiple columns, add them in separate ALTER TABLE statements so that one duplicate does not prevent the others from being created.

With sqlite-net, we did something like this. It's not perfect, since we can't distinguish duplicate sqlite errors from other sqlite errors.

Dictionary<string, string> columnNameToAddColumnSql = new Dictionary<string, string>
{
    {
        "Column1",
        "ALTER TABLE MyTable ADD COLUMN Column1 INTEGER"
    },
    {
        "Column2",
        "ALTER TABLE MyTable ADD COLUMN Column2 TEXT"
    }
};

foreach (var pair in columnNameToAddColumnSql)
{
    string columnName = pair.Key;
    string sql = pair.Value;

    try
    {
        this.DB.ExecuteNonQuery(sql);
    }
    catch (System.Data.SQLite.SQLiteException e)
    {
        _log.Warn(e, string.Format("Failed to create column [{0}]. Most likely it already exists, which is fine.", columnName));
    }
}

If you are doing this in a DB upgrade statement, perhaps the simplest way is to just catch the exception thrown if you are attempting to add a field that may already exist.

try {
   db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN foo TEXT default null");
} catch (SQLiteException ex) {
   Log.w(TAG, "Altering " + TABLE_NAME + ": " + ex.getMessage());
}

ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

Related

Recent Posts