Implementing SQLite Database Migrations in .NET MAUI

🗃️ Implementing SQLite Database Migrations in .NET MAUI

SQLite is one of the most common choices for local persistence in .NET MAUI applications.

It is lightweight, embedded, fast, requires no external database server, and works particularly well for scenarios such as offline storage, cached API data, user preferences, synchronization metadata, drafts, queues, and domain data.

Creating the initial database is usually straightforward.

    await database.CreateTableAsync<Customer>();
    await database.CreateTableAsync<Order>();

The real challenge begins after the application has been released. Imagine version 1.0 of your application contains:

    Customer
    ────────────
    Id
    Name
    Email

A few months later, version 2.0 requires:

    Customer
    ────────────
    Id
    Name
    Email
    PhoneNumber
    CreatedAt

New installations aren't a problem. They can simply create the latest schema.

Existing users are different.

Their devices may already contain thousands of records using the old schema.

Deleting the database and recreating it would technically solve the schema mismatch—but it would also delete the user's data. 💥

A production application therefore needs a controlled way to evolve:

    Schema v1
       │
       ▼
    Schema v2
       │
       ▼
    Schema v3
       │
       ▼
    Schema v4

without destroying existing data.

That process is known as database migration.

In this article, we'll build a practical migration architecture for SQLite databases in .NET MAUI using explicit schema versions, incremental migrations, transactions, validation, logging, recovery strategies, and automated tests. 🚀

📌 Table of Contents

Why Database Migrations Matter
The Problem with Existing Installations
Why CreateTableAsync Is Not a Migration Strategy
Schema Versioning
Designing a Migration Contract
Building Individual Migrations
Creating the Migration Runner
Incremental Migrations
Running Migrations in Transactions
Schema Changes
Data Migrations
Creating Indexes
Handling Application Updates Across Multiple Versions
Preventing Duplicate Migration Execution
Handling Migration Failures
Application Startup Integration
Logging and Diagnostics
Testing Migrations
Migration Immutability
Common Mistakes
Production Architecture
Best Practices
Conclusion

1. 🧠 Why Database Migrations Matter

During development, resetting a database is easy. Something changes? Delete:

    app.db

and recreate it.

Problem solved.

But production devices are different.

A user's local database might contain:

    Offline records
    Draft forms
    Cached business data
    Pending synchronization operations
    User-created content
    Outbox messages
    Configuration
    Application state

Deleting that database during an upgrade may be unacceptable.

Consider an application installed six months ago:

    Installed App
    Version 1.0
    
    Database
    Schema v1

The user later installs:

    App Version 3.0
    
    Expected Database
    Schema v4

The application must transform the existing database:

while preserving valid data.

That's the responsibility of the migration system.

2. 📱 The Existing Installation Problem

New installations are easy because there is no previous schema.

    Install latest app
           │
           ▼
    Database doesn't exist
           │
           ▼
    Create latest schema

Existing installations are more complicated.

    Existing App
    Schema v2
        │
        ▼
    App Store / Play Store Update
        │
        ▼
    New App
    expects Schema v5

The application cannot assume the database already matches its current models. Without migration handling, code like:

    await database.Table<Customer>()
        .ToListAsync();

may eventually fail because application code expects columns or tables that don't exist on the device.

This creates a fundamental rule:

Application version and database schema version are related, but they are not the same thing.

Your application might be:

    App version:    4.7.2
    Schema version: 9

The schema should have its own explicit version.

3. ⚠️ Why `CreateTableAsync<T>()` Is Not a Complete Migration Strategy

A common initialization approach looks like:

    await database.CreateTableAsync<Customer>();
    await database.CreateTableAsync<Order>();
    await database.CreateTableAsync<Product>();

This is useful for creating tables.

But schema evolution involves much more than ensuring a table exists.

You may eventually need to:

    Add a column
    Rename a column
    Remove a column
    Create an index
    Transform existing data
    Split one table into two
    Merge tables
    Populate new fields
    Change relationships
    Normalize existing records
    Rebuild a table

For example:

    v1
    
    Customer
    ├── Id
    ├── Name
    └── Email

becomes:

    v2
    
    Customer
    ├── Id
    ├── FirstName
    ├── LastName
    ├── Email
    └── CreatedAt

That's not simply:

    Create table if missing

It is a controlled transformation of existing state.

