Firebase SQL Connect-Vorgänge mit nativem SQL implementieren

Anleitung zum Schreiben von Firebase SQL Connect-Vorgängen mit SQL anstelle von GraphQL. page_type: guide announcement: > Natives SQL ist als Feature in der Vorschau verfügbar. Das bedeutet, dass es keinen SLAs oder Richtlinien zur Einstellung von Produkten und Diensten unterliegt und sich auf nicht abwärtskompatible Weise ändern kann. Wenn Sie dieses Feature mit gespeicherten Prozeduren oder Funktionen verwenden, die dynamisches SQL ausführen, beachten Sie die Best Practices für die Sicherheit , die unten auf dieser Seite erläutert werden.

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

• Natives GraphQL: Definieren Sie Typen in Ihrer schema.gql-Datei. SQL Connect übersetzt Ihre GraphQL-Vorgänge in SQL. Dies ist der Standardansatz, der eine starke Typisierung und schemabezogene Strukturen bietet. In den meisten SQL Connect Dokumenten außerhalb dieser Seite wird diese Option behandelt. Verwenden Sie diese Methode nach Möglichkeit, 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 von Standard-GraphQL nicht unterstützt werden, 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 wird die Option Natives SQL behandelt.

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 Geodaten), 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 (Data Manipulation Language, 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 SQL Connect Dienstkonto gewährt wurden.

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.

Namenskonventionen

Wenn Sie natives SQL schreiben, interagieren Sie direkt mit Ihrer PostgreSQL-Datenbank. Daher müssen Sie die tatsächlichen Datenbanknamen für Tabellen und Spalten verwenden. Standardmäßig ordnet SQL Connect die Namen in Ihrem GraphQL Schema automatisch Snake Case in der Datenbank zu, es sei denn, Sie passen die PostgreSQL-Kennungen explizit mit den @table(name) und @col(name) Direktiven an.

Wenn Sie einen Typ ohne Direktiven definieren, werden die GraphQL-Tabellen- und Feldnamen den Standard-PostgreSQL-Kennungen im Format snake_case zugeordnet:

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

Bei PostgreSQL-Kennungen wird standardmäßig nicht zwischen Groß- und Kleinschreibung unterschieden. Wenn Sie Direktiven wie @table oder @col verwenden, um einen Namen anzugeben, der Groß- oder Kleinbuchstaben enthält, müssen Sie diese Kennung in Ihren SQL-Anweisungen in doppelte Anführungszeichen setzen.

Im folgenden Beispiel müssen Sie "UserProfiles" für den Tabellennamen und "profileId" für die userId Spalte verwenden. Für das Feld displayName wird die Standardkonvertierung in display_name verwendet:

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

Anwendungsbeispiele

Beispiel 1: Einfache SELECT-Anweisung mit Feldaliasen

Sie können das Stammfeld (z. B. movies: _select) mit einem Alias versehen, 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!) @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 und _executeReturningFirst umschließen Ihre Abfrage in einer übergeordneten CTE, um die Ausgabe als JSON zu formatieren. PostgreSQL lässt es nicht zu, eine CTE, die Daten ändert, in einer anderen Anweisung zu verschachteln, die Daten ändert. Daher schlägt die Abfrage fehl.

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 Ihre 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

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