Firebase Data Connect-Vorgänge mit nativem SQL implementieren

Firebase Data Connect bietet mehrere Möglichkeiten zur Interaktion mit Ihrer Cloud SQL Datenbank:

• Natives GraphQL: Definieren Sie Typen in Ihrer schema.gql und Data Connect übersetzt Ihre GraphQL-Vorgänge in SQL. Dies ist der Standardansatz, der eine starke Typisierung und schemabezogene Strukturen bietet. In den meisten Data Connect Dokumenten außerhalb dieser Seite wird diese Option behandelt. Wenn möglich, sollten Sie diese Methode verwenden, um die vollständige Typsicherheit und Toolunterstützung zu nutzen.
• Die Direktive @view: Definieren Sie einen GraphQL-Typ in schema.gql, der durch eine benutzerdefinierte SELECT SQL-Anweisung unterstützt wird. Dies ist nützlich, um schreibgeschützte, stark typisierte Ansichten basierend auf komplexer SQL-Logik zu erstellen. Diese Typen können wie reguläre Typen abgefragt werden. Weitere Informationen finden Sie unter @view.
• Natives SQL: Betten Sie SQL-Anweisungen direkt in benannte Vorgänge in .gql-Dateien ein, indem Sie spezielle Stammfelder verwenden. Dies bietet maximale Flexibilität und direkte Kontrolle, insbesondere für Vorgänge, die nicht einfach in Standard-GraphQL ausgedrückt werden können, datenbankspezifische Funktionen nutzen oder PostgreSQL-Erweiterungen verwenden. Im Gegensatz zu GraphQL und der Direktive @view bietet natives SQL keine stark typisierte Ausgabe.

In dieser Anleitung geht es um die Option Natives SQL.

Häufige Anwendungsfälle für natives SQL

Während natives GraphQL vollständige Typsicherheit bietet und die Direktive @view stark typisierte Ergebnisse für schreibgeschützte SQL-Berichte bietet, bietet natives SQL die Flexibilität, die für Folgendes erforderlich ist:

• PostgreSQL-Erweiterungen: Fragen Sie direkt alle installierten PostgreSQL Erweiterungen ab und verwenden Sie sie (z. B. PostGIS für geografische Daten), ohne komplexe Typen in Ihrem GraphQL-Schema zu zuordnen.
• Komplexe Abfragen: Führen Sie komplexe SQL-Abfragen mit Joins, Unterabfragen, Aggregationen, Fensterfunktionen und gespeicherten Prozeduren aus.
• Datenbearbeitung (DML): Führen Sie INSERT, UPDATE, DELETE Vorgänge direkt aus. Verwenden Sie natives SQL jedoch nicht für DDL-Befehle (Data Definition Language). Sie müssen weiterhin Änderungen auf Schemaebene mit GraphQL vornehmen, damit Ihr Back-End und die generierten SDKs synchron bleiben.
• Datenbankspezifische Funktionen: Verwenden Sie Funktionen, Operatoren oder Datentypen die nur in PostgreSQL verfügbar sind.
• Leistungsoptimierung: Optimieren Sie SQL-Anweisungen manuell für kritische Pfade.

Stammfelder für natives SQL

Verwenden Sie eines der folgenden Stammfelder der Typen query oder mutation, um Vorgänge mit SQL zu schreiben:

query-Felder

mutation-Felder

Hinweise:

• Vorgänge werden mit den Berechtigungen ausgeführt, die dem Data Connect Dienstkonto gewährt wurden.

• Wenn Sie den Tabellennamen explizit mit der @table Direktive (@table(name: "ExampleTable")) festlegen, müssen Sie den Tabellennamen auch in Ihren SQL-Anweisungen in Anführungszeichen setzen (SELECT field FROM "ExampleTable" ...).

  Ohne die Anführungszeichen konvertiert Data Connect den Tabellen namen in Snake Case (example_table).

Syntaxregeln und Einschränkungen

Natives SQL erzwingt strenge Parsingregeln, um die Sicherheit zu gewährleisten und SQL-Injection zu verhindern. Beachten Sie folgende Einschränkungen:

• Kommentare: Verwenden Sie Blockkommentare (/* ... */). Zeilenkommentare (--) sind nicht zulässig, da sie nachfolgende Klauseln (z. B. Sicherheits filter) während der Abfrageverkettung abschneiden können.
• Parameter: Verwenden Sie Positionsparameter ($1, $2), die der Reihenfolge des params-Arrays entsprechen. Benannte Parameter ($id, :name) werden nicht unterstützt.
• Strings: Erweiterte Stringliterale (E'...') und Strings mit Dollarzeichen ($$...$$) werden unterstützt. PostgreSQL-Unicode-Escapes (U&'...') werden nicht unterstützt.

