Best practices for in-app database migration for Sqlite

I am using sqlite for my iphone and I anticipate the database schema might change over time. What are the gotchas, naming conventions and things to watch out for to do a successful migration each time?

For example, I have thought of appending a version to the database name (e.g. Database_v1).

I maintain an application that periodically needs to update a sqlite database and migrate old databases to the new schema and here's what I do:

For tracking the database version, I use the built in user-version variable that sqlite provides (sqlite does nothing with this variable, you are free to use it however you please). It starts at 0, and you can get/set this variable with the following sqlite statements:

> PRAGMA user_version;  
> PRAGMA user_version = 1;

When the app starts, I check the current user-version, apply any changes that are needed to bring the schema up to date, and then update the user-version. I wrap the updates in a transaction so that if anything goes wrong, the changes aren't committed.

For making schema changes, sqlite supports "ALTER TABLE" syntax for certain operations (renaming the table or adding a column). This is an easy way to update existing tables in-place. See the documentation here: http://www.sqlite.org/lang_altertable.html. For deleting columns or other changes that aren't supported by the "ALTER TABLE" syntax, I create a new table, migrate date into it, drop the old table, and rename the new table to the original name.

The answer from Just Curious is dead-on (you got my point!), and it's what we use to track the version of the database schema that is currently in the app.

To run through the migrations that need to occur to get user_version matching the app's expected schema version, we use a switch statement. Here's a cut-up example of what this look like in our app Strip:

- (void) migrateToSchemaFromVersion:(NSInteger)fromVersion toVersion:(NSInteger)toVersion {	
	// allow migrations to fall thru switch cases to do a complete run
	// start with current version + 1
    [self beginTransaction];
	switch (fromVersion + 1) {
		case 3:
			// change pin type to mode 'pin' for keyboard handling changes
			// removing types from previous schema
			sqlite3_exec(db, "DELETE FROM types;", NULL, NULL, NULL);
			NSLog(@"installing current types");
			[self loadInitialData];
		case 4:
			//adds support for recent view tracking
			sqlite3_exec(db, "ALTER TABLE entries ADD COLUMN touched_at TEXT;", NULL, NULL, NULL);
		case 5:
			{
				sqlite3_exec(db, "ALTER TABLE categories ADD COLUMN image TEXT;", NULL, NULL, NULL);
				sqlite3_exec(db, "ALTER TABLE categories ADD COLUMN entry_count INTEGER;", NULL, NULL, NULL);
				sqlite3_exec(db, "CREATE INDEX IF NOT EXISTS categories_id_idx ON categories(id);", NULL, NULL, NULL);
				sqlite3_exec(db, "CREATE INDEX IF NOT EXISTS categories_name_id ON categories(name);", NULL, NULL, NULL);
				sqlite3_exec(db, "CREATE INDEX IF NOT EXISTS entries_id_idx ON entries(id);", NULL, NULL, NULL);
			
               // etc...
			}
	}
	
	[self setSchemaVersion];
    [self endTransaction];
}

Let me share some migration code with FMDB and MBProgressHUD.

Here's how you read and write the schema version number (this is presumably part of a model class, in my case it's a singleton class called Database):

- (int)databaseSchemaVersion {
    FMResultSet *resultSet = [[self database] executeQuery:@"PRAGMA user_version"];
    int version = 0;
    if ([resultSet next]) {
        version = [resultSet intForColumnIndex:0];
    }
    return version;
}

- (void)setDatabaseSchemaVersion:(int)version {
    // FMDB cannot execute this query because FMDB tries to use prepared statements
    sqlite3_exec([self database].sqliteHandle, [[NSString stringWithFormat:@"PRAGMA user_version = %d", DatabaseSchemaVersionLatest] UTF8String], NULL, NULL, NULL);
}

Here's [self database] method that lazily opens the database:

- (FMDatabase *)database {
    if (!_databaseOpen) {
        _databaseOpen = YES;

        NSString *documentsDir = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
        NSString *databaseName = [NSString stringWithFormat:@"userdata.sqlite"];

        _database = [[FMDatabase alloc] initWithPath:[documentsDir stringByAppendingPathComponent:databaseName]];
        _database.logsErrors = YES;

        if (![_database openWithFlags:SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FILEPROTECTION_COMPLETE]) {
            _database = nil;
        } else {
            NSLog(@"Database schema version is %d", [self databaseSchemaVersion]);
        }
    }
    return _database;
}

