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: > Natywny SQL jest dostępny w wersji przedpremierowej, 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 lub funkcjami przechowywanymi, które wykonują dynamiczny kod SQL, postępuj zgodnie ze sprawdzonymi metodami dotyczącymi bezpieczeństwa opisanymi na dole tej strony.

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

  • Natywny GraphQL: zdefiniuj typy w 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ą dotyczy tej opcji. Jeśli to możliwe, używaj tej metody, aby w pełni wykorzystać bezpieczeństwo typów i obsługę narzędzi.
  • Dyrektywa @view: zdefiniuj typ GraphQL w schema.gql, który jest obsługiwany przez niestandardową instrukcję SQL SELECT. Przydaje się to do tworzenia widoków tylko do odczytu o silnym typowaniu, które są oparte na złożonej logice SQL. Te typy można wyszukiwać tak jak zwykłe typy. Zobacz @view.
  • Natywny SQL: umieszczaj instrukcje SQL bezpośrednio w nazwanych operacjach w .gqlplikach 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 wykorzystywania rozszerzeń PostgreSQL. W przeciwieństwie do GraphQL i dyrektywy @view natywny SQL nie zapewnia silnie typizowanych 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 typizowane wyniki w przypadku raportów SQL tylko do odczytu. Natywny SQL zapewnia jednak elastyczność potrzebną do:

  • Rozszerzenia PostgreSQL: bezpośrednio wysyłaj zapytania i używaj dowolnych zainstalowanych rozszerzeń 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 ze złączeniami, podzapytaniami, agregacjami, funkcjami okna i procedurami przechowywanymi.
  • Manipulowanie danymi (DML): wykonywanie operacji INSERT, UPDATE, DELETE bezpośrednio. (Nie używaj jednak natywnego SQL do poleceń języka definiowania danych (DDL). Zmiany na poziomie schematu musisz nadal wprowadzać za pomocą GraphQL, aby zachować synchronizację backendu i wygenerowanych pakietów SDK).
  • Funkcje specyficzne dla bazy danych: korzystaj z funkcji, operatorów lub typów danych unikalnych dla PostgreSQL.
  • Optymalizacja wydajności: ręczne dostrajanie instrukcji SQL na potrzeby ś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

Pole Opis
_select

Wykonuje zapytanie SQL, które zwraca zero lub więcej wierszy.

Argumenty:

  • sql: literał ciągu znaków instrukcji SQL. Aby zapobiec wstrzyknięciu kodu SQL, używaj symboli zastępczych pozycji ($1, $2 itd.) dla wartości parametrów.
  • params: uporządkowana lista wartości do powiązania z symbolami zastępczymi. Mogą to być literały, zmienne GraphQL i specjalne mapy kontekstu wstrzykiwane przez serwer, np. {_expr: "auth.uid"} (identyfikator uwierzytelnionego użytkownika).

Zwraca: tablicę JSON ([Any]).
_selectFirst

Wykonuje zapytanie SQL, które powinno zwrócić 0 lub 1 wiersz.

Argumenty:

  • sql: literał ciągu znaków instrukcji SQL. Aby zapobiec wstrzyknięciu kodu SQL, używaj symboli zastępczych pozycji ($1, $2 itd.) dla wartości parametrów.
  • params: uporządkowana lista wartości do powiązania z symbolami zastępczymi. Mogą to być literały, zmienne GraphQL i specjalne mapy kontekstu wstrzykiwane przez serwer, np. {_expr: "auth.uid"} (identyfikator uwierzytelnionego użytkownika).

Zwraca: obiekt JSON (Any) lubnull.

Pola: mutation

Pole Opis
_execute

Wykonuje instrukcję DML (INSERT, UPDATE, DELETE).

Argumenty:

  • sql: literał ciągu znaków instrukcji SQL. Aby zapobiec wstrzyknięciu kodu SQL, używaj symboli zastępczych pozycji ($1, $2 itd.) dla wartości parametrów.

    Możesz tu używać wyrażeń CTE modyfikujących dane (np. WITH new_row AS (INSERT...)), ponieważ to pole zwraca tylko liczbę wierszy. Tylko _execute obsługuje wyrażenia CTE.

  • params: uporządkowana lista wartości do powiązania z symbolami zastępczymi. Mogą to być literały, zmienne GraphQL i specjalne mapy kontekstu wstrzykiwane przez serwer, np. {_expr: "auth.uid"} (identyfikator uwierzytelnionego użytkownika).

Zwraca: Int (liczbę wierszy, na które wpłynęła zmiana).

Klauzule RETURNING są ignorowane w wyniku.
_executeReturning

Wykonuje instrukcję DML z klauzulą RETURNING i zwraca zero lub więcej wierszy.

Argumenty:

  • sql: literał ciągu znaków instrukcji SQL. Aby zapobiec wstrzyknięciu kodu SQL, używaj symboli zastępczych pozycji ($1, $2 itd.) dla wartości parametrów. Wyrażenia CTE modyfikujące dane nie są obsługiwane.
  • params: uporządkowana lista wartości do powiązania z symbolami zastępczymi. Mogą to być literały, zmienne GraphQL i specjalne mapy kontekstu wstrzykiwane przez serwer, np. {_expr: "auth.uid"} (identyfikator uwierzytelnionego użytkownika).

