Pulled Apart - Part V: You are a DB server with SQLite

Published on 19 August 2010 • Estimated reading time: 3 minutes

[]

Note: This is part of a series, you can find the rest of the parts in the series index.

One of the design decisions for Pull is that it should run without needing an install. This requirement meant that everything it needed to run should always be available, and this presented an interesting design problem: I needed a database to store all the information Pull uses (podcasts, episodes, etc.) but couldn’t require users to install SQL Server Express or PostgreSQL.

The solution was to use a file-based database called SQLite, which only requires a few DLL files to provide full database functionality without needing a database server. Since this is a .NET application, I used one of the wrappers for .NET called System.Data.SQLite. Usage with System.Data.SQLite can be done via ADO.NET-like code or through the Entity Framework.

I initially used EF for development, but it caused significant issues due to its assumptions about when to open and close connections. While these assumptions make sense for a DB server, they backfired when dealing with a file accessed by multiple threads, leading to stability problems.

To address this, I built my own ORM. It uses reflection for class mapping—similar to EF—but avoids connection issues by using a static instance and aggressively closing file connections. These changes resolved major stability problems while still delivering a solid developer experience.

Mapping

Mapping between my classes and the database uses a simple attribute with a name and a primary key indicator. For example, the Log class looks like this—note that I reuse the same attribute for both columns and tables, assuming Class=Table and Property=Column. While property names often match database column names, this isn’t a requirement.

[DataStore(Name = "Log")]
internal class Log
{
    [DataStore(Name = "PK", PrimaryKey = true)]
    public Guid PK { get; set; }

    [DataStore(Name = "Source")]
    public string Source { get; set; }

    [DataStore(Name = "Occurred")]
    public DateTime Occurred { get; set; }

    [DataStore(Name = "StackTrace")]
    public string StackTrace { get; set; }

    [DataStore(Name = "Message")]
    public string Message { get; set; }
}

Using this metadata, I generate SQL dynamically via reflection. For example, here’s how I build an update command:

private static SQLiteCommand ConvertToUpdateCommand<T>(T item)
{
    SQLiteCommand command = new SQLiteCommand();
    string updateCommandText = string.Format(CultureInfo.CurrentCulture, "UPDATE [{0}] SET ",
        ((DataStoreAttribute)typeof(T).GetCustomAttributes(typeof(DataStoreAttribute), false)[0]).Name);
    int parameterCounter = 0;

    object PKValue = null;
    string PKColumn = string.Empty;

    GetAttributedProperties(typeof(T), (property, attribute) =>
    {
        if (!attribute.PrimaryKey)
        {
            updateCommandText += string.Format(CultureInfo.CurrentCulture, "[{0}]=@A{1}, ",
                attribute.Name, parameterCounter);
            command.Parameters.AddWithValue(string.Format(CultureInfo.CurrentCulture, "A{0}", parameterCounter),
                property.GetValue(item, null));
            parameterCounter++;
        }
        else
        {
            PKValue = property.GetValue(item, null);
            PKColumn = attribute.Name;
        }
    });

    updateCommandText = updateCommandText.Remove(updateCommandText.Length - 2);
    updateCommandText += string.Format(CultureInfo.CurrentCulture, " WHERE [{0}]=@PK", PKColumn);
    command.Parameters.AddWithValue("PK", PKValue);
    command.CommandText = updateCommandText;

    return command;
}

Final Thoughts

I’ve been very happy with SQLite as a database, though the learning curve was steep coming from a database-server background. Once I grasped its limitations and differences from server-based databases, it became a great choice.

The ORM I built has been a major success, making development much easier by working with objects while abstracting database logic. There are performance optimizations I could make—such as:

• Implementing batch transactions (currently, each podcast episode creates a new command and transaction).
• Adding object-tracking to avoid updating all fields when only some have changed.

However, these issues aren’t urgent, as Pull’s initial data volume is lightweight. Overall, I’m satisfied with both the database choice and the ORM implementation.