4. 🔢 Schema Versioning

SQLite provides a convenient database-level integer that applications can use for schema versioning:

    PRAGMA user_version;

Reading it returns an integer. For example:

for an unversioned/new database. We can update it with:

    PRAGMA user_version = 3;

This gives us a lightweight version marker stored directly inside the SQLite database. Conceptually:

    Database
    
    Tables
    Indexes
    Data
    Metadata
    
    PRAGMA user_version = 4

Define the latest version in the application:

    public static class DatabaseSchema
    {
        public const int CurrentVersion = 4;
    }

Now startup can compare:

    Installed schema = 2
    Application schema = 4

and determine that migrations are required.

5. 🧩 Designing a Migration Contract

Each migration should represent one transition. For example:

    Migration 1 → 2
    Migration 2 → 3
    Migration 3 → 4

Define:

    public interface IDatabaseMigration
    {
        int FromVersion { get; }
    
        int ToVersion { get; }
    
        Task MigrateAsync(
            SQLiteAsyncConnection database,
            CancellationToken cancellationToken = default);
    }

A migration now explicitly declares:

    Where it starts
    Where it ends
    How to transform the database

This also makes migrations easy to discover, test, order, and validate.

6. 🧱 Creating the First Migration

Suppose schema v1 contains:

    Customer
    ────────
    Id
    Name
    Email

Schema v2 adds:

    PhoneNumber

Our migration could be:

    public sealed class MigrationV1ToV2
        : IDatabaseMigration
    {
        public int FromVersion => 1;
    
        public int ToVersion => 2;
    
        public async Task MigrateAsync(
            SQLiteAsyncConnection database,
            CancellationToken cancellationToken = default)
        {
            cancellationToken.ThrowIfCancellationRequested();
    
            await database.ExecuteAsync(
                """
                ALTER TABLE Customer
                ADD COLUMN PhoneNumber TEXT;
                """);
        }
    }

The transition is explicit:

    Schema v1
       │
       │ ALTER TABLE
       ▼
    Schema v2

We don't need a giant migration containing the entire database history.

Each migration performs one evolution step.

7. 🏃 Building the Migration Runner

Now we need something responsible for discovering which migrations are required.

    public interface IDatabaseMigrationRunner
    {
        Task MigrateAsync(
            CancellationToken cancellationToken = default);
    }

Implementation:

    public sealed class DatabaseMigrationRunner
        : IDatabaseMigrationRunner
    {
        private readonly SQLiteAsyncConnection _database;
        private readonly IReadOnlyList<IDatabaseMigration> _migrations;
        private readonly ILogger<DatabaseMigrationRunner> _logger;
    
        public DatabaseMigrationRunner(
            SQLiteAsyncConnection database,
            IEnumerable<IDatabaseMigration> migrations,
            ILogger<DatabaseMigrationRunner> logger)
        {
            _database = database;
            _migrations = migrations
                .OrderBy(x => x.FromVersion)
                .ToArray();
    
            _logger = logger;
        }
    
        public async Task MigrateAsync(
            CancellationToken cancellationToken = default)
        {
            var currentVersion =
                await GetCurrentVersionAsync();
    
            if (currentVersion > DatabaseSchema.CurrentVersion)
            {
                throw new InvalidOperationException(
                    $"Database schema {currentVersion} is newer than " +
                    $"supported schema {DatabaseSchema.CurrentVersion}.");
            }
    
            while (currentVersion < DatabaseSchema.CurrentVersion)
            {
                cancellationToken.ThrowIfCancellationRequested();
    
                var migration =
                    _migrations.SingleOrDefault(
                        x => x.FromVersion == currentVersion);
    
                if (migration is null)
                {
                    throw new InvalidOperationException(
                        $"No migration exists from schema version {currentVersion}.");
                }
    
                await RunMigrationAsync(
                    migration,
                    cancellationToken);
    
                currentVersion = migration.ToVersion;
            }
        }
    
        private async Task<int> GetCurrentVersionAsync()
        {
            return await _database.ExecuteScalarAsync<int>(
                "PRAGMA user_version;");
        }
    
        private async Task SetCurrentVersionAsync(
            int version)
        {
            await _database.ExecuteAsync(
                $"PRAGMA user_version = {version};");
        }
    
        private async Task RunMigrationAsync(
            IDatabaseMigration migration,
            CancellationToken cancellationToken)
        {
            _logger.LogInformation(
                "Migrating SQLite database from schema {FromVersion} to {ToVersion}",
                migration.FromVersion,
                migration.ToVersion);
    
            await migration.MigrateAsync(
                _database,
                cancellationToken);
    
            await SetCurrentVersionAsync(
                migration.ToVersion);
    
            _logger.LogInformation(
                "SQLite migration {FromVersion} -> {ToVersion} completed",
                migration.FromVersion,
                migration.ToVersion);
        }
    }

