ネイティブ SQL を使用して Firebase SQL Connect オペレーションを実装する

GraphQL ではなく SQL を使用して Firebase SQL Connect オペレーションを記述するためのガイド。page_type: guideannouncement: >ネイティブ SQL は機能プレビューとして提供されています。そのため、SLA または非推奨ポリシーの対象ではなく、下位互換性のない方法で変更される可能性があります。この機能を動的 SQL を実行するストアド プロシージャ または関数で使用する場合は、このページの下部で説明されているセキュリティのベスト プラクティス に従ってください。

Firebase SQL Connect には、Cloud SQL データベースとやり取りする方法がいくつかあります。

• ネイティブ GraphQL: schema.gql で型を定義すると、 SQL Connect が GraphQL オペレーションを SQL に変換します。これは標準的なアプローチで、厳密な型指定とスキーマ適用構造を提供します。この ページ以外の SQL Connect ドキュメントのほとんどで、このオプションについて説明しています。可能な場合は、この方法を使用して、完全な型安全性とツールサポートを活用してください。
• **@view ディレクティブ**: schema.gql を基盤とする カスタム SELECT SQL ステートメントを定義します。これは、複雑な SQL ロジックに基づいて、読み取り専用の厳密に型指定されたビューを作成する場合に便利です。これらの型は、通常の型と同様にクエリ可能です。 @view をご覧ください。
• ネイティブ SQL: 特殊なルートフィールドを使用して、.gql ファイルの名前付きオペレーションに SQL ステートメントを直接埋め込みます。これにより、特に標準の GraphQL でサポートされていないオペレーション、データベース固有の機能の活用、PostgreSQL 拡張機能の利用において、最大限の柔軟性と直接的な制御が可能になります。 GraphQL や @view ディレクティブとは異なり、ネイティブ SQL では厳密に型指定された出力は提供されません。

このガイドでは、ネイティブ SQL オプションについて説明します。

ネイティブ SQL の一般的なユースケース

ネイティブ GraphQL は完全な型安全性を提供し、@view ディレクティブは読み取り専用の SQL レポートに対して厳密に型指定された結果を提供しますが、ネイティブ SQL は次の目的で必要な柔軟性を提供します。

• PostgreSQL 拡張機能: GraphQL スキーマで複雑な型をマッピングしなくても、インストールされている PostgreSQL 拡張機能（地理空間データ用の PostGIS など）を直接クエリして使用できます。
• 複雑なクエリ: 結合、サブクエリ、 集計、ウィンドウ関数、ストアド プロシージャを使用して複雑な SQL を実行します。
• データ操作（DML）: INSERT, UPDATE, DELETE オペレーションを 直接実行します。（ただし、データ定義言語（DDL）コマンドにはネイティブ SQL を使用しないでください。バックエンドと生成された SDK の同期を維持するには、GraphQL を使用してスキーマレベルの変更を行う必要があります）。
• データベース固有の機能: PostgreSQL 固有の関数、演算子、データ型 を利用します。
• パフォーマンスの最適化: クリティカル パス用に SQL ステートメントを手動で調整します。

ネイティブ SQL ルートフィールド

SQL でオペレーションを記述するには、query 型または mutation 型の次のいずれかのルートフィールドを使用します。

query フィールド

mutation フィールド

注:

• オペレーションは、 SQL Connect サービス アカウントに付与された権限を使用して実行されます。

構文規則と制限事項

ネイティブ SQL は、セキュリティを確保し、SQL インジェクションを防ぐために、厳密な解析ルールを適用します。次の制限事項にご注意ください。

• コメント: ブロック コメント (/* ... */) を使用します。行コメント (--) は、クエリの連結時に後続の句（セキュリティ フィルタなど）が切り捨てられる可能性があるため 禁止されています。
• パラメータ: params 配列の順序に一致する位置パラメータ（$1、$2）を使用します。名前付きパラメータ（$id、:name）はサポートされていません。
• 文字列: 拡張文字列リテラル（E'...'）とドル引用符付き文字列 （$$...$$）がサポートされています。PostgreSQL Unicode エスケープ（U&'...'）はサポートされていません。

コメント内のパラメータ

パーサーは、ブロック コメント内のすべてを無視します。パラメータを含む行をコメントアウトする場合（/* WHERE id = $1 */ など）、そのパラメータを params リストから削除する必要があります。削除しないと、オペレーションはエラー unused parameter: $1 で失敗します。

命名規則

ネイティブ SQL を記述する場合は、PostgreSQL データベースと直接やり取りするため、テーブルと列には実際のデータベース名を使用する必要があります。デフォルトでは、SQL Connect は、@table(name) ディレクティブと @col(name) ディレクティブを使用して Postgres 識別子を明示的にカスタマイズしない限り、GraphQL スキーマの名前をデータベースの スネークケース に自動的にマッピングします。

