Manual ALTER TABLE Migration vs. Ti.Database.open Flags for Schema Upgrade

Answer to the three core questions

1. Does a single Ti.Database.execute('ALTER TABLE …') automatically recreate all indexes and triggers on the affected table on both platforms?

No. A supported ALTER TABLE operation (e.g., ADD COLUMN, RENAME COLUMN) only changes the table definition. Indexes and triggers that are already attached to the table stay in place; they are not dropped or recreated automatically. However, if the migration script drops the table or recreates it, all associated indexes and triggers are lost unless they are explicitly recreated.

2. If not, what pattern should developers follow to preserve these objects during a schema upgrade?

Use a “copy‑and‑replace” migration pattern for changes that SQLite can’t express in a single ALTER statement (e.g., changing a column type). The steps are:

1. Begin a transaction: Ti.Database.execute('BEGIN TRANSACTION');
2. Create a temporary table with the new schema: Ti.Database.execute('CREATE TABLE new_table AS SELECT ...');
3. Copy data from the old table: Ti.Database.execute('INSERT INTO new_table SELECT ... FROM old_table');
4. Drop the old table: Ti.Database.execute('DROP TABLE old_table');
5. Rename the new table to the original name: Ti.Database.execute('ALTER TABLE new_table RENAME TO old_table');
6. Re‑create any indexes and triggers that were on the original table. You can script them from sqlite_master before dropping the table or maintain a versioned migration script that includes the DDL for each index/trigger.
7. Commit the transaction: Ti.Database.execute('COMMIT');

For simple ADD COLUMN or RENAME COLUMN operations where the table is not dropped, you can run the ALTER statements directly and then verify that sqlite_master still lists the indexes and triggers.

3. Can Ti.Database.open with Ti.Database.CREATE be used to apply a schema change without dropping indexes, or must indexes be recreated manually?

The Ti.Database.CREATE flag only ensures that the database file exists; it does not run any migration logic or modify the existing schema. If you need to change the schema, you must execute ALTER TABLE or use a migration script as described above. The flag does not preserve or recreate indexes automatically; you will still need to recreate them if the underlying table is dropped or recreated.

Practical verification steps

// Verify indexes/triggers after an ALTER
Ti.Database.execute('ALTER TABLE myTable ADD COLUMN newCol TEXT');
var rows = Ti.Database.execute(
  "SELECT name, sql FROM sqlite_master WHERE type IN ('index','trigger') AND tbl_name='myTable'"
);
while (rows.isValidRow()) {
  Ti.API.info('Object: ' + rows.fieldByName('name') + ' SQL: ' + rows.fieldByName('sql'));
  rows.next();
}
rows.close();

Running the above after an ALTER confirms whether the indexes/triggers still exist. If you drop and recreate a table, run the same query to see that they are missing and need to be recreated.

What we still need to know

To tailor the migration script to your exact needs, could you share the current number of indexes and triggers on the table you plan to modify? That will help us generate the exact DDL needed to recreate them after a table replacement.