Trello Android Schema Upgrades

For many years, Trello Android has used a simple system for upgrading the database schema: drop the entire database, recreate it, and repopulate the data from the server!

While far from ideal, this solution worked because the Trello server was the only source of truth. Any change you made in the app was immediately transmitted to the server. If you were offline, we just wouldn't allow you to make the change.

With us now (secretly) working on making Trello work offline, we have to abandon this tactic. We can't simply drop the database because there may be offline data that hasn't been sent to the server yet.

That means we have to do proper schema upgrades.

Setup

We store all our schema SQL as assets. We have a single create.sql as well as one file per schema upgrade (1.sql, 2.sql, etc).

Our strategy:

• If the user has no database, apply create.sql and call it a day.

• If the user needs a database upgrade from A to Z, then apply a+1.sql, a+2.sql, ..., z.sql.

There are a few advantages to this strategy:

1. There is a simple source for our current schema.
2. There is a documented history of schema changes throughout the ages.
3. Since we just apply updates from A to Z sequentially, we can focus on one upgrade at a time for implementation and testing.

Testing

Testing schema upgrades is important. We want to avoid losing data! Moreover, we need to avoid putting the database in an unrecoverable state.

There are three components to our schema tests. I'll show some pseudocode on how they're structured.

First, we've got one test for each upgrade:

@Test
public void upgrade1to2() {
  SQLiteDatabase db = SQLiteDatabase.create(null);

  applyUpgrades(db, 0, 1);

  // Insert data into database that is going to be modified by upgrade

  applyUpgrades(db, 1, 2);

  // Assert that data remains

  // Assert that schema is what we want now
}

We've got upgrade1to2(), upgrade2to3(), and so on. These tests help ensure that upgrading from one version to another will work, even if there are multiple upgrades being applied.

Next, we want to make sure that the SQL upgrades are equivalent to our create.sql.

@Test
public void upgradesEqualCreate() {
  // Apply the sole create script
  SQLiteDatabase createDb = SQLiteDatabase.create(null);
  executeSqlFile(createDb, "create.sql");

  // Apply all updates in order
  SQLiteDatabase upgradeDb = SQLiteDatabase.create(null);
  applyUpgrades(upgradeDb, 0, TrelloDbHelper.DB_VERSION);

  // Assert that the database schemas are equivalent
}

In both cases, we've written tools that can extract schema information from our database in order to make assertions. I'm not comfortable sharing them because they are customized for our code and make assumptions that may not work for other apps. If you want to write your own tools, a good place to start would be the SQLite documentation on querying the schema.

The last test prevents us from writing a database upgrade without adding tests for it.

@Test
public void hasAllNecessaryTests() {
  assertEquals(11, TrelloDbHelper.DB_VERSION);
}

If someone bumps DB_VERSION without at least touching this test then it'll fail, reminding them to write an upgrade test!

These tests don't guarantee success; we could still fail to write the tests correctly. However, they do give me peace of mind (as well as an easy way to develop each schema upgrade via test-driven development).

This article was originally posted on the Trello engineering blog and has been reproduced here for posterity.