SQLite rehabilitated

 About 10 years ago I attempted to use SQLite in some projects, but getting it integrated and working nicely in a .NET project was a fragile and frustrating process. I remember a cavalcade of compile and runtime errors caused by 32/64 bit confusion and making bad choices due to scatter-brained documentation.

As an experiment this weekend I tried to process one of my old SQLite databases in a modern .NET Core project via Entity Framework, and I'm very pleased to report that it was a really smooth process. I now consider SQLite rehabilitated and is a good candidate for anyone who needs an in-process relational database that needs little to no configuration and has no external dependencies.

The SQLite documentation points out that you don't have to use it for relational data, it can be used as a mini file-system for storing arbitrary key-value data. Their simplest example is a table with a string primary key and another BLOB column for anything you like. A decade ago I would have considered using an ESENT database for that sort of thing, but now I consider SQLite to be a far superior choice.

What follows is a reminder to myself about how to process a SQLite database in a .NET Core project.

Add the Nuget package Microsoft.EntityFrameworkCore.Sqlite.

Create the classes for the tables. Here are skeletons of two tables that had a one-many relationship.

public sealed class Media
{
  public long Id { get; set; }
  public string Name { get; set; }
  :
  public List<Volume> Volumes { set; set; }
}
public sealed class Volume
{
  public long Id { get; set; }
  public string Title { get; set; }
  public long MedId { get; set; }
  :
  public Media Media { get; set; }
}

The relationship is one Media to many Volumes. Note also that the foreign key column is named MedId, which caused a confusing runtime error. When I ran a query like this:

var source = context.Volume.Include(v => v.Media);

I got the runtime error column MediaId is not defined, which is quite true, that name is not defined anywhere in my code or the database. It turns out that Entity Framework assumes that foreign keys are named tableId. So had I chosen the name MediaId instead of MedId I wouldn't have had this problem. To work around this I had to override OnModelCreating in the following code that defines the context.

public class OpusContext : DbContext
{
  public DbSet<Media> Media { get; set; }
  public DbSet<Volume> Volume { get; set; }
  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    optionsBuilder.UseSqlite(@"Data Source=C:\temp\opus.db3");
  }
  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Volume>()
      .HasOne(v => v.Media)
      .WithMany(m => m.Volumes)
      .HasForeignKey(nameof(EFLiteTest.Volume.MedId));
  }
}

This manually defines the relationship and which column is the foreign key.

In summary: by adding the package and defining the row and context classes in the traditional way I had a complete way of processing the SQLite database with all of the strongly-typed convenience provided by Entity Framework.

Note - Types

Remember that SQLite has a lot of quirks compared to other big name RDBs. The weirdest thing is that it's "weakly typed" (in my words) or "flexibly typed" (in their words). You can create a table with columns that all have strictly defined types and lengths as you would in SQL Server, but SQLite ignores the column definitions and will let you put anything of any length into any column at runtime. This is all described on their quirks documentation page. If you're using Entity Framework and not using raw INSERT statements, then this quirk is irrelevant because all data manipulation will be strongly-typed.

Orthogonal Programming