Incremental ALTER Scripts vs Copy‑and‑Recreate for Safe Rollback in libgdx gdx-sql

Direct answer

Incremental ALTER scripts guarantee rollback on SQLite < 3.35 when the schema change is expressible with ALTER TABLE and the entire migration runs inside a single transaction. Copy-and-recreate is the portable fallback when the change cannot be reversed with ALTER—for example, dropping or retyping a column, which pre-3.35 does not support. The premise that DDL auto-commits before 3.35 is incorrect; SQLite has supported transactional DDL well before 3.35, so version alone does not force copy-and-recreate.

Confirmed facts vs. likely explanation

• Confirmed: gdx-sql is a thin SQL wrapper, not a migration framework. Track your own schema version with PRAGMA user_version or a migrations table and run explicit up/down steps.
• Confirmed: Pre-3.35 lacks ALTER TABLE DROP COLUMN; pre-3.25 lacks RENAME COLUMN and related reference fixes.
• Likely explanation: If a migration cannot be reversed with ALTER on your minimum supported SQLite version, the only safe path is copy-and-recreate using CREATE TABLE, INSERT INTO ... SELECT, DROP, and RENAME.

Verifying incremental ALTER down-migrations without a backup file

1. Clone the schema and data into a disposable in-memory or temporary database.
2. Apply the up-migration to reach version N+1.
3. Apply the down-migration to return to version N.
4. Compare canonical sqlite_master output and per-table data checksums against the baseline.
5. Re-apply the up-migration to confirm the round-trip is stable.

Copy-and-recreate verification and latency

For copy-and-recreate, run the swap with PRAGMA foreign_keys=OFF, then run PRAGMA foreign_key_check afterward. Recreate triggers, indexes, generated columns, and WITHOUT ROWID tables explicitly. No universal latency figure is defensible: cost scales with rows copied, index and trigger rebuild, and journal/WAL fsync behavior. Android and iOS devices ship different SQLite versions and storage performance. Measure on the minimum Android API level and iOS version you support using a representative table, recording wall time, peak temporary space, journal mode, synchronous setting, and cold versus warm cache. Report medians and p95 across device tiers rather than a single measurement.

One diagnostic that changes the recommendation

If your schema change requires dropping or retyping a column, ALTER-based rollback is not expressible on pre-3.35 and copy-and-recreate becomes mandatory. If the change is limited to adding a column or renaming, incremental ALTER inside a transaction is sufficient.