Azure Static Web Apps のデータベース接続 (プレビュー) を試してみた - SQL Database

# はじめに

:::note warn
本記事で紹介する機能はプレビュー段階であるため、[Microsoft Azure プレビューの追加使用条件](https://azure.microsoft.com/ja-jp/support/legal/preview-supplemental-terms/) に同意した上で使用するようにしてください。
:::

最近 (2023/03) Static Web Apps のデータベース接続という機能がパブリックプレビューになりました。
データベース接続機能を使用すると、**バックエンド側のコードを一切書かずに**、RESTやGraphQLのエンドポイントを通じて、データベースのテーブルやエンティティに対してCRUD操作や組み込みの認証を行うことができます。
Azure Cosmos DB, Azure SQL, Azure Database for MySQL, Azure Database for PostgreSQLなどのさまざまなデータベースタイプをサポートしています。
[Data API Builder](https://learn.microsoft.com/en-us/azure/data-api-builder/overview-to-data-api-builder) というエンジン (こちらもプレビュー版) が使われています。

前回は GraphQL を使用して Cosmos DB への接続を試してみたので、今回は REST を使用して Azure SQL Database への接続を試してみたいと思います。

https://qiita.com/fsdg-nagumo/items/aad108e607be2297ad87

# まずはローカルで動かしてみる

公式のチュートリアルを参考に動かしてみます。

https://learn.microsoft.com/ja-jp/azure/static-web-apps/database-azure-sql?tabs=bash&pivots=static-web-apps-rest

## 設定ファイル作成

[Static Web Apps CLI](https://azure.github.io/static-web-apps-cli/) を使用して DB 接続用の設定ファイルの雛形を作成できます。

- データベースの種類
    - SQL Database
- 接続文字列
    - `@env('MSSQL_CONNECTION_STRING')`
    - ※ `@env()` は環境変数を参照

```bash:bash
npx swa db init \
    --database-type mssql \
    --connection-string "@env('MSSQL_CONNECTION_STRING')"
```

実行すると以下のフォルダーとファイルが出来上がります。
基本的には Cosmos DB の時と同じですが、GraphQL のスキーマ定義ファイルは作られませんでした。

```text
swa-db-connections/
└── staticwebapp.database.config.json
```

### staticwebapp.database.config.json

データベース接続の設定ファイルです。

<details> 
<summary><b>staticwebapp.database.config.json (全体)</b></summary> 

```json:staticwebapp.database.config.json
{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v0.6.14/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "options": {
      "set-session-context": false
    },
    "connection-string": "@env('MSSQL_CONNECTION_STRING')"
  },
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/rest"
    },
    "graphql": {
      "allow-introspection": true,
      "enabled": true,
      "path": "/graphql"
    },
    "host": {
      "mode": "production",
      "cors": {
        "origins": [],
        "allow-credentials": false
      },
      "authentication": {
        "provider": "StaticWebApps"
      }
    }
  },
  "entities": {}
}
```

</details> 

ブロックごとに見ていきます。

まずはデータソースの設定ですね。
コマンドで指定した値が設定されています。

```json
  "data-source": {
    "database-type": "mssql",
    "options": {
      "set-session-context": false
    },
    "connection-string": "@env('MSSQL_CONNECTION_STRING')"
  },
```

ランタイム (REST、GraphQL) 毎の設定です。
Cosmos DB (NoSQL) では GraphQL のみ対応でしたが、こちらは REST も有効となっています。

```json
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/rest"
    },
    "graphql": {
      "allow-introspection": true,
      "enabled": true,
      "path": "/graphql"
    },
```

ホストの設定です。
Cosmos DB の時と同じです。
CORS や 認証プロバイダの設定があります。
認証プロバイダは Static Web Apps では `StaticWebApps` 固定になります。

```json
    "host": {
      "mode": "production",
      "cors": {
        "origins": [],
        "allow-credentials": false
      },
      "authentication": {
        "provider": "StaticWebApps"
      }
    }
```

データベースのエンティティ (テーブル) の設定です。
最初は空です。

```json
  "entities": {}
```

## 設定ファイル編集

### staticwebapp.database.config.json

先ほど空だった `entities` を設定します。
設定内容は Cosmos DB の時と同様です。

```json
  "entities": {
    "User": {
      "source": "users",
      "permissions": [
        {
          "role": "anonymous",
          "actions": ["*"]
        }
      ]
    }
  }
```

## SQL Database の準備

SQL Server、SQL Database、テーブルを作成します。

- SQL Server 名
    - <任意の名前>
- データベース名
    - sqldb-swadb-demo
- テーブル名
    - users
      ```mermaid
      erDiagram
        users {
          NVARCHAR id PK "ユーザーID"
          NVARCHAR name "ユーザー名"
          TINYINT age "年齢"
        }
      ```

また、Static Web Apps からアクセスできるようにするため、SQL Server リソースのネットワーク設定で下記を設定します。

- 選択したネットワーク (パブリックアクセス)
    - クライアントの IP アドレスを追加する
    - [x] Azure サービスおよびリソースにこのサーバーへのアクセスを許可する

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/40b8fed0-41db-267d-c4b1-6efeb644454e.png)

ローカル開発の場合は接続文字列が必要になるので、SQL データベースリソースの「接続文字列」メニューで「ADO.NET (SQL 認証)」の値を控えておきます。(Data API Builder は .NET で作られている)

<details> 

<summary><b>az コマンドで作成する場合</b></summary> 

```bash:bash 
# リソースグループは作成されている前提

resourceGroup=<リソースグループ名>
sqlServerName=<SQL Server名>
sqldbName=sqldb-swadb-demo
adminUser=<管理者ユーザー名>
adminPassword=<管理者パスワード>
sqlServerFirewallName=sqlfw-swadb-demo

# クライアントのグローバルIPを取得
clientIp=$(curl inet-ip.info)

# SQL Server 作成
az sql server create \
    --name $sqlServerName \
    --resource-group $resourceGroup \
    --admin-user $adminUser \
    --admin-password $adminPassword

# ネットワーク設定
#   ローカルクライアントからのアクセスを許可
az sql server firewall-rule create \
    --name $sqlServerFirewallName \
    --resource-group $resourceGroup \
    --server $sqlServerName \
    --start-ip-address $clientIp \
    --end-ip-address $clientIp

#   Azureサービスからアクセスを許可
az sql server firewall-rule create \
    --name $sqlServerFirewallName \
    --resource-group $resourceGroup \
    --server $sqlServerName \
    --start-ip-address 0.0.0.0 \
    --end-ip-address 0.0.0.0

# SQL Database 作成
az sql db create \
    --name $sqldbName \
    --resource-group $resourceGroup \
    --server $sqlServerName \
    --compute-model Provisioned \
    --edition Basic \
    --capacity 5

# SQL Database の接続文字列取得
sqlDatabaseConnectionString=$(
    az sql db show-connection-string \
        --client ado.net \
        --name $sqldbName \
        --server $sqlServerName \
        --outpu tsv
)

echo 
echo SQL Database Connection String:
echo "    $sqlDatabaseConnectionString"
``` 

最後に出力される SQL Database Connection String の値を控えておきます。
※テーブルはコマンドで作成できないので、お好みのクライアントアプリ、またはポータル上から作成してください

</details> 

## 環境変数の設定

上記で取得した Cosmos DB の接続文字列を環境変数に設定します。
(プロジェクトルートに配置した .env ファイルに記載しておいても読み込んでくれました)

```bash:bash
export MSSQL_CONNECTION_STRING="Server=tcp:<SQL Server名>.database.windows.net,1433;Initial Catalog=sqldb-swadb-demo;Persist Security Info=False;User ID=<adminユーザー名>;Password=<adminパスワード>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;""
```

## アプリの起動

`--data-api-location` オプションを指定してコマンドを実行します。

```bash
npx swa start --data-api-location swa-db-connections
```

## 動作確認

CRUD のリクエストを投げてレスポンスを表示するだけのアプリを作って確認します。
公開される REST エンドポイントには基本的な CRUD 操作が含まれています。
`/data-api/rest/<エンティティ名>[/<プライマリキー名>][/プライマリキー値]`

| No. | メソッド   | エンドポイント                     | 概要             |
| --: | ------ | --------------------------- | -------------- |
| 1   | GET    | /data-api/rest/User         | 一覧取得           |
| 2   | GET    | /data-api/rest/User/id/{id} | プライマリキーを指定して取得 |
| 3   | POST   | /data-api/rest/User         | 作成             |
| 4   | PUT    | /data-api/rest/User/id/{id} | 更新             |
| 5   | DELETE | /data-api/rest/User/id/{id} | 削除             |

### DB の初期状態
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/578840a3-3d3d-9dee-1593-627b6f289087.png)

### 一覧取得

`GET /data-api/rest/User` にリクエストします。

```tsx:リクエスト
/** 一覧 */
const listUsers: SendRequest<ListParams<User>> = async () => {
  return sendRequest('GET', '/data-api/rest/User')
}
```

<details> 
<summary><b>実行結果</b></summary> 

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/08b3b5f7-37ff-42b3-44fb-06c5737d2295.png)