And here are migration methods called from the view controller:

- (BOOL)databaseNeedsMigration {
    return [self databaseSchemaVersion] < databaseSchemaVersionLatest;
}

- (void)migrateDatabase {
    int version = [self databaseSchemaVersion];
    if (version >= databaseSchemaVersionLatest)
        return;

    NSLog(@"Migrating database schema from version %d to version %d", version, databaseSchemaVersionLatest);

    // ...the actual migration code...
    if (version < 1) {
        [[self database] executeUpdate:@"CREATE TABLE foo (...)"];
    }

    [self setDatabaseSchemaVersion:DatabaseSchemaVersionLatest];
    NSLog(@"Database schema version after migration is %d", [self databaseSchemaVersion]);
}

And here's the root view controller code that invokes the migration, using MBProgressHUD to display a progress bezel:

- (void)viewDidAppear {
    [super viewDidAppear];
    if ([[Database sharedDatabase] userDatabaseNeedsMigration]) {
        MBProgressHUD *hud = [[MBProgressHUD alloc] initWithView:self.view.window];
        [self.view.window addSubview:hud];
        hud.removeFromSuperViewOnHide = YES;
        hud.graceTime = 0.2;
        hud.minShowTime = 0.5;
        hud.labelText = @"Upgrading data";
        hud.taskInProgress = YES;
        [[UIApplication sharedApplication] beginIgnoringInteractionEvents];

        [hud showAnimated:YES whileExecutingBlock:^{
            [[Database sharedDatabase] migrateUserDatabase];
        } onQueue:dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_BACKGROUND, 0) completionBlock:^{
            [[UIApplication sharedApplication] endIgnoringInteractionEvents];
        }];
    }
}

1. Create /migrations folder with the list of SQL-based migrations, where each migration looks something like this:

/migrations/001-categories.sql

-- Up
CREATE TABLE Category (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO Category (id, name) VALUES (1, 'Test');

-- Down
DROP TABLE User;

/migrations/002-posts.sql

-- Up
CREATE TABLE Post (id INTEGER PRIMARY KEY, categoryId INTEGER, text TEXT);

-- Down
DROP TABLE Post;

2. Create db table containing the list of applied migrations, for example:

CREATE TABLE Migration (name TEXT);

3. Update application bootstrap logic so that before it starts, it grabs the list of migrations from the /migrations folder and runs the migrations that have not yet been applied.

Here is an example implemented with JavaScript: SQLite Client for Node.js Apps

The best solution IMO is to build a SQLite upgrade framework. I had the same problem (in the C# world) and I built my own such framework. You can read about it here. It works perfectly and makes my (previously nightmarish) upgrades work with minimal effort on my side.

Although the library is implemented in C#, the ideas presented there should work fine in your case also.

Some tips...

1. I recommend putting all the code to migrate your database into an NSOperation and running it in the background thread. You can show a custom UIAlertView with a spinner while the database is being migrated.

2. Make sure you are copying your database from the bundle into the app's documents and using it from that location, otherwise you will just overwrite the whole database with each app update, and then migrate the new empty database.

3. FMDB is great, but its executeQuery method can't do PRAGMA queries for some reason. You'll need to write your own method that uses sqlite3 directly if you want to check the schema version using PRAGMA user_version.

4. This code structure will ensure that your updates are executed in order, and that all updates are executed, no matter how long the user goes between app updates. It could be refactored further, but this is a very simple way to look at it. This method can safely be run every time your data singleton is instantiated, and only costs one tiny db query that only happens once per session if you set up your data singleton properly.

   • (void)upgradeDatabaseIfNeeded { if ([self databaseSchemaVersion] < 3) { if ([self databaseSchemaVersion] < 2) { if ([self databaseSchemaVersion] < 1) { // run statements to upgrade from 0 to 1 } // run statements to upgrade from 1 to 2 } // run statements to upgrade from 2 to 3

       // and so on...
     
       // set this to the latest version number
       [self setDatabaseSchemaVersion:3];
     

     } }

For .net you can use lib:

EntityFrameworkCore.Sqlite.Migrations

It is simple, so for any other platform you can easily implement the same behavior as in lib.

If you change the database schema and all code that's using it in lockstep, as is likely to be the case in embedded and phone-located apps, the problem is actually well under control (nothing comparable to the nightmare that's schema migration on an enterprise DB that may be serving hundreds of apps -- not all under the DBA's control either;-).