Appcelerator Alloy migration throwing SQL error when app is freshly installed

Question

in my application I am having a model with some columns, which needs to be added a new column.

So according to the documentation I wrote an migration file with an SQL "alter table add column.." and added the property to the alloy model file as well. Like expected this worked perfectly.

But when the app is installed on a device for the first time an SQL error is thrown saying that the column my migration is trying to add is already existing. Since the database schema is created from the model files I guess the exception is correct, but I am wondering howto accomplish a database change for existing and freshly installed APPS. Removing the migration file, just adding the property to the model file will make it work on fresh installation, but not on updates.

Best regards, Sven

Update 1: I tried adding an initial migration creating the table without the new field and then adding the new field in another migration (see answer from Ray). Still the same error.

Appcelerator Version: 5.2.2

Model-adapter-type: sqlrest

Update 2 (some Code):

model:

config: {
URL: Alloy.Globals.jsonEndPoint + Alloy.Globals.jsonRequestParams + "foto",
columns:{
    id:                                     "INTEGER PRIMARY KEY AUTOINCREMENT",
    dateiname:                              "TEXT",
    beschreibung:                           "TEXT",
    primaerfoto:                            "TEXT",
    aufnahmedatum:                          "TEXT",
    anlage_id:                              "INTEGER",
    foto_label_id:                          "INTEGER",
    latest_sync_date:                       "TEXT",
    dirty:                                  "INTEGER",
    begehungsbericht_protokoll_id:          "INTEGER",          
    begehungsbericht_protokoll_server_id:   "INTEGER",          
},
adapter: {
    remoteBackup: false,                        //Prevent database from being saved in icloud   
    db_name: this.Alloy.Globals.currentDatabase,
    type: "sqlrest",
    collection_name: "foto",
    idAttribute: "id"
}

migration 1:

migration.up = function(migrator) {
Ti.API.info(">>>>>>>>>>>>>>>> migrate create table UP <<<<<<<<<<<<<");
migrator.createTable({
    columns: {
        id:                                     "INTEGER PRIMARY KEY AUTOINCREMENT",
        dateiname:                              "TEXT",
        beschreibung:                           "TEXT",
        primaerfoto:                            "TEXT",
        aufnahmedatum:                          "TEXT",
        anlage_id:                              "INTEGER",
        foto_label_id:                          "INTEGER",
        latest_sync_date:                       "TEXT",
        dirty:                                  "INTEGER",
        begehungsbericht_protokoll_id:          "INTEGER",
    }
});

migration 2:

migration.up = function(migrator) {
Ti.API.info(">>>>>>>>>>>>>>>> migrate ALTER table UP <<<<<<<<<<<<<");
migrator.db.execute('ALTER TABLE foto ADD COLUMN begehungsbericht_protokoll_server_id INTEGER;');

};

Update 3 (solution with workaround): Since I know the number of columns the table should have when running the migration I use this information for a conditional adding of the column (like Cesar proposed).

migration.up = function(migrator) {
Ti.API.info("migrating foto table");

var rows = migrator.db.execute("SELECT * FROM foto");
Ti.API.info("field count: " + rows.fieldCount);

if (rows.fieldCount < 11) {
    Ti.API.info("adding column: begehungsbericht_protokoll_server_id");
    migrator.db.execute('ALTER TABLE foto ADD COLUMN begehungsbericht_protokoll_server_id INTEGER');
} else {
    Ti.API.info("NOT adding column: begehungsbericht_protokoll_server_id");
}

};

Added workaround solution based on the answer of Cesar. Dont like that too much but for the moment its ok. — Sven
I wonder if the SQLRest migration is working properly? The problem with a custom adapter is that it may not work the same way as others... — Ray

Cesar Cavazos Cesar Cavazos · Accepted Answer · 2017-01-23T18:41:29

DB migrations are always a complicated piece in app development. That said, there are pros and cons which will at some point make you wipe out the entire DB and start from scratch on major updates.

The good thing is that you should be able to check if the column exist or not, either by doing a simple SELECT in the migration.up function and validating if it was a successful query using http://docs.appcelerator.com/platform/latest/#!/api/Titanium.Database.ResultSet-method-isValidRow

You can also "version" your DB by adding a single Alloy.Globals attribute with every change you do to the DB schema. Something like Alloy.Globals.DatabaseVersion = 1 and validate this in your migration.up and migration.down functions, this way you at least know where you are in your current DB schema and where you want to be in your migrated DB schema.

The not-so-good thing is that with every change you will have to write code to manage all the possible uses cases (e.g. a user with version 1 jumps to version 3 without going to 2) so think about this also as you go.

Appcelerator Alloy migration throwing SQL error when app is freshly installed

2 Answers