</details> 


また、フィルターでの絞り込みや、ソートの指定も可能となっています。

<details> 
<summary><b>実行結果</b></summary> 
※上記ソースコードから変更しています

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/678a7e6d-4670-a5c9-630d-880d9872eafa.png)

</details>

更にページングにも対応しています。
パラメータに `$first=n` を指定すると、最初の n 件を取得し、残りのデータがある場合はレスポンスに `nextLink` プロパティが含まれます。
`nextLink` が次のページデータを取得するための REST エンドポイントとなっています。

<details> 
<summary><b>実行結果</b></summary> 

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/0d5fb3a5-72fd-b842-9747-aa67e40a10cc.png)

</details>

:::note warn
[2023/05/03 現在]
この `nextLink` ですが、現在 [バグ](https://github.com/Azure/data-api-builder/issues/1420) があり、URLに `/data-api` が含まれていないため、Static Web Apps ではそのままリクエストすると 404 となってしまいます。

誤：`"nextLink": "http://localhost:4280/rest/User?xxxxxxx..."`
正：`"nextLink": "http://localhost:4280/data-api/rest/User?xxxxxxx..."`

なので、現状は自分でURLを加工してリクエストする必要があります。

```typescript:例
const nextLink = new URL(response.nextLink)
// nextLink.origin ではなく location.origin にしているのは
// Azure にデプロイしたときも何故か nextLink のプロトコルが http のままだから
const nextLinkSwa = `${location.origin}/data-api${nextLink.pathname}${nextLink.search}`
```
:::

#### 指定できるパラメータ

指定できるパラメータは以下ドキュメントを参照。

https://learn.microsoft.com/en-us/azure/data-api-builder/rest#query-parameters

### プライマリキーを指定して取得

`GET /data-api/rest/User/id/{id}` にリクエストします。

```tsx:リクエスト
/** キーを指定して取得 */
const getUser: SendRequest<Pick<User, 'id'>> = async ({ id }) => {
  return sendRequest('GET', `/data-api/rest/User/id/${id}`)
}
```

<details> 
<summary><b>実行結果</b></summary> 

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/b5ad7e94-3d71-2619-29ec-7535b6c0b40d.png)

