Upgrade database with Sqlite.net Pcl for Xamarin android

Question

i have the problem that when i release new version of my application, if i add a new column to one of my db tables, the database doesn't update. Any one know how to create a script of upgrade versione in case there are new columns or new tables??

Thanks

Answer 1

You have to remember that CreateTable it's already doing the columns update for you, because internally it calls a method called MigrateTable.

However you could have to handle more advanced modification to your database, like adding triggers or something similar. In that case i suggest you to perform modifications manually.

In Xamarin Forms i've ended up with this: https://gist.github.com/matpag/b2545cc22c8e22449cd7eaf6b4910396

Could not be the best strategy ever but seems to work for me.

Summarizing :
You have to save the database version in an internal flag of the SQlite database called user_version accessible with PRAGMA keyword. Every time you get the database connection, you have to perform a check and see if the current database version is the same as the app last database version. If not you need to perform a database update and set the new current version.

Answer 2

It's not a matter of a script, as there isn't such a thing. You can release a version with a "patch" that will run once, extracting all your records to a temporary form -> deleting the table -> creating it again (will assure it's created with the new columns and so on) -> reinserting the records again. After a while, when you know that all your users (or whenever you set the limit) have moved to the newer version you can just eliminate the "patch" from your code.

Hope it helps.

Answer 3

The automatic migration feature is still not working in sqlite.net-pcl, but it looks like it does work in the other sqlite package: sqlite-net-pcl, which is actually the Xamarin recommend sqlite package.