Now the migration runner decides what needs to execute.

8. ⬆️ Incremental Migrations

Suppose the application currently expects:

    Schema v5

but a user hasn't opened the application since:

    Schema v2

The migration runner should not jump blindly from:

    v2 → v5

Instead:

    Current = v2
    
    MigrationV2ToV3
            │
            ▼
           v3
            │
    MigrationV3ToV4
            │
            ▼
           v4
            │
    MigrationV4ToV5
            │
            ▼
           v5

This makes every migration responsible for one known source schema. For example:

    public sealed class MigrationV2ToV3
        : IDatabaseMigration
    {
        public int FromVersion => 2;
    
        public int ToVersion => 3;
    
        public Task MigrateAsync(
            SQLiteAsyncConnection database,
            CancellationToken cancellationToken = default)
        {
            return database.ExecuteAsync(
                """
                CREATE INDEX IF NOT EXISTS
                IX_Customer_Email
                ON Customer(Email);
                """);
        }
    }

And:

    public sealed class MigrationV3ToV4
        : IDatabaseMigration
    {
        public int FromVersion => 3;
    
        public int ToVersion => 4;
    
        public async Task MigrateAsync(
            SQLiteAsyncConnection database,
            CancellationToken cancellationToken = default)
        {
            await database.ExecuteAsync(
                """
                ALTER TABLE Customer
                ADD COLUMN CreatedAt TEXT;
                """);
    
            await database.ExecuteAsync(
                """
                UPDATE Customer
                SET CreatedAt = CURRENT_TIMESTAMP
                WHERE CreatedAt IS NULL;
                """);
        }
    }

9. 🔐 Migration Transactions

A migration may execute multiple statements. Consider:

    1. Add column
    2. Create index
    3. Transform data
    4. Update schema version

What happens if step 3 fails?

Without appropriate transactional behavior, the database may become partially migrated:

    Column added       ✅
    Index created      ✅
    Data transformed   ❌
    Version updated    ❌

Now the database is neither clearly v2 nor v3.

Where supported by the operations involved, schema and data transformations should be executed transactionally. Conceptually:

    BEGIN TRANSACTION
            │
            ├── Schema change
            ├── Data migration
            ├── Index creation
            └── Version update
            │
          COMMIT

If something fails:

    ROLLBACK

The invariant we want is:

Either the migration completes, or the database remains in a recoverable previous state.

With your SQLite abstraction, expose transaction execution at the migration-runner level rather than letting every migration invent its own transaction policy.

10. 🧱 Schema Migrations

Schema migrations change database structure. Typical operations include:

Adding columns

    ALTER TABLE Customer
    ADD COLUMN PhoneNumber TEXT;

Creating tables

    CREATE TABLE Address
    (
        Id INTEGER PRIMARY KEY,
        CustomerId INTEGER NOT NULL,
        Street TEXT,
        City TEXT
    );

Creating indexes

    CREATE INDEX IF NOT EXISTS
    IX_Address_CustomerId
    ON Address(CustomerId);

Removing structures

Depending on the SQLite version and exact operation, you may need to rebuild a table rather than relying on a direct ALTER TABLE operation.

This is particularly important when performing more complex schema transformations.

11. 🔄 Data Migrations

Not every migration changes structure. Sometimes existing data itself needs to evolve. Imagine:

    v2
    
    Customer.Name
    "Jorge Perales"

Schema v3 introduces:

    FirstName
    LastName

Simply adding columns isn't enough.

Existing records need transformation. Conceptually:

    Old Data
       │
       ▼
    Migration
       │
       ├── Parse Name
       ├── Populate FirstName
       └── Populate LastName
       │
       ▼
    New Data

A migration may therefore execute both:

    DDL
    +
    Data transformation

