ソース管理を構成し、参照データを管理する

完了済み

ソース管理のない SQL データベース プロジェクトは、誰かのコンピューター上のファイルのフォルダーにすぎません。 その人のラップトップが壊れたり、2人の開発者が同じストアドプロシージャを編集したりすると、振り出しに戻ります。 プロジェクトを Git に配置すると、変更履歴、分岐要求とプル要求によるコラボレーション、および CI/CD パイプラインに必要なすべての基盤が提供されます。

しかし、ソース管理だけではすべてを解決することはできません。 データベースには、スキーマと共に存在する参照テーブル、状態コード、および既定の構成が含まれている可能性があります。 そのデータの戦略も必要です。

SQL データベース プロジェクトのソース管理を設定する

SQL データベース プロジェクトは .sql ファイルと .sqlproj ファイルだけで構成されているため、Git に自然に適合します。 各データベース オブジェクトは独自のファイルに存在するため、Git はオブジェクト レベルで変更を追跡します。 Customers テーブルを変更し、ストアド プロシージャを更新するコミットでは、何十もの無関係な変更を含むモノリシック移行スクリプトではなく、差分内のこれら 2 つのファイルが正確に表示されます。

開始するには、プロジェクト フォルダー内の Git リポジトリを初期化します。

cd MyDatabaseProject
git init
git add .
git commit -m "Initial commit of database project"

プロジェクト フォルダーを整理する

一貫性のあるフォルダー構造により、プロジェクトが一目でナビゲートできるようになります。 最も一般的なパターンは、オブジェクトの種類別にファイルをグループ化します。

MyDatabaseProject/
├── MyDatabaseProject.sqlproj
├── Tables/
│   ├── Customers.sql
│   └── Orders.sql
├── Views/
│   └── vw_ActiveCustomers.sql
├── StoredProcedures/
│   └── usp_GetCustomerOrders.sql
├── Scripts/
│   ├── PostDeployment/
│   │   └── seed-data.sql
│   └── PreDeployment/
│       └── prep-db.sql
└── PostDeploy.sql

SDK スタイルのプロジェクトでは、既定の globbing パターンによって、フォルダー内のすべての .sql ファイルが自動的に取得されます。 手動のファイル エントリは必要ありません。

.gitignore ファイルを追加する

ビルド出力とユーザー固有の設定を除外して、リポジトリをソース ファイルに集中させます。

bin/
obj/
*.dacpac
*.user

.dacpacは CI/CD ビルドごとにプロジェクトから再作成されるため、Git で追跡する理由はありません。

デプロイ前スクリプトとデプロイ後スクリプトを使用して参照データを管理する

宣言型モデルは、テーブル、ビュー、ストアド プロシージャなどのスキーマ オブジェクトを処理しますが、一部のデータはスキーマ自体と同じくらい不可欠です。 状態コード、参照テーブル、既定の構成、リージョン リスト。 そのデータが消えた場合、アプリケーションは中断されます。 プロジェクトに属し、それに依存するオブジェクトと共にバージョン管理されます。

デプロイ前スクリプトとデプロイ後スクリプトによって、この問題が解決されます。 これらは、デプロイ中に実行されるが、コンパイルされたデータベース モデルの外部に配置される SQL スクリプトです。

  • デプロイ前スクリプトは、デプロイ計画の前に実行されます。 制約の削除やデータの移行など、スキーマを変更する前に完了する必要があるタスクに使用します。
  • デプロイ後のスクリプトは、デプロイ計画の完了後に実行されます。 これを使用して、参照データの設定、参照テーブルのシード処理、またはアプリケーションの既定値の設定を行います。

プロジェクトにスクリプトを追加する

プロジェクトでは、デプロイ前スクリプトと配置後スクリプトが 1 つだけサポートされます。 .sqlprojとPreDeploy項目エントリを使用して、PostDeploy ファイルで宣言します。

<ItemGroup>
    <PreDeploy Include="prep-db.sql" />
</ItemGroup>
<ItemGroup>
    <PostDeploy Include="PostDeploy.sql" />
</ItemGroup>

複数のデータファイルにSQLCMDのインクルード機能を利用する

1 つのスクリプト ファイルは、1 つの巨大なファイルを意味するわけではありません。 SQLCMD :r 構文を使用して、1 つのエントリ ポイントから複数のファイルをプルします。 一般的な PostDeploy.sql は次のようになります。

:r .\Scripts\PostDeployment\seed-statuses.sql
:r .\Scripts\PostDeployment\seed-regions.sql
:r .\Scripts\PostDeployment\seed-app-settings.sql

参照される各ファイルをビルドから除外する必要があります。それ以外の場合、ビルド プロセスはスキーマ オブジェクトとしてコンパイルを試み、失敗します。 .sqlproj ファイルで、Build Removeを使用してコンパイルを防ぎ、None Includeしてファイルをプロジェクトに表示したままにします。

<ItemGroup>
    <Build Remove="Scripts\PostDeployment\seed-statuses.sql" />
    <None Include="Scripts\PostDeployment\seed-statuses.sql" />
</ItemGroup>

📝 デプロイ前またはデプロイ後のスクリプトで参照される各ファイルに対して、このパターンを繰り返します。

冪等性のある参照データスクリプトを記述する

デプロイ後のスクリプトは、最初のデプロイだけでなく、すべてのデプロイで実行されます。 プレーン INSERT ステートメントを使用する場合、2 つ目のデプロイは重複するキー違反で失敗します。 代わりに MERGE ステートメントを使用して、スクリプトを安全に繰り返し実行できます。

MERGE INTO [dbo].[OrderStatuses] AS target
USING (VALUES
    (1, N'Pending'),
    (2, N'Processing'),
    (3, N'Shipped'),
    (4, N'Delivered'),
    (5, N'Cancelled')
) AS source ([StatusID], [StatusName])
ON target.[StatusID] = source.[StatusID]
WHEN MATCHED THEN
    UPDATE SET [StatusName] = source.[StatusName]
WHEN NOT MATCHED THEN
    INSERT ([StatusID], [StatusName])
    VALUES (source.[StatusID], source.[StatusName]);

ヒント

.dacpacファイル拡張子を.zipに変更して展開することで、プロジェクトのビルド後に配置前および配置後のスクリプトを検証できます。 スクリプトの種類ごとに 1 つの .sql ファイルに、すべての参照ファイルから結合された T-SQL コンテンツが含まれています。

重要なポイント

ソリューション レベルで Git リポジトリを初期化し、 .gitignore ファイルを使用して、 bin/、 obj/、 .dacpac ファイルなどのビルド出力を除外します。 スキーマ オブジェクトを、オブジェクトごとに 1 つのファイルを使用して、データベース構造をミラー化するフォルダーに整理します。 配置後スクリプトに参照データを配置し、SQLCMD :r を使用して、各スクリプトを 1 つのテーブルに集中させ続けます。 データ スクリプトがスキーマ オブジェクトとしてコンパイルされないようにするには、Build Remove ファイルでNone Includeと.sqlprojを使用します。 MERGE ステートメントではなくプレーンINSERT ステートメントを記述して、リファレンス データ スクリプトが冪等性を持ち、すべてのデプロイメントで安全に実行できるようにします。 次に、このリポジトリを配置して、分岐とプル要求のワークフローを操作します。