Using SQLite from C# in Business Apps — WAL Mode, Exclusive Locking, Corruption Countermeasures, and When to Reach for EF Core

In the previous article, “Choosing Where Windows Apps Should Store Local Data,” I wrote that SQLite is the first choice for storing growing business data and history. That decision is settled, but once you actually sit down to embed it, a different set of hesitations shows up. In consulting sessions we often hear things like: “Searching NuGet for SQLite turns up several packages, and I don’t know which one to install,” “It works, but database is locked shows up occasionally. I’m papering over it with retries — is that actually correct?”, and “Is it fine to back up by just copying the DB file?”

Every one of these is an issue you will run into sooner or later if you operate SQLite in a business app for several years, and the kind of thing that saves you a lot of grief later if you nail it down in the initial design. This article works through, with Microsoft.Data.Sqlite as the baseline, the library choice, connection strings and pooling, how WAL mode actually works, how to deal with SQLITE_BUSY, type-mapping pitfalls, corruption countermeasures and backups, and where EF Core fits in versus the bare ADO.NET provider — the full set of items I check every time in a design review.

1. The short version

• For new development, the library to use is Microsoft.Data.Sqlite (or the EF Core SQLite provider built on top of it). It is a different animal from System.Data.SQLite — the connection string and even fine-grained behavior are not compatible — so always be aware of which one a sample you find online is assuming.¹
• Turn on WAL mode from the very first release. It raises read/write concurrency and eliminates most instances of database is locked. The setting persists in the DB file itself, but it does not work on a network share.²
• database is locked (SQLITE_BUSY) is not something to paper over with more error handling — it is a sign your design needs revisiting. Microsoft.Data.Sqlite automatically retries up to the command timeout (30 seconds by default)³, but the real fix is consolidating your write path into one.
• When doing a large number of small INSERTs, wrapping them in an explicit transaction alone buys you two to three orders of magnitude in speed. If SQLite “feels slow,” suspect your commit granularity first.
• SQLite effectively has only four storage types — INTEGER / REAL / TEXT / BLOB — and DateTime, Guid, and decimal are all stored as TEXT. decimal in particular gets compared and sorted by string rules, so it’s safest to store monetary amounts as integers in the smallest currency unit.⁴
• Never back up by copying the file while the app is running. Use VACUUM INTO or the Backup API (SqliteConnection.BackupDatabase). Manually deleting the -wal / -shm files is also strictly off-limits.⁵
• Plain SQLite does not support encryption. The Password keyword in the connection string only has an effect if you’ve bundled a SQLCipher-family native library⁶; for a small amount of sensitive data, consider protecting it with DPAPI before reaching for full DB encryption.

2. Choosing a library — similar names, different insides

There are several packages for using SQLite from .NET, and the confusingly similar names are the first stumbling block. Here’s how they break down:

Microsoft.Data.Sqlite is maintained by the EF Core team and is also the foundation the EF Core SQLite provider is built on.¹ Because the NuGet package bundles the native binary (the SQLite engine itself), there’s no separate distribution step for client PCs, and NuGet absorbs the x86/x64/ARM64 differences for you.

One thing to watch for: information written for System.Data.SQLite doesn’t carry over as-is. Because it has been around for a long time, a great deal of the sample code online assumes System.Data.SQLite, and you’ll run into incompatibilities like these:

• The connection strings are not compatible. Keywords such as Version=3, UseUTF16Encoding, and the date-format-changing DateTimeFormat don’t exist in Microsoft.Data.Sqlite, and specifying them throws an exception. The supported keywords are a short list: Data Source / Mode / Cache / Password / Foreign Keys / Default Timeout / Pooling, and a few others.⁶
• Type handling differs. For example, a Guid is stored as BLOB by default in System.Data.SQLite, but as TEXT in Microsoft.Data.Sqlite. During a migration period where the same DB file is read and written by both libraries, this difference shows up as data inconsistency.

