Versioning Your Business App's Database Schema — Migration Practices to Prevent 'Every Customer Has a Different DB'

“The database we installed at Company A has this column, but the one at Company B doesn’t. And at this point nobody even remembers which version added it.” — Take over maintenance of a business app that gets installed separately at each customer site, and you’ll run into this situation more often than not.

The update runbook says, “Run this SQL against the database.” But whether it was actually run is known only to whoever did the work on site, and over time you end up with a mix of customers: some where the step got forgotten, some where it errored out partway through and was left as-is, and some that skipped a version during an update and ended up missing an intermediate ALTER TABLE. Years later, engineering effort gets endlessly swallowed up investigating “an error that only happens at this one customer.”

On this blog, we’ve already covered a minimal version-numbered schema in Choosing Where a Windows App Stores Its Data, and covered SQLite’s operational design in Using SQLite in Business Apps with C#. This article picks up where those left off, and digs into how to version schema changes and safely apply them to the many databases scattered across your customers’ sites. SQLite is the main subject, but the design generalizes equally to SQL Server (Express).

1. The Bottom Line First

• Schema changes should ship as code — numbered migrations bundled into the app itself — rather than as an SQL runbook, and get applied automatically at startup. Any process that depends on a human running a runbook breaks down the moment the database is scattered across customer sites.
• The database itself should record its current schema version. In SQLite, PRAGMA user_version is a slot reserved exactly for this purpose.¹ In SQL Server, keep an application history in a dedicated table.
• Migrations only move forward, and only get appended to. Never rewrite the SQL under a number that’s already shipped; fix it under a new number instead. That way, even a skip-update from v1.2 straight to v1.5 becomes nothing more than “run whatever hasn’t been applied yet, in order.”
• Handle breaking changes — dropping or renaming a column — as a two-stage expand-contract release. Ship an addition-only release first, and only ship the release that removes the old form once nothing references it anymore.
• Guard against the accident of an old app version opening a newer database with a minimum-version check. The principle is: never let the app write into a future schema it doesn’t understand.
• Take an automatic backup before applying anything. In SQLite, a single VACUUM INTO statement produces a consistent copy,² which turns recovery from a failure into a simple file swap.
• One migration equals one transaction, and the version-number update belongs inside that same transaction. SQLite can roll back DDL within a transaction too.³ SQL Server has DDL statements that are the exception to this, so isolate those operations into migrations of their own.⁴

2. Why “Every Customer Has a Different Database” Happens

Break the causes down, and every one of them traces back to an operational process that assumes a human will handle it.

• A manual ALTER gets missed. Nothing in the database itself records whether the runbook’s SQL was actually run, and once your only way to check is “eyeball the table definition,” omissions are guaranteed to happen.
• A mid-run failure gets left alone. If the third of five SQL statements in the runbook throws an error, whoever is running it can’t judge whether to push forward or roll back, and it ends up as “the app still runs, so leave it.” That database now has a one-of-a-kind schema that doesn’t match any version at all.
• Skip-version updates. A customer going straight from v1.2 to v1.5 needs to correctly walk through both v1.3’s and v1.4’s schema changes together, which is difficult to get right under runbook-driven operations.
• An emergency on-site patch. “We added this column early, just for this one customer” happens, and the later, official update then fails with a double-apply error.

A single-server web system has exactly one database, and its state is always known. What makes desktop business apps fundamentally harder is that the same app’s database is scattered across dozens or hundreds of PCs at customer sites and branch offices, and not all of them are necessarily on the same version. An operational process where a human handles machines one at a time collapses in direct proportion to the machine count, so there’s only one real conclusion: give the app itself the ability to inspect its own database and bring it up to the latest schema.

3. The Basic Pattern: Schema Version + Forward Migrations

The skeleton of the mechanism has just three parts.

1. The database itself holds a schema version number (a schema-only integer, separate from the app’s product version).
2. Schema changes get appended to the app’s code as a numbered sequence of migrations.
3. At startup (immediately after connecting to the database), the app applies, in order and inside transactions, every migration numbered higher than the current version.

