Recommendations for migrating a database in an application for Sqlite

Question

I am using sqlite for my iphone, and I expect the database schema to change over time. What are gotchas, naming conventions and things to keep track of successful migration every time?

For example, I was thinking of adding a version to the database name (e.g. Database_v1).

Answer 1

I support an application that periodically needs to update the sqlite database and migrate the old databases to the new schema, and here is what I do:

To track the version of the database, I use the built-in user-version variable that sqlite provides (sqlite does nothing with this variable, you can use it however you like). 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 application starts, I check the current user version, apply any changes necessary to update the schema, and then update the user version. I transfer updates to the transaction so that if something goes wrong, the changes are not performed.

To create schema changes, sqlite supports the ALTER TABLE syntax for specific operations (renaming a 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 . To remove columns or other changes that are not supported by the ALTER TABLE syntax, I create a new table, transfer the date to it, delete the old table, and rename the new table to the original name.

Answer 2

The answer from Just Curious is dead (you got my opinion!), And this is what we use to track the version of the database schema that is currently in the application.

To perform the migration that must occur in order to get the user_version corresponding to the expected version of the application schema, we use the switch statement. Here's an example of how this looks in our Strip app:

 - (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]; } 

Answer 3

Let me share some migration code with FMDB and MBProgressHUD.

Here you read and write the version number of the schema (this is apparently part of the model class, in my case it is 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 the [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 the 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 is the root view controller code that causes the migration, using MBProgressHUD to display the progress frame:

 - (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]; }]; } } 

Answer 4

The best IMO solution is to create a SQLite update infrastructure. I had the same problem (in the C # world) and I created my own infrastructure. You can read about it here . It works great and makes my (previously nightmarish) updates work with minimal effort on my side.

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

Answer 5

1 . Create a /migrations folder with a 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 a db table containing a list of applicable migrations, for example:

 CREATE TABLE Migration (name TEXT); 

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

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

Answer 6

If you change the database schema and all the code that uses it in lock mode, as this will probably happen in embedded and telephone applications, the problem is actually under control (nothing comparable to the nightmare that migrating the schema to corporate A database that can serve hundreds of applications is not all under DBA control :-).

Answer 7

Some tips ...

1) I recommend putting all the code to transfer the database to NSOperation and running it in the background thread. You can show a custom UIAlertView with a counter when the database is migrated.

2) Make sure that you copy your database from the package to the application documents and use it from this place, otherwise you just overwrite the entire database with each application update, and then transfer the new empty database.

3) FMDB is great, but its executeQuery method for some reason cannot make PRAGMA queries. You will need to write your own method that directly uses sqlite3 if you want to check the version of the schema using PRAGMA user_version.

4) This code structure ensures that your updates will be performed in order and that all updates will be performed, no matter how long the user goes between application updates. It can be reorganized further, but it is a very simple way to look at it. This method can be safely run every time you create a singleton data instance, and there is only one tiny db request that runs only once per session if you correctly configured your data.

 - (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]; } } 

Answer 8

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.