SQLite-Net Extensions - Does the CreateTable function alter my existing tables?

Question

I am using SQLite-net together with the TwinCoders NuGet for extension methods in a MvvmCross Xamarin project. I want to make the database to stay updated even if I will modify the models in future.

My question is: If I use the CreateTable function for creating a SQLite table based on the model and the database already exists on the tablet/phone, but it has a different structure (let's say that the table has a missing column which was added in the last update), will this function alter the existing table? Thanks!

Kerikeriann

github.com/praeclarum/sqlite-net/wiki/AutomaticMigrations – Eager

Answer 1

For me the answer has always been yes. I changed the name of one of the properties in my model and that ended up leaving the data in place and each existing record had that new property but it was empty.

So it went something like this:

Original:

class Model {
    int Id { get; set; }
    string Date { get; set; }
}

Changed:

class Model {
    int Id { get; set; }
    string Dates { get; set; }
}

Now all of the records still had Id values and were not erased but they all also had empty Dates columns, as would be expected.

Needless to say, a new table was not created.

Edit: Also note that I am using SQLite-Net only, no extension libraries or anything but the end result should be the same.

Leaves

Answer 2

Thanks for your answers! SQLite-Net implements automatic migrations indeed. It treats the lack of "ALTER DROP COLUMN" and "ALTER RENAME COLUMN" commands from SQLite as follows:

• if the name of one column is changed, then a new column will be created with the new name and the old one will be ignored by the queries
• if the column is dropped, then it will remain in the database, but the queries will ignore it

I didn't try yet what happens when I change the datatype of one column (attribute), but I hope it will not be the case in our project.

Kerikeriann