Using SQLite With ASP.NET And Azure App Service

I’m a fan of using SQLite for small projects where a full MSSQL instance is not warranted, but I wasn’t sure whether it could be packaged and shipped with the app when deploying to Azure App Service. Turns out it can and it works well.

There are a couple of considerations around deployments, essentially ensuring you don’t overwrite your production database during a new code deploy, and EF Core migrations involve getting a copy of the prod database to your local machine, running the migration, and then re-uploading. For my small projects none of these were deal breakers.

This post covers those few considerations, and assumes some familiarity with App Service so the provisioning and setup of that is not discussed.

• Deployment
  • Initial Deployment
  • Subsequent deployments
• EF Core Database Migrations

Deployment

There are two deployment scenarios to be considered:

1. The initial deployment
2. All subsequent deploys

Initial Deployment

The initial deployment will upload a copy of our database, either blank or existing. However, after the initial deployment we will want to exclude the database from future deploys otherwise our data would be overwritten.

The following assumes your SQLite DB is located in ./Data/db.sqlite, inside your project. Adjust the paths to suit your own needs.

We will need to update our .csproj file to tell it to include our database in the deployment package.

<ItemGroup>
    <None Update="Data/db.sqlite">
        <CopyToOutputDirectory>Always</CopyToOutputDirectory>
    </None>
</ItemGroup>

We can now build our deployment package and upload it to Azure App Service.

# Build
dotnet restore
dotnet build --no-restore --configuration Release --os linux
dotnet publish --no-build --configuration Release --os linux

Compress-Archive -Path "bin\Release\net8.0\linux-x64\publish\*" -DestinationPath C:\Temp\publish.zip -Force

# Deploy
az webapp deploy --resource-group <resource-group-name> --name <app-name> --src-path "C:\Temp\publish.zip" --type zip --async false --restart

Subsequent deployments

For all future deployments we no longer want to include the database file as we would be overwriting the deployed instance. All we need to do is update our csproj file to explicitly exclude it.

<ItemGroup>
    <None Update="Data/db.sqlite">
        <CopyToOutputDirectory>Never</CopyToOutputDirectory>
    </None>
</ItemGroup>

The rest of the deployment process is identical.

EF Core Database Migrations

At some point you may need to run a database migration and the simplest way to do this is to stop the app, download a copy of the database using SCM via https://<site>.scm.azurewebsites.net/newui/ or FTPS if your App Service configuration allows it.

Update appsettings.Development.json to point to the downloaded database file (assuming this is how you have configured EF Core), run dotnet ef database update. Delete the remote file, upload the modified database and start the app.