Implementowanie operacji Firebase SQL Connect za pomocą natywnego SQL

Przewodnik po pisaniu operacji Firebase SQL Connect za pomocą SQL zamiast GraphQL. page_type: guide announcement: > Native SQL jest dostępny w wersji Preview, co oznacza, że nie podlega gwarancji jakości usług ani zasadom wycofywania i może ulec zmianie w sposób niezgodny z poprzednimi wersjami. Jeśli używasz tej funkcji z procedurami przechowywanymi lub funkcjami, które wykonują dynamiczny kod SQL, postępuj zgodnie ze sprawdzonymi metodami dotyczącymi bezpieczeństwa opisanymi u dołu tej strony.

Firebase SQL Connect oferuje kilka sposobów interakcji z bazą danych Cloud SQL:

• Native GraphQL: zdefiniuj typy w pliku schema.gql, a SQL Connect przetłumaczy operacje GraphQL na SQL. Jest to standardowe podejście, które zapewnia silne typowanie i struktury wymuszane przez schemat. Większość dokumentacji SQL Connect poza tą stroną omawia tę opcję. Jeśli to możliwe, używaj tej metody, aby korzystać z pełnego bezpieczeństwa typów i obsługi narzędzi.
• Dyrektywa @view: zdefiniuj typ GraphQL w schema.gql obsługiwany przez niestandardową instrukcję SQL SELECT. Jest to przydatne do tworzenia widoków tylko do odczytu o silnym typowaniu na podstawie złożonej logiki SQL. Te typy można wysyłać do nich zapytania tak jak zwykłe typy. Zobacz @view.
• Native SQL: osadzaj instrukcje SQL bezpośrednio w nazwanych operacjach w .gql plikach za pomocą specjalnych pól głównych. Zapewnia to maksymalną elastyczność i bezpośrednią kontrolę, zwłaszcza w przypadku operacji nieobsługiwanych przez standardowy GraphQL, korzystania z funkcji specyficznych dla bazy danych lub korzystania z rozszerzeń PostgreSQL. W przeciwieństwie do GraphQL i dyrektywy @view natywny SQL nie zapewnia silnie typowanych danych wyjściowych.

W tym przewodniku skupimy się na opcji Native SQL.

Typowe przypadki użycia natywnego SQL

Chociaż natywny GraphQL zapewnia pełne bezpieczeństwo typów, a dyrektywa @view oferuje silnie typowane wyniki w przypadku raportów SQL tylko do odczytu, natywny SQL zapewnia elastyczność potrzebną do:

• Rozszerzenia PostgreSQL: bezpośrednio wysyłaj zapytania i używaj dowolnego zainstalowanego rozszerzenia PostgreSQL (np. PostGIS do danych geoprzestrzennych) bez konieczności mapowania złożonych typów w schemacie GraphQL.
• Złożone zapytania: wykonuj złożone zapytania SQL z łączeniami, podzapytaniami, agregacjami, funkcjami okiennymi i procedurami przechowywanymi.
• Manipulowanie danymi (DML): wykonuj INSERT, UPDATE, DELETE operacje bezpośrednio. (Nie używaj jednak natywnego SQL do poleceń języka definiowania danych (DDL). Aby zachować synchronizację backendu i wygenerowanych pakietów SDK, musisz nadal wprowadzać zmiany na poziomie schematu za pomocą GraphQL).
• Funkcje specyficzne dla bazy danych: używaj funkcji, operatorów lub typów danych unikalnych dla PostgreSQL.
• Optymalizacja wydajności: ręcznie dostrajaj instrukcje SQL dla ścieżek krytycznych.

Pola główne natywnego SQL

Aby pisać operacje za pomocą SQL, użyj jednego z tych pól głównych typów query lub mutation:

Pola query

Pola mutation

Uwagi:

• Operacje są wykonywane z użyciem uprawnień przyznanych kontu usługi SQL Connect.

Reguły składni i ograniczenia

Natywny SQL wymusza ścisłe reguły analizowania, aby zapewnić bezpieczeństwo i zapobiec wstrzyknięciu kodu SQL. Pamiętaj o tych ograniczeniach:

• Komentarze: używaj komentarzy blokowych (/* ... */). Komentarze wierszowe (--) są zabronione, ponieważ mogą obcinać kolejne klauzule (np. filtry bezpieczeństwa) podczas konkatenacji zapytań.
• Parametry: używaj parametrów pozycyjnych ($1, $2), które pasują do params kolejności tablicy. Parametry nazwane ($id, :name) nie są obsługiwane.
• Ciągi znaków: obsługiwane są rozszerzone literały ciągów (E'...') i ciągi znaków w cudzysłowach dolarowych ($$...$$). Sekwencje Unicode PostgreSQL (U&'...') nie są obsługiwane.

Parametry w komentarzach

Parser ignoruje wszystko, co znajduje się w komentarzu blokowym. Jeśli zakomentujesz wiersz zawierający parametr (np. /* WHERE id = $1 */), musisz też usunąć ten parametr z listy params. W przeciwnym razie operacja zakończy się niepowodzeniem z powodu błędu unused parameter: $1.

Konwencje nazewnictwa

