Implémenter des opérations Firebase Data Connect à l'aide de SQL natif

Firebase Data Connect propose plusieurs façons d'interagir avec votre base de données Cloud SQL :

  • GraphQL natif : définissez des types dans vos schema.gql et Data Connect, et traduisez vos opérations GraphQL en SQL. Il s'agit de l'approche standard, qui offre un typage fort et des structures imposées par le schéma. La plupart de la documentation Data Connect en dehors de cette page aborde cette option. Dans la mesure du possible, vous devez utiliser cette méthode pour profiter de la sécurité des types et de la prise en charge des outils.
  • Directive @view : définissez un type GraphQL dans schema.gql, soutenu par une instruction SQL SELECT personnalisée. Cela est utile pour créer des vues en lecture seule et fortement typées basées sur une logique SQL complexe. Ces types peuvent être interrogés comme des types standards. Consultez @view.
  • SQL natif : intégrez des instructions SQL directement dans les opérations nommées dans .Fichiers gql utilisant des champs racine spéciaux. Cela offre une flexibilité maximale et un contrôle direct, en particulier pour les opérations qui ne sont pas facilement exprimées dans GraphQL standard, qui exploitent des fonctionnalités spécifiques à la base de données ou qui utilisent des extensions PostgreSQL.

Ce guide se concentre sur l'option SQL natif.

Cas d'utilisation courants de SQL natif

Alors que GraphQL natif offre une sécurité de type complète et que la directive @view propose des résultats fortement typés pour les rapports SQL en lecture seule, le SQL natif offre la flexibilité nécessaire pour :

  • Extensions PostgreSQL : interrogez et utilisez directement n'importe quelle extension PostgreSQL installée (telle que PostGIS pour les données géospatiales) sans avoir à mapper des types complexes dans votre schéma GraphQL.
  • Requêtes complexes : exécutez des requêtes SQL complexes avec des jointures, des sous-requêtes, des agrégations, des fonctions de fenêtrage et des procédures stockées.
  • Manipulation de données (LMD) : effectuez directement des opérations INSERT, UPDATE, DELETE. (Toutefois, n'utilisez pas le langage SQL natif pour les commandes LDD (langage de définition de données). Vous devez continuer à modifier le schéma à l'aide de GraphQL pour que votre backend et les SDK générés restent synchronisés.)
  • Fonctionnalités spécifiques à la base de données : utilisez des fonctions, des opérateurs ou des types de données propres à PostgreSQL.
  • Optimisation des performances : ajustez manuellement les instructions SQL pour les chemins critiques.

Champs racine SQL natifs

Pour écrire des opérations avec SQL, utilisez l'un des champs racine des types query ou mutation :

query champs

Champ Description
_select

Exécute une requête SQL qui renvoie zéro ou plusieurs lignes.

