Ti.Database schema change leaves database partially migrated on failure

Answer

No. A failing ALTER TABLE does not reliably roll back a preceding CREATE TABLE on all current Titanium SDK versions and platforms. SQLite itself can roll back DDL inside an explicit transaction, but Ti.Database does not guarantee that your DDL runs inside one transaction. The explicit transaction API, error propagation, and autocommit behavior vary across SDK releases and between iOS and Android. Relying on Ti.App.Properties version alone will hide a partial migration.

Confirmed facts

• Ti.Database is low‑level SQLite access. There is no built‑in migration runner.
• Schema versioning is manual, typically via Ti.App.Properties with hand‑written DDL.
• SQLite can roll back DDL within a single BEGIN…COMMIT block, including CREATE TABLE followed by a failing ALTER TABLE.

Likely explanation for partial migrations

• Many apps issue separate Ti.Database.execute calls in autocommit mode. Each successful statement is committed immediately.
• Ti.Database.transaction behavior is version and platform sensitive. Some releases do not wrap DDL in a true SQLite transaction, or they commit on first success.
• Updating the stored version key before COMMIT makes a partial schema invisible on restart and prevents safe retry.
• SQLite ALTER TABLE is limited. Complex changes require multi‑statement table recreation which is more prone to partial apply if not wrapped correctly.

Detect a partially applied schema on next launch

Do not trust the version key alone. Introspect the database.

1. Read stored schema version from Ti.App.Properties.
2. Query sqlite_master for expected tables and PRAGMA table_info for expected columns and indexes for the target version.
3. Compare to a canonical manifest for that version. Mismatches indicate a partial migration.
4. Log journal_mode to confirm whether BEGIN/COMMIT are actually sent by your SDK version on the platform in use.

// Example introspection, not a full runner
var db = Ti.Database.open('app.db');
var tables = db.execute('SELECT name FROM sqlite_master WHERE type="table"');

Atomic upgrade pattern without a framework

1. Read stored version. If behind, do not update the version key yet.
2. Start an explicit transaction. On SDKs where Ti.Database.transaction is reliable, use it; otherwise send BEGIN IMMEDIATE via execute and manage COMMIT/ROLLBACK yourself.
3. Run all DDL for the next version in order inside the transaction.
4. Verify critical objects exist via sqlite_master and PRAGMA table_info before COMMIT.
5. On success, COMMIT, then persist the new version to Ti.App.Properties.
6. On any error, ROLLBACK and leave the version unchanged so the upgrade can be retried safely.

Idempotent DDL and a pre‑upgrade snapshot of sqlite_master make retries safer, but idempotency does not guarantee atomicity and can mask data‑schema drift.

Missing diagnostic detail

The recommendation changes with the exact Titanium SDK version and target platform. Confirm which SDK version and iOS/Android you are running, and whether Ti.Database.transaction in that release actually sends BEGIN/COMMIT for DDL. That determines whether an explicit transaction wrapper is sufficient or you need to fall back to manual BEGIN IMMEDIATE with manual rollback.