For SQLite, PRAGMA user_version is available as the place to store the version number. It’s an integer stored in the database header (at offset 60), and the official documentation states plainly that “the application is free to use it, and SQLite itself never touches this value.”¹ Without creating any dedicated table, a single database file can declare its own version on its own.

A hand-rolled C# implementation becomes practical in the next few dozen lines.

using Microsoft.Data.Sqlite;

public static class SchemaMigrator
{
    // Append-only list. Never rewrite the SQL under a number that has already shipped
    private static readonly (int Version, string Sql)[] Migrations =
    {
        (1, "CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT NOT NULL)"),
        (2, "ALTER TABLE customer ADD COLUMN phone TEXT"),
        (3, """
            CREATE TABLE invoice (
                id          INTEGER PRIMARY KEY,
                customer_id INTEGER NOT NULL REFERENCES customer(id),
                issued_at   TEXT    NOT NULL,  -- store as UTC, in a fixed format
                amount      INTEGER NOT NULL   -- amounts are integers in the smallest currency unit
            )
            """),
    };

    public static void Migrate(SqliteConnection conn)
    {
        // An append mistake (a duplicate or out-of-order number) turns into a silent
        // double-apply or a silent omission, so detect it and stop before applying anything
        for (int i = 1; i < Migrations.Length; i++)
            if (Migrations[i].Version <= Migrations[i - 1].Version)
                throw new InvalidOperationException(
                    "Migration version numbers must be strictly ascending and unique.");

        int current = GetUserVersion(conn);
        int latest = Migrations[^1].Version;

        if (current > latest)
            // The case where a newer app's database gets opened by an older app (see 5.2).
            // It's safest to stop here rather than touch a schema we don't understand
            throw new InvalidOperationException(
                $"This database (schema v{current}) was created by a newer version of the " +
                "app. Please update the app.");

        foreach (var (version, sql) in Migrations)
        {
            if (version <= current) continue;

            using var tx = conn.BeginTransaction();
            using var cmd = conn.CreateCommand();
            cmd.Transaction = tx;
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            // Commit the version update inside the same transaction.
            // That eliminates the state where "the change went in but the number is still old"
            cmd.CommandText = $"PRAGMA user_version = {version}";
            cmd.ExecuteNonQuery();
            tx.Commit();
        }
    }

    private static int GetUserVersion(SqliteConnection conn)
    {
        using var cmd = conn.CreateCommand();
        cmd.CommandText = "PRAGMA user_version";
        return Convert.ToInt32(cmd.ExecuteScalar());
    }
}

This solves Chapter 2’s problems structurally. Nothing gets missed (it’s checked on every startup), a mid-run failure rolls back (Chapter 6), and skipping versions is fine (if a v1.2 database is at schema v2, a v1.5 app just applies 3, 4, and 5 in order). Even “what state is this customer’s database in” can be answered with a single read of PRAGMA user_version.

There are exactly two operational rules to hold to without exception.

• Never rewrite a number that’s already shipped. Even if v3’s SQL has a bug, fix it in v4. Rewriting it creates a fresh source of divergence: some databases have “the old v3 applied,” others have “the new v3 applied.”
• Include data transformation in the migration too. Beyond adding a column, migrating existing data (an UPDATE) belongs under the same number. Unifying date/time columns on UTC and a fixed format from the start — as covered in Dates, Times, and Time Zones in Business Apps — keeps later migrations simpler.

There’s one more piece of work that’s needed only when retrofitting this mechanism onto an existing system. A database that’s been operated through manual patches can end up in a state where “user_version is still 0, but the actual schema has partially moved forward” (the emergency patch from Chapter 2 is exactly this). Put it straight onto this chain as-is, and an ALTER TABLE for a change that’s already been applied fails with a “column already exists” error. For the first release that introduces this system, inspect the real schema as a one-time baseline step (in SQLite, check for a column’s presence with PRAGMA table_info), burn in the matching version number for any database with a known manual patch already applied, and only then hand everything after that off to forward migrations. Skipping this step is only an option if you built this mechanism in from the very first release.