Microsoft.Data.Sqlite is deliberately built thin — it has no proprietary type conversions or convenience features of its own — so the official SQLite documentation applies directly. If an existing app is running stably on System.Data.SQLite, there’s no need to force a migration, but as a rule new code should lean toward Microsoft.Data.Sqlite, and if you do migrate, first enumerate the incompatibilities above as migration line items.

3. Connections and connection strings — pooling holds onto the file

The basic form of the connection string is just a file path. What matters in practice is Mode and Pooling.⁶

using Microsoft.Data.Sqlite;

var builder = new SqliteConnectionStringBuilder
{
    DataSource = dbPath,
    Mode = SqliteOpenMode.ReadWriteCreate  // Default value: create the file if it doesn't exist
};
using var conn = new SqliteConnection(builder.ConnectionString);
conn.Open();

• Mode: The default is ReadWriteCreate (create if it doesn’t exist). For cases like distributing a read-only master DB, specifying ReadOnly prevents accidental writes from bugs or operator mistakes.
• Cache: Normally leave this at the default. The documentation explicitly states that Cache=Shared is not recommended alongside WAL mode, so it doesn’t come up under the WAL-first policy this article recommends.⁶
• Password: If you specify it, PRAGMA key is sent right after the connection opens, but the standard native library doesn’t support encryption, so nothing actually happens.⁶ If full DB encryption is a requirement, you need to swap in a SQLCipher-family bundle (e.g., SQLitePCLRaw.bundle_e_sqlcipher), and storing that encryption key will still require DPAPI (Chapter 1).
• Default Timeout: The command timeout (30 seconds by default). This becomes the upper bound on the retry window discussed in Chapter 5.

One more note, on asynchronous APIs. Because SQLite itself has no asynchronous I/O, async methods like ExecuteNonQueryAsync run synchronously under the hood.⁷ “I made it async, so the UI won’t freeze” doesn’t hold, so heavy queries should be explicitly pushed off to a worker thread with something like Task.Run. The criteria for deciding when to use async/await are laid out in “C# async/await: a practical decision guide.”

3.1 The pooling trap — the file stays open even after Close

Microsoft.Data.Sqlite has had connection pooling enabled by default since version 6.0.⁶ The upside is that it avoids reopening the file every time you call Open, but the downside is that even after Close / Dispose, the native connection stays in the pool and keeps holding the DB file handle. As a result, operations like the following fail with “the file is in use”:

• A “reset data” feature that deletes the DB file and recreates it
• Restoring from a backup by swapping the DB file
• Moving the DB file during uninstall or evacuation processing

The fix is to empty the pool immediately before the file operation.

// Dispose of the pool associated with this connection string, releasing the file handle
SqliteConnection.ClearPool(new SqliteConnection(connectionString));
File.Delete(dbPath);

With WAL mode (next chapter) the -wal / -shm files may also still be around, so clean those up too. If you want to release everything when the app exits, use SqliteConnection.ClearAllPools(); and if a one-off tool has no need for pooling at all, you can also set Pooling=False in the connection string. “I called Close but I still can’t delete it” is a support question that comes up often after migrating to SQLite, so build ClearPool into any utility routine that touches the DB file from the start.

4. How WAL mode works — understand it before you flip the switch

The previous article only said “enable WAL mode,” so this time I’ll go into how it actually works. Under the default rollback-journal method, reads are blocked while a write is in progress, which is the main cause of database is locked. The WAL (Write-Ahead Logging) method writes changes to an append-only log file instead of the DB body, so reads never block writes, and writes never block reads.² The typical business-app shape of a UI thread displaying history while a background process writes measurement values works exactly as-is.

Once you switch to WAL mode, two extra files show up next to the DB body (the content finalized by a checkpoint):

There are four properties worth keeping in mind:

