Database migrations

Last updated: 2022-10-13

This document provides instructions for adding migrations to expo-updates' local SQLite db schema.

It's critical to get these types of migrations right, because they will take place on thousands of users' devices over which we have no centralized control if something goes wrong.

Common pitfalls

• Once you define your migration, make sure to actually add it to the database builder / initializer (step 5 on Android, 4 on iOS)! This is a critical step but is very easy to overlook (has happened before). If forgotten, this will result in a destructive migration taking place -> all your hard work is for naught.
• On iOS, there's no automatic schema verification, so it's really important to verify that your migration produces a DB whose schema is identical to a newly created DB.
• On Android, make sure to avoid committing the changes in step 7.

Android

The Room library we use on Android provides a mechanism for migrations. Documentation here.

IMPORTANT NOTE: we are now using a version of Room that supports autogenerated migrations. You can try to do this following the official guide, but note that all the existing migrations were written before this feature was added, so your process and code will be somewhat different than existing manual migrations / the below steps. In either case, it's important to still go through steps 7-11 below to add tests.

On Android, we don't have control over the SQLite schema at a language level; it's auto-generated by Room using the entity classes that are included in UpdatesDatabase.

To add a manual migration, use the following steps (automatic migrations will likely differ in steps 4-5):

1. Make the desired schema changes in the entity classes.
2. Bump the @Database version parameter in UpdatesDatabase.kt. Room will now fall back to a destructive migration by default.
3. Commit these changes.
4. Add a new migration to UpdatesDatabase.kt following the established conventions. If your migration involves column operations, it might help to read this document about ALTER TABLE in SQLite.
5. Make sure to add the migration to the database builder in getInstance.
6. Commit these changes.
7. (Temporary change) Set @Database exportSchema to true in UpdatesDatabase.kt, and uncomment these lines in android/build.gradle. Run a build of some project that uses expo-updates, like Android Expo Go.
8. If all has gone well, a new autogenerated JSON schema file should appear inside of packages/expo-updates/android/src/androidTest/schemas/expo.modules.updates.db.UpdatesDatabase/ . (Nothing in this folder should ever be manually modified.)
   1. If there are errors here, your migration might not be fully correct (i.e. the resulting DB schema doesn't match the schema of a newly created DB).
9. Undo the changes in step 7, and commit the new JSON schema.
10. Finally, add a new test to UpdatesDatabaseMigrationTest. Room will test that the DB schema is migrated correctly, but it's your responsibility to test that data is preserved correctly, and, if applicable, migrated.
    1. You can follow the examples that are already there. Note that:
    2. You must use SQL statements directly rather than the Room DAO objects/methods since Room will always be tied to the latest DB schema.
    3. You will need to enable foreign keys manually after creating the database and running the migrations.
11. Make sure the test passes, and commit. You're done!

This PR can be used as a reference for the finished product.

iOS

On iOS we use the sqlite3 library directly rather than interacting with SQLite through a wrapper. We've rolled our own migration system to roughly match the functionality Room provides on Android, which means there isn't any documentation for it besides the following.

Note that we use the filename of the database to determine the schema version number.

To add a migration, use the following steps:

1. Copy the existing schema from EXUpdatesDatabaseInitialization.m into a new static variable in EXUpdatesDatabaseInitializationTests.m, following the existing pattern.
2. Make your changes to the schema in EXUpdatesDatabaseInitialization.m, and increment the version number in the EXUpdatesDatabaseLatestFilename variable.
   1. You may also need to make changes in some of the SQL statements in EXUpdatesDatabase if, for instance, you added new NOT NULL columns to a table. Unit tests should catch this.
3. Create a new class that implements the EXUpdatesDatabaseMigration protocol, following the patterns in existing migrations.
   1. Return NO from the runMigrationOnDatabase method anytime there are unrecoverable errors; if NO is returned then expo-updates will fall back to a destructive migration. This is better than a partially migrated or corrupt DB!
   2. You should be able to mostly reuse the SQL statements from the Android migration, though note that the schema is slightly different between platforms, so be careful.
   3. Make sure the old database filename is returned in filename.
4. Add this new migration to EXUpdatesDatabaseMigrationRegistry.
5. Double check that your migration produces a schema identical to the one in EXUpdatesDatabaseInitialization.m. Unlike on Android, we don't currently have an automated way of ensuring the migration produces an identical schema, so this is really critical to get right.
6. Finally, add a new test to EXUpdatesDatabaseInitializationTests.m to ensure data is preserved and migration correctly.
   1. You can follow the examples at the bottom of this file.
   2. Again, you should be able to reuse most of the SQL statements if you already did the Android migration test.
   3. You don't need to enable foreign keys manually; the EXUpdatesDatabaseInitialization class will do this for you.
7. Double check one more time that a newly created DB's schema is identical to a migrated DB's schema. (Really, just do it.)
8. Make sure all the tests pass, and commit. You're done!