SQL Server has no equivalent of user_version, so INSERT one row per migration into a dedicated table (e.g. schema_version) recording the version number, when it was applied, and which app version applied it. Because the history persists as rows, it’s more resilient when you need to investigate later.

4. Use a Tool, or Roll Your Own — A Decision Table

There are three families of tooling that achieve the same thing: EF Core Migrations, a migration library (such as DbUp), and the hand-rolled implementation from the previous chapter.

Here’s the recommendation by situation.

DbUp is a “.NET library that helps deploy changes to a SQL Server database”; it records executed scripts in a journal table and runs only the scripts that haven’t been run yet. It also supports SQLite, PostgreSQL, MySQL, and more.⁶ As a migration path for “turning runbook SQL into automatic, execution-tracked application,” it’s about as short as it gets.

4.1 Caveats for Using EF Core Migrations in a Distributed App

During development, EF Core migrations get applied with dotnet ef database update, but a customer’s PC has neither the SDK nor the source. The realistic way to apply them is context.Database.Migrate() at app startup.

What you need to know here is that Microsoft’s documentation explicitly warns against applying migrations at startup as a way of managing a production database. The five reasons given are: (1) failures or corruption from multiple instances applying migrations simultaneously (before EF Core 9), (2) other apps accessing the database while migrations are being applied can cause serious problems, (3) the app needs elevated permission to change the schema, (4) there’s little in the way of a rollback mechanism, and (5) you can’t review or fix the SQL that will run beforehand — and the recommendation is instead to generate SQL scripts and apply them as part of the deployment process.⁷

But that recommendation assumes a server system with “one database and an actual deployment process.” For a desktop app with a local database on every customer PC, physically carrying scripts around to each site to apply them is exactly the problem from Chapter 2 — so Migrate() at startup becomes the de facto standard answer. Address the remaining concerns instead.

• Concurrency: Starting with EF Core 9, Migrate() automatically acquires a lock to prevent multiple processes from running migrations at the same time.⁷ On earlier versions, serialize it yourself as described in Chapter 6. Note that this lock only serializes migration runs against each other — it does not stop an old app version from reading and writing normally while a migration is in progress. For a shared database, plan on combining this with the minimum-version check (section 5.2) or a maintenance window.
• Reviewing the SQL beforehand: Always review the generated migration, and rehearse it against a database with data comparable to production before release (section 6.3).
• Don’t mix it with EnsureCreated(): it builds the schema with no migration history, and Migrate() then fails later on. Standardize on Migrate() from the very start.⁷

With the SQLite provider, a migration that changes a column’s type or drops a column runs as a full table rebuild — create a new table, copy the data, drop the old table, rename — and idempotent scripts can’t be generated either.⁵ Whether to use EF Core at all is covered in Chapter 8 of Using SQLite in Business Apps with C#.

5. Writing Migrations That Don’t Break

The governing principle for any individual migration is: never make a backward-incompatible change in a single release.

5.1 Handle Breaking Changes With Expand-Contract (a Two-Stage Release)

Adding a column is safe, but dropping, renaming, or changing the type of one breaks “whatever assumed the old shape.” Even with a local SQLite database in a one-to-one relationship with the app, you’ll usually still have at least one of: (a) the possibility of rolling the app back to an older version if something breaks, (b) some other tool that reads the database directly (a reporting tool, a CSV exporter, an Access integration), or (c) a SQL Server setup where old and new clients both connect at once. So breaking changes get split into two stages — expand, then contract.

It’s safest to ship the contract (removal) side of the release only once the minimum-version check (next section) is in place and can actually lock out old apps.

