Azure API Management の GraphQL API の リゾルバー で Azure SQL データソースを使ってみる

はじめに

API Managementは、API を簡単に管理できるサービスですが、REST API だけでなく GraphQL の API も作成できることができます。
API Management では、これまで既存の REST API をデータソースとして GraphQL の API を作成することができましたが、Cosmos DB と Azure SQL Database をデータソースとして使用できるようになりました。(※プレビュー段階)
これらのデータソースを使用することで、別途バックエンドを用意せずにデータベースを直接 API として公開することができます。
この記事では、Azure SQL Database のデータソースを使って、API Management で GraphQL の API を作成してみたいと思います。
（タイトルが「パルスのファルシのルシがパージでコクーン」みたいになってしまった）

前提

• API Management のリソースが作成済みであること。
  • この記事では API Management のリソース作成手順については説明しません。
  • 価格レベルは何でも良いと思いますが、執筆時は従量課金レベルで検証しています。
• Azure SQL Database のリソースが作成済みであること
  • この記事では Azure SQL Database のリソース作成手順については説明しません。

[2023/08/20 時点]
ドキュメントには従量課金レベルではサポートされていないと記載されていますが、使用可能になったようです。
(最初うまく動かずに MS の Q&A で問い合わせたら、「従量課金だと使えないよ」と回答があって放置してましたが、先日、使用可能になったと連絡をいただきました)

事前準備

基本的にはドキュメントに従って進めていきます。

API Management のマネージド ID を有効にする

今回 SQL Database にはマネージド ID を使用して接続するため、マネージド ID を有効にします。
Azure Portal で API Management のリソースを表示し、マネージド ID から 状態を オン に変更します。

SQL Database の Azure AD アクセスを有効化

マネージド ID で接続するために、SQL Database で Azure AD によるアクセスを有効にする必要があります。
SQL Server のリソースを表示し、Azure Active Directory > 管理者の設定で自身のアカウントを選択し、保存をクリック。

※Azure Active Directory の表記はいずれ Microsoft Entra ID に変更される...と思います

SQL Database のロールの割り当て

API Management の SQL Database 上でのロールを設定します。
Azure Portal のクエリーエディタ、または任意のクライアントツールでデータベースに接続し、以下のクエリを実行します。

DECLARE @identityName nvarchar(60) = '<API Managementのリソース名>';