ディレクティブなしで型を定義すると、GraphQL のテーブル名とフィールド名はデフォルトの snake_case Postgres 識別子にマッピングされます。

type UserProfile {
  userId: ID!
  displayName: String
}

query GetUserProfileDefault($id: ID!) {
  profile: _selectFirst(
    sql: """
      SELECT user_id, display_name
      FROM user_profile
      WHERE user_id = $1
    """,
    params: [$id]
  )
}

PostgreSQL 識別子では、デフォルトで大文字と小文字が区別されません。@table や @col などのディレクティブを使用して、大文字または大文字と小文字が混在する名前を指定する場合は、SQL ステートメントでその識別子を二重引用符で囲む必要があります 。

次の例では、テーブル名に "UserProfiles"、 "profileId" を userId 列に使用する必要があります。displayName フィールドは、デフォルトの変換に従って display_name になります。

type UserProfileCustom @table(name: "UserProfiles") {
  userId: ID! @col(name: "profileId")
  displayName: String
}

query GetUserProfileCustom($id: ID!) {
  profile: _selectFirst(
    sql: """
      SELECT "profileId", display_name
      FROM "UserProfiles"
      WHERE "profileId" = $1
    """,
    params: [$id]
  )
}

使用例

例 1: フィールドのエイリアスを使用した基本的な SELECT

ルートフィールドにエイリアス（movies: _select など）を設定すると、クライアント レスポンスがよりクリーンになります（data._select ではなく data.movies）。

queries.gql:

query GetMoviesByGenre($genre: String!, $limit: Int!) @auth(level: PUBLIC) {
  movies: _select(
    sql: """
      SELECT id, title, release_year, rating
      FROM movie
      WHERE genre = $1
      ORDER BY release_year DESC
      LIMIT $2
    """,
    params: [$genre, $limit]
  )
}

クライアント SDK を使用してクエリを実行すると、結果は data.movies に格納されます。

例 2: 基本的な UPDATE

mutations.gql:

mutation UpdateMovieRating(
  $movieId: UUID!,
  $newRating: Float!
) @auth(level: NO_ACCESS) {
  _execute(
    sql: """
      UPDATE movie
      SET rating = $2
      WHERE id = $1
    """,
    params: [$movieId, $newRating]
  )
}

クライアント SDK を使用してミューテーションを実行すると、影響を受けた行数が data._execute に格納されます。

例 3: 基本的な集計

queries.gql:

query GetTotalReviewCount @auth(level: PUBLIC) {
  stats: _selectFirst(
    sql: "SELECT COUNT(*) as total_reviews FROM \"Reviews\""
  )
}

クライアント SDK を使用してクエリを実行すると、結果は data.stats.total_reviews に格納されます。

例 4: RANK を使用した高度な集計

queries.gql:

query GetMoviesRankedByRating @auth(level: PUBLIC) {
  _select(
    sql: """
      SELECT
        id,
        title,
        rating,
        RANK() OVER (ORDER BY rating DESC) as rank
      FROM movie
      WHERE rating IS NOT NULL
      LIMIT 20
    """,
    params: []
  )
}

クライアント SDK を使用してクエリを実行すると、結果は data._select に格納されます。

例 5: RETURNING と認証コンテキストを使用した UPDATE

mutations.gql:

mutation UpdateMyReviewText(
  $movieId: UUID!,
  $newText: String!
) @auth(level: USER) {
  updatedReview: _executeReturningFirst(
    sql: """
      UPDATE "Reviews"
      SET review_text = $2
      WHERE movie_id = $1 AND user_id = $3
      RETURNING movie_id, user_id, rating, review_text
    """,
    params: [$movieId, $newText, {_expr: "auth.uid"}]
  )
}

クライアント SDK を使用してミューテーションを実行すると、更新された投稿データが data.updatedReview に格納されます。

例 6: アップサート（アトミックな取得または作成）を使用した高度な CTE

このパターンは、子レコード（レビューなど）を挿入する前に、依存レコード（ユーザーや映画など）が存在することを保証する場合に便利です。すべてを 1 つのデータベース トランザクションで行います。

mutations.gql:

mutation CreateMovieCTE($movieId: UUID!, $userId: UUID!, $reviewId: UUID!) @auth(level: USER) {
  _execute(
    sql: """
      WITH
      new_user AS (
        INSERT INTO "user" (id, username)
        VALUES ($2, 'Auto-Generated User')
        ON CONFLICT (id) DO NOTHING
        RETURNING id
      ),
      movie AS (
        INSERT INTO movie (id, title, image_url, release_year, genre)
        VALUES ($1, 'Auto-Generated Movie', 'https://placeholder.com', 2025, 'Sci-Fi')
        ON CONFLICT (id) DO NOTHING
        RETURNING id
      )
      INSERT INTO "Reviews" (id, movie_id, user_id, rating, review_text, review_date)
      VALUES (
        $3,
        $1,
        $2,
        5,
        'Good!',
        NOW()
      )
    """,
    params: [$movieId, $userId, $reviewId]
  )
}

