Windows Universal existing app add new columns to SQLite table

1
votes

We have a Windows 8.1 universal app published on the Windows store. Due to a requirement change now we need to add some fields to an existing table. We have used SQLite for Windows Runtime (Windows 8.1) and SQLite for Windows Phone 8.1. Now if we add some new fields to an existing table (by updating the model classes) will that cause any issues when the existing users update the app?

Instead of adding new columns if we create a new table, then can that cause any issue or will that be safer than adding new fields?

1
sqliteuwpwindows-store-appswin-universal-app

1 Answers

2
votes

Adding a new column to an existing table is perfectly safe even with an existing SQLite database. Same goes for creating a new table. The concrete process depends on which SQLite library you are using.

If you are querying the database directly using SQL queries, then you will use ALTER TABLE ... ADD COLUMN query. If you are using library like sqlite-net-pcl, you get automatic migrations out of the box when you run the CreateTable method. As documentation says:

The automatic migration, currently, only supports adding new columns. If your classes have new properties that are not associated with columns in the table, then alter table ... add column commands will be executed to bring the database up to date. These new columns will not have default values and will therefore be null.

Tip

When you change the model, always test out the changes against the previous version of the database. This can be done in two ways:

  • Install the existing version from Store, launch the app and use it so that it contains some data, then close it and go to C:\Users\[UserName]\AppData\Local\Packages. Here navigate to Packages and there find your app's package folder. Inside go to LocalState where you should find your sqlite database. Copy it somewhere, uninstall the Store version and launch the new version for debugging and place a breakpoint in the App.xaml.cs constructor. When the debugger stops there, copy the original database back to the same location in app package LocalState folder and continue debugging. Observe if the database is migrated properly.
  • Using Git source control, go in history to the previous release commit, install the app for debugging, fill the DB with some data, close the app. Now go to the latest commit and launch app for debugging again. Verify the migration works.