DEV Community

Cover image for SQLite on Azure Files SMB: A Debugging Story With a Humbling Ending
Argha Sarkar
Argha Sarkar

Posted on

SQLite on Azure Files SMB: A Debugging Story With a Humbling Ending

#dotnet #azure #sqlite #rag

SQLite on Azure Files SMB: A Debugging Story With a Humbling Ending

Three hours of debugging. One line of code. A lesson I won't forget.

The Setup

I was building a .NET 8 RAG API deployed to Azure Container Apps. The idea was simple: use SQLite with EF Core to store document chunks and embeddings, mount the database file from Azure Files so it would survive container restarts.

Simple plan. Clean architecture. What could go wrong?

Everything.

The Symptom

The container kept restarting. Over and over. The log was painfully consistent:

SQLite Error 5: database is locked.
Enter fullscreen mode Exit fullscreen mode

No crash stack. No underlying exception. Just that one line, mocking me every single time.

Attempt 1 — Retry Logic

My first instinct: this is a transient lock. Maybe another process briefly holds it during startup. Classic race condition.

I added exponential backoff — 2s → 4s → 8s → 16s — with five retries.

var retryPolicy = Policy
    .Handle<SqliteException>(ex => ex.SqliteErrorCode == 5)
    .WaitAndRetryAsync(
        retryCount: 5,
        sleepDurationProvider: attempt => TimeSpan.FromSeconds(Math.Pow(2, attempt)),
        onRetry: (exception, timeSpan, attempt, context) =>
        {
            logger.LogWarning("SQLite locked. Retry {Attempt} in {Delay}s", attempt, timeSpan.TotalSeconds);
        });
Enter fullscreen mode Exit fullscreen mode

The container dutifully retried five times, logged politely, then gave up with the same error.

Not a transient lock. Something deeper.

Attempt 2 — Journal Mode

I started digging. EF Core's EnsureCreatedAsync runs PRAGMA journal_mode = 'wal' when it creates a new database. WAL (Write-Ahead Logging) mode requires memory-mapped I/O and POSIX byte-range locking.

Azure Files SMB supports neither.

The write would hang for 30 seconds, then time out with — you guessed it — SQLite Error 5.

Solution: set Journal Mode=Delete in the connection string to prevent WAL from ever being set.

Data Source=/mnt/azure/ragapi.db;Journal Mode=Delete
Enter fullscreen mode Exit fullscreen mode

Except:

ArgumentException: Connection string keyword 'journal mode' is not supported.
Microsoft.Data.Sqlite
Enter fullscreen mode Exit fullscreen mode

Microsoft.Data.Sqlite only accepts a handful of valid keywords. Journal Mode is not one of them. You have to set it via PRAGMA after opening the connection.

Dead end.

Attempt 3 — Pre-Create the Database File

EF Core skips the Create() step (which sets WAL mode) if the database file already exists. So I came up with a workaround:

  1. Open a raw SqliteConnection before EnsureCreatedAsync
  2. Manually run PRAGMA journal_mode=DELETE
  3. Close it
  4. Let EF Core run EnsureCreatedAsync — it sees the file, skips Create(), goes straight to CreateTablesAsync() 
// Pre-create the DB and set journal mode before EF Core touches it
await using var preConn = new SqliteConnection(connectionString);
await preConn.OpenAsync();
await using var cmd = preConn.CreateCommand();
cmd.CommandText = "PRAGMA journal_mode=DELETE;";
await cmd.ExecuteNonQueryAsync();
await preConn.CloseAsync();

// Now let EF Core take over
await dbContext.Database.EnsureCreatedAsync();
Enter fullscreen mode Exit fullscreen mode

EF Core skipped Create(). ✅

It still failed. ❌

Turns out COMMIT inside CreateTablesAsync() also hits the SMB locking issue. The problem wasn't just WAL mode — it was the entire SMB locking model.

The Fix — Accept Reality 

Data Source=ragapi.db
Enter fullscreen mode Exit fullscreen mode

That's it. Switch to local ephemeral storage inside the container. No mount, no SMB, no network. Standard POSIX locking. Works instantly.

// appsettings.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=ragapi.db"
  }
}
Enter fullscreen mode Exit fullscreen mode

Documents reset on container restart. For a demo, that's perfectly fine.

What Actually Went Wrong

SQLite's locking model is built on one assumption: a local filesystem with proper POSIX byte-range locking.

SMB (Server Message Block) — the protocol Azure Files uses — doesn't provide that. Neither does NFS. Neither do most cloud-mounted volumes.

This is documented behaviour, not a bug. Right there in the SQLite FAQ:

"SQLite uses reader/writer locks to control access to the database. [...] If the filesystem does not support POSIX advisory locks, SQLite cannot properly serialize concurrent database accesses."

I didn't read it. I assumed "it's just a file, it'll work anywhere." It doesn't.

Your Options for SQLite Persistence on Serverless Containers

Option 1 — Accept Ephemeral Storage

Use local storage (Data Source=ragapi.db). For demos, prototypes, or read-heavy apps where data can be regenerated, this is the simplest and most reliable choice.

Option 2 — Startup Migration from Blob Storage

On container startup, copy the database file from Azure Blob Storage to local disk, use it locally, and optionally write it back on shutdown.

// Startup: copy DB from blob to local
var blobClient = new BlobClient(connectionString, "databases", "ragapi.db");
await blobClient.DownloadToAsync("/app/ragapi.db");

// On graceful shutdown: push it back
await blobClient.UploadAsync("/app/ragapi.db", overwrite: true);
Enter fullscreen mode Exit fullscreen mode

Option 3 — Switch to a Proper Networked Database

If you need persistence, concurrency, and reliability in a containerised environment:

  • Azure SQL (managed SQL Server)
  • Azure Database for PostgreSQL with pgvector extension (great for RAG)
  • Azure Cosmos DB (document storage)

EF Core supports all of these with minimal provider swapping.

The Real Lesson

SQLite and network file systems are fundamentally incompatible.

Not partially. Not in certain modes. Fundamentally.

I lost three hours to a problem that was documented, well-known, and completely avoidable. The fix was one line. The lesson cost an afternoon.

If you're building RAG APIs in .NET, my recommendation: start with PostgreSQL + pgvector. It's containerisation-friendly, Azure-native, and EF Core's pgvector support via Npgsql.EntityFrameworkCore.PostgreSQL is excellent.

SQLite is a phenomenal database — for local dev, testing, and embedded apps. Just not on a network mount.

TL;DR

What I tried Result
Exponential backoff retry ❌ Not a transient lock
Journal Mode=Delete in connection string ❌ Not a valid Microsoft.Data.Sqlite keyword
Pre-create DB + set PRAGMA journal_mode=DELETE ❌ COMMIT still fails on SMB
Switch to local Data Source=ragapi.db ✅ Works instantly

Root cause: Azure Files SMB doesn't support POSIX byte-range locking. SQLite requires it.

Fix: Use local ephemeral storage, copy from Blob on startup, or switch to PostgreSQL.

Tags: dotnet csharp sqlite azure rag entityframework debugging cloudnative

Top comments (0)