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).
Answer
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.