A Database Initialiser for SQLite and NPoco

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

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

<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>