Xamarin Change the db structure(add column, table) without losing existing data (ie migrations)

Cdn_Euro

Whenever I change the sqlite database in my Xamarin mobile project, by adding a column, or adding a table, renaming a table/column etc. I get an exception of this sort when running over an existing installation(which makes sense since I modified the local db):

SQLite Error 1: 'no such table: SomeTable'.

How can I be able to change the DB structure when submitting a new build for testing, without the tester having to erase the whole app installation(and db file) because of the db being modified?

Is there any way in Xamarin to automatically implement MIGRATIONS in Sqlite automatically?

This my db context code:

public class MyMobileAppDbContext : DbContext
    {
        public DbSet<User> Users { get; set; }

        public DbSet<SomeEntity> SomeEntityTable { get; set; }

        public MyMobileAppDbContext()
        {
            SQLitePCL.Batteries_V2.Init();
            this.Database.EnsureCreated();
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            string dbPath = Path.Combine(FileSystem.AppDataDirectory, AppConstants.Constants.DatabaseFilename);

            optionsBuilder
                .UseSqlite($"Filename={dbPath}");
        }

    }

JohnHardman

@Cdn_Euro

What I do is probably overkill, but it's a technique I picked up doing release management in non-mobile enterprise environments that does the job when database schemas change between releases.

Basically, I have a "Versions" table, in which each row of data contains a table name and the currently installed schema version for that table. The entire content of the Versions table is basically a collection of all of the tables that my app needs to run.

When my app starts up, it reads the Versions table and compares the version number that the app expects for each table against what is found in the Versions table. If they match, everything is good to go. If any row doesn't match, the app performs whatever manipulations are required on the database to bring it up to date with the current app version.

e.g. if there is a Product table, and the latest version of the app requires the Product schema to be version 5, but the Versions table contains 2 for the Product row, the app will run the update that would convert Product table with version 2 to version 3, then the update that goes from 3 to 4, then the update that goes from 4 to 5. At that point, the Product table is good to use (as each update is applied the schema version number in the Product table is incremented).

Each schema change is a single increment, so that the app can update from any previous version by running a series of schema changes as required.

I hope that's clear. It's the sort of system that makes sense in enterprise applications, particularly after the first public release. Whether it makes sense in pre-production for releases done to testers is less clear. As somebody who has done a lot of QA/testing in the past, I'm used to recreating data sets myself, whether manually or programmatically. I'd expect to test the update process for release candidates, but I'm not sure that I would expect that to work for anything not considered a release candidate.

jezh

Xamarin Change the db structure(add column, table) without losing existing data (ie migrations)

You should do your update operation (add column, table) by override function OnUpgrade .

For example:

    public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        String upgradeQuery = "ALTER TABLE mytable ADD COLUMN mycolumn TEXT";
        if (oldVersion == 1 && newVersion == 2)
            db.ExecSQL(upgradeQuery);
    }

---

Xamarin forums are migrating to a new home on Microsoft Q&A!
We invite you to post new questions in the Xamarin forums’ new home on Microsoft Q&A!
For more information, please refer to this sticky post.

JohnHardman

@jezh said:
You should do your update operation (add column, table) by override function OnUpgrade .

Is that the OnUpgrade in SQLiteOpenHelper ? If so, isn't that just for Android?

Cdn_Euro

@JohnHardman @jezh Thank you for your suggestions. I am using the Microsoft.EntityFrameworkCore.Sqlite.Core package right now ( I am NOT using the sqlite-net-pcl one). Where is the OnUpgrade coming from and is it for both platforms iOS and Android?

jezh

Where is the OnUpgrade coming from and is it for both platforms iOS and Android?

Sorry, I'm talking about the SQLite database in Android.

When we use the SQLite database in android, and if we want to update the database based on the installed app, we can add our code(add column, table) in method OnUpgrade just as I mentioned above.

public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
    String upgradeQuery = "ALTER TABLE mytable ADD COLUMN mycolumn TEXT";
    if (oldVersion == 1 && newVersion == 2)
        db.ExecSQL(upgradeQuery);
}