Android Schemas for Mobile Development
Article Summary
Dan Lew from Atlassian reveals how Trello Android went from dropping the entire database on every schema change to implementing proper upgrades. The reason? They were secretly building offline mode.
For years, Trello Android took a nuclear approach to database upgrades: drop everything and repopulate from the server. This worked fine when the server was the single source of truth. But when the team started building offline functionality, they needed a real migration strategy that wouldn't lose unsynced data.
Key Takeaways
- Store schema as assets: one create.sql plus numbered upgrade files (1.sql, 2.sql, etc.)
- Write individual tests for each upgrade path to prevent data loss
- Add a test that fails if DB_VERSION bumps without new upgrade tests
- Verify all sequential upgrades produce same schema as create.sql
- Sequential upgrades let you focus on one version transition at a time
A simple file-based schema upgrade strategy with three types of automated tests gives you confidence that database migrations won't lose offline user data.
About This Article
Trello Android used to drop the entire database whenever the schema changed. This approach fell apart once the team started building offline functionality, because unsynced user data that hadn't reached the server yet could be lost.
Dan Lew's team built a file-based migration system. It stores one create.sql file plus numbered upgrade files like 1.sql, 2.sql, and so on. This lets you apply upgrades sequentially from any version to any other version.
They set up three layers of testing. Individual upgrade tests check each migration works. Schema equivalence validation ensures the end result is correct. Version bump enforcement prevents mistakes. Together, these eliminated the risk of unrecoverable database states and made it possible to develop migrations using test-driven development.