Titanium JS: Error trying to apply a database migration

Question

I've added a new column to my models/beers.js file, which needs a database migration for current users of my app. I created a migration as per the documentation, but now when I run my app in the simulator, I get the following error:

[ERROR] :  Script Error {
[ERROR] :      backtrace = "#0 Migrate() at file:///Users/owen/Library/Application%20Support/iPhone%20Simulator/7.1-64/Applications/2BAE6ECA-8995-4E0F-BA2F-9CF00162E81E/Beer%20Pad.app/alloy/sync/sql.js:245\n#1 () at file:///Users/owen/Library/Application%20Support/iPhone%20Simulator/7.1-64/Applications/2BAE6ECA-8995-4E0F-BA2F-9CF00162E81E/Beer%20Pad.app/alloy/sync/sql.js:329\n#2 () at file:///Users/owen/Library/Application%20Support/iPhone%20Simulator/7.1-64/Applications/2BAE6ECA-8995-4E0F-BA2F-9CF00162E81E/Beer%20Pad.app/alloy.js:95\n#3 () at file:///Users/owen/Library/Application%20Support/iPhone%20Simulator/7.1-64/Applications/2BAE6ECA-8995-4E0F-BA2F-9CF00162E81E/Beer%20Pad.app/alloy/models/Beers.js:100";
[ERROR] :      line = 72;
[ERROR] :      message = "invalid SQL statement";
[ERROR] :      nativeLocation = "-[TiDatabaseProxy execute:] (TiDatabaseProxy.m:191)";
[ERROR] :      nativeReason = "Error Domain=com.plausiblelabs.pldatabase Code=3 \"An error occured parsing the provided SQL statement.\" UserInfo=0xfa7d920 {com.plausiblelabs.pldatabase.error.vendor.code=1, NSLocalizedDescription=An error occured parsing the provided SQL statement., com.plausiblelabs.pldatabase.error.query.string=ALTER TABLE beers ADD COLUMN is_sample BOOLEAN;, com.plausiblelabs.pldatabase.error.vendor.string=no such table: beers}";
[ERROR] :      sourceId = 351886816;
[ERROR] :      sourceURL = "file:///Users/owen/Library/Application%20Support/iPhone%20Simulator/7.1-64/Applications/2BAE6ECA-8995-4E0F-BA2F-9CF00162E81E/Beer%20Pad.app/alloy/models/Beers.js";
[ERROR] :  }
[ERROR] :  Script Error Module "alloy/models/Beers" failed to leave a valid exports object

Here is my model file code

exports.definition = {
config: {
    columns: {
        "name": "text",
        "brewery": "text",
        "rating": "integer",
        "percent": "integer",
        "establishment": "text",
        "location": "text",
        "notes": "text",
        "date": "text",
        "date_string": "text",
        "beer_image": "text",
        "latitude": "integer",
        "longitude": "integer",
        "favourite": "boolean",
        "is_sample": "boolean"  // this is the new column
    },
    adapter: {
        type: "sql",
        collection_name: "beers"
    }
}
/* and so on ... */

Here is my recently added migration file:

201405291604237_Beers.js

migration.up = function(migrator) {
    migrator.db.execute('ALTER TABLE ' + migrator.table + ' ADD COLUMN is_sample BOOLEAN;');
};

migration.down = function(migrator) {
    var db = migrator.db;
    var table = migrator.table;
    db.execute('CREATE TEMPORARY TABLE beers_backup(alloy_id,name,brewery,rating,percent,establishment,location,notes,date,date_string,beer_image,latitude,longitude,favourite);');
    db.execute('INSERT INTO beers_backup SELECT alloy_id,name,brewery,rating,percent,establishment,location,notes,date,date_string,beer_image,latitude,longitude,favourite FROM ' + table + ';');
    migrator.dropTable();
    migrator.createTable({
        columns: {
            "name": "text",
            "brewery": "text",
            "rating": "integer",
            "percent": "integer",
            "establishment": "text",
            "location": "text",
            "notes": "text",
            "date": "text",
            "date_string": "text",
            "beer_image": "text",
            "latitude": "integer",
            "longitude": "integer",
            "favourite": "boolean"
        },
    });
    db.execute('INSERT INTO ' + table + ' SELECT alloy_id,name,brewery,rating,percent,establishment,location,notes,date,date_string,beer_image,latitude,longitude,favourite FROM beers_backup;');
    db.execute('DROP TABLE beers_backup;');
};

So, where am I going wrong here? I've followed the example in the documentation and also some tutorials too.

Answer 1

I solved this by adding an initial migration as well. I created another migration, which had an older datetime code prefixed in its file name and then added the following:

migration.up = function(migrator) {
    migrator.createTable({
        columns: {
            "name": "text",
            "brewery": "text",
            "rating": "integer",
            "percent": "integer",
            "establishment": "text",
            "location": "text",
            "notes": "text",
            "date": "text",
            "date_string": "text",
            "beer_image": "text",
            "latitude": "integer",
            "longitude": "integer",
            "favourite": "boolean"
        },
        adapter: {
            type: "sql",
            collection_name: "beers"
        }
    });
};

migration.down = function(migrator) {
    migrator.dropTable("beers");
};

I'm not sure why I needed to do this, as I thought that creating a model file would take care of the initial creation of the table, but this worked and solved my problem!

Answer 2

Read the error message.

NSLocalizedDescription=An error occured parsing the provided SQL statement., com.plausiblelabs.pldatabase.error.query.string=ALTER TABLE beers ADD COLUMN is_sample BOOLEAN;, com.plausiblelabs.pldatabase.error.vendor.string=no such table: beers}";

You are trying to ALTER TABLE beers ,but there is no such a table.