</details> 

### 登録

`POST /data-api/rest/User` にリクエストします。

```tsx:リクエスト
/** 登録 */
const createUser: SendRequest<User> = async (user) => {
  return sendRequest('POST', '/data-api/rest/User', user)
}
```

<details>  

<summary><b>実行結果</b></summary>  

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/5d769145-e7b9-651b-4286-244d86c45914.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/bbcc1fe9-7467-9bdb-5078-f442baf0fe42.png)

</details>  

### 更新

`PUT /data-api/rest/User/id/{id}` にリクエストします。

```tsx:リクエスト
/** 更新 */
const updateUser: SendRequest<User> = async ({ id, name, age }) => {
  return sendRequest('PUT', `/data-api/rest/User/id/${id}`, { name, age })
}
```

<details>  

<summary><b>実行結果</b></summary>  

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/315aca75-3378-c53f-487c-860eb258a2bc.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/b949c0cc-6526-79c3-e705-468c30bd63fb.png)

</details>  

### 削除

`DELETE /data-api/rest/User/id/{id}` にリクエストします。

```tsx:リクエスト
/** 削除 */
const deleteUser: SendRequest<Pick<User, 'id'>> = async ({ id }) => {
  return sendRequest('DELETE', `/data-api/rest/User/id/${id}`)
}
```