Zwraca: tablicę JSON ([Any]).
_executeReturningFirst

Wykonuje instrukcję DML z klauzulą RETURNING, która powinna zwrócić 0 lub 1 wiersz.

Argumenty:

  • sql: literał ciągu znaków instrukcji SQL. Aby zapobiec wstrzyknięciu kodu SQL, używaj symboli zastępczych pozycji ($1, $2 itd.) dla wartości parametrów. Wyrażenia CTE modyfikujące dane nie są obsługiwane.
  • params: uporządkowana lista wartości do powiązania z symbolami zastępczymi. Mogą to być literały, zmienne GraphQL i specjalne mapy kontekstu wstrzykiwane przez serwer, np. {_expr: "auth.uid"} (identyfikator uwierzytelnionego użytkownika).

Zwraca: obiekt JSON (Any) lubnull.

Uwagi:

  • Operacje są wykonywane z użyciem uprawnień udzielonych SQL Connectkontu usługi.

Reguły składni i ograniczenia

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

  • Komentarze: używaj komentarzy blokowych (/* ... */). Komentarze wierszowe (--) są zabronione, ponieważ mogą obcinać kolejne klauzule (np. filtry zabezpieczeń) podczas konkatenacji zapytań.
  • Parametry: używaj parametrów pozycyjnych ($1, $2), które pasują do kolejności tablicy params. Nazwane parametry ($id, :name) nie są obsługiwane.
  • Ciągi znaków: obsługiwane są rozszerzone literały ciągów znaków (E'...') i ciągi znaków w cudzysłowie dolarowym ($$...$$). Znaki ucieczki Unicode w 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 się nie powiedzie i wyświetli się błąd unused parameter: $1.

Konwencje nazewnictwa

Podczas pisania natywnego kodu SQL wchodzisz w interakcję bezpośrednio z bazą danych PostgreSQL, więc 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ą dyrektyw @table(name) i @col(name).

Jeśli zdefiniujesz typ bez dyrektyw, nazwy tabeli i pól GraphQL będą mapowane na domyślne identyfikatory snake_case PostgreSQL:

schema.gql queries.gql 
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 lub mieszane litery, musisz ująć ten identyfikator w cudzysłów w instrukcjach SQL.

W tym przykładzie musisz użyć "UserProfiles" jako nazwy tabeli i "profileId" jako kolumny userId. Pole displayName jest zgodne z domyślną konwersją na display_name:

schema.gql queries.gql 
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. Podstawowe zapytanie SELECT z aliasami pól

Możesz utworzyć alias pola głównego (np. movies: _select), aby odpowiedź klienta była bardziej przejrzysta (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 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 zmodyfikowanych wierszy będzie podana w data._execute.

Przykład 3. Agregacja podstawowa

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 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 w data._select.

Przykład 5. UPDATE z RETURNING i kontekstem autoryzacji

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ą w data.updatedReview.

Przykład 6. Zaawansowane wyrażenie CTE z operacjami upsert (atomowe pobieranie lub tworzenie)

Ten wzorzec jest przydatny, gdy chcesz mieć pewność, że rekordy zależne (np. użytkownicy lub filmy) istnieją przed wstawieniem rekordu podrzędnego (np. recenzji) w ramach jednej transakcji w bazie 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 otaczają zapytanie nadrzędnym wyrażeniem CTE, aby sformatować dane wyjściowe jako JSON. PostgreSQL nie zezwala na zagnieżdżanie CTE modyfikujących dane w innych instrukcjach modyfikujących 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 na schemat GraphQL ani modyfikowania tabel bazowych.

W tym przykładzie załóżmy, że aplikacja restauracji ma tabelę, która przechowuje dane o lokalizacji w kolumnie JSON z metadanymi (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 w data.nearby.

Sprawdzone metody zapewniania bezpieczeństwa: dynamiczny SQL i procedury przechowywane

SQL Connect bezpiecznie parametryzuje wszystkie dane wejściowe na granicy GraphQL-baza danych, w pełni chroniąc standardową wersję SQL przed wstrzykiwaniem kodu SQL pierwszego rzędu. Jeśli jednak używasz SQL do wywoływania niestandardowych procedur składowanych lub funkcji PostgreSQL, które wykonują dynamiczny SQL, musisz zadbać o to, aby wewnętrzny kod PL/pgSQL bezpiecznie obsługiwał te parametry.

Jeśli procedura składowana bezpośrednio łączy dane wejściowe użytkownika w EXECUTEciąg znaków, pomija parametryzację i tworzy lukę w zabezpieczeniach, która umożliwia wstrzyknięcie 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 składowanych zawsze używaj klauzuli USING, aby bezpiecznie wiązać parametry danych.
  • Używaj format() w przypadku identyfikatorów: używaj format() z flagą %I, aby bezpiecznie wstrzykiwać identyfikatory bazy danych (np. nazwy tabel).
  • Ściśle zezwalaj na identyfikatory: nie zezwalaj aplikacjom klienckim na dowolne wybieranie identyfikatorów bazy danych. Jeśli procedura wymaga dynamicznych identyfikatorów, przed wykonaniem sprawdź dane wejściowe pod kątem 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;
$$;