A Database Initialiser for SQLite and NPoco

• Download source code - 49.7 KB

Introduction

This article came about when I was creating a small blog styled web application where EF and SQL Server was just too much overhead, I needed a smaller, lighter, in-process database which I could use with the Microsoft AspNet Identity framework. I had previously used PetaPoco as a lightweight ORM when working with Umbraco and subsequently found NPoco which had added some nice features including async versions of many methods. SQLite was my choice for an in-process database.

However, this solution did not offer any way of automatically keeping the database updated with schema changes and so I created a simple initialization framework which is what this article is about.

IMHO SQLite is a great product even supporting full text searching. However, one small limitation is that in some cases, SQLite supports only a subset of standard SQL commands. For example, ALTER TABLE cannot be use to drop a column and unlike CREATE TABLE IF NOT EXISTS, there is no similar ALTER TABLE ADD COLUMN IF NOT EXISTS. Thus, any initialiser must be able to handle more complex scenarios beyond simply executing a script. Of course, the initialiser must also have an effective versioning mechanism so that it knows when and where to start applying changes.

The resulting initialiser is by no means perfect and I am sure that readers will find many ways to improve it however it does the job, it's flexible, automatic and simple to implement. It comprises 2 interfaces:

IDbInitialiser

/// <summary>
/// IDbInitialiser is used to manage database initialisation and upgrades.
/// You would normally call this once at application startup
/// </summary>
public interface IDbInitialiser : IDisposable
{
    /// <summary>
    /// Perform database initialisation. 
    /// This should move the database from it's current
    /// version up to the latest version. 
    /// The IDbInitialiser will resolve how to locate and sort the 
    /// required IDatabaseConfigurators
    /// </summary>
    void InitialiseDatabase();
    /// <summary>
    /// Perform database initialisation. 
    /// This should move the database from it's current
    /// version up to the latest version. 
    /// The correct sequence of the IDatabaseConfigurators is the 
    /// responsibility of the caller.
    /// </summary>
    /// <param name="configurators">An array of 
    /// IDatabaseConfigurators which will be execute in order</param>
    /// <param name="dispose">Set to true to cause 
    /// each IDatabaseConfigurator to be disposed after use</param>
    void InitialiseDatabase(IDatabaseConfigurator[] configurators, bool dispose = false);
    /// <summary>
    /// After completion this should show the initial version of the database
    /// </summary>
    long InitialVersion { get; }
    /// <summary>
    /// After completion this should show the final version of the database
    /// </summary>
    long FinalVersion { get; }
    /// <summary>
    /// After completion this should show 
    /// the number of IDatabaseConfigurators which were executed
    /// </summary>
    long ConfiguratorsRun { get; }
}

This interface provides the main functionality for maintaining the database schema. The method InitialiseDatabase is called each time the application starts and should perform all updates necessary to bring the database to the current version. The properties it provides are not essential but are useful for debugging or logging database change activity.

IDatabaseConfigurator

/// <summary>
/// Configurators are the components which actually make database changes. 
/// To use automatic configuration the configurator classes 
/// must use a consistent naming convention 
/// which ends in 3 numeric digits starting 
/// with 001 e.g. Config001, Config002, Config003 etc. 
/// The 3 digit number is the database version number 
/// and is stored in Sqlite using a PRAGMA.
/// The convention must use a consistent name 
/// e.g. Configxxx so that the configurators can be sorted
/// into the correct operational sequence.
/// For manual configuration then class naming is 
/// irrelevant since you must provide an array of
/// configurators to IDbInitialiser which are already 
/// in the correct order and which provide their
/// own version numbers.
/// </summary>
public interface IDatabaseConfigurator : IDisposable
{
    /// <summary>
    /// Provides the version number of this set of database changes
    /// </summary>
    int Version { get; }
    /// <summary>
    /// Perform any actions on the database before changing the schema. This might 
    /// involve copying data to temp tables etc to avoid data loss
    /// </summary>
    /// <param name="db">An Npoco Database object</param>
    void PreMigrate(IDatabase db);
    /// <summary>
    /// Update the database schema
    /// The final task must be to update the version number in Sqlite
    /// </summary>
    /// <param name="db">An Npoco Database object</param>
    void Migrate(IDatabase db);
    /// <summary>
    /// Perform any actions on the database after changing the schema. This might
    /// include copying data back from temporary tables and then cleaning up.
    /// </summary>
    /// <param name="db">An Npoco Database object</param>
    void PostMigrate(IDatabase db);
    /// <summary>
    /// Perform any seeding needed by the database. This might include setting
    /// new column values to a default as well as genuine data seeding
    /// </summary>
    /// <param name="db">An Npoco Database object</param>
    void Seed(IDatabase db);
}

This interface provides the core functionality for actioning a specific database version change. Each time a database change is needed for a release, a new IDatabaseConfigurator is created which will perform all of the changes needed.

Example Implementation

One of the nice features of SQLite is that when you first access the database in any way, SQLite will create the empty database if it does not exist. I use SQLite itself to store the current database version number. This is done using the PRAGMA user_version command.

When using the update process by convention, then each IDatabaseConfigurator must provide its version number using the last three characters of the class name (e.g. Config000, Config001, etc.). The initialiser locates and instantiates the configurators using Reflection, it also disposes them when complete. The second version of InitialiseDatabase allows you to provide your own list of pre-initialised configurator objects, these must be in the correct sequence and you are optionally responsible for disposing them.

MyDbInitialiser wraps the entire update sequence in a transaction, this way the database is either upgraded completely or, in the event of an error not at all.

Each release of the application will have its own IDatabaseConfigurator assuming some database changes are needed. These allow you to perform pre and post migration tasks in addition to seeding. The example provided illustrates how this can be hooked up to the Microsoft Identity framework in order to seed Role and User objects.

The example project is provided for VS2017, the execution of the initialiser is demonstrated using unit tests rather than a dummy application (note that these are not a proper set of genuine tests but merely a way to demonstrate execution of the initialiser).

When using the initialiser, it should be executed each time the application starts up. For an MVC application, a good point is possibly the Application_Start() method:

using (var db = new MyDb())
{
    using (var initialiser = new MyDbInitialiser(db))
    {
        initialiser.InitialiseDatabase();
    }
}

Points to Note

When using SQLite with System.Data.SQLite, you will need to add the required DbProviderFactory entry in your config file.

<system.data>
    <DbProviderFactories>
        <add name="SQLite Data Provider" 
        invariant="System.Data.SQLite" 
        description=".NET Framework Data Provider for SQLite" 
        type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>
</system.data>

SQLite has a habit of leaving SQLite.Interop.dll open causing a build failure. This happens because the test runner remains in memory between tests. Resolve this by using Test Settings to stop keeping the execution engine in memory.

This example is not production code, it is just an example of how the interfaces might be implemented using SQLite and NPoco.

History

• 22^nd June, 2017: Initial version