為業務應用程式的 DB 結構做版本管理 ── 防止「各客戶端 DB 不一致」的遷移實踐

「裝在 A 公司的 DB 有這個欄位，B 公司的 DB 卻沒有。連是哪個版本加上去的都已經搞不清楚了」── 接手安裝在各客戶端的業務應用程式維護工作時，相當高機率會遇到這種狀況。

更新操作手冊上寫著「請把這段 SQL 執行到 DB 上」。但實際上是否執行過，只有現場的作業人員知道，忘記執行的客戶端、執行到一半出錯就放著不管的客戶端、因為跳版更新而漏掉中間 ALTER TABLE 的客戶端，全部混雜在一起。數年之後，「只在特定客戶端發生的錯誤」的調查，就會沒完沒了地吃掉工時。

本部落格已經在「Windows 應用程式的資料儲存位置選擇」中，寫過為結構加上版本編號的最小可行程式碼，並在「在 C# 中將 SQLite 用於業務應用程式」中，解說了 SQLite 的運用設計。本文作為後續篇章，將深入探討該如何為 DB 結構的變更做版本管理，以及如何安全地套用到分散在各客戶端的大量 DB 上。內容以 SQLite 為主要題材，同時整理成也適用於 SQL Server（Express）的通用設計。

1. 先講結論

• 結構變更不用 SQL 操作手冊，而是以程式碼（編號遷移）的形式與應用程式本體一起發佈，並在啟動時自動套用。由人執行操作手冊的運作方式，一旦 DB 分散到各客戶端，就會走向破綻。
• 讓 DB 自己記錄目前的結構版本。SQLite 的話，PRAGMA user_version 正是為此用途而設的欄位。¹ SQL Server 則用專用資料表留下套用歷程。
• 遷移只往前進、只能追加。已經出貨的編號 SQL 不改寫，修正也用新的編號來做。這樣一來，v1.2 → v1.5 的跳版更新，也只是「依序套用尚未套用的部分」而已。
• 破壞性變更（刪除欄位・重新命名）用 expand-contract 兩階段發佈來進行。expand-contract 是指先保留既有結構、新增新結構（expand），等應用程式端的遷移完成後，再刪除舊結構（contract）的兩階段發佈手法。先發佈只做新增的版本，等到對舊格式的參照消失後，再發佈刪除的版本（5.1 節）。
• 舊版應用程式打開新 DB 的意外，用最低版本檢查來防範。這時候，「目前的結構版本」與「拒絕存取的下限」要當成兩個不同的值來保存。如果用同一個值兼任，套用 expand 的瞬間舊版應用程式就會被擋在門外，上面說的共存期間就無法成立（5.2 節）。
• 套用前自動備份。SQLite 的話，用一句 VACUUM INTO 就能取得一致的複本²，失敗時的復原就變成替換檔案而已。
• 1 個遷移＝1 個交易，版本編號更新也放進同一個交易。SQLite 的 DDL 也能在交易中回滾。³ SQL Server 有例外的 DDL，因此要把例外操作獨立成單一遷移。⁴

2. 為什麼會出現「各客戶端 DB 不一致」的問題

把原因拆解開來，最終都會歸結到「以人工作業為前提的運作方式」。

• 手動 ALTER 漏套用。操作手冊的 SQL 是否已經執行，DB 上任何地方都不會留下記錄，確認手段只剩「用肉眼看資料表定義」，光是這一點，漏套用就必然會發生。
• 中途失敗放著不管。操作手冊裡 5 段 SQL 中的第 3 段出錯時，作業人員既無法判斷該繼續還是回滾，於是就變成「應用程式還能動，那就這樣吧」。那個 DB 從此就成了不符合任何版本的、全世界獨一無二的結構。
• 跳版更新。在 v1.2 之後直接安裝 v1.5 的客戶端，需要正確地一併走完 v1.3 與 v1.4 的結構變更，但在操作手冊的運作方式下很難做到。
• 緊急處理造成的現場修補。會發生「只有這個客戶端先加了欄位」的情況，等到日後正式更新時，就會出現重複套用的錯誤。

如果是單一伺服器的 Web 系統，DB 只有一份，狀態隨時都能掌握。桌面業務應用程式在本質上的困難之處在於，同一個應用程式的 DB 會分散在客戶端、據點的數十、數百台 PC 上，而且不見得全部都是同一個版本。由人一台一台去處理的運作方式，會隨著台數增加而按比例走向破綻，所以結論只有一個：讓應用程式自己具備檢查自身 DB、並將其提升到最新結構的能力。

3. 基本模式：結構版本＋前進遷移

這套機制的骨架只有 3 個要素。

1. DB 自己擁有結構版本編號（與應用程式的產品版本不同、專屬於結構的整數）。
2. 結構變更以編號遷移的形式，逐一追加到應用程式的程式碼裡。
3. 應用程式在啟動時（DB 連線後立刻執行），依序在交易中套用編號大於目前版本的遷移。