EXECUTE(N'
CREATE USER ['+ @identityName +'] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [' + @identityName + '];
ALTER ROLE db_datawriter ADD MEMBER [' + @identityName + '];
ALTER ROLE db_ddladmin ADD MEMBER [' + @identityName + '];
');

以下は Portal のクエリエディタを使用した場合。(SQL Server のリソースより)

テーブル作成

適当なテーブルを作成し、適当なデータを投入します。

API の作成

Schema ファイルの作成

GraphQL API を作成する際に Schema ファイルを選択する必要があるため、事前に作成します。
今回は以下のように作成しました。

type Query {
  getUser(id: ID!): User!
  getUsers: UserList!
}

type Mutation {
  createUser(input: CreateUserInput!): User!
  updateUser(input: UpdateUserInput!): User!
  deleteUser(id: ID!): User!
}

type User {
  id: ID!
  name: String!
  age: Int!
}

type UserList {
  items: [User!]!
}

input UpdateUserInput {
  id: ID!
  name: String!
  age: Int!
}

input CreateUserInput {
  id: ID!
  name: String!
  age: Int!
}

GraphQL API の追加

API Management のリソースを表示し、API > Add API > GraphQLをクリック。

画像の様に設定して Create をクリック。
GraphQL type で Synthetic GraphQL を選択すると Schema ファイルを選択できるようになります。
Schema ファイルに先ほど作成したファイルを指定します。

Resolver の作成

作成した API が一覧に表示されるので、クリックすると先ほど選択した Schema が見られます。
更に行番号の左にある＋をクリックすると、Resolver の登録画面に行くことができます。

Resolver の登録画面で Data source に Azure SQL を設定し、Resolver policy(後述)を入力して Create をクリック。
※Schema から ジャンプした場合は Name、Type、Field は自動的に入力されています

ポリシーの設定

接続設定(共通)

マネージドIDで接続するため、connection-stringの属性にuse-managed-identity="true"を指定します。

<sql-data-source>
    <!-- 接続設定 -->
	<connection-info>
		<connection-string use-managed-identity="true">
            Server=tcp:<サーバー名>.database.windows.net,1433;Initial Catalog=<データベース名>;
        </connection-string>
	</connection-info>
    <!-- 省略 -->
</sql-data-source>

単一結果の取得

単一結果を取得する場合は以下の様なポリシーとなります。
(getUser)

<!-- getUser -->
<sql-data-source>
	<!-- 接続設定は省略 -->
	<request single-result="true">
		<sql-statement>
            SELECT
                u.[id],
                u.[name],
                u.[age]
            FROM
                [dbo].[users] u
            WHERE
                u.[id] = @id
        </sql-statement>
		<parameters>
			<parameter name="@id">@(context.GraphQL.Arguments["id"])</parameter>
		</parameters>
	</request>
</sql-data-source>

結果セットが1件であることを明示するため、requestの属性にsingle-result="true"を指定します。
parametersに指定したパラメータをsql-statement内で使用することができます。
parameterには ポリシー式 を指定できます。
context.GraphQL.Argumentsにリクエスト時に指定した引数が格納されています。
(ちなみにcontext.GraphQL.Argumentsの型はJSONオブジェクトを操作するためのクラスである JToken となっていました)

複数行の取得

複数行の結果を取得する場合は以下の様なポリシーとなります。
(getUsers)

<!-- getUsers -->
<sql-data-source>
	<!-- 接続設定は省略 -->
	<request>
		<sql-statement>
            SELECT
                u.[id],
                u.[name],
                u.[age]
            FROM
                [dbo].[users] u
        </sql-statement>
	</request>
</sql-data-source>

結果セットが複数行の場合は、requestの属性のsingle-result="true"は不要です。

更新系

更新系の場合は以下の様なポリシーとなります。
(createUser)

<!-- createUser -->
<sql-data-source>
	<!-- 接続設定は省略 -->
	<request single-result="true">
		<sql-statement>
            INSERT INTO [dbo].[users] (
                [id],
                [name],
                [age]
            )
            OUTPUT inserted.*
            VALUES (
                @id,
                @name,
                @age
            )
        </sql-statement>
		<parameters>
			<parameter name="@id">@(context.GraphQL.Arguments["input"]["id"])</parameter>
			<parameter name="@name">@(context.GraphQL.Arguments["input"]["name"])</parameter>
			<parameter name="@age">@(context.GraphQL.Arguments["input"]["age"])</parameter>
		</parameters>
	</request>
</sql-data-source>

更新系も結局 SQL が変わるだけなのですが、Schema 定義にあわせて挿入したレコードを返すため、OUTPUT inserted.*で取得しています。
更にsingle-result="true"に設定しています。
OUTPUT ...がないデフォルトの場合は、挿入した件数が返されました。

その他の更新系ポリシー

表示する

updateUser

<!-- updateUser -->
<sql-data-source>
	<!-- 接続設定は省略 -->
	<request single-result="true">
		<sql-statement>
            UPDATE [dbo].[users] SET 
                [name] = @name,
                [age] = @age
            OUTPUT inserted.*
            WHERE
                [id] = @id
        </sql-statement>
		<parameters>
			<parameter name="@id">@(context.GraphQL.Arguments["input"]["id"])</parameter>
			<parameter name="@name">@(context.GraphQL.Arguments["input"]["name"])</parameter>
			<parameter name="@age">@(context.GraphQL.Arguments["input"]["age"])</parameter>
		</parameters>
	</request>
	<response />
</sql-data-source>

deleteUser

<!-- deleteUser -->
<sql-data-source>
	<!-- 接続設定は省略 -->
	<request single-result="true">
		<sql-statement>
            DELETE
            FROM [dbo].[users] 
            OUTPUT deleted.*
            WHERE
                [id] = @id
        </sql-statement>
		<parameters>
			<parameter name="@id">@(context.GraphQL.Arguments["id"])</parameter>
		</parameters>
	</request>
	<response />
</sql-data-source>

API のテスト

API Management の API を選択し、Testタブから API のテストを実行できます。
左側に Schema 定義に従って一覧が表示され、チェックボックスをチェックすることで自動的に query が組み立てられます。
SendまたはTraceをクリックするとリクエストを送信します。
Trace はその名の通り、トレース情報を出力してくれるので、デバッグする際は有効です。

おまけ

レスポンスをカスタマイズする

以下のようにset-bodyでレスポンスのフォーマットをカスタマイズすることができます。

<!-- getUsers -->
<sql-data-source>
	<!-- 接続設定は省略 -->
	<request>
		<sql-statement>
            SELECT
                u.[id],
                u.[name],
                u.[age]
            FROM
                [dbo].[users] u
        </sql-statement>
	</request>
+   <response>
+       <set-body template="liquid">
+           [
+               {% JSONArrayFor user in body.items %}
+               {
+                   "id": "{{ user.id }}",
+                   "name": "{{ user.name }}",
+                   "age": {{ user.age }}
+               }
+               {% endJSONArrayFor %}
+           ]
+       </set-body>
+   </response>
</sql-data-source>

もともとは以下の様なフォーマットのところ

{
  "items": [
    {
      "id": "user001",
      "name": "Name001",
      "age": 10,
    },
    ...
  ]
}

以下のようにカスタマイズしています。

[
  {
    "id": "user001",
    "name": "Name001",
    "age": 10,
  },
  ...
]

set-bodyでは Liquid というテンプレートエンジンの記法で記述することができます。
Liquid は Power Pages でも採用されているみたいですね。

オプショナルなパラメータを指定する

ポリシー式を使用する

オプショナルなパラメータを定義して、指定された場合のみ条件に含めるといったことがやりたくなると思います。
そういった場合、SQL を動的に組み立てる必要がありますが、ポリシー式を使用することで実現できます。

<!-- getUsers -->
<sql-data-source>
	<!-- 接続設定は省略 -->
   <request single-result="true">
+       <sql-statement>
+       @{
+           var sql = new StringBuilder(@"
+               SELECT
+                   u.[id],
+                   u.[name],
+                   u.[age]
+               FROM
+                   users u
+           ");
+
+           var conditions = new List<string>();
+           if (context.GraphQL.Arguments["name"].Value<string>() != null)
+           {
+               conditions.Add("u.[name] LIKE '%' + @name + '%'");
+           }
+           if (context.GraphQL.Arguments["age"].Value<int?>() != null)
+           {
+               conditions.Add("u.[age] >= @age");
+           }
+
+           if (conditions.Count > 0)
+           {
+               sql.Append($" WHERE {string.Join(" AND ", conditions)}");
+           }
+
+           return sql.ToString();
+       }
+       </sql-statement>
+       <parameters>
+           <parameter name="@name">@(context.GraphQL.Arguments["name"].Value<string>() ?? "empty")</parameter>
+           <parameter name="@age">@(context.GraphQL.Arguments["age"].Value<int?>() ?? 0)</parameter>
+       </parameters>
  </request>
</sql-data-source>

@{}で囲むと普通に C# のコードの様に複数行で記述することができます。
returnされた文字列が SQL クエリとなります。

パラメータを以下のように書いてしまうと、指定しない場合に「必須だよ！」と怒られてしまうので

<parameter name="@name">@(context.GraphQL.Arguments["name"])</parameter>
<parameter name="@age">@(context.GraphQL.Arguments["age"])</parameter>

以下のように未指定の場合はダミーの値を設定するようにします。
他にやり方ありそうな気がしますが、わかりませんでした。
もっとスマートな方法知っている方は是非教えてください。

<parameter name="@name">@(context.GraphQL.Arguments["name"].Value<string>() ?? "empty")</parameter>
<parameter name="@age">@(context.GraphQL.Arguments["age"].Value<int?>() ?? 0)</parameter>

Liquid テンプレートを使用する

もう1つ動的に SQL を組み立てる方法として、set-bodyで Liquid テンプレートを使用する方法があります。

<!-- getUsers -->
<sql-data-source>
	<!-- 接続設定は省略 -->
    <request single-result="true">
+       <set-body template="liquid"><![CDATA[
+           SELECT
+               u.[id],
+               u.[name],
+               u.[age]
+           FROM
+               users u
+           WHERE 
+               1 = 1  --(横着しました)
+       {% if body.arguments.name != null %}
+           AND u.[name] LIKE '%' + @name + '%'
+       {% endif %}
+       {% if body.arguments.age != null %}
+           AND u.[age] >= @age 
+       {% endif %}
+       ]]></set-body>
+       <sql-statement>
+           @(Regex
+               .Match(context.Request.Body.As<string>(), @"<!\[CDATA\[(?<sql>.*?)\]\]>", RegexOptions.Singleline)
+               .Groups["sql"]?.Value)
+       </sql-statement>
        <parameters>
            <parameter name="@name">@(context.GraphQL.Arguments["name"].Value<string>() ?? "empty")</parameter>
            <parameter name="@age">@(context.GraphQL.Arguments["age"].Value<int?>() ?? 0)</parameter>
        </parameters>
    </request>
</sql-data-source>

レスポンスのカスタマイズのところでset-bodyを使用していましたが、リクエストの方でも使うことができます。
SQL で不等号を使うと保存したときに勝手にエスケープされてしまうので、<![CDATA[...]]>で囲んでいます。
そして、set-bodyで設定した body をsql-statementで参照しています。
そのままだと<![CDATA[ ]]>の部分も含まれてしまうので、囲まれた部分だけ抽出するようにしています。

おわりに

API Management と データベースだけで GraphQL の API を作成することができました。
ゴリゴリ SQL 書かないといけないので、あまりお手軽ではないかもしれませんが、逆に言うと柔軟に対応できるということでもありますね。
ストアドプロシージャも呼び出すことができるみたいなので、複雑な処理もできそうです。
デバッグはトレース情報見るくらいしかないので、もう少しやりやすくなると良いなーと思いました。
(自分が知らないだけかもしれない)

次の機会に Cosmos DB のデータソースも試してみたいと思います。