_executeReturning と _executeReturningFirst は、出力を JSON としてフォーマットするために、クエリを 親 CTE でラップします。PostgreSQL では、データ変更 CTE を別のデータ変更ステートメント内にネストすることはできません。これにより、クエリが失敗します。

例 7: Postgres 拡張機能の使用

ネイティブ SQL を使用すると、複雑なジオメトリ型を GraphQL スキーマにマッピングしたり、基盤となるテーブルを変更したりすることなく、PostGIS などの Postgres 拡張機能を使用できます。

この例では、レストラン アプリに、メタデータ JSON 列（{"latitude": 37.3688, "longitude": -122.0363} など）に位置 データを保存するテーブルがあるとします。PostGIS 拡張機能を有効にしている場合は、標準の Postgres JSON 演算子（->>）を使用してこれらの値をオンザフライ で抽出し、PostGIS ST_MakePoint 関数に渡すことができます。

query GetNearbyActiveRestaurants(
  $userLong: Float!,
  $userLat: Float!,
  $maxDistanceMeters: Float!
) @auth(level: USER) {
  nearby: _select(
    sql: """
      SELECT
        id,
        name,
        tags,
        ST_Distance(
          ST_MakePoint(
            (metadata->>'longitude')::float,
            (metadata->>'latitude')::float
          )::geography,
          ST_MakePoint($1, $2)::geography
        ) as distance_meters
      FROM restaurant
      WHERE active = true
        AND metadata ? 'longitude' AND metadata ? 'latitude'
        AND ST_DWithin(
          ST_MakePoint(
            (metadata->>'longitude')::float,
            (metadata->>'latitude')::float
          )::geography,
          ST_MakePoint($1, $2)::geography,
          $3
        )
      ORDER BY distance_meters ASC
      LIMIT 10
    """,
    params: [$userLong, $userLat, $maxDistanceMeters]
  )
}

クライアント SDK を使用してクエリを実行すると、結果は data.nearby に格納されます。

セキュリティのベスト プラクティス: 動的 SQL とストアド プロシージャ

SQL Connect は、 GraphQL からデータベースへの境界ですべての入力を安全にパラメータ化し、標準の SQL クエリを 1 次の SQL インジェクションから完全に保護します。ただし、SQL を使用して動的 SQL を実行するカスタム Postgres ストアド プロシージャまたは関数を呼び出す場合は、内部 PL/pgSQL コードでこれらのパラメータが安全に処理されるようにする必要があります。

ストアド プロシージャがユーザー入力を EXECUTE 文字列に直接連結すると、パラメータ化がバイパスされ、2 次の SQL インジェクションの脆弱性が生じます。

-- INSECURE: Do not concatenate parameters into dynamic strings!
CREATE OR REPLACE PROCEDURE unsafe_update(user_input TEXT)
LANGUAGE plpgsql AS $$
BEGIN
    -- A malicious user_input (e.g., "val'; DROP TABLE users; --")
    -- will execute as code.
    EXECUTE 'UPDATE target_table SET status = ''' || user_input || '''';
END;
$$;

これを回避するためのおすすめの方法は次のとおりです。

• USING 句を使用する: ストアド プロシージャで動的 SQL を記述する場合は、常に USING 句を使用してデータ パラメータを安全にバインドします。
• 識別子に format() を使用する: テーブル名などの安全なデータベース識別子インジェクションには、%I フラグを指定して format() を使用します。
• 識別子を厳密に許可する: クライアント アプリケーションがデータベース識別子を任意に選択できないようにします。プロシージャで動的識別子が必要な場合は、実行前に PL/pgSQL ロジック内のハードコードされた許可リストに対して入力を検証します。

-- SECURE: Use format() for identifiers and USING for data values
CREATE OR REPLACE PROCEDURE secure_update(
    target_table TEXT, new_value TEXT, row_id INT
)
LANGUAGE plpgsql AS $$
BEGIN
    -- Validate the dynamic table name against an allowlist
    IF target_table NOT IN ('orders', 'users', 'inventory') THEN
        RAISE EXCEPTION 'Invalid table name';
    END IF;

    -- Execute securely
    EXECUTE format('UPDATE %I SET status = $1 WHERE id = $2', target_table)
    USING new_value, row_id;
END;
$$;