Implementowanie operacji Firebase Data Connect za pomocą natywnego języka SQL

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

• Natywny GraphQL: zdefiniuj typy w schema.gql i Data Connect, a usługa przetłumaczy operacje GraphQL na SQL. Jest to standardowe podejście, które zapewnia silne typowanie i struktury wymuszane przez schemat. Większość dokumentacji Data Connect poza tą stroną omawia tę opcję. Jeśli to możliwe, używaj tej metody, aby w pełni wykorzystać zabezpieczenia wpisywania 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 na podstawie złożonej logiki SQL. Te typy można wyszukiwać tak jak zwykłe typy. Zobacz @view.
• Natywny SQL: osadzaj instrukcje SQL bezpośrednio w nazwanych operacjach w .gql plików za pomocą specjalnych pól głównych. Zapewnia to maksymalną elastyczność i bezpośrednią kontrolę, zwłaszcza w przypadku operacji, których nie można łatwo wyrazić w standardowym języku GraphQL, korzystając z funkcji specyficznych dla bazy danych lub rozszerzeń PostgreSQL.

W tym przewodniku skupimy się na opcji Native SQL.

Typowe przypadki użycia natywnego SQL

Natywny GraphQL zapewnia pełne bezpieczeństwo wpisywania, 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 wykonywać operacje zapisu 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ń udzielonych kontu usługi Data Connect.

• Jeśli jawnie ustawisz nazwę tabeli za pomocą dyrektywy @table (@table(name: "ExampleTable")), musisz też ująć nazwę tabeli w cudzysłów w instrukcjach SQL (SELECT field FROM "ExampleTable" ...).

  Bez cudzysłowów funkcja Data Connect przekonwertuje nazwę tabeli na format snake case (example_table).

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 z dolarem ($$...$$). Znaki ucieczki 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 i wyświetli się błąd unused parameter: $1.

Przykłady

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. Podstawowe polecenie 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!) {
  _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]
  )
}

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 na schemat GraphQL ani modyfikowania tabel bazowych.

W tym przykładzie załóżmy, że aplikacja restauracji ma tabelę, w której dane o lokalizacji są przechowywane w kolumnie JSON z metadanymi (np. {"latitude": 37.3688, "longitude": -122.0363}). Jeśli masz włączone rozszerzenie PostGIS, możesz użyć standardowych operatorów JSON Postgres (->>), aby na bieżąco wyodrębniać te wartości i przekazywać je do funkcji ST_MakePoint PostGIS.

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

Data 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 Postgres, 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 w odniesieniu 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;
$$;