Parameter in Kommentaren

Der Parser ignoriert alles innerhalb eines Blockkommentars. Wenn Sie eine Zeile mit einem Parameter auskommentieren (z. B. /* WHERE id = $1 */), müssen Sie diesen Parameter auch aus der Liste params entfernen. Andernfalls schlägt der Vorgang mit dem Fehler unused parameter: $1 fehl.

Beispiele

Beispiel 1: Einfache SELECT-Anweisung mit Feldaliasen

Sie können das Stammfeld aliasieren (z. B. movies: _select), um die Clientantwort übersichtlicher zu gestalten (data.movies anstelle von 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]
  )
}

Nachdem Sie die Abfrage mit einem Client-SDK ausgeführt haben, befindet sich das Ergebnis in data.movies.

Beispiel 2: Einfache UPDATE-Anweisung

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

Nachdem Sie die Mutation mit einem Client-SDK ausgeführt haben, befindet sich die Anzahl der betroffenen Zeilen in data._execute.

Beispiel 3: Einfache Aggregation

queries.gql:

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

Nachdem Sie die Abfrage mit einem Client-SDK ausgeführt haben, befindet sich das Ergebnis in data.stats.total_reviews.

Beispiel 4: Erweiterte Aggregation mit 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: []
  )
}

Nachdem Sie die Abfrage mit einem Client-SDK ausgeführt haben, befindet sich das Ergebnis in data._select.

Beispiel 5: UPDATE-Anweisung mit RETURNING und Auth-Kontext

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

Nachdem Sie die Mutation mit einem Client-SDK ausgeführt haben, befinden sich die aktualisierten Postdaten in data.updatedReview.

Beispiel 6: Erweiterte CTE mit Upserts (atomares Get-or-Create)

Dieses Muster ist nützlich, um sicherzustellen, dass abhängige Datensätze (z. B. Nutzer oder Filme) vorhanden sind, bevor ein untergeordneter Datensatz (z. B. eine Rezension) eingefügt wird. Dies geschieht in einer einzigen Datenbanktransaktion.

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

Beispiel 7: PostgreSQL-Erweiterungen verwenden

Mit nativem SQL können Sie PostgreSQL-Erweiterungen wie PostGIS verwenden, ohne komplexe Geometrietypen in Ihr GraphQL-Schema zu übertragen oder die zugrunde liegenden Tabellen zu ändern.

Angenommen, Ihre Restaurant-App hat eine Tabelle, in der Standort daten in einer JSON-Spalte mit Metadaten gespeichert sind (z. B. {"latitude": 37.3688, "longitude": -122.0363}). Wenn Sie die PostGIS-Erweiterung aktiviert haben, können Sie mit den Standard-PostgreSQL-JSON-Operatoren (->>) diese Werte im laufenden Betrieb extrahieren und an die PostGIS-Funktion ST_MakePoint übergeben.

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

Nachdem Sie die Abfrage mit einem Client-SDK ausgeführt haben, befindet sich das Ergebnis in data.nearby.

Best Practices für die Sicherheit: Dynamisches SQL und gespeicherte Prozeduren

Data Connect parametrisiert alle Eingaben an der Grenze zwischen GraphQL und Datenbank sicher und schützt Ihre Standard-SQL-Abfragen vollständig vor SQL-Injection erster Ordnung. Wenn Sie jedoch SQL verwenden, um benutzerdefinierte gespeicherte PostgreSQL-Prozeduren oder -Funktionen aufzurufen, die dynamisches SQL ausführen, müssen Sie sicherstellen, dass Ihr interner PL/pgSQL-Code diese Parameter sicher verarbeitet.

Wenn Ihre gespeicherte Prozedur Nutzereingaben direkt in einen EXECUTE-String verkettet, umgeht sie die Parametrisierung und erzeugt eine SQL-Injection-Schwachstelle zweiter Ordnung:

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

Beachten Sie folgende Best Practices, um dies zu vermeiden:

• Verwenden Sie die Klausel USING: Wenn Sie dynamisches SQL in Ihren gespeicherten Prozeduren schreiben, verwenden Sie immer die Klausel USING, um Datenparameter sicher zu binden.
• Verwenden Sie format() für Kennungen: Verwenden Sie format() mit dem Flag %I für die sichere Einfügung von Datenbankkennungen (z. B. Tabellennamen).
• Kennungen streng zulassen: Lassen Sie nicht zu, dass Clientanwendungen Datenbankkennungen beliebig auswählen. Wenn Ihre Prozedur dynamische Kennungen erfordert, validieren Sie die Eingabe vor der Ausführung anhand einer fest codierten Zulassungsliste in Ihrer PL/pgSQL-Logik.

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