Using Models and Migrations in Titanium

Appcelerator Models allow you to create and manipulate data stored using backbone.js in various persistent storage. One of the most capable and easy storage choices is the SQLite database, available on both iOS and Android devices. You can read about how to setup an SQL model in the Appcelerator documentation here: http://docs.appcelerator.com/platform/latest/#!/guide/Alloy_Sync_Adapters_and_Migrations

The example from Appcelerator shows defining a model called book, using the following:

book.js

exports.definition = {
    config: {
      "columns": {
        "title": "TEXT",
        "author": "TEXT",
        "book_id": "INTEGER PRIMARY KEY AUTOINCREMENT"
    },
    "adapter": {
      "type": "sql",
      "collection_name": "books",
      "idAttribute": "book_id"
      }
    }
}

This file would be placed in the models folder of your project. Now you create your project, publish and never touch your model again! Not likely :)

The first time you add data to this model, the database file is created named _alloy_.sql and the table and record are added. This is VERY IMPORTANT TO NOTE, so I will say it again, the first time you add data to this model, the database file is created named _alloy_.sql and the table and record are added. Why is this so important? It’s really because you can get yourself in trouble assuming your file and database exist, when they don’t until you add your first record of data. I have had developers ask me why they can’t find their database that they have defined using the models and migrations… They are trying to find in the file in a simulator or on a device. It’s because until you put the first record in, the model is not created. Anytime you add a new model file, it will create a new table when you add a new record to the model.

So, when you decide that the book model needs to be ‘expanded’, how do we do that? The logical approach is to use the migration capabilities built into Alloy. The scenario we will use is that we need to expand our book definition to include the ISBN number as a new field.

When you are modifying an existing table, you need to do the following:

1. Make sure your model reflects the final version of the table, including the fields you are adding.
2. Create an initial migration that uses the migrator.createTable to create your ORIGINAL database structure.
3. Create a second migration that does the add column sql code.

So, in our book example above, we need to make the following changes:

book.js — Step 1: The model file needs to reflect the new structure. So we add the isbn field to the definition. See below in BOLD

exports.definition = {
config: {
      "columns": {
        "title": "TEXT",
        "author": "TEXT",
        "book_id": "INTEGER PRIMARY KEY AUTOINCREMENT",
        "isbn" : "TEXT"
    },
    "adapter": {
      "type": "sql",
      "collection_name": "books",
      "idAttribute": "book_id"
      }
    }
}

Step 2 : This initial migration needs to run the createTable function with the original structure. The migration is shown below. Notice it only includes the original database fields from the original book.js model, not the isbn.

201701010258407_book.js

migration.up = function(migrator) {
     migrator.createTable({
     "columns": {
        "title": "TEXT",
        "author": "TEXT",
        "book_id": "INTEGER PRIMARY KEY AUTOINCREMENT"
     }
   });
};
migration.down = function(migrator) {
};

Step 3: Now we create another migration that will run after the first migration and add the field.

201701070103325_book.js

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

Hopefully that will help anyone in how to implement migrations in Alloy.