For complex transformations, C# can sometimes be clearer than trying to encode all business rules in SQL.

    public async Task MigrateAsync(
        SQLiteAsyncConnection database,
        CancellationToken cancellationToken = default)
    {
        var customers =
            await database.QueryAsync<LegacyCustomer>(
                "SELECT Id, Name FROM Customer;");
    
        foreach (var customer in customers)
        {
            cancellationToken.ThrowIfCancellationRequested();
    
            var parts =
                customer.Name.Split(
                    ' ',
                    2,
                    StringSplitOptions.RemoveEmptyEntries);
    
            var firstName =
                parts.ElementAtOrDefault(0) ?? string.Empty;
    
            var lastName =
                parts.ElementAtOrDefault(1) ?? string.Empty;
    
            await database.ExecuteAsync(
                """
                UPDATE Customer
                SET FirstName = ?, LastName = ?
                WHERE Id = ?;
                """,
                firstName,
                lastName,
                customer.Id);
        }
    }

Whether SQL or C# is better depends on the migration.

12. ⚡ Creating Indexes Through Migrations

Indexes are part of your schema. Suppose production telemetry shows that this query is expensive:

    SELECT *
    FROM Orders
    WHERE CustomerId = ?;

Version 6 can introduce:

    CREATE INDEX IF NOT EXISTS
    IX_Orders_CustomerId
    ON Orders(CustomerId);

That belongs in:

    MigrationV5ToV6

rather than being created opportunistically every time the application starts.

Why?

Because your migration history should describe how the production database reached its current structure.

13. 📱 Users Can Skip Many Application Versions

Never assume:

    User has v4
    User installs v5

Mobile users may skip months of releases. For example:

    January
    App 1.0
    Schema v1
    
           ↓ user doesn't update
    
    March
    App 2.0
    Schema v3
    
           ↓
    
    June
    App 3.0
    Schema v5
    
           ↓ user finally updates
    
    September
    App 4.0
    Schema v8

The user's database may need:

in a single application startup.

This is one of the strongest reasons to keep migrations incremental and deterministic.

14. 🛑 Validate the Migration Chain

Suppose your application contains:

    1 → 2
    2 → 3
    4 → 5

There's no:

    3 → 4

A user on schema 3 can never reach schema 5.

This should be detected before shipping.

A validator can verify the migration graph:

    public static void ValidateMigrationChain(
        IEnumerable<IDatabaseMigration> migrations,
        int latestVersion)
    {
        var map =
            migrations.ToDictionary(
                x => x.FromVersion);
    
        var version = 1;
    
        while (version < latestVersion)
        {
            if (!map.TryGetValue(
                    version,
                    out var migration))
            {
                throw new InvalidOperationException(
                    $"Missing migration from version {version}.");
            }
    
            if (migration.ToVersion <= version)
            {
                throw new InvalidOperationException(
                    $"Invalid migration {version} -> {migration.ToVersion}.");
            }
    
            version = migration.ToVersion;
        }
    }

This can be covered by an automated test.

15. 🔁 Preventing Duplicate Migration Execution

A migration must not run again after it succeeds.

That's why schema version updates are essential. Suppose:

    Current schema = 3

The runner sees:

    3 < 4

and executes:

    MigrationV3ToV4

After success:

    PRAGMA user_version = 4;

Next startup:

    Current schema = 4
    Expected schema = 4

Therefore:

    No migrations required

The version is the migration checkpoint.

16. 💥 Handling Migration Failures

Migration failures should be treated seriously.

A failed migration may mean the application cannot safely use its local data.

Avoid doing this:

    try
    {
        await migrationRunner.MigrateAsync();
    }
    catch
    {
        // Ignore
    }

The application then continues against an unknown schema. Instead:

    try
    {
        await migrationRunner.MigrateAsync();
    }
    catch (Exception ex)
    {
        logger.LogCritical(
            ex,
            "Database migration failed.");
    
        throw;
    }

The application can then enter a controlled failure/recovery path.

Depending on the nature of the data, recovery might include:

    Retry migration
    Restore backup
    Rebuild disposable cache database
    Require synchronization
    Display recovery UI
    Stop database-dependent initialization

The correct strategy depends heavily on what the database contains.

17. 🧯 Cache Database vs User Data Database

Not every SQLite database deserves the same failure policy. Consider two applications.

Database A

Contains:

    Downloaded product catalog
    Cached images metadata
    API response cache

