【SQL Server】Update-DatabaseではなくScript-Migrationをつかう

# はじめに

Docker の SQL Server を試している過程で
**`Update-Database`でデータベースを更新することは本番には適していない**と知った

理由は以下の2点

>・SQL コマンドはツールによって直接適用されます。開発者はこのコマンドを検査または変更することはできません。 これは、運用環境では危険である可能性があります。
>・.NET SDK と EF ツールは、実稼働サーバーにインストールする必要があります。

https://docs.microsoft.com/ja-jp/ef/core/managing-schemas/migrations/applying

- 1点目については、SQL コマンドを精査できるほど知識がないので何とも言えない
- 2点目については、身に覚えがある
    - **本番のマシンだろうが構わずSDK（ランタイム）やら解析ツールをインストールするようなダラけた精神は叩き直すべき**（自身のこと）

# 運用データベースへの移行は、SQL スクリプトがおススメです

先述の[公式ドキュメント](https://docs.microsoft.com/ja-jp/ef/core/managing-schemas/migrations/applying)では、**移行をデータベースに反映させる方法が4つ**紹介されている

方法|コマンド|運用データベースへの適正
----|---|---
SQL スクリプト|`Script-Migration`|〇適している
べき等 SQL スクリプト|`Script-Migration -Idempotent`|〇適している
コマンドライン ツール|`Update-Database`|×適していない
実行時に移行を適用する|`context.Database.Migrate()`|×適していない

## 今回は、べき等 SQL スクリプトを試す

べき等 SQL スクリプトの特徴を引用する（[引用元](https://docs.microsoft.com/ja-jp/ef/core/managing-schemas/migrations/applying?tabs=vs#idempotent-sql-scripts)）

> 既に適用されている移行を内部で確認し、不足しているもののみを適用します これは、データベースに最後に適用された移行の内容を正確に把握していない場合、またはそれぞれが異なる移行に配置されている複数のデータベースに配置する場合に便利です。

**データベースがどこまで移行されているかを関知せずに実行できる**らしく、扱いやすそう


### 表1行目と2行目の違いは？

1番目も2番目も同じ`Script-Migration`コマンドを使うから、動作は同じはず
ただ、1番目のセクションでは**「`-From`・`-To`オプションを使うと移行の始点と終点を指定することができるよ」**と書いてあり、やや上級者向け（細かく指定したい人向け？）にみえた
なので、今回は**べき等に動作する2番目をチョイス**した

https://docs.microsoft.com/en-us/ef/core/cli/powershell#script-migration

# SQL スクリプトをつかって、`Blogging`データベースを移行してみる

[この記事](https://qiita.com/t13801206/items/4c0da0936d4f637940de)に至るまで、`Blogging`データベースをつくって初期データを与えるまでの2つの移行（マイグレーション）を行っている
移行は`Update-Database`をつかってデータベースに反映させてきた
（※ 繰り返しになるが、`Update-Database`は運用データベースに向いていない）

今回は、`Blogging`データベースをつくって初期データを与えるまでの2つの移行（マイグレーション）を**SQL スクリプトにより行う**

## SQL スクリプトを生成する`Script-Migration`

まずは、SQL スクリプトを生成する

SQL スクリプトを生成するときは、`-V`オプションを付けておくとよい
**`-V`オプションがないと、生成された SQL スクリプトの場所がわからない**

```Powershell:パッケージマネージャーコンソール（最終行にsqlファイルの出力先が書いてある）
Script-Migration -Idempotent -Context BloggingContext -Project Intro -StartupProject WPF_EFCore -V

# Using project 'Intro'.
# Using startup project 'WPF_EFCore'.
# Build started...
# Build succeeded.
# ---中略---
# Writing '{.slnファイルがあるディレクトリ}\Intro\obj\Debug\netcoreapp3.1\jzy0rczc.sql'...
```

`obj`ディレクトリ以下、おそらくビルド環境に合わせたディレクトリに降りていけば、そこに`sql`ファイルが生成されているはず

生成された`sql`ファイルの全容は[付録](#付録)に載せる

## 移行先のデータベースをつくる

[この記事](https://qiita.com/t13801206/items/9330dd3a540966261320#%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%82%92%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B)と同じ操作で、Docker の SQL サーバにログインして作業する

いろいろデータベースの中で作業してきたので、いったん壊して作り直す
**SQL スクリプトはデータベースの作成までは関与していない**（[付録](#付録)のコード参照）

```sql
-- データベース削除
1> DROP DATABASE Blogging;
2> GO
-- データベース作成
1> CREATE DATABASE Blogging;
2> GO
```

## SQL スクリプトを実行する

スクリプトもコマンドラインで打ちたかったけど、Docker for Windows のマウントで詰まったので、さくっと
[SSMS](https://qiita.com/t13801206/items/9330dd3a540966261320#%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%82%92%E7%A2%BA%E8%AA%8D%E3%81%99%E3%82%8B)の力を借りる
（`sqlcmd`で同じ操作をする方法は別記事にする予定）

さきほど作った`Blogging`データベースを選択し、SQL スクリプトを実行する

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/272511/4db5a7e8-ef04-114c-ceca-ba1d94e6b9a5.png)

実行すると成功のメッセージが表示される

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/272511/2df450f8-12a3-7107-1144-172006b0c39f.png)

なお、続けざまに実行しても`コマンドは正常に完了しました。`と表示されるのみで
**べき等 SQL スクリプト**である（**同じ処理をすれば、結果は一意に決まる**）ことがわかる

```sql:SQLスクリプトを複数回実行したあとのテーブル
-- Blogsテーブル
1> SELECT * FROM Blogs;
2> GO
BlogId Url Rating
------ --- ------
11 http://one.com 0
22 http://two.com 0

(2 rows affected)

-- Postsテーブル
1> SELECT * FROM Posts;
2> GO
PostId Title Content BlogId
------ ----- ------- ------
11 title11 content11 11
22 title12 content12 11
33 title23 content23 22
44 title24 content24 22

(4 rows affected)
```

# 余談

[マイクロソフトの公式ドキュメントを日本語で表示](https://docs.microsoft.com/ja-jp/ef/core/cli/powershell#script-migration)すると、**オプション引数も日本語表記になる**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/272511/a1a88d94-ac7f-3e2a-354c-f1f1d2a175b5.png)

文字通りにコマンドを打ったら怒られた

```Powershell:パッケージマネージャーコンソール
Script-Migration -べき等
Script-Migration : パラメーター名 'べき等' に一致するパラメーターが見つかりません。
発生場所 行:1 文字:18
```

# おわりに

これまでは`Update-Database`しか使ってこなかった

今回は**べき等 SQL スクリプト**を試してみた

生のSQL文が見えるのは、たしかに安心感があり、手を加えてみたくなる

# 参考にさせていただいた記事

https://qiita.com/zaburo/items/6edf7c05c5d4f5e039eb

# 付録

`Script-Migration`により生成された`.sql`ファイル

```sql
IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
GO

BEGIN TRANSACTION;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Init')
BEGIN
    CREATE TABLE [Blogs] (
        [BlogId] int NOT NULL IDENTITY,
        [Url] nvarchar(max) NULL,
        [Rating] int NOT NULL,
        CONSTRAINT [PK_Blogs] PRIMARY KEY ([BlogId])
    );
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Init')
BEGIN
    CREATE TABLE [Posts] (
        [PostId] int NOT NULL IDENTITY,
        [Title] nvarchar(max) NULL,
        [Content] nvarchar(max) NULL,
        [BlogId] int NOT NULL,
        CONSTRAINT [PK_Posts] PRIMARY KEY ([PostId]),
        CONSTRAINT [FK_Posts_Blogs_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blogs] ([BlogId]) ON DELETE CASCADE
    );
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Init')
BEGIN
    CREATE INDEX [IX_Posts_BlogId] ON [Posts] ([BlogId]);
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Init')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'2021**********_Init', N'5.0.9');
END;
GO

COMMIT;
GO

BEGIN TRANSACTION;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Seed')
BEGIN
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Rating', N'Url') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] ON;
    EXEC(N'INSERT INTO [Blogs] ([BlogId], [Rating], [Url])
    VALUES (11, 0, N''http://one.com'')');
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Rating', N'Url') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] OFF;
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Seed')
BEGIN
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Rating', N'Url') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] ON;
    EXEC(N'INSERT INTO [Blogs] ([BlogId], [Rating], [Url])
    VALUES (22, 0, N''http://two.com'')');
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'BlogId', N'Rating', N'Url') AND [object_id] = OBJECT_ID(N'[Blogs]'))
        SET IDENTITY_INSERT [Blogs] OFF;
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Seed')
BEGIN
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'PostId', N'BlogId', N'Content', N'Title') AND [object_id] = OBJECT_ID(N'[Posts]'))
        SET IDENTITY_INSERT [Posts] ON;
    EXEC(N'INSERT INTO [Posts] ([PostId], [BlogId], [Content], [Title])
    VALUES (11, 11, N''content11'', N''title11''),
    (22, 11, N''content12'', N''title12''),
    (33, 22, N''content23'', N''title23''),
    (44, 22, N''content24'', N''title24'')');
    IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'PostId', N'BlogId', N'Content', N'Title') AND [object_id] = OBJECT_ID(N'[Posts]'))
        SET IDENTITY_INSERT [Posts] OFF;
END;
GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'2021**********_Seed')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'2021**********_Seed', N'5.0.9');
END;
GO

COMMIT;
GO
```