<details>  

<summary><b>実行結果</b></summary>  

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/7afe72dd-4c8a-4bb0-c1f5-3f3b1a82993a.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/1567a1da-8f8e-9c1f-64d9-24aad007c8ae.png)

</details>  

## アクセス制御の確認

Cosmos DB (GraphQL) と同様にテーブルレベルでロールベースのアクセス制御を設定することができます。
更に Cosmos DB 以外の DB では、フィールドレベルでの制御も設定することができます。
ここではフィールドレベルでの制御を試してみたいと思います。

### 設定ファイル編集

staticwebapp.database.config.json を編集します。

```diff_json:staticwebapp.database.config.json
  "entities": {
    "User": {
      "source": "dbo.users",
      "permissions": [
        {
          "role": "anonymous",
-         "actions": ["*"]
+         "actions": [
+           "create",
+           "update",
+           "delete",
+           {
+             "action": "read",
+             "fields": {
+               "exclude": ["age"]
+             }
+           }
+         ]
        }
      ]
    }
  }
```

`create`、`update`、`delete` は全てのフィールドにアクセス可能
`read` は `age` フィールドのみアクセス不可
としました。

### 制限されたフィールドにアクセスしない場合

データ取得時に `$select` パラメータを指定すると取得するフィールドを指定できるので、`age` 以外を取得してみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/0bcc31d6-0c41-b6ae-6f21-001760fc7a73.png)
アクセスできてますね。

### 制限されたフィールドにアクセスしようとした場合

今度は `age` も含めて取得しようとしてみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/89b9f6b4-8f96-71ac-3c86-55f7f48b8bad.png)
エラーになりました！ちゃんと制御できてますね🐤

## リレーションの設定

Cosmos DB 以外のリレーショナルデータベースでは、テーブル間のリレーションも設定できます。
ただし、GraphQL 限定の機能となっているので、ここだけ GraphQL で検証します。
(もしかして Cosmos DB でもできる...？今度検証してみます → できませんでした・・・！)

https://learn.microsoft.com/en-us/azure/data-api-builder/relationships

### テーブル構成変更

組織テーブルを追加して、1(組織)：n(ユーザー) のリレーションを設定することにします。
※`organizationId` は FK と書きましたが、実際には外部キー制約は設定していません

```mermaid
erDiagram
  users {
    NVARCHAR id PK "ユーザーID"
    NVARCHAR name "ユーザー名"
    TINYINT age "年齢"
    NVARCHAR organizationId FK "組織ID"
  }

  organizations {
    NVARCHAR id PK "組織ID"
    NVARCHAR name "組織名"
  }

  organizations |o--o{ users : ""
```

### スキーマ定義作成

Cosmos DB の時と同様、staticwebapp.database.schema.gql を作成します。

```graphql:staticwebapp.database.schema.gql
type User @model {
  id: ID!
  name: String!
  age: Int!
  organizationId: String
}

type Organization @model {
  id: ID!
  name: String!
  users: UserConnection!
}
```

### 設定ファイル編集

staticwebapp.database.config.json の `entities` にリレーションの設定を追加します。

```diff_json:staticwebapp.database.config.json
  "entities": {
    "User": {
      ・・・
    },
+   "Organization": {
+     "source": "dbo.organizations",
+     "permissions": [
+       {
+         "role": "anonymous",
+         "actions": ["*"]
+       }
+     ],
+     "relationships": {
+       "users": {
+         "source.fields": ["id"],
+         "target.entity": "User",
+         "target.fields": ["organizationId"],
+         "cardinality": "many"
+       }
+     }
    }
  }
```