SQLite has its own specific limits here: ALTER TABLE only supports renaming the table, renaming a column, adding a column, and dropping a column, and even dropping a column comes with a long list of restrictions — you can’t drop a column that’s part of a PRIMARY KEY or UNIQUE constraint, or one referenced by an index, a CHECK constraint, a foreign key, or a view. Any other change goes through the procedure the official documentation lays out: create a new table inside a transaction, move the data with INSERT INTO new_X SELECT ... FROM X, then drop the old table and rename.³ On a large table this becomes a full copy, so plan for both the apply time and the free disk space it needs.

5.2 Guarding Against Downgrades — a Minimum-Version Check

In a forward-only migration design, you don’t write down-migration scripts (they never get exercised at a customer site, and untested code is just a liability). What you need instead is a mechanism that stops if an old app version ever opens a newer database. That’s exactly what the top of Chapter 3’s code does: if user_version is greater than the highest number the app knows about, it throws and aborts startup.

If you commit up front to “no breaking changes in any release that might need to roll back — expand only,” then it becomes safe for an old app to read a newer database, and you can loosen the check to something like “warn and start up read-only” instead. Which one you pick depends on how tolerant the business is of downtime.

5.3 Automatic Backups Before Applying

A migration is surgery performed on “production data sitting on someone else’s PC.” Automate the practice of taking a backup before running it. In SQLite, VACUUM INTO is the ideal tool — a single statement produces a consistent snapshot in a separate file, even from a live database.²

// Only when a migration is actually needed, take one generation's backup right before it
if (GetUserVersion(conn) < latest)
{
    Directory.CreateDirectory(backupDir);
    var backupPath = Path.Combine(backupDir,
        $"app_schema_v{GetUserVersion(conn)}_{DateTime.Now:yyyyMMdd_HHmmss}.db");
    // Create it under a temporary name and rename only after success, so an incomplete file
    // left by a power outage or a killed process mid-write never looks like a "finished backup"
    var tempPath = backupPath + ".tmp";
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "VACUUM INTO $path";
    cmd.Parameters.AddWithValue("$path", tempPath);
    cmd.ExecuteNonQuery();  // Run VACUUM outside of a transaction
    File.Move(tempPath, backupPath);
    // If a *.tmp file is still around at startup, it's a trace of a previous failure — delete it
}

Putting the schema version in the file name makes it obvious at a glance “how far back” you’re restoring to during recovery. For more on backups — including why a plain file copy of a live database is a breeding ground for corruption — see Chapter 7 of Using SQLite in Business Apps with C#. For SQL Server, the equivalent is running BACKUP DATABASE before applying; the underlying idea is the same.

6. Operational Pitfalls

6.1 Mid-Run Failures and Transactions — Know the Differences Between Database Engines

Chapter 3’s code wraps one migration in one transaction and includes the user_version update in that same transaction. This works because SQLite can run DDL (CREATE TABLE, ALTER TABLE, and so on) inside a transaction and roll it back on failure. The official table-rebuild procedure itself is structured as “begin a transaction, do the CREATE/INSERT/DROP/RENAME, and commit.”³ Even if the power drops partway through, the database at next startup is left in the consistent state of “right before that migration.”

SQL Server can also run a great deal of DDL inside a transaction, but there are exceptions. ALTER DATABASE, for instance, can’t be used inside an explicit transaction, and CREATE FULLTEXT INDEX can’t be placed inside a user transaction either.⁴ EF Core also automatically wraps each migration in a transaction where it can, while stating plainly that “some operations cannot be executed within a transaction on some databases.”⁸ In practice, the rule comes down to just this: never mix an operation that can’t join a transaction into the same migration as an ordinary schema change. When you switch database engines, always check whether DDL participates in transactions.

A classic accident is “the version update ends up in a separate transaction.” If the change itself succeeds but the process dies before the version updates, the same migration re-runs on next startup and startup fails forever with a “table already exists” error. Keep the version update inside the same transaction, and this can’t happen, as a matter of principle.

