Seeding your Metro style app with a SQLite database

It looks like people are really glad about being able to use SQLite within their Metro style apps.  I had written two previous posts (Using SQLite in your Metro style app and HOWTO: Build and include SQLite) about this topic.  I’m pleased to report that since those posts the SQLite team released a build (3.7.13 as of the datestamp on this post) which also provides the binary (32- and 64-bit versions) pre-compiled for you for inclusion in your Metro style app.  You can get them from the SQLite download page.

I’ve received a few comments/questions that I thought I might clarify in my own opinion (and some facts) about using SQLite in your app.

Creating new databases

The first thing to understand is that your app lives in a secure sandbox during operation.  This is also referred to as the AppContainer in the Metro style app world.  What this means is that you can only do certain operations in certain places or through brokers provided by the various WinRT APIs.  The first stumbling block I’ve seen people try to do is create a database in places where they cannot create databases.  When using SQLite, regardless of whatever client method you use to program with it, you need to pass in a full path to where the database should be created (or an existing one that you are opening).  Simply passing “foo.db” in the open method is not enough as that will assume an incorrect path to create the database file.  Another incorrect thing that folks are doing is using the Windows.ApplicationModel.Package.Current.InstalledLocation.Path API.  This represents the location of where your app is installed which is not an area you can directly write files/content.

NOTE: SQLite uses the CreateFile2 API which is not a WinRT broker API.  This means that it is restricted to certain areas of the AppContainer.

The other area where people are trying to create files is in the document library location for the user.  If you have declared the Document Library capability as well as provided a file association for the file you want to create, then you can read/write files in the Document Library using the WinRT broker APIs.  This, however, is not possible using the CreateFile2 Win32 APIs. 

This leaves the app’s ApplicationData location.  So the correct path to create your database from your app is Windows.Storage.ApplicationData.Current.LocalFolder.Path as a starting point.  Here’s an example (using the sqlite-net library and C#):

   1: using (var db = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "foo.db")))

   2: {

   3:     // do stuff here

   4: }

Now whenever I need to query this database I would use that same path from my app.

Seeding your app with starting data

Some folks want to start their application with some seed data.  There are a few ways that you could do this.  One way would be to create a database during startup and execute SQL statements against the newly-created database.  You would basically be shipping a script in your app that you’d run on the first run of the app after install.  If you went this route, then you’d use the method above to create the database and then execute your INSERT statements.

Another method is to use an existing database file that perhaps you’ve already created.  The misconception here that people have is that since they include a seed database in their app that they can just open that database file and read/write on it.  The read part is correct, however you will fail to write to that file as it is in the package install location and not the ApplicationData location.  The first step you want to do in this approach is move your seed database to the place where you can write to it.  You can use the Windows.Storage APIs to accomplish this.  Here’s an example of how you might do this.  This assumes that your app has a file named “Northwind.sqlite” in your package:

   1: // grab the file from the package installed location into a StorageFile

   2: StorageFile seedFile = await StorageFile.GetFileFromPathAsync(

   3:     Path.Combine(Windows.ApplicationModel.Package.Current.InstalledLocation.Path,

   4:     "Northwind.sqlite"));

   5:  

   6: // copy the StorageFile to the ApplicationData folder

   7: await seedFile.CopyAsync(Windows.Storage.ApplicationData.Current.LocalFolder);

Now the code above does the copy.  Of course you would want to add some logic to verify that you aren’t overwriting an existing database.  Just like anything else there are various ways to do this so I am not prescribing any one way.  Once you get the data where you need it to be, then you can work with the database how you’d like.

I hope this helps understand the method of creating (in the right place) and seeding your app with a SQLite database.  Hope this helps!