If migration fails:

    Delete database
    Rebuild cache

may be acceptable.

Database B

Contains:

    Offline inspections
    Unsigned forms
    Pending orders
    Outbox messages
    User-generated records

Deleting it would be catastrophic.

Therefore migration recovery policy should consider data criticality.

Database content	Rebuild on failure?
Disposable API cache	Often yes
Search cache	Often yes
UI metadata	Possibly
Offline forms	Usually no
Pending synchronization	No
User-generated data	No
Outbox	No

Migration architecture is partly a data-governance problem.

18. 📝 Logging Migrations

Migration execution should be observable. Useful logging:

    _logger.LogInformation(
        "SQLite schema version {CurrentVersion}; target version {TargetVersion}",
        currentVersion,
        DatabaseSchema.CurrentVersion);

Before each migration:

    _logger.LogInformation(
        "Starting database migration {FromVersion} -> {ToVersion}",
        migration.FromVersion,
        migration.ToVersion);

After completion:

    _logger.LogInformation(
        "Completed database migration {FromVersion} -> {ToVersion}",
        migration.FromVersion,
        migration.ToVersion);

Failure:

    _logger.LogError(
        exception,
        "Database migration {FromVersion} -> {ToVersion} failed",
        migration.FromVersion,
        migration.ToVersion);

This makes production troubleshooting much easier.

19. ⏱️ Measuring Migration Duration

Large migrations may affect startup performance. Measure them.

    var stopwatch =
        Stopwatch.StartNew();
    
    await migration.MigrateAsync(
        _database,
        cancellationToken);
    
    stopwatch.Stop();
    
    _logger.LogInformation(
        "Migration {FromVersion} -> {ToVersion} completed in {ElapsedMs} ms",
        migration.FromVersion,
        migration.ToVersion,
        stopwatch.ElapsedMilliseconds);

Now you can detect problematic migrations. For example:

    1 → 2      24 ms
    2 → 3      17 ms
    3 → 4      31 ms
    4 → 5   8,912 ms ⚠️

That last migration deserves investigation.

20. 🚀 Running Migrations During Startup

The database should not become available to the rest of the application before migrations finish. Bad:

    App starts
       │
       ├──── ViewModel queries database
       │
       └──── Migration starts

This creates a race.

Prefer:

    App starts
       │
       ▼
    Initialize database
       │
       ▼
    Read schema version
       │
       ▼
    Run migrations
       │
       ▼
    Validate database
       │
       ▼
    Database Ready
       │
       ▼
    Application continues

This can be represented by an initialization service.

    public interface IDatabaseInitializer
    {
        Task InitializeAsync(
            CancellationToken cancellationToken = default);
    }

Implementation:

    public sealed class DatabaseInitializer
        : IDatabaseInitializer
    {
        private readonly IDatabaseMigrationRunner _migrationRunner;
    
        public DatabaseInitializer(
            IDatabaseMigrationRunner migrationRunner)
        {
            _migrationRunner = migrationRunner;
        }
    
        public async Task InitializeAsync(
            CancellationToken cancellationToken = default)
        {
            await _migrationRunner.MigrateAsync(
                cancellationToken);
        }
    }

The key is that database-dependent application flows wait for initialization.

21. 🖥️ Don't Freeze the UI

A migration involving thousands of rows can take noticeable time.

Don't perform heavy synchronous work on the UI thread.

Use asynchronous initialization and present appropriate startup state when necessary.

    Starting application...
            │
            ▼
    Preparing local data...
            │
            ▼
    Migration
            │
            ▼
    Ready

For a 30 ms migration, a dedicated migration UI would be unnecessary.

For a 15-second transformation of a large offline database, feedback may be important.

Design according to actual measured duration.

22. 💉 Dependency Injection

    builder.Services.AddSingleton<
        IDatabaseMigration,
        MigrationV1ToV2>();
    
    builder.Services.AddSingleton<
        IDatabaseMigration,
        MigrationV2ToV3>();
    
    builder.Services.AddSingleton<
        IDatabaseMigration,
        MigrationV3ToV4>();
    
    builder.Services.AddSingleton<
        IDatabaseMigrationRunner,
        DatabaseMigrationRunner>();

The runner receives:

    IEnumerable<IDatabaseMigration>

and builds the ordered migration chain.