把啟動時的流程畫成圖，就是下面這樣。第 5 章、第 6 章要加上的防禦措施，全部都會落在這個流程中的某個環節。

flowchart TD
    S["應用程式啟動，連線至 DB"] --> R["讀取 PRAGMA user_version 與最低相容版本編號"]
    R --> Q1{"最低相容版本編號<br/>是否大於應用程式已知的最大值"}
    Q1 -->|"是"| STOP["中斷啟動，見 5.2 節"]
    Q1 -->|"否"| Q2{"目前的版本編號<br/>是否大於應用程式已知的最大值"}
    Q2 -->|"是"| FUT["相容的未來結構。<br/>不套用，直接正常啟動，見 5.2 節"]
    Q2 -->|"否"| Q3{"是否有尚未套用的遷移"}
    Q3 -->|"沒有"| OK["直接正常啟動"]
    Q3 -->|"有"| BK["套用前先取得備份<br/>VACUUM INTO，見 5.3 節"]
    BK --> LOOP["依編號由小到大逐一套用尚未套用的遷移，見 6.1 節<br/>BEGIN TRANSACTION → 結構變更・資料轉換 →<br/>PRAGMA user_version = 該編號 → COMMIT"]
    LOOP -.->|"途中失敗時"| FAIL["只有那一件會回滾，<br/>停在前一個編號"]
    LOOP --> DONE["到達最新版本後正常啟動"]

圖 1：啟動時的套用流程。第 5 章、第 6 章要加上的防禦措施，全部都會落在這個流程中的某個環節

在 SQLite 的情況下，可以用 PRAGMA user_version 來存放版本編號。它是儲存在資料庫標頭（偏移量 60）的整數，官方文件明確寫著「應用程式可以自由使用，SQLite 本身不會用到這個值」。¹ 不需要另外建立專用資料表，單靠 DB 檔案本身就能宣告自己的版本。

用 C# 自行實作的話，下面這幾十行程式碼就能實際上路。

using Microsoft.Data.Sqlite;