Podczas pisania natywnego SQL wchodzisz w bezpośrednią interakcję z bazą danych PostgreSQL, dlatego musisz używać rzeczywistych nazw tabel i kolumn. Domyślnie SQL Connect automatycznie mapuje nazwy w schemacie GraphQL na snake case w bazie danych, chyba że jawnie dostosujesz identyfikatory Postgres za pomocą @table(name) i @col(name) dyrektyw.

Jeśli zdefiniujesz typ bez dyrektyw, nazwy tabel i pól GraphQL zostaną zmapowane na domyślne identyfikatory Postgres snake_case:

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]
  )
}

Domyślnie w identyfikatorach PostgreSQL nie jest rozróżniana wielkość liter. Jeśli używasz dyrektyw takich jak @table lub @col, aby określić nazwę zawierającą wielkie litery lub litery o różnej wielkości, musisz umieścić ten identyfikator w cudzysłowie w instrukcjach SQL.

W tym przykładzie musisz użyć "UserProfiles" nazwy tabeli i "profileId" nazwy kolumny userId. Pole displayName jest domyślnie konwertowane na 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]
  )
}

Przykłady użycia

Przykład 1. Podstawowa instrukcja SELECT z aliasowaniem pól

Możesz utworzyć alias pola głównego (np. movies: _select), aby uprościć odpowiedź klienta (data.movies zamiast data._select).

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]
  )
}

Po uruchomieniu zapytania za pomocą pakietu SDK klienta wynik będzie dostępny w data.movies.

Przykład 2. Podstawowa instrukcja 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]
  )
}

Po uruchomieniu mutacji za pomocą pakietu SDK klienta liczba wierszy, których dotyczy zmiana, będzie dostępna w data._execute.

Przykład 3. Podstawowa agregacja

queries.gql:

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

Po uruchomieniu zapytania za pomocą pakietu SDK klienta wynik będzie dostępny w data.stats.total_reviews.

Przykład 4. Zaawansowana agregacja z 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: []
  )
}

Po uruchomieniu zapytania za pomocą pakietu SDK klienta wynik będzie dostępny w data._select.

Przykład 5. Instrukcja UPDATE z RETURNING i kontekstem uwierzytelniania

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"}]
  )
}

Po uruchomieniu mutacji za pomocą pakietu SDK klienta zaktualizowane dane posta będą dostępne w data.updatedReview.

Przykład 6. Zaawansowane CTE z upsertami (atomowe pobieranie lub tworzenie)

Ten wzorzec jest przydatny do zapewnienia, że rekordy zależne (np. użytkownicy lub filmy) istnieją przed wstawieniem rekordu podrzędnego (np. recenzji), a wszystko to w ramach jednej transakcji bazy danych.

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 i _executeReturningFirst opakowują zapytanie w nadrzędne CTE, aby sformatować dane wyjściowe jako JSON. PostgreSQL nie zezwala na zagnieżdżanie CTE modyfikującego dane w innej instrukcji modyfikującej dane, co powoduje niepowodzenie zapytania.

Przykład 7. Korzystanie z rozszerzeń Postgres

Natywny SQL umożliwia korzystanie z rozszerzeń Postgres, takich jak PostGIS, bez konieczności mapowania złożonych typów geometrii w schemacie GraphQL ani zmieniania tabel bazowych.

W tym przykładzie załóżmy, że aplikacja restauracji ma tabelę, która przechowuje dane o lokalizacji w kolumnie metadanych JSON (np. {"latitude": 37.3688, "longitude": -122.0363}). Jeśli masz włączone rozszerzenie PostGIS, możesz użyć standardowych operatorów JSON Postgres (->>), aby wyodrębnić te wartości w locie i przekazać je do funkcji 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]
  )
}

Po uruchomieniu zapytania za pomocą pakietu SDK klienta wynik będzie dostępny w data.nearby.

Sprawdzone metody dotyczące bezpieczeństwa: dynamiczny SQL i procedury przechowywane

SQL Connect bezpiecznie parametryzuje wszystkie dane wejściowe na granicy GraphQL i bazy danych, w pełni chroniąc standardową wersję SQL przed wstrzyknięciem kodu SQL pierwszego rzędu. Jeśli jednak używasz SQL do wywoływania niestandardowych procedur lub funkcji przechowywanych w Postgres, które wykonują dynamiczny kod SQL, musisz się upewnić, że wewnętrzny kod PL/pgSQL bezpiecznie obsługuje te parametry.

Jeśli procedura przechowywana bezpośrednio łączy dane wejściowe użytkownika z ciągiem EXECUTE, pomija parametryzację i tworzy lukę w zabezpieczeniach przed wstrzyknięciem kodu SQL drugiego rzędu:

-- 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;
$$;

Aby tego uniknąć, postępuj zgodnie z tymi sprawdzonymi metodami:

• Używaj klauzuli USING: podczas pisania dynamicznego kodu SQL w procedurach przechowywanych zawsze używaj klauzuli USING, aby bezpiecznie powiązać parametry danych.
• Używaj format() do identyfikatorów: używaj format() z flagą %I, aby bezpiecznie wstrzykiwać identyfikatory bazy danych (np. nazwy tabel).
• Ściśle zezwalaj na identyfikatory: nie pozwalaj aplikacjom klienckim na dowolne wybieranie identyfikatorów bazy danych. Jeśli procedura wymaga dynamicznych identyfikatorów, przed wykonaniem sprawdź dane wejściowe w zakodowanej na stałe liście dozwolonych w logice 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;
$$;