This makes adding a new schema version straightforward:

    Create MigrationV4ToV5
    Register migration
    Set CurrentVersion = 5
    Add migration tests

23. 🧪 Testing Migrations

Migration code changes production data.

It deserves tests.

Do not test only:

    Fresh database → latest schema

because most migration bugs affect existing installations. Test:

    v1 → latest
    v2 → latest
    v3 → latest
    ...

For example:

    Create database at v1
            │
            ▼
    Insert representative v1 data
            │
            ▼
    Run migration runner
            │
            ▼
    Assert latest schema
            │
            ▼
    Assert original data survived

This is far more valuable than simply verifying that the migration didn't throw an exception.

24. 🧪 Test Real Data Transformations

Suppose:

    v2 → v3

splits Name. Seed:

    Name = "Jorge Perales"

Run the migration. Verify:

    FirstName = "Jorge"
    LastName  = "Perales"

Also test edge cases:

    "Jorge"
    ""
    NULL
    Very long values
    Unicode characters
    Unexpected legacy values

Production databases contain data that clean development databases often don't.

25. 🧪 Test Every Supported Starting Version

Suppose latest schema is:

v6

Your test matrix should ideally include:

Starting Schema	Target	Expected
v1	v6	Success
v2	v6	Success
v3	v6	Success
v4	v6	Success
v5	v6	Success
v6	v6	No-op

This catches broken migration chains. It also verifies that an already-current database isn't modified unnecessarily.

26. 💥 Test Migration Failure

Inject a failure. For example:

    Migration 3 → 4
    Step 1 succeeds
    Step 2 throws

Then verify the expected transactional behavior.

You want to know what happens before a real user's database experiences that failure.

Tests should verify:

    Schema version
    Existing records
    New columns/tables
    Transaction rollback
    Retry behavior

after failure.

27. 🧬 Migrations Should Be Immutable

Once:

    MigrationV3ToV4

has shipped to users, avoid modifying its historical behavior casually. Why?

Some users may already have executed it.

Others may not.

Suppose release 2.0 ships:

    MigrationV3ToV4 = implementation A

Half the users upgrade.

Then release 2.1 changes it to:

    MigrationV3ToV4 = implementation B

Now two devices can both report:

    Schema v4

while having reached that version through different transformations.

That creates schema drift.

A strong rule is:

Once a migration ships, treat it as immutable. Fix new problems with a new migration.

For example:

    Bad:
    
    Modify MigrationV3ToV4
    
    Better:
    
    Add MigrationV4ToV5

28. 🔙 Should You Support Down Migrations?

Backend migration frameworks often discuss:

    Up
    Down

For mobile applications, rollback is more complicated. Imagine:

    App v3 upgrades DB to schema v7

Then somehow the user installs:

    App v2 expecting schema v5

Automatically downgrading:

    v7 → v5

may cause data loss.

In many mobile applications, supporting forward migrations only is safer.

The application can explicitly reject databases newer than it understands:

    if (currentVersion > DatabaseSchema.CurrentVersion)
    {
        throw new UnsupportedDatabaseVersionException(
            currentVersion,
            DatabaseSchema.CurrentVersion);
    }

Don't silently attempt destructive downgrade behavior unless the product genuinely requires it.

29. 🔎 Schema Validation

Version numbers are useful, but they are metadata. A corrupted or manually modified database could claim:

    user_version = 7

while missing required structures.

For high-reliability applications, lightweight validation after migration can verify critical assumptions.

For example:

    Required tables exist
    Required indexes exist
    Critical columns exist
    Schema version matches

Conceptually:

    Run migrations
          │
          ▼
    Expected version?
          │
          ▼
    Validate critical schema
          │
          ▼
    Database Ready

Avoid performing an excessively expensive full schema audit on every startup unless the risk justifies it.

30. 🗄️ Backups Before Risky Migrations

For critical offline data, some migrations may justify a database backup before execution.

    Existing DB
        │
        ▼
    Create backup
        │
        ▼
    Run migration
        │
     ┌──┴────┐
     ▼       ▼
    OK      Failure
     │        │
     ▼        ▼
    Keep    Recovery
    new DB   option

This isn't necessary for every migration.

Copying a multi-gigabyte database every time the application starts would be wasteful.

But for particularly risky transformations of irreplaceable local data, backup strategy deserves consideration.