public static class SchemaMigrator
{
    // 追記專用的清單。已經出貨的編號 SQL 絕對不能改寫
    private static readonly (int Version, string Sql)[] Migrations =
    {
        (1, """
            CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
            -- 放置拒絕存取下限的表。要在 1 號中建立並放入初始值。
            -- 如果把這裡切到別的地方，就會變成沒有人執行、GetMinCompatibleVersion
            -- 永遠回傳 0，拒絕存取完全失效，而且第一次 contract 時
            -- UPDATE schema_meta 會因為「表不存在」而失敗（5.2 節）
            CREATE TABLE schema_meta (key TEXT PRIMARY KEY, value INTEGER NOT NULL);
            INSERT INTO schema_meta (key, value) VALUES ('min_compatible_version', 0);
            """),
        (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,  -- 以 UTC、固定格式儲存
                amount      INTEGER NOT NULL   -- 金額為最小貨幣單位的整數
            )
            """),
    };

    public static void Migrate(SqliteConnection conn)
    {
        // 追加時的失誤（編號重複・順序顛倒）會變成悄悄的重複套用・漏套用，
        // 所以要在套用任何東西之前先偵測並中止
        for (int i = 1; i < Migrations.Length; i++)
            if (Migrations[i].Version <= Migrations[i - 1].Version)
                throw new InvalidOperationException(
                    "遷移編號必須遞增且唯一。");

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

        // 拒絕存取不是用「結構版本編號」判斷，而是用「最低相容版本編號」。
        // 如果這裡用 current > latest 判斷，新版應用程式套用 expand 的
        // 瞬間 user_version 就會上升，舊版應用程式當場就無法開啟 DB ──
        // 5.1 節中決定的「共存期間內新舊兩邊都會寫入」設計就完全無法成立。
        // 最低相容版本編號只在 contract 時才調高
        if (minCompatible > latest)
            throw new InvalidOperationException(
                $"此資料庫需要能理解結構 v{minCompatible} 以上版本的應用程式" +
                $"（本應用程式僅認得到 v{latest}）。" +
                "請更新應用程式。");

        if (current > latest)
            // 這是自己不認識的未來結構，但被宣告為相容。
            // 沒有需要套用的東西（全部都在 current 以下），
            // 所以不碰不認識的欄位，直接進入正常啟動（詳見 5.2 節）
            return;

        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();

            // 版本更新也在同一個交易中確定。
            // 這樣就不會出現「變更已套用但編號還是舊的」這種狀態
            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());
    }

    // 「可以開啟這個 DB 的應用程式下限」。重點是要與 user_version 分開保存，
    // 如果用同一個值兼任，套用 expand 的瞬間就會把舊版應用程式擋在門外。
    // 只在 contract 的遷移時才調高（5.1・5.2 節）
    private static int GetMinCompatibleVersion(SqliteConnection conn)
    {
        using var exists = conn.CreateCommand();
        exists.CommandText =
            "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'schema_meta'";
        if (exists.ExecuteScalar() is null) return 0;   // 表不存在的舊 DB。沒有下限

        using var cmd = conn.CreateCommand();
        cmd.CommandText =
            "SELECT value FROM schema_meta WHERE key = 'min_compatible_version'";
        var value = cmd.ExecuteScalar();
        return value is null or DBNull ? 0 : Convert.ToInt32(value);
    }
}

如上所示，schema_meta 要在 1 號遷移中建立。如果切到別的腳本，就會變成沒有人執行它，導致 GetMinCompatibleVersion 永遠回傳 0，拒絕存取完全失效。而且第一次 contract 時，UPDATE schema_meta 還會因為「表不存在」而失敗。

要把這套機制後補到既有 DB 上時，已經套用過 1 號的 DB 是沒有 schema_meta 的。請在導入用的那次遷移中，用 CREATE TABLE IF NOT EXISTS 與 INSERT OR IGNORE 重新建立它（只需要多加一個編號）。GetMinCompatibleVersion 之所以會先確認表是否存在，就是為了這段過渡期。

只有在 contract 的遷移中，才會把這個值往上調。

-- 在刪除舊欄位的那次遷移中，於同一個交易內調高
UPDATE schema_meta SET value = 7 WHERE key = 'min_compatible_version';
ALTER TABLE customer DROP COLUMN old_name;

這樣一來，第 2 章的問題就能從結構上解決。不會發生漏套用（每次啟動都會檢查）、中途失敗會回滾（第 6 章）、跳版也沒問題（如果 v1.2 的 DB 結構是 v2，v1.5 的應用程式只要依序套用 3、4、5 就好）。「這個客戶端的 DB 現在是什麼狀態」，也只要讀一次 PRAGMA user_version 就能回答。

只有 2 條運作規則需要嚴格遵守。

• 已經出貨的編號不改寫。就算 v3 的 SQL 有 bug，也要用 v4 來修正。改寫的話，會製造出「套用了舊版 v3」「套用了新版 v3」這種新的分歧。
• 資料轉換也要放進遷移裡。不只是新增欄位，既有資料的搬遷（UPDATE）也要在同一個編號裡完成。日期時間欄位的格式・時區，如果一開始就依照「業務應用程式的日期時間與時區」統一成 UTC・固定格式，後面的遷移會單純很多。

還有一件事，是只有把這套機制後補到既有系統時才需要做的工作。一直靠手動修補運作的 DB，可能會出現「user_version 停在 0，實際結構卻只部分推進」的狀態（第 2 章的緊急修補正是這種情況）。如果直接把它接上這條鏈，對已經套用過的變更再執行 ALTER TABLE，就會因為「欄位已經存在」而失敗。導入時的第一個版本，要當成一次性的基準線處理，檢查實際結構（SQLite 的話用 PRAGMA table_info 確認欄位是否存在），為已知套過手動修補的 DB 燒錄對應的版本編號，之後才交給前進遷移接手。能省略這一步的，只有從最初的版本就導入這套機制的情況。

SQL Server 沒有相當於 user_version 的機制，因此要在專用資料表（例如 schema_version）中，逐行 INSERT「版本編號・套用時間・套用時的應用程式版本」。歷程以行的形式留下來，事後調查起來也更有利。

4. 該用工具還是自行實作 ── 判斷表

要達成同一件事，有 EF Core Migrations、遷移函式庫（DbUp 等）、上一章的自行實作，這 3 種路線。單看名稱不容易看出這些工具在做什麼，先各用一句話說明。

• EF Core（Entity Framework Core） ── Microsoft 為 .NET 打造的 O/R 對映工具（把物件與資料表對應起來、自動產生 SQL 的函式庫）。它附帶的功能EF Core Migrations，會偵測用 C# 寫的模型（類別定義）的變更，並自動產生結構變更的程式碼。
• DbUp ── 專門用來管理 SQL 腳本套用的開源 .NET 函式庫。SQL 自己寫，它只負責記錄「哪些腳本已經套用過」，並執行尚未套用的部分。⁵

依狀況分類的建議如下。

DbUp 是「協助部署 SQL Server 資料庫變更的 .NET 函式庫」，會把已執行的腳本記錄在 Journal 資料表中，只執行尚未執行的部分。也支援 SQLite・PostgreSQL・MySQL 等。⁵ 作為「把操作手冊的 SQL，改成帶有執行紀錄的自動套用」這條遷移路徑，是最短的一條。

4.1 在發佈用應用程式中使用 EF Core Migrations 時的注意事項

開發階段套用 EF Core 遷移用的是 dotnet ef database update，但客戶端的 PC 上既沒有 SDK，也沒有原始碼。現實可行的套用方式，是在應用程式啟動時呼叫 context.Database.Migrate()。

這裡需要知道的是，Microsoft 的文件明確對「把啟動時套用當成正式環境資料庫的管理手段」提出警示。理由有 5 點：(1) 多個執行個體同時套用可能導致失敗或損毀（EF Core 9 之前）；(2) 套用過程中若有其他應用程式存取該 DB，可能引發嚴重問題；(3) 應用程式需要具備結構變更的提升權限；(4) 缺乏回滾手段；(5) 無法事先確認・修改要執行的 SQL。官方建議的做法，是產生 SQL 腳本後在部署流程中套用。⁷

不過這項建議，是以「DB 只有一份、且存在部署流程」的伺服器系統為前提。在每台客戶端 PC 都有本機 DB 的桌面應用程式中，「帶著腳本跑到現場」這種運作方式正是第 2 章的問題本身，因此啟動時呼叫 Migrate() 實際上就是標準解法。剩下的疑慮，就逐一處理。

• 同時執行：EF Core 9 以後，Migrate() 會自動取得鎖定，防止多個處理序同時執行遷移。⁷ 更早的版本則要如第 6 章那樣自行做序列化。不過這個鎖定序列化的只有遷移執行之間的關係，套用過程中舊版應用程式照常讀寫這件事並無法阻止。在共用 DB 的情況下，必須以最低版本檢查（5.2 節）搭配維護時段一起使用為前提。
• 事先確認 SQL：務必檢閱產生出來的遷移，並在發佈前用近似實際資料的 DB 做彩排（6.3 節）。
• 不與 EnsureCreated() 混用：因為會在沒有遷移歷程的情況下建構結構，導致之後的 Migrate() 失敗。從一開始就統一使用 Migrate()。⁷

在 SQLite 提供者上，包含欄位型別變更或刪除的遷移，會以「建立新資料表 → 複製資料 → 刪除舊資料表 → 重新命名」這種資料表重建的方式執行，也無法產生冪等腳本。⁶ 該不該用 EF Core 本身的判斷，如「在 C# 中將 SQLite 用於業務應用程式」第 8 章整理的那樣。

5. 不會壞掉的遷移寫法

每個遷移的原則是「不要在一次發佈裡做破壞向下相容性的變更」。

5.1 破壞性變更用 expand-contract（兩階段發佈）來處理

新增欄位是安全的，但刪除・重新命名・型別變更，會破壞掉「以舊格式為前提」的某些東西。就算是本機 SQLite、應用程式與 DB 一對一的情況，通常也還是存在下列其中一種：(a) 出問題時把應用程式退回舊版本的可能性；(b) 直接讀取 DB 的其他工具（報表工具、CSV 匯出程式、Access 串接）；(c) SQL Server 由新舊客戶端同時參照的架構。因此破壞性變更要分成 expand（展開）→ contract（收斂）兩個階段。

從時間軸來看，重點是在兩者之間插入一段「新舊兩種形態都能動的共存期間」。

flowchart TB
    A["發佈 A－expand，展開<br/>新增新結構，舊結構原封不動保留<br/>應用程式同時寫入新舊兩邊，讀取以舊結構為準"]
    B["共存期間<br/>舊版應用程式・舊工具照常運作－兩種結構都還活著<br/>在此期間內把所有客戶端換成新版本"]
    C["透過最低版本檢查－見 5.2 節<br/>進入可以拒絕舊版應用程式的狀態"]
    D["發佈 B－contract，縮減<br/>把舊結構的最新值最終複製／最終轉換到新結構<br/>將讀取切換到新結構，刪除舊結構"]
    A --> B --> C --> D

圖 2：在 expand 與 contract 之間插入共存期間。重點是在能夠拒絕舊版應用程式之前，不發佈 contract

欄位重新命名的條件分支較多，無法塞進表格的一個儲存格裡。把步驟拆解開來如下。

1. expand：新增欄位，複製舊欄位的值。在同一個遷移編號中執行 ALTER TABLE ... ADD COLUMN 與 UPDATE。
2. 共存期間：新版應用程式同時寫入新舊兩個欄位，讀取以舊欄位為準。之所以讓讀取端維持在舊欄位，是因為在新舊版應用程式同時運作的共用 DB 中，舊版應用程式只會寫入舊欄位。如果讀的是新欄位，就會漏掉舊版應用程式寫入的更新。也可以用 DB 端的觸發器，做舊欄位 → 新欄位的同步。
3. 拒絕舊版應用程式。用最低版本檢查（5.2 節），讓舊版應用程式無法開啟該 DB。在此之前都不拒絕。步驟 1 會讓 user_version 上升，但最低相容版本編號維持不變，所以舊版應用程式仍然能開啟 DB、繼續寫入舊欄位。如果這兩者用同一個值兼任，套用步驟 1 的瞬間，步驟 2 的共存期間就會消失。
4. contract：把舊欄位的最新值最終複製到新欄位後，才把讀取切換到新欄位，並刪除舊欄位。這個順序很重要，如果在拒絕舊版應用程式之前就把讀取切到新欄位，就會漏接舊版應用程式只寫進舊欄位的更新。

contract（收斂）這一側的發佈，要等到能用最低版本檢查（下一節）拒絕舊版應用程式之後再發佈，才安全。

SQLite 特有的情況是，ALTER TABLE 只支援資料表改名・欄位改名・新增欄位・刪除欄位，而且刪除欄位還有很多限制，例如「PRIMARY KEY 或 UNIQUE 限制的欄位不可以、索引・CHECK 限制・外部索引鍵・被檢視表參照的欄位不可以」等等。除此之外的變更，要照官方文件規定的「在交易內建立新資料表，用 INSERT INTO new_X SELECT ... FROM X 搬移資料，刪除舊資料表後重新命名」步驟來進行。³ 大型資料表會變成全量複製，因此要預先估算套用時間與磁碟可用空間。

5.2 防範降級 ── 最低版本檢查

在只做前進遷移的設計裡，不會寫向下（降級）的腳本（在客戶端根本用不上，而且沒經過測試的程式碼只會帶來危險）。取而代之需要的，是一套在舊版應用程式不小心開啟新 DB 時能夠擋下來的機制。

這裡的重點在於把「結構版本編號」與「拒絕存取的下限」分開。在第 3 章的程式碼中，有這兩個值：

• user_version ── 目前的結構版本編號。每套用一次遷移就會上升
• schema_meta 的 min_compatible_version ── 可以開啟這個 DB 的應用程式下限。只有在 contract 時才會調高

拒絕存取只用後者來判斷。如果用 user_version 來拒絕，5.1 節的共存期間就無法成立。因為新版應用程式套用 expand 的當下，user_version 就會上升，如果判斷邏輯是「大於自己已知的最大值就拒絕」，舊版應用程式從那一刻起就無法開啟 DB。這樣一來，「共存期間內新舊兩邊都會寫入」這個設計本身就無法運作。

只要把兩者分開，就會照下面這樣進行。

從舊版應用程式的角度來看，會在「這是自己不認識的結構編號，但被宣告為相容」的狀態下運作。這時的前提是不去碰觸自己不認識的欄位。正因如此，expand 側加入的變更只限於新增欄位，不去改變既有欄位的意義。

另外，只要事先決定「有可能切回舊版的發佈中，不放入破壞性變更（只做 expand）」，那麼用舊版應用程式讀取新 DB 這件事本身就是安全的。也可以選擇把拒絕存取放寬成「發出警告後以唯讀模式啟動」的設計。要選哪一種，就看業務能不能承受停擺來決定。

5.3 套用前的自動備份

遷移是對「別人 PC 上的正式資料」動手術。要把「先備份再執行」這件事機械化。SQLite 的話用 VACUUM INTO 最合適，即使是在執行中的 DB 上，也能只用一句 SQL，就在另一個檔案裡建立一致的快照。²

// conn      … 已開啟的 SqliteConnection（與傳給第 3 章 Migrate 的是同一個）
// latest    … Migrations 最後一個編號（與第 3 章的 latest 相同）
// backupDir … 備份的存放位置。放在與 DB 本體相同的資料夾
//             會在磁碟故障時一起遺失，建議放在別的磁碟機或共用資料夾
var backupDir = Path.Combine(
    Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData),
    "MyApp", "db-backup");

// 只有在需要套用時，才在動手前先取得一份備份
if (GetUserVersion(conn) < latest)
{
    Directory.CreateDirectory(backupDir);

    // 在這裡實際清理上次失敗留下的作業檔案。
    // 就算殘留下來，通常也不會妨礙下一次執行（因為檔名裡有時間），
    // 但只要不清掉，每失敗一次就會累積一份 DB 大小的垃圾。
    // 而且如果在同一秒內重新執行，檔名就會衝突，VACUUM INTO
    // 要求「輸出目的地不存在（或為空）」，所以會卡在這裡。
    //
    // 只清除「夠舊的」檔案。這個區塊的前提是在 6.2 節的 Mutex
    // 內側執行，但即使如此，仍然可能有別的版本或別的工具在使用同一個資料夾，
    // 如果把現在正在執行的那次的作業檔案清掉，
    // 那次執行就會在 File.Move 前一刻丟出 FileNotFoundException
    DateTime staleBefore = DateTime.UtcNow - TimeSpan.FromHours(1);
    foreach (var stale in Directory.EnumerateFiles(backupDir, "*.db.tmp"))
    {
        try
        {
            if (File.GetLastWriteTimeUtc(stale) < staleBefore) { File.Delete(stale); }
        }
        catch (IOException) { }                  // 被其他處理序占用中。留到下次再處理
        catch (UnauthorizedAccessException) { }
    }

    var backupPath = Path.Combine(backupDir,
        $"app_schema_v{GetUserVersion(conn)}_{DateTime.Now:yyyyMMdd_HHmmss}.db");
    // 為了不讓執行途中因斷電・強制終止處理序而產生的不完整檔案
    // 被誤認成「已完成的備份」，先用暫存名稱建立，成功後才重新命名
    var tempPath = backupPath + ".tmp";
    using var cmd = conn.CreateCommand();
    cmd.CommandText = "VACUUM INTO $path";
    cmd.Parameters.AddWithValue("$path", tempPath);
    cmd.ExecuteNonQuery();  // VACUUM 要在交易之外執行
    File.Move(tempPath, backupPath);
}

請務必把這個區塊放在 6.2 節準備的排他鎖內側執行。備份是遷移的一部分，而不是另一道獨立工序。如果沒有把「檢查版本 → 取得備份 → 套用」整段包起來，在每天早上大家同時開機、同時啟動應用程式這種業務應用程式的日常場景中，就會有 2 個處理序同時走到同一個判斷點。其中一個的作業檔案被另一個清掉，File.Move 前一刻就丟出 FileNotFoundException ── 明明備份是正常取得的，卻只有啟動失敗，變成一種很難說明的故障形式。上面的程式碼之所以只清除「夠舊的」檔案，是為了在忘記包排他鎖時，把損害降到最低的一道保險。是保險，不是排他鎖的替代品。

.tmp 的清理不要當成「以後再寫」，請把它寫成這段處理的一部分。VACUUM INTO 如果因為斷電或處理序被強制終止而中斷，會留下中途而廢、已經損毀的輸出檔案。² 因為是用暫存名稱建立的，不會被誤認成「已完成的備份」，但只要不清掉，每失敗一次，使用者的 PC 上就會累積一份 DB 大小的垃圾。這會悄悄吃掉備份目的地的容量，復原時也會多一項「哪一個才是真的」的判斷。

而且 VACUUM INTO 要求輸出目的地的檔案不存在（或是空的）。² 上面的命名方式因為帶有時間，通常不會衝突，但如果因遷移而當掉的應用程式被使用者立刻重新啟動（或是被監控服務重新啟動），落在同一秒內名稱就會一致，於是卡在那裡。這是「因為備份失敗而無法啟動」這種最難說明的故障形式。

在檔名中放入結構版本編號，復原時就能一眼看出「要回到哪個時間點」。關於執行中 DB 單純檔案複製會成為損毀溫床等備份的細節，請參考「在 C# 中將 SQLite 用於業務應用程式」第 7 章。SQL Server 的話則是在套用前執行 BACKUP DATABASE，思路是一樣的。

6. 運作上的陷阱

6.1 中途失敗與交易 ── 要了解 DBMS 之間的差異

第 3 章的程式碼把 1 個遷移包在 1 個交易裡，user_version 的更新也放進同一個交易。這之所以成立，是因為SQLite 的 DDL（CREATE TABLE、ALTER TABLE 等）可以在交易內執行，失敗時能夠回滾。官方的資料表重建程序本身，就是「開始交易，執行 CREATE／INSERT／DROP／RENAME，再提交」的結構。³ 就算執行到一半斷電，下次啟動時的 DB 也會停在「該遷移執行之前」的一致狀態。

SQL Server 同樣可以在交易內執行大多數 DDL，但有例外。例如 ALTER DATABASE 不能在明確交易內使用，CREATE FULLTEXT INDEX 也不能放進使用者交易內。⁴ EF Core 也是一樣，雖然在可能的情況下會自動把每個遷移包進交易，但也明確寫著「有些操作依資料庫而定，無法在交易內執行」。⁸ 實務上的規則就一句話：不要把無法納入交易的操作，混進和一般結構變更相同的遷移裡。在更換 DBMS 時，務必先確認「DDL 是否參與交易」。

常見的經典事故是「只有版本更新放在另一個交易」。變更本體成功、但在版本更新之前當掉，下次啟動時就會重新執行同一個遷移，並因為「資料表已經存在」而永久啟動失敗。只要把版本更新放進同一個交易，原理上就不會發生這種事。

6.2 多個處理序同時啟動 ── 套用的序列化

業務應用程式是一種「早上大家一起啟動」的軟體。查看共用 DB（SQL Server）的多個客戶端，或同一台 PC 上的多重啟動，都有可能同時跑起遷移。

• EF Core 9 以後，Migrate() 會自動取得整個資料庫的鎖定，防止同時套用（在那之前沒有這層保護）。另外，SQLite 提供者的鎖定是用一張鎖定用資料表實作的，官方也註明了套用中的處理序異常結束時，這張表有可能會殘留下來。⁷ 如果一直卡在等待鎖定而無法啟動，可以在確認沒有其他處理序正在執行遷移之後，把殘留的鎖定用資料表（__EFMigrationsLock）DROP 掉來復原。
• 自行實作時，如果是本機 DB，用具名 Mutex 來做序列化最簡單。

// using System.Threading;   (Mutex / AbandonedMutexException)
// conn … 已開啟的 SqliteConnection。在執行遷移之前
//        先把連線建立好，取得 Mutex 之後再呼叫 Migrate
using var conn = new SqliteConnection(connectionString);
conn.Open();

// 加上 Global\，讓即使因為 RDP 或使用者切換而有多個登入工作階段，
// 也能在整台 PC 的範圍內做序列化（Local\ 只在同一個工作階段內有效）
using var mutex = new Mutex(false, @"Global\MyApp.SchemaMigration");
try
{
    mutex.WaitOne();
}
catch (AbandonedMutexException)
{
    // 前一個持有者的處理序沒有呼叫 Release 就異常結束的情況。
    // 就算丟出例外，所有權本身其實已經取得，可以直接繼續。
    // 對於套用可能中途而廢的情況，後面的版本再次確認
    // 以及每個遷移各自的交易，會提供保障
}
try
{
    SchemaMigrator.Migrate(conn);
}
finally
{
    mutex.ReleaseMutex();
}

被迫等待的一方，在取得鎖定之後會再確認一次版本（第 3 章的程式碼每次套用前都會檢查 version <= current），所以不會重複套用。另外，Global\ 的具名物件，預設會帶有建立者所屬使用者的 ACL，因此從另一個 Windows 帳戶的工作階段開啟同一個 Mutex 時，可能會出現 UnauthorizedAccessException。如果前提是多帳戶共用，就要用 System.Threading.AccessControl 的 MutexAcl，在建立時給予使用者同步・變更的存取權，或是改用下面提到的 DB 端鎖定。在共用 DB 的情況下，因為 Mutex 無法跨機器，要改成「在發佈更新之前先在伺服器端完成套用」「在開始套用時取得 DB 端的鎖定（SQLite 用 BEGIN IMMEDIATE，SQL Server 用應用程式鎖定）」等方式，靠 DB 端來做序列化。

6.3 彩排 ── 測試從「最古老的 DB」一口氣套用

遷移的 bug 幾乎不會在開發機上被發現。因為開發機的 DB 永遠是最新結構，資料也很乾淨。真正會壞掉的，是客戶端那些又舊、又大、裡面塞了預期之外資料的 DB。發佈前至少該做的事有 3 件。

• 把各個結構版本的 DB 檔案存成測試固件，並自動化「從各版本一口氣套用到最新版」的測試。「從 v1 到 v5」「從 v3 到 v5」這類跳版模式，才是客戶端的現實。SQLite 的話只要把 DB 檔案放進 repository 就好，屬於容易寫測試的類型。
• 用相當於實際資料的量與品質來測試。滿是 NULL 的欄位、預期之外的重複資料、大型資料表的重建時間（5.1 節），資料如果不夠接近真實，就不會顯露出來。有條件的話，用匿名化過的客戶端 DB 來彩排。
• 測試失敗情境。在套用途中把處理序砍掉，確認下次啟動能不能正確恢復（從回滾後的版本重新開始套用）。

6.4 現場的確認步驟與切回

導入這套機制之後，要把「有沒有順利完成」「失敗時該怎麼做」寫成明確的步驟。因為一定會遇到要在電話裡指示現場人員操作的場面，寫成指令的形式最實用。

確認套用結果。如果有 SQLite 官方的命令列 shell（sqlite3），一行就能讀出目前的結構版本。回傳的是一個整數。

sqlite3 "C:\ProgramData\MyApp\app.db" "PRAGMA user_version;"

客戶端的 PC 上通常沒辦法放 sqlite3.exe，所以在應用程式的版本資訊畫面上，同時顯示產品版本與結構版本，就能靠一通電話確認狀態。只要直接呼叫第 3 章的 GetUserVersion 就好。SQL Server 的話，SELECT MAX(version) FROM schema_version; 扮演同樣的角色。

失敗時切回。5.3 節的備份，用下列步驟還原。

1. 把應用程式完全關閉。包含多重啟動，以及查看同一個 DB 的其他終端，全部都要關閉。
2. 把現有檔案移走保存。把目前的 DB 檔案，如果是 WAL 模式，連同名稱相同的 -wal／-shm 檔案，一起移到別的資料夾。不要刪除、留著。這是原因調查所需要的。
3. 把備份檔案用原本的檔名複製回去。因為在 5.3 節已經在檔名裡放入了結構版本，光看檔名就能知道要回到哪個時間點。
4. 啟動應用程式，確認 PRAGMA user_version 已經變成想要回復的編號。在此之上，直到修好原因的新版應用程式發佈之前，都用舊版應用程式繼續運作。

有沒有寫下這份步驟，會直接影響故障當天的復原時間。請在實作遷移的同一次發佈裡，順便在運作手冊上多加這一頁。

7. 總結

• 「各客戶端 DB 不一致」不是負責人注意力的問題，而是由人執行 SQL 操作手冊這種運作方式在結構上必然的結果。在 DB 分散的桌面業務應用程式中，只能讓應用程式自己把自己的 DB 更新到最新。
• 骨架是DB 自己擁有的結構版本編號（SQLite 用 PRAGMA user_version¹），加上編號前進遷移在啟動時的套用。用 C# 的話，幾十行自行實作的程式碼就能成立。
• 手段有 EF Core Migrations、DbUp 等函式庫、自行實作這 3 種路線。要依是否已經在用 EF Core，以及有多少原生 SQL 資產來選（第 4 章的判斷表）。EF Core 啟動時呼叫的 Migrate()，官方已經列出注意事項⁷，使用時要搭配同時執行對策與彩排。
• 破壞性變更用 expand-contract 兩階段發佈來進行，舊版應用程式打開新 DB 的意外，用最低版本檢查擋下來。SQLite 的 ALTER TABLE 限制與重建步驟，依照官方文件進行。³
• 1 個遷移＝1 個交易，版本更新也放進同一個交易是原則。SQL Server 有無法納入交易的 DDL⁴，因此要把例外操作獨立出來。連同套用前的 VACUUM INTO 備份²，以及從最古老版本一口氣套用的彩排都做到，才是一套「能發佈給客戶端」的遷移。

如果你也對操作手冊式的 ALTER 運作方式心裡有數，不妨在下一次發佈裡，先只加入「記錄版本編號」與「啟動時套用」這兩件事看看。只要有了地基，兩階段發佈與備份，之後都可以一點一點慢慢補上。

相關文章

• 在 C# 中將 SQLite 用於業務應用程式 ── WAL 模式・排他控制・損毀對策・與 EF Core 的取捨
• Windows應用程式資料儲存位置怎麼選 ── SQLite / JSON / 登錄檔 / Access 判斷表
• 不只是 appsettings.json ── Windows 商用應用程式的組態管理實務(環境別設定・機密資訊・寫入位置)
• 業務應用程式的日期時間與時區 ── 從 DateTime 的陷阱到 UTC 儲存原則、測試設計

相關諮詢領域

合同會社小村軟體承接安裝在各客戶端的業務應用程式的 DB 設計・遷移基礎架構導入、因操作手冊運作而分歧的結構調查與正常化，以及 EF Core／原生 SQL 各種架構下的更新發佈設計。

• Windows 應用程式開發
• 既有 Windows 軟體的改造・維護
• 技術諮詢・設計審查
• 聯絡我們

參考連結

相關文章

共用相同標籤的最新文章。能以相近的主題延伸理解。

相關主題

與本文相近的主題頁面。以本文為起點，可進一步連到相關服務與其他文章。

與本主題相關的服務

本文連結到以下服務頁面，歡迎從最接近的入口查看。

常見問題

整理諮詢這個主題時常見的問題。

業務應用程式的 DB 結構變更該如何管理？

不應該採用由人執行 SQL 操作手冊的運作方式，而應該把編號的遷移（結構變更程式碼）與應用程式本體一起發佈，在啟動時自動套用。讓 DB 自己記錄目前的結構版本（SQLite 用 PRAGMA user_version，SQL Server 用專用資料表），應用程式只依序在交易中套用尚未套用的編號。這樣一來，即使是從 v1.2 跳到 v1.5 這種跳版更新，中間的結構變更也會全部套用，「各客戶端 DB 形態不一致」的狀態就不會再從結構上發生。

可以在應用程式啟動時呼叫 EF Core 的 Migrate() 嗎？

有條件地說，這是可行的選擇。Microsoft 的文件因為多個執行個體同時套用、讓應用程式擁有結構變更權限、無法事先確認要執行的 SQL 等理由，對正式環境中在啟動時套用一事提出警示，並建議伺服器應用程式改用產生 SQL 腳本後再套用的方式。另一方面，每台客戶端 PC 各自擁有本機 DB 的桌面業務應用程式，並不具備「到現場執行腳本」這種運作方式，因此啟動時呼叫 Migrate() 實際上就是標準解法。即使如此，也務必搭配同時啟動的對策（EF Core 9 以後的自動鎖定，或自行實作的 Mutex）以及套用前的備份。

如果遷移執行到一半失敗，DB 會變成怎樣？

只要把一個遷移包在一個交易裡，並把版本編號的更新也放進同一個交易，失敗時就會回滾到該遷移開始前的狀態，不會留下中途而廢的結構。SQLite 的 CREATE TABLE、ALTER TABLE 等 DDL 也能在交易內執行，官方的資料表重建程序本身就是以交易為前提撰寫的。SQL Server 同樣可以在交易內執行大多數 DDL，但 ALTER DATABASE、全文檢索索引相關等操作是例外，因此要把這類例外操作獨立成單一遷移。此外，只要有套用前的自動備份，即使遇到最壞的情況，也能靠替換檔案來復原。

如果各客戶端的 DB 結構已經分歧了，該怎麼把它們正常化？

首先要決定「應有結構的正解」只有一個版本，並調查各客戶端的 DB，找出與現狀的差異。接著針對沒有版本編號的 DB，寫一個初始遷移，用來偵測實際存在的各種樣態並統一成正規形式，並在完成的時間點刻上版本編號。SQLite 可以用 sqlite_master 或 PRAGMA table_info 機械式地判斷欄位是否存在，用「沒有欄位就新增」這種防禦性 SQL 就能吸收差異。之後只要把所有變更都放進編號遷移裡，分歧就不會再發生。

作者檔案

本文作者的個人檔案頁面。

Go Komura

小村軟體有限公司 代表

以 Windows 軟體開發、技術諮詢與故障調查為中心，在難以重現的故障調查與既有資產仍在運作的專案上具有優勢。