• Checkpoints: the process that transcribes the contents of -wal into the body, which by default runs automatically once WAL reaches 1,000 pages (about 4MB).² If a long-running read transaction sticks around, checkpointing can’t proceed and -wal swells, so avoid a design where a “read connection is kept open and passed around” — open it when you need it and close it (pooling makes reopening fast).
• The setting persists in the DB: PRAGMA journal_mode=WAL is recorded in the DB file itself once you run it, and the DB stays in WAL mode thereafter no matter which connection opens it.² You don’t need to issue it on every connection.
• Writes are still one at a time: what WAL raises is read/write concurrency — writes against each other are still mutually exclusive. Misread this as “WAL means I can write freely from multiple threads” and you’ll run straight into the SQLITE_BUSY discussed in Chapter 5.
• Doesn’t work on network shares: because the wal-index assumes shared memory, it doesn’t function between processes on different machines.² As covered in the previous article, you shouldn’t be putting SQLite on a network share in the first place.

One operational note: the -wal file contains transactions that are committed but not yet applied to the body. Both “it’s fine to copy just the body .db” and “the -wal is a temp file, so it’s fine to delete it” are wrong — either the most recent commits are lost, or in the worst case the DB is corrupted.⁵ This connects directly to the “backups can’t be a plain file copy” discussion in Chapter 7.

5. Locking and SQLITE_BUSY — consolidate writes into one path

SQLITE_BUSY (surfaced in exception messages as database is locked) occurs when another connection is holding the write lock. The first thing worth knowing is that Microsoft.Data.Sqlite automatically retries busy/locked errors up to the command timeout (30 seconds by default).³ In other words, rolling your own catch-sleep-retry logic in the app is usually unnecessary. If an exception does still come flying out after hitting the timeout, it’s one of the following:

• Another connection (or another process) is holding a long transaction that exceeds 30 seconds
• A transaction that started as a read attempted to upgrade to a write and collided with another write — waiting doesn’t resolve this, so it fails immediately. Processes that “read, then write” should be designed from the start as write transactions
• A large number of small writes from multiple threads are piling in at once, fighting over the lock

None of these are solved by “increasing the retry count.” The fix is to shorten transactions and consolidate the write path into one.

5.1 Building a write queue with System.Threading.Channels

For data that originates on multiple threads (measurements, operation logs, and the like), rather than each thread writing directly to the DB, hand it off to a queue that a dedicated write loop processes. In .NET, System.Threading.Channels fits this directly.

using System.Globalization;
using System.Threading.Channels;
using Microsoft.Data.Sqlite;

public sealed record Measurement(string DeviceId, double Value, DateTime CreatedAtUtc);

public sealed class MeasurementWriter : IAsyncDisposable
{
    private readonly Channel<Measurement> _channel =
        Channel.CreateBounded<Measurement>(new BoundedChannelOptions(10_000)
        {
            FullMode = BoundedChannelFullMode.Wait  // Block the producer if the queue overflows
        });
    private readonly string _connectionString;
    private readonly Task _loop;

    public MeasurementWriter(string connectionString)
    {
        _connectionString = connectionString;
        _loop = Task.Run(WriteLoop);
    }

    // Safe to call from any thread. Never touches the DB. If the write loop
    // has died, the producer finds out immediately via ChannelClosedException
    public ValueTask EnqueueAsync(Measurement m) => _channel.Writer.WriteAsync(m);

    private async Task WriteLoop()
    {
        try
        {
            await WriteLoopCore();
        }
        catch (Exception ex)
        {
            // Propagate the fact that the writer died (e.g. disk full) to
            // producers via the channel. Skip this and once the queue fills
            // up, EnqueueAsync will wait forever with no one noticing the failure
            _channel.Writer.TryComplete(ex);
            throw;
        }
    }

