How to Use SQLite on ASP.NET Core Project and Host on Azure

Often time, we just want to experiment some ideas. Just have a basic database setup on Azure will have some cost.

High Level Cost Options:

	Min Cost	Pros	Cons
SQL Server/Database	>$0	Full Feature Production	Not Free
MySQL/Database	>$0	Full Feature Production	Not Free
MySQL Preview/Database	$0	Free	Not scalable, Storage Size on Server
SQLite/Database	$0	Free, small	Not production

What if we just want to keep the cost down? Furthermore, if the idea really works, we can have easy way to migrate to SQL Server. Well, this post is for you.

NOTE: DO NOT USE SQLITE on PRODUCTION!

Prerequisite: Let’s assume you created the project with Authentication option selected. This will have Application DB Context setup, as well as Startup.cs will have services.AddDbContext. Let’s start.

Port SQLite

Step 1: Add SQLite support. In {your project}.csproj, add

<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.1.1" />

Note: At the time of writing, I am using version 3.1.1

Step 2: Add DefaultSQLiteConnection connection string. In appsettings.json, add a DefaultSQLiteConnection

  "ConnectionStrings": {
    "DefaultConnection": "omitted", 
    "DefaultSQLiteConnection": "Data Source=appdb.sqlite"
  },

Step 3: Modify Startup.cs

Remove

services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(                  Configuration.GetConnectionString("DefaultConnection")));

Add

    services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlite(Configuration.GetConnectionString("DefaultSQLiteConnection")));

Step 4: Migration. Delete \Migrations folder. Open Package Manager Console (Tools->Nuget Package Manager -> Package Manager Console).

add-migration initSQLite
update-database

That’s it! Run and test. You should see appdb.sqlite being created at the project root.

Troubleshoot: If something went wrong, check csproj file and make sure Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Tools are there.

DEPLOY SQLite on Azure

I had lots of trouble hosting on azure mainly due to lack of documentation. The key is to have the correct db connection string and export the sqlite db to Azure.

1. Connection String: In Azure portal, Settings -> Configurations -> Application Settings -> Connection String. The Name is what you selected (using the example above, it would be “DefaultSQLiteConnection”. The Value is: Data Source=D:\home\site\wwwroot\App_Data\appdb.sqlite. The Type is: “Custom”
2. For the actual db. Since we cannot migrate, we have to ftp it. We can use Kudu tool: https://{your project}.scm.azurewebsites.net . Then navigate to Debug Console -> PowerShell. Go to D:\home\site\wwwroot\ and create App_Data folder. There you can drag and drop your sqlite db. Your app should work now

Troubleshoot: If it doesn’t work and you’ve tried 100 times. You keep getting 500.30. Just delete the app and recreate it. I have spent hours and yet to figure out what’s the issue.

Caveat on using SQLite and EF Core. Migration deployment doesn’t work. Migration doesn’t even work locally. So migrate the database (I just delete migration folder and reinit). Then, I copy the new db to Azure.