AspNet Core Identity and SQLite

Tonight I set up AspNet Core Identity to use SQLite as its backing store. SQLite is a great piece of software that is a joy to use, and rightly deserves its popularity. It seemed like a great fit for storing a small number of user details for a little app I was building. Although this stackoverflow post which I discovered after I finished getting it working does a reasonable job of explaining things I did a few steps differently. This assumes you’ve created a new .NET Core web app in Visual Studio 2015, and set the ‘Authentication’ method to ‘Individual User Accounts’ when you created the project.

Nuget Packages

I added references to the latest version of Microsoft.EntityFrameworkCore.Sqlite (1.1.0-preview1-final at the time of writing), resolved a couple of other packages that this was dependent on that required minor version updates, and removed the two SQLServer package references.

Changes to Startup.cs

I changed the ConfigureServices method to call options.UseSqlite instead of options.UseSqlServer

public void ConfigureServices(IServiceCollection services)
{
    // Add framework services.
    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseSqlite(Configuration.GetConnectionString("ConnectionName")));
        ...

Changes to appsettings.json

In appsettings.json I changed the connectionstring referred to in Startup.cs (here called “ConnectionName”) to refer to a SQLite file name.

{
    "ConnectionStrings": {
        "ConnectionName": "Filename=./yourappname.sqlite"
    },
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  }
}

Applying Database Migrations

Applying the database migrations create the necessary tables in SQLite for storing account/login/role information. After doing a full build in VS I opened a command prompt and ran the following:

dotnet ef database update

This created a SQLite file in the following directory your-app-name\bin\Debug\netcoreapp1.0\yourappname.sqlite . The file name is controlled by the connectionstring from the previous step.

Done

After doing all this I had a basic working app I could log in to, and open and query the SQLite file in a tool like SQLite Studio. It was nice to have EntityFramework pull its weight for once. I’ll post an update if there is anything else to report with using SQLite for this purpose.

Photo Credit Tom Lee