    private async Task WriteLoopCore()
    {
        using var conn = new SqliteConnection(_connectionString);
        conn.Open();
        var buffer = new List<Measurement>(500);
        while (await _channel.Reader.WaitToReadAsync())
        {
            // Drain up to 500 pending items and write them in a single transaction
            buffer.Clear();
            while (buffer.Count < 500 && _channel.Reader.TryRead(out var m))
                buffer.Add(m);

            using var tx = conn.BeginTransaction();
            using var cmd = conn.CreateCommand();
            cmd.Transaction = tx;
            cmd.CommandText =
                "INSERT INTO measurement (device_id, value, created_at) " +
                "VALUES ($device, $value, $at)";
            var pDevice = cmd.Parameters.Add("$device", SqliteType.Text);
            var pValue  = cmd.Parameters.Add("$value",  SqliteType.Real);
            var pAt     = cmd.Parameters.Add("$at",     SqliteType.Text);
            foreach (var m in buffer)
            {
                pDevice.Value = m.DeviceId;
                pValue.Value  = m.Value;
                // Use InvariantCulture explicitly so the culture-default calendar/numerals don't intrude
                pAt.Value     = m.CreatedAtUtc.ToString(
                    "yyyy-MM-dd HH:mm:ss.fffffff", CultureInfo.InvariantCulture);
                cmd.ExecuteNonQuery();
            }
            tx.Commit();
        }
    }

    public async ValueTask DisposeAsync()
    {
        // Don't throw if the write loop already closed the channel due to failure
        // (Complete() would throw on a double-close, hiding the original exception)
        _channel.Writer.TryComplete();
        await _loop;  // Drain what's left. If the loop died, the original exception surfaces here
    }
}

This shape structurally eliminates write contention, and because it naturally batches whatever has piled up in the queue, you also get the speedup discussed in the next section for free. A few other details matter in a business app: draining the queue with DisposeAsync before shutting down, explicitly deciding the overflow behavior via FullMode, and propagating a failure in the write loop back to producers via TryComplete(ex) (if the sole writer dies silently, the failure shows up as producers waiting forever once the queue fills). This same “consolidate the path into one” idea also shows up in the exclusive-locking design for file-based integration, in “File Integration and Locking Best Practices.”

5.2 Batching into transactions changes the order of magnitude

Because SQLite performs a synchronous disk write (fsync) on every commit, inserting rows one at a time with implicit commits tops out at a few hundred to a few thousand rows per second even on an SSD, and only a few dozen per second on an HDD. Simply wrapping the same INSERTs in explicit transactions of 1,000 rows at a time (bracketed with BeginTransaction and a final Commit) gets you into the tens of thousands to hundreds of thousands of rows per second. A three-line change that’s almost too small to call “tuning” moves the needle by two to three orders of magnitude.

A lot of complaints like “CSV import takes 20 minutes” or “the startup data migration never finishes” trace back to exactly this, and are resolved just by wrapping things in transactions and reusing parameters (the shape shown in the previous section). Conversely, if you make a transaction too long, it now makes other writes wait, so a practical rule of thumb is to land on one commit per “a few hundred to a few thousand rows, or a few hundred milliseconds’ worth.”

6. Type-mapping pitfalls — what do you assign to only four types?

SQLite can actually only store four types — INTEGER / REAL / TEXT / BLOB — and every .NET type gets mapped to one of them. The main mappings in Microsoft.Data.Sqlite are as follows:⁴

The three that fall through to TEXT need special care:

• DateTime: as long as the format and time zone are kept consistent, the ISO-8601-style format in TEXT means string sorting equals chronological sorting, so in practice there’s no problem. Put the other way around, things break the moment UTC and local time get mixed. Deciding up front “store as UTC, convert to local only for display” and enforcing it across every code path is the only real solution; SQLite functions like datetime('now') also return UTC. Another point: when formatting the string yourself, always pass CultureInfo.InvariantCulture to ToString. Left at the culture default, only machines running under a non-Gregorian culture (Japanese era names, Buddhist calendar, and so on) will get a different year representation, breaking both sorting and reading (see the code example in 5.1).
• Guid: stored as a string, so matching is a string comparison. If a different tool or library writes it in a different notation (upper/lower case, BLOB form), cross-referencing fails, so if multiple languages/tools touch it, document the notation as a firm specification.
• decimal: the biggest trap. It’s stored as TEXT because REAL would lose precision⁴, but a comparison like WHERE amount > 1000 against a TEXT column doesn’t behave as intended, because in SQLite’s type ordering TEXT always sorts greater than numeric. Aggregates like SUM also get internally converted to REAL, losing the precision that was the whole point of choosing decimal.

