네이티브 SQL을 사용하여 Firebase Data Connect 작업 구현

Firebase Data Connect는 Cloud SQL 데이터베이스와 상호작용하는 여러 방법을 제공합니다.

• 기본 GraphQL: schema.gql에서 유형을 정의하면 Data Connect 가 GraphQL 작업을 SQL로 변환합니다. 강력한 유형 지정 및 스키마 적용 구조를 제공하는 표준 접근 방식입니다. 이 페이지 외부의 대부분의 Data Connect 문서에서는 이 옵션을 설명합니다. 가능하면 이 메서드를 사용하여 완전한 유형 안전성 및 도구 지원을 활용해야 합니다.
• **@view 지시어**: schema.gql에서 커스텀 SELECT SQL 문으로 지원되는 GraphQL 유형을 정의합니다. 이는 복잡한 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 필드

참고:

• 작업은 Data Connect 서비스 계정에 부여된 권한을 사용하여 실행됩니다.

• @table 지시어 (@table(name: "ExampleTable"))를 사용하여 테이블 이름을 명시적으로 설정하는 경우 SQL 문의 테이블 이름도 따옴표로 묶어야 합니다 (SELECT field FROM "ExampleTable" ...).

  따옴표가 없으면 Data Connect가 테이블 이름을 스네이크 케이스 (example_table)로 변환합니다.

구문 규칙 및 제한사항

기본 SQL은 보안을 보장하고 SQL 삽입을 방지하기 위해 엄격한 파싱 규칙을 적용합니다. 다음 제약 조건을 고려해야 합니다.

• 의견: 블록 의견 (/* ... */)을 사용합니다. 줄 의견 (--)은 쿼리 연결 중에 후속 절 (예: 보안 필터)을 자를 수 있으므로 금지됩니다.
• 매개변수: 배열 순서와 일치하는 위치 매개변수 ($1, $2)를 사용합니다.params 명명된 매개변수 ($id, :name)는 지원되지 않습니다.
• 문자열: 확장 문자열 리터럴 (E'...') 및 달러로 인용된 문자열 ($$...$$)이 지원됩니다. PostgreSQL 유니코드 이스케이프 (U&'...')는 지원되지 않습니다.

의견의 매개변수

파서가 블록 의견 내의 모든 항목을 무시합니다. 매개변수가 포함된 줄 (예: /* WHERE id = $1 */)을 주석 처리하는 경우 params 목록에서 해당 매개변수도 삭제해야 합니다. 그렇지 않으면 작업이 unused parameter: $1 오류와 함께 실패합니다.

예

예 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: upsert (원자적 가져오기 또는 만들기)가 있는 고급 CTE

이 패턴은 단일 데이터베이스 트랜잭션에서 하위 레코드 (예: 리뷰)를 삽입하기 전에 종속 레코드 (예: 사용자 또는 영화)가 있는지 확인하는 데 유용합니다.

mutations.gql:

mutation CreateMovieCTE($movieId: UUID!, $userId: UUID!, $reviewId: UUID!) {
  _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]
  )
}

예 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 및 저장 프로시저

Data Connect는 GraphQL-데이터베이스 경계에서 모든 입력을 안전하게 매개변수화하여 1차 SQL 삽입으로부터 표준 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;
$$;