Arguments :

  • sql : chaîne littérale de l'instruction SQL. Pour éviter les injections SQL, utilisez des espaces réservés positionnels ($1, $2, etc.) pour les valeurs de paramètres.
  • params : liste ordonnée de valeurs à associer aux espaces réservés. Cela peut inclure des littéraux, des variables GraphQL et des mappages de contexte spéciaux injectés par le serveur, comme {_expr: "auth.uid"} (ID de l'utilisateur authentifié).

Renvoie un tableau JSON ([Any]).
_selectFirst

Exécute une requête SQL qui doit renvoyer zéro ou une ligne.

Arguments :

  • sql : chaîne littérale de l'instruction SQL. Pour éviter les injections SQL, utilisez des espaces réservés positionnels ($1, $2, etc.) pour les valeurs de paramètres.
  • params : liste ordonnée de valeurs à associer aux espaces réservés. Cela peut inclure des littéraux, des variables GraphQL et des mappages de contexte spéciaux injectés par le serveur, comme {_expr: "auth.uid"} (ID de l'utilisateur authentifié).

Renvoie : un objet JSON (Any) ou null.

mutation champs

Champ Description
_execute

Exécute une instruction LMD (INSERT, UPDATE, DELETE).

Arguments :

  • sql : chaîne littérale de l'instruction SQL. Pour éviter les injections SQL, utilisez des espaces réservés positionnels ($1, $2, etc.) pour les valeurs de paramètres.

    Vous pouvez utiliser des expressions de table communes modifiant les données (par exemple, WITH new_row AS (INSERT...)) ici, car ce champ ne renvoie que le nombre de lignes. Seule la région _execute est compatible avec les CTE.

  • params : liste ordonnée de valeurs à associer aux espaces réservés. Cela peut inclure des littéraux, des variables GraphQL et des mappages de contexte spéciaux injectés par le serveur, comme {_expr: "auth.uid"} (ID de l'utilisateur authentifié).

Renvoie un Int (nombre de lignes concernées).

Les clauses RETURNING sont ignorées dans le résultat.
_executeReturning

Exécute une instruction LMD avec une clause RETURNING, en renvoyant zéro ligne ou plus.

Arguments :

  • sql : chaîne littérale de l'instruction SQL. Pour éviter les injections SQL, utilisez des espaces réservés positionnels ($1, $2, etc.) pour les valeurs de paramètres. Les expressions de table communes modifiant les données ne sont pas acceptées.
  • params : liste ordonnée de valeurs à associer aux espaces réservés. Cela peut inclure des littéraux, des variables GraphQL et des mappages de contexte spéciaux injectés par le serveur, comme {_expr: "auth.uid"} (ID de l'utilisateur authentifié).

Renvoie un tableau JSON ([Any]).
_executeReturningFirst

Exécute une instruction LMD avec une clause RETURNING, qui doit renvoyer zéro ou une ligne.

Arguments :

  • sql : chaîne littérale de l'instruction SQL. Pour éviter les injections SQL, utilisez des espaces réservés positionnels ($1, $2, etc.) pour les valeurs de paramètres. Les expressions de table communes modifiant les données ne sont pas acceptées.
  • params : liste ordonnée de valeurs à associer aux espaces réservés. Cela peut inclure des littéraux, des variables GraphQL et des mappages de contexte spéciaux injectés par le serveur, comme {_expr: "auth.uid"} (ID de l'utilisateur authentifié).

Renvoie : un objet JSON (Any) ou null.

Remarques :

  • Les opérations sont exécutées à l'aide des autorisations accordées au compte de service Data Connect.

  • Si vous définissez explicitement le nom de la table à l'aide de la directive @table (@table(name: "ExampleTable")), vous devez également mettre le nom de la table entre guillemets dans vos instructions SQL (SELECT field FROM "ExampleTable" ...).

    Sans guillemets, Data Connect convertit le nom de la table en snake case (example_table).

Règles et limites de syntaxe

Le SQL natif applique des règles d'analyse strictes pour assurer la sécurité et éviter l'injection SQL. Tenez compte des contraintes suivantes :

  • Commentaires : utilisez des commentaires de bloc (/* ... */). Les commentaires de ligne (--) sont interdits, car ils peuvent tronquer les clauses suivantes (comme les filtres de sécurité) lors de la concaténation des requêtes.
  • Paramètres : utilisez des paramètres positionnels ($1, $2) qui correspondent à l'ordre du tableau params. Les paramètres nommés ($id, :name) ne sont pas acceptés.
  • Chaînes : les littéraux de chaîne étendus (E'...') et les chaînes entre guillemets dollar ($$...$$) sont acceptés. Les séquences d'échappement Unicode PostgreSQL (U&'...') ne sont pas prises en charge.

Paramètres dans les commentaires

L'analyseur ignore tout ce qui se trouve à l'intérieur d'un commentaire de bloc. Si vous mettez en commentaire une ligne contenant un paramètre (par exemple, /* WHERE id = $1 */), vous devez également supprimer ce paramètre de la liste params. Sinon, l'opération échouera et l'erreur unused parameter: $1 s'affichera.

Exemples

Exemple 1 : SELECT de base avec alias de champ

Vous pouvez créer un alias pour le champ racine (par exemple, movies: _select) afin de rendre la réponse du client plus claire (data.movies au lieu de 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]
  )
}

Après avoir exécuté la requête à l'aide d'un SDK client, le résultat se trouve dans data.movies.

Exemple 2 : Instruction UPDATE de base

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

Après avoir exécuté la mutation à l'aide d'un SDK client, le nombre de lignes concernées se trouvera dans data._execute.

Exemple 3 : Agrégation de base

queries.gql :

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

Après avoir exécuté la requête à l'aide d'un SDK client, le résultat se trouve dans data.stats.total_reviews.

Exemple 4 : Agrégation avancée avec 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: []
  )
}

Après avoir exécuté la requête à l'aide d'un SDK client, le résultat se trouve dans data._select.

Exemple 5 : UPDATE avec RETURNING et le contexte d'authentification

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

Après avoir exécuté la mutation à l'aide d'un SDK client, les données de post mises à jour se trouveront dans data.updatedReview.

Exemple 6 : CTE avancé avec upserts (get-or-create atomique)

Ce modèle est utile pour s'assurer que les enregistrements dépendants (comme les utilisateurs ou les films) existent avant d'insérer un enregistrement enfant (comme un avis), le tout dans une seule transaction de base de données.

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

Exemple 7 : Utiliser les extensions Postgres

Le SQL natif vous permet d'utiliser des extensions Postgres, telles que PostGIS, sans avoir à mapper des types de géométrie complexes dans votre schéma GraphQL ni à modifier vos tables sous-jacentes.

Dans cet exemple, supposons que votre application de restaurant comporte un tableau qui stocke les données de localisation dans une colonne JSON de métadonnées (par exemple, {"latitude": 37.3688, "longitude": -122.0363}). Si vous avez activé l'extension PostGIS, vous pouvez utiliser les opérateurs JSON Postgres standards (->>) pour extraire ces valeurs à la volée et les transmettre à la fonction 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]
  )
}

Après avoir exécuté la requête à l'aide d'un SDK client, le résultat se trouve dans data.nearby.

Bonnes pratiques de sécurité : SQL dynamique et procédures stockées

Data Connect paramètre de manière sécurisée toutes les entrées à la limite entre GraphQL et la base de données, protégeant ainsi entièrement vos requêtes SQL standards contre l'injection SQL de premier ordre. Toutefois, si vous utilisez SQL pour appeler des procédures ou des fonctions stockées Postgres personnalisées qui exécutent du code SQL dynamique, vous devez vous assurer que votre code PL/pgSQL interne gère ces paramètres de manière sécurisée.

Si votre procédure stockée concatène directement les entrées utilisateur dans une chaîne EXECUTE, elle contourne la paramétrisation et crée une vulnérabilité d'injection SQL de second ordre :

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

Pour éviter cela, suivez les bonnes pratiques suivantes :

  • Utilisez la clause USING : lorsque vous écrivez du code SQL dynamique dans vos procédures stockées, utilisez toujours la clause USING pour lier les paramètres de données de manière sécurisée.
  • Utilisez format() pour les identifiants : utilisez format() avec l'indicateur %I pour une injection d'identifiants de base de données sécurisée (comme les noms de tables).
  • Autoriser strictement les identifiants : n'autorisez pas les applications clientes à choisir arbitrairement les identifiants de base de données. Si votre procédure nécessite des identifiants dynamiques, validez l'entrée par rapport à une liste d'autorisation codée en dur dans votre logique PL/pgSQL avant l'exécution.
-- 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;
$$;