6.2 Multiple Processes Starting at Once — Serializing the Apply Step

A business app is software that “everyone starts up at once in the morning.” Multiple clients pointed at a shared database (SQL Server), or multiple instances launched on the same PC, can end up running migrations at the same time.

• Starting with EF Core 9, Migrate() automatically acquires a database-wide lock to prevent concurrent applies (earlier versions have no such protection). Note that the SQLite provider’s lock is implemented with a dedicated lock table, and the official docs note that the table can be left behind if the process applying a migration crashes.⁷ If startup ever gets stuck waiting on that lock, confirm no other process is actually running a migration, then recover by dropping the leftover lock table (__EFMigrationsLock).
• With a hand-rolled implementation, for a local database, serializing with a named Mutex is the easy route.

// Prefix with Global\ so serialization holds across the whole PC even when launched
// from multiple logon sessions via RDP or user switching (Local\ is scoped to one session only)
using var mutex = new Mutex(false, @"Global\MyApp.SchemaMigration");
try
{
    mutex.WaitOne();
}
catch (AbandonedMutexException)
{
    // The previous owning process crashed without ever calling Release.
    // Even though an exception is thrown, ownership itself has still been acquired, so it's
    // fine to continue. The possibility that the previous apply ended halfway is covered by
    // the version re-check right after this, plus the per-migration transactions
}
try
{
    SchemaMigrator.Migrate(conn);
}
finally
{
    mutex.ReleaseMutex();
}

Whichever process was waiting checks the version again once it acquires the lock (Chapter 3’s code checks version <= current fresh, every time, before applying), so this never turns into a double apply. One caveat: a named Global\ object by default carries an ACL derived from the user who created it, so opening the same Mutex from a different Windows account’s session can throw UnauthorizedAccessException. If use across multiple accounts is expected, either create it with System.Threading.AccessControl’s MutexAcl granting synchronize/modify access to the relevant users, or lean on the database-side locking described next instead. Because a Mutex can’t reach across machines, for a shared database, lean on serialization at the database level instead — “finish applying server-side before rolling out the update,” or “take a database-side lock when the apply starts (BEGIN IMMEDIATE for SQLite, an application lock for SQL Server).”

6.3 Rehearsal — Test a One-Shot Apply Starting From “the Oldest Database”

Migration bugs almost never surface on a developer’s machine, because a dev machine’s database is always on the latest schema and its data is clean. What actually breaks is a customer’s database that’s old, large, and full of data nobody planned for. There are three things you should do, at minimum, before a release.

• Keep a database file at each schema version as a test fixture, and automate a test that applies straight from each one up to the latest. Skip patterns like “from v1 to v5” or “from v3 to v5” are exactly the reality at customer sites. With SQLite, since it’s just a matter of putting the database file in the repository, this kind of test is on the easier side to write.
• Test with data of comparable volume and character to production. Columns full of NULLs, unexpected duplicates, and rebuild time on a huge table (section 5.1) never surface unless the data is close to the real thing. Where possible, rehearse against an anonymized customer database.
• Test the failure path. Kill the process partway through an apply, and confirm that the next startup recovers correctly — that it re-applies starting from the version it rolled back to.

7. Summary

• “Every customer has a different database” isn’t a failure of anyone’s diligence — it’s the structural consequence of an operational process where a human runs an SQL runbook. For a desktop business app whose databases are scattered across sites, the only real option is having the app bring its own database up to date itself.
• The skeleton is a schema version number owned by the database itself (PRAGMA user_version for SQLite¹) plus applying numbered, forward-only migrations at startup. In C#, a few dozen lines of hand-rolled code is enough to make it work.
• The three families of tooling are EF Core Migrations, a library like DbUp, and a hand-rolled implementation. Choose based on whether you’re already using EF Core, and how much raw SQL you already have as an asset (the decision table in Chapter 4). EF Core’s startup-time Migrate() comes with officially documented caveats,⁷ so use it alongside concurrency handling and rehearsal.
• Handle breaking changes as an expand-contract two-stage release, and stop the accident of an old app opening a new database with a minimum-version check. Follow the official documentation for SQLite’s ALTER TABLE limits and rebuild procedure.³
• The principle is one migration equals one transaction, with the version update inside that same transaction. SQL Server has DDL that can’t join a transaction,⁴ so isolate those operations. Only once you’ve added a VACUUM INTO backup before applying² and rehearsed a one-shot apply starting from the oldest version does a migration actually become something you can ship to a customer.