The practical guidance for decimal is simple: store monetary amounts as an integer (INTEGER) in the smallest currency unit. For Japanese yen, store a long in yen and convert only for display. With an integer, comparison and aggregation are both accurate and fast, and the type trap disappears. If existing schema constraints force you to keep decimal as-is, resign yourself to doing comparisons and aggregation on the .NET side after reading the data rather than in SQL.

One more thing: the type name you write in CREATE TABLE is just a hint for “affinity” — a custom type name like STRING is an invitation to implicit-conversion accidents. The official recommendation is to use only the four type names INTEGER / REAL / TEXT / BLOB for column types too.⁴

7. Operations — don’t break it, and be able to recover if it does

7.1 quick_check at startup

Even though SQLite is protected by transactions, corruption from disk failure or a mistaken file operation can never be reduced to zero. To avoid running on a corrupted DB and spreading the damage, add an integrity check at startup. A full integrity_check can take a long time on a large DB, so the lightweight quick_check is enough for everyday use.

using var cmd = conn.CreateCommand();
cmd.CommandText = "PRAGMA quick_check";
var result = (string)cmd.ExecuteScalar()!;
if (result != "ok")
{
    // Don't write further to a corrupted DB. Degrade to read-only and prompt for restoration
    logger.LogError("Database corruption detected: {Detail}", result);
    EnterReadOnlyMode(result);
}

As written in section 6.2 of the previous article, the policy is to not auto-repair or auto-rollback on detecting corruption, but instead to involve a human operator.

7.2 Backups — why a file copy doesn’t work

A plain copy of the DB file while it’s running can pick up a mix of mid-transaction state, and the official SQLite documentation explicitly names this as a cause of corruption.⁵ On top of that, under WAL mode, copying only the body and leaving the -wal (which contains commits not yet applied to the body — Chapter 4) behind means the most recent data drops out.

There are two correct methods, both of which take a consistent snapshot while the app keeps running:

• VACUUM INTO: a single SQL statement that produces a defragmented, minimally-sized copy.⁸ The code sample is in section 6.3 of the previous article, so refer to it there.
• SqliteConnection.BackupDatabase: a wrapper around SQLite’s Backup API that copies between connection objects.

using var source = new SqliteConnection($"Data Source={dbPath}");
using var target = new SqliteConnection($"Data Source={backupPath}");
source.Open();
target.Open();
source.BackupDatabase(target);  // Produces a consistent snapshot even while running

That said, BackupDatabase has a caveat. The current Microsoft.Data.Sqlite implementation copies as fast as possible and blocks writes from other connections until it’s done.⁹ If you back up a large DB while measurements or user operations are actively underway, writes during that window will surface as SQLITE_BUSY or a frozen screen. A safe split is to use VACUUM INTO for routine generational backups, and reserve BackupDatabase for round-tripping with an in-memory DB, or for duplicating during a maintenance window when writes have paused. If you use Task Scheduler for periodic execution, don’t do the file copy from outside — have the app itself (or a small tool that opens SQLite correctly) run the backup described above. The design of the periodic-execution mechanism itself is covered in “Running Scheduled Tasks Reliably with Task Scheduler.”

7.3 Placement and migration

The basic location for the DB file is %LOCALAPPDATA%\<company name>\<app name>, and startup migration via PRAGMA user_version is the minimal setup for schema version management. Both are covered with code in the previous article (Chapter 3 and section 6.1), so they aren’t repeated here. One addition: taking one generation of backup from 7.2 before running a migration turns the worst case — “migration failed and the app won’t start” — into a simple file swap for recovery.

