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 żadnej gwarancji jakości usług ani zasadom wycofywania i może ulec zmianie w sposób niezapewniający zgodności wstecznej. 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 tylko do odczytu, silnie typowanych widoków opartych na złożonej logice SQL. Te typy można wysyłać do nich zapytania tak jak do zwykłych typów. 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

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 operacje INSERT, UPDATE, DELETE bezpośrednio. (Nie używaj jednak natywnego SQL do poleceń DDL (Data Definition Language). 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 w przypadku ś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 łączenia 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 PostgreSQL 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 PostgreSQL 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 czy @col, aby określić nazwę zawierającą wielkie lub mieszane litery, 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 aliasami 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 funkcją 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 klauzulą 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 operacjami upsert (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ń PostgreSQL

Natywny SQL umożliwia korzystanie z rozszerzeń PostgreSQL, 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żywać standardowych operatorów JSON PostgreSQL (->>), aby wyodrębniać te wartości na bieżąco i przekazywać 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 standardowe zapytania SQL przed wstrzyknięciem kodu SQL pierwszego rzędu. Jeśli jednak używasz SQL do wywoływania niestandardowych procedur lub funkcji przechowywanych w PostgreSQL, 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 stosunku do zakodowanej na stałe listy 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;
$$;