ASP.NET Core 2: Using SQLite as a light weight database

SQLite370.svg.pngAzure is a great place to store your data of any size, but sometimes that may not be possible for a number of reasons. In this article I’ll show you how to use Microsoft Entity Framework Core (EF) and SQLite to create and store a small amount of data.

This code is a bare-bones example. It doesn’t include important features like error handling or DI.

Note: There is no database file. EF Core will create the SQLite database file for you.

Step 1: Create any simple ASP.NET Core site via the (File | New) menu

Step 2: Add these NuGet packages

  1. Microsoft.EntityFrameworkCore
  2. Microsoft.EntityFrameworkCore.Sqlite
  3. Microsoft.EntityFrameworkCore.Sqlite.Core

Step 3: DbContext

All of the magic takes place in a single simple data class. Add this to your solution:

public class SimpleDataStorageDbContext
    public SimpleDataStorage(){}
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        //Set the filename of the database to be created
        optionsBuilder.UseSqlite("Data Source=db.sqlite");
    public DbSet<Cat> Cats { getset; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
        //Define the Table(s) and References to be created automatically
        modelBuilder.Entity<Cat>(b =>
            b.HasKey(e => e.Id);
            b.Property(e => e.Id).ValueGeneratedOnAdd();
            b.Property(e => e.Name).IsRequired().HasMaxLength(255);

What’s very interesting here is that the database doesn’t exist, the class SimpleDataStorage tells EF how and where to create it.

Step 4: Create the database and tables in Startup.cs

public class Startup
   public Startup()
        using (var client = new SimpleDataStorage())
            //Create the database file at a path defined in SimpleDataStorage
            //Create the database tables defined in SimpleDataStorage

Step 5: Add Cats to the database!

We can now access the database anywhere in code

public IActionResult Index(SimpleDataStorage db)
    for (int i = 0; i < 1000; i++)
        db.Cats.Add(new Cat() {Name = "Caty McCat: " + i}); 
    return View();

Resources: A good SQLite database editor