8. When to reach for EF Core — the ORM break-even point

Everything up to this point has been written against bare Microsoft.Data.Sqlite, but there are clear cases where the EF Core SQLite provider is the right call. The deciding axis is the character of the app.

If you choose EF Core, you need to know the constraints specific to the SQLite provider.¹⁰

• Table rebuilds due to ALTER TABLE constraints: SQLite doesn’t directly support changing or dropping a column’s type, so migrations involving AlterColumn or DropColumn execute as a rebuild — “create new table → copy data → drop old table → rename.” This affects apply time and disk usage on environments with large data volumes, so plan schema changes to large tables deliberately.
• Idempotent scripts can’t be generated: migration scripts with if-then branching, like those for SQL Server, cannot be produced. Applying via dbContext.Database.Migrate() at app startup is the realistic approach.
• decimal / DateTimeOffset operations are client-evaluated: the type situation from Chapter 6 doesn’t go away under EF Core. Anything beyond equality comparison or sorting is evaluated client-side, so the guidance to store monetary amounts as an integer in the smallest unit still applies under EF Core (you can convert to long for storage via a value converter).
• WAL is enabled by default: a DB created by EF Core is in WAL mode from the start⁷, so the configuration in Chapter 4 isn’t needed. You still need to understand the behavior, though.

Even when you adopt EF Core, using a SQLite in-memory DB for unit tests of the repository layer works well. Because it runs on the same provider as production, the gap where “it passes with a mock but fails against the real DB” shrinks. For how to think about which layer to write tests at, see “Where to Draw the Line Between Unit Tests and Integration Tests.”

9. Summary

SQLite is a library where “just embedding it takes 30 minutes, but running it correctly takes design work.” That said, the design work needed is well-defined, and turning the contents of this article into a checklist gives you these six points:

• Use the library Microsoft.Data.Sqlite (or EF Core on top of it). Don’t mix in information that assumes System.Data.SQLite
• Enable WAL mode from the first release, and understand the roles of -wal / -shm
• Consolidate writes into one path (a write queue with Channels), and batch small INSERTs into transactions
• Keep DateTime in UTC consistently, and store money as an integer in the smallest currency unit. Don’t compare or aggregate decimal stored as TEXT as-is
• Run quick_check at startup, and back up with VACUUM INTO or BackupDatabase. Never copy the file while running
• Don’t forget SqliteConnection.ClearPool for any process that deletes or replaces the DB file

If any of this sounds familiar — papering over database is locked with repeated retries, taking backups via plain file copy — it’s worth going through the items in this article once before something actually breaks. Every one of these is a small fix on its own.

Related Articles

• Choosing Where Windows Apps Should Store Local Data — A Decision Table for SQLite / JSON / Registry / Access
• Protecting Sensitive Data in Windows Apps — Avoiding Plaintext Settings with DPAPI
• Running Scheduled Tasks Reliably with Task Scheduler
• C# async/await: a practical decision guide — writing code that doesn’t freeze the UI or deadlock

Related Consulting Areas

Komura Soft LLC handles design reviews for business apps built on SQLite (locking design, backup strategy, migration design), troubleshooting of production issues such as database is locked, data corruption, and performance degradation, and migration support from existing data stores such as Access.

• Technical Consulting / Design Review
• Windows App Development
• Legacy Asset Reuse / Migration Support
• Contact

References

</content>

Related Articles

Recent articles sharing the same tags. Deepen your understanding with closely related topics.

Related Topics

These topic pages place the article in a broader service and decision context.

Where This Topic Connects

This article connects naturally to the following service pages.

Author Profile

Profile page for the article author.

Go Komura

Representative of KomuraSoft LLC

Focused on Windows software development, technical consulting, and investigations into failures that are difficult to reproduce.