If runbook-driven ALTER statements sound familiar, try introducing just “recording a version number” and “applying at startup” in your next release. Once that foundation is in place, you can add the two-stage releases and backups bit by bit afterward.

Related Articles

• Using SQLite in Business Apps with C# — WAL Mode, Locking, Corruption Prevention, and When to Reach for EF Core
• Choosing Where a Windows App Stores Its Data — A SQLite / JSON / Registry / Access Decision Table
• It’s Not Just appsettings.json — Configuration Management Practices for Windows Business Apps
• Dates, Times, and Time Zones in Business Apps — From DateTime Pitfalls to the UTC-Storage Principle and Test Design

Related Consulting Areas

Komura Software LLC handles database design and migration-framework rollout for business apps installed at each customer site, investigating and normalizing schemas that have drifted apart under runbook-driven operations, and designing update distribution for both EF Core and raw-SQL setups.

• Windows App Development
• Maintenance & Modernization of Existing Windows Software
• Technical Consulting & Design Review
• Contact

References

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.

Frequently Asked Questions

Common questions about the topic of this article.

How should schema changes to a business app's database be managed?

Instead of an operational process where a human runs an SQL runbook, you should bundle numbered migrations (schema-change code) directly into the app and apply them automatically at startup. Have the database itself record its current schema version (PRAGMA user_version for SQLite, a dedicated table for SQL Server), and have the app apply, in order and inside transactions, only the version numbers that haven't been applied yet. With this shape, even a skip-update from v1.2 to v1.5 applies every schema change along the way, and the state of 'every customer has a different database shape' stops happening as a matter of structure.

Is it okay to call EF Core's Migrate() when the app starts up?

It's a reasonable choice, with conditions. Microsoft's documentation warns against applying migrations at startup in production — citing reasons such as simultaneous application from multiple instances, giving the app schema-change permissions, and being unable to review the SQL beforehand — and recommends generating SQL scripts for server apps instead. For a desktop business app with a local database on each client PC, on the other hand, running scripts on-site simply isn't a workable process, so Migrate() at startup becomes the de facto standard answer. Even then, always combine it with protection against simultaneous startups (EF Core 9's automatic lock, or a hand-rolled Mutex) and a backup taken before applying.

What happens to the database if a migration fails partway through?

If you wrap a single migration in a single transaction and include the version-number update in that same transaction, a failure rolls back to the state right before that migration started, and no half-finished schema is left behind. SQLite can run DDL such as CREATE TABLE and ALTER TABLE inside a transaction too, and the official table-rebuild procedure is itself written on the assumption of a transaction. SQL Server can likewise run a lot of DDL inside a transaction, but there are exceptions — ALTER DATABASE and full-text-index-related statements among them — so isolate those exceptional operations into migrations of their own. And with an automatic backup taken before applying, even a worst case can be recovered from with a simple file swap.

If schemas have already drifted apart across customer databases, how do you bring them back in line?

First, settle on a single 'correct, intended schema,' and survey each customer's database to identify how far it has drifted from that. Then, for databases with no version number, write a baseline migration that detects each pattern actually present out in the field and normalizes it, and stamp in the version number once that migration completes. In SQLite, you can mechanically detect whether a column exists using sqlite_master or PRAGMA table_info, and absorb the differences with defensive SQL along the lines of 'add the column if it isn't already there.' Once every subsequent change rides on numbered migrations, the drift won't recur.

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.