`source.fields` は親テーブル (organizations) のキーとなるフィールド名
`target.entity` は子テーブル (users) のエンティティ名 (この設定ファイル上の名前)
`target.fields` は子テーブルが持つ外部キーのフィールド名
`cardinality` は子テーブルの関係。(1:1、1:n、n:n)

### 動作確認

#### データの状態

組織
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/289839be-3354-b39d-c7c6-902636044a6a.png)
ユーザー
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/a9476ee7-7eff-d5f0-46b5-19ea992bc6cb.png)

#### リクエスト

こんな感じのクエリをリクエストします。

```graphql
query Organizations {
    organizations {
        items {
            id
            name
            users {
                items {
                    id
                    name
                    age
                }
            }
        }
    }
}
```

#### レスポンス

組織とユーザーが紐づいたレスポンスが返ってきます！

```json
{
  "data": {
    "organizations": {
      "items": [
        {
          "id": "org1",
          "name": "Organization001",
          "users": {
            "items": [
              {
                "id": "user1",
                "name": "Name001",
                "age": 20
              },
              {
                "id": "user3",
                "name": "Name333",
                "age": 5
              }
            ]
          }
        },
        {
          "id": "org2",
          "name": "Organization002",
          "users": {
            "items": [
              {
                "id": "user2",
                "name": "Name002",
                "age": 30
              }
            ]
          }
        }
      ]
    }
  }
}
```

# Azure へのデプロイ

## デプロイ

ローカルで動かすことができたので、Azure 環境にデプロイしてみます。
Cosmos DB の時と同様、Static Web Apps CLI でデプロイします。
デプロイ方法は以下の記事を参考にしてください。

https://qiita.com/fsdg-nagumo/items/f09b3c567050fc042634#azure-%E3%81%B8%E3%81%AE%E3%83%87%E3%83%97%E3%83%AD%E3%82%A4

## SQL Database のリンク

デプロイできたら Cosmos DB を Static Web Apps にリンクさせます。
Static Web Apps のリソース画面で `データベース接続(プレビュー)` > `既存のデータベースのリンク` を選択。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/7b4e3187-120d-219d-ebd4-baf8d30cf1c7.png)

先ほど作成した SQL Database の情報を入力してリンクします。
認証の種類は接続文字列の他にマネージドIDが選べます。
ここでは接続文字列を選択しました。(というか Free プランだと接続文字列しか選択できない)

:::note warn
マネージドIDを選ぶには、Static Web Apps のホスティングプランを「Standard」に設定して、IDを有効にする必要があります
:::
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2884047/4e983317-d054-6f30-9346-d63258cec530.png)

<details>  

<summary><b>az コマンドで設定する場合</b></summary>  

```bash:bash
# Static Web Apps のリソースが作成済みの前提

resourceGroup=<リソースグループ名>
swaName=<Static Web Appsリソース名>
sqlServerName=<SQL Server名>
sqldbName=sqldb-swadb-demo
adminUser=<adminユーザー名>
adminPassword=<adminパスワード>

# SQL Server の ID 取得
sqlServerId=$(
    az sql server show \
        --name $sqlServerName \
        --resource-group $resourceGroup \
        --query id \
        --output tsv
)

# データベース接続設定
echo
echo Creating Database Connection to $sqlServerId ...
az staticwebapp dbconnection create \
    --db-resource-id "$sqlServerId" \
    --db-name $sqldbName \
    --name $swaName \
    --resource-group $resourceGroup \
    --username $adminUser \
    --password $adminPassword
```

</details>  

# まとめ

バックエンドのコードを一切書かずに REST による DB 操作ができました。
データベース接続機能を使用すれば、背後にある DB が変わったとしてもソースコードはそのままで、設定ファイルの修正のみで済むため、アーキテクチャが固まっていない状態でも柔軟に対応できそうです。

実際のアプリケーションでは、もっと複雑な検索が必要だったり、複数のデータを一括更新したりする場面も当然あると思います。そういった複雑な部分は バックエンドを実装し、単純な CRUD はデータベース接続を使う というように使い分けていけば、実装を最小限に抑えられるかもしれませんね😊