31. 🧹 Removing Old Columns

SQLite schema changes can sometimes require rebuilding a table depending on the exact target SQLite capabilities and transformation.

The general migration strategy is:

    Old Table
        │
        ▼
    Create New Table
        │
        ▼
    Copy / transform data
        │
        ▼
    Validate
        │
        ▼
    Drop Old Table
        │
        ▼
    Rename New Table

Conceptually:

    CREATE TABLE Customer_New (...);
    
    INSERT INTO Customer_New (...)
    SELECT ...
    FROM Customer;
    
    DROP TABLE Customer;
    
    ALTER TABLE Customer_New
    RENAME TO Customer;

This is precisely the kind of transformation that should be migration-controlled rather than scattered throughout startup code.

32. 🧭 Separate Initialization from Migration

These concepts are related but different.

Initialization

Answers:

Does the database exist and is it ready for use?

Migration

Answers:

How do I transform an existing supported schema into the current schema?

A clean architecture might be:

    DatabaseInitializer
          │
          ├── Ensure database exists
          │
          ├── Detect version
          │
          ├── MigrationRunner
          │
          ├── Validate schema
          │
          └── Mark database ready

This keeps migration logic out of:

    ViewModels
    Pages
    Repositories
    MauiProgram
    Random service constructors

33. 🚫 Don't Migrate from Repositories

Avoid code such as:

    public CustomerRepository(...)
    {
        EnsurePhoneNumberColumnExists();
    }

Now database structure depends on which repository happens to be instantiated first. That can create:

    Hidden migrations
    Race conditions
    Duplicated checks
    Unpredictable startup
    Difficult testing

Schema evolution should have one authoritative pipeline.

34. 🚫 Don't Use Exception Handling as Schema Detection

Avoid:

    try
    {
        await QueryNewColumnAsync();
    }
    catch
    {
        await AddColumnAsync();
    }

Exceptions aren't a migration strategy.

Use explicit versioning:

    Schema v3
          │
          ▼
    Known migration
          │
          ▼
    Schema v4

This makes evolution deterministic and auditable.

35. 🚫 Don't Reset Production Databases Automatically

One of the most dangerous migration strategies is:

    try
    {
        await MigrateAsync();
    }
    catch
    {
        File.Delete(databasePath);
        await CreateDatabaseAsync();
    }

For disposable cache data, that may be an intentional recovery policy.

For user data, it can be disastrous.

Imagine deleting:

    42 offline inspections
    17 pending orders
    8 unsynchronized photos
    13 Outbox operations

because one ALTER TABLE failed.

Recovery policy must understand the value of the data.

36. 📊 Migration Diagnostics Model

For larger applications, expose migration results explicitly.

    public sealed record DatabaseMigrationResult(
        int InitialVersion,
        int FinalVersion,
        int AppliedMigrations,
        TimeSpan Duration,
        bool Success);

Then startup diagnostics can record:

    Initial schema:       3
    Target schema:        7
    Final schema:         7
    Migrations applied:   4
    Duration:             418 ms
    Status:                Success

This can be extremely useful when diagnosing device-specific upgrade problems.

37. 🩺 Integration with Application Health

After initialization, database state can contribute to application health. For example:

    Healthy
    Database initialized
    Schema current
    
    Degraded
    Migration succeeded but maintenance required
    
    Unhealthy
    Migration failed
    Schema unsupported
    
    Unknown
    Initialization not completed

This allows the rest of the application to avoid pretending everything is healthy while the persistence layer is unusable.

38. 🏗️ Production Architecture

A clean migration architecture could look like:

    ┌──────────────────────────────────────────────┐
    │                 .NET MAUI App                │
    └───────────────────────┬──────────────────────┘
                            │
                            ▼
    ┌──────────────────────────────────────────────┐
    │             App Initialization               │
    └───────────────────────┬──────────────────────┘
                            │
                            ▼
    ┌──────────────────────────────────────────────┐
    │           Database Initializer               │
    │                                              │
    │  • Open database                             │
    │  • Read schema version                       │
    │  • Run migrations                            │
    │  • Validate schema                           │
    └───────────────────────┬──────────────────────┘
                            │
                            ▼
    ┌──────────────────────────────────────────────┐
    │          Database Migration Runner           │
    └───────────────────────┬──────────────────────┘
                            │
              ┌─────────────┼─────────────┐
              ▼             ▼             ▼
           v1 → v2       v2 → v3       v3 → v4
              │             │             │
              └─────────────┼─────────────┘
                            │
                            ▼
                      SQLite Database
                            │
                            ▼
                    Current Schema
                            │
                            ▼
                     Repositories
                            │
                            ▼
                   Application Ready

The important boundary is:

    Repositories
         │
         X
    Must not execute before database initialization completes

39. 📋 Migration Strategy Comparison

Strategy	Production Safety	Complexity	Recommended
Delete and recreate	Low	Low	❌ for persistent user data
`CreateTableAsync` only	Limited	Low	⚠️ Initial creation
Runtime column checks everywhere	Low	Medium	❌
Explicit version migrations	High	Medium	✅
Versioned + transactional migrations	Very High	Medium	✅
Versioned + tested + validated migrations	Excellent	Higher	⭐ Production

For most serious .NET MAUI applications with persistent local data, explicit incremental migrations provide the best balance.

40. 🏆 Best Practices

When implementing SQLite migrations in .NET MAUI:

🔢 Give the database schema its own explicit version.
🗃️ Consider PRAGMA user_version for lightweight SQLite schema tracking.
🧩 Represent migrations as explicit version transitions.
⬆️ Keep migrations incremental.
🔐 Use transactions where the migration operations allow them.
💾 Never assume deleting the database is safe.
📱 Test users upgrading from old schema versions.
🧪 Seed realistic legacy data during migration tests.
🛑 Validate that the migration chain has no gaps.
🔁 Ensure completed migrations aren't executed again.
🧬 Treat released migrations as immutable.
📝 Log migration start, completion, duration, and failure.
⚡ Measure expensive migrations.
🧱 Keep schema changes out of repositories and ViewModels.
🚀 Complete migration before database-dependent features start.
🔙 Avoid automatic downgrade migrations unless explicitly required.
🔎 Validate critical schema assumptions after migration when appropriate.
🗄️ Consider backups for risky transformations involving irreplaceable data.
🧹 Define recovery according to data criticality.
📊 Include migration state in diagnostics for production applications.

🎯 Conclusion

Creating a SQLite database in .NET MAUI is easy. Maintaining that database across years of application updates is a different engineering problem. The moment an application stores data that must survive an update, schema evolution becomes part of the application's compatibility contract. A production application needs to handle situations such as:

    User A → Schema v8
    User B → Schema v7
    User C → Schema v4
    User D → Fresh install

while all of them install the same new application version.

A migration pipeline turns that uncertainty into deterministic transitions:

    Existing Database
           │
           ▼
    Read Schema Version
           │
           ▼
    Determine Required Migrations
           │
           ▼
    vN → vN+1 → vN+2 → Latest
           │
           ▼
    Validate
           │
           ▼
    Application Ready

The most important principle is simple:

A production database should evolve intentionally, not accidentally.

By combining explicit schema versions, incremental migrations, transactions, testing, validation, structured logging, and controlled recovery, a .NET MAUI application can evolve its local SQLite schema without treating every update as a fresh installation. 🗃️🚀

And as the application grows, this architecture provides something even more valuable than convenience: confidence that users can upgrade without sacrificing the data they already trust your application to preserve.

🔗 References

Microsoft Learn — Local databases with SQLite in .NET MAUIhttps://learn.microsoft.com/dotnet/maui/data-cloud/database-sqlite
Microsoft Learn — .NET MAUI App Lifecyclehttps://learn.microsoft.com/dotnet/maui/fundamentals/app-lifecycle
Microsoft Learn — Dependency Injection in .NET MAUIhttps://learn.microsoft.com/dotnet/maui/fundamentals/dependency-injection
Microsoft Learn — Logging in .NEThttps://learn.microsoft.com/dotnet/core/extensions/logging
SQLite — PRAGMA user_versionhttps://www.sqlite.org/pragma.html#pragma_user_version
SQLite — ALTER TABLEhttps://www.sqlite.org/lang_altertable.html
SQLite — Transactionshttps://www.sqlite.org/lang_transaction.html
SQLite — CREATE INDEXhttps://www.sqlite.org/lang_createindex.html
SQLite — Database File Formathttps://www.sqlite.org/fileformat.html

Was this useful?

Comments (0)

No approved comments yet.