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

Guide pour écrire des opérations Firebase SQL Connect avec SQL au lieu de GraphQL. page_type: guide announcement: > Le langage SQL natif est disponible en version Preview, ce qui signifie qu'il n'est soumis à aucun contrat de niveau de service ni à aucun règlement relatif aux abandons, et qu'il peut être modifié d'une manière qui empêche sa rétrocompatibilité. Si vous utilisez cette fonctionnalité avec des procédures stockées ou des fonctions qui exécutent du code SQL dynamique, suivez les bonnes pratiques de sécurité expliquées en bas de cette page.

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

  • GraphQL natif : définissez des types dans votre schema.gql et SQL Connect traduit vos opérations GraphQL en SQL. Il s'agit de l'approche standard, qui offre un typage fort et des structures appliquées par le schéma. La plupart de la documentation SQL Connect en dehors de cette page traite de cette option. Dans la mesure du possible, vous devez utiliser cette méthode pour profiter d'une sécurité des types et d'une assistance des outils complètes.
  • Directive @view : définissez un type GraphQL dans schema.gql basé sur une instruction SQL personnalisée SELECT. 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 des opérations nommées dans des .gql fichiers à l’aide de champs racine spéciaux. Cela offre une flexibilité maximale et un contrôle direct, en particulier pour les opérations non compatibles avec GraphQL standard, en tirant parti des fonctionnalités spécifiques à la base de données ou en utilisant des extensions PostgreSQL. Contrairement à GraphQL et à la directive @view, le langage SQL natif ne fournit pas de sortie fortement typée.

Ce guide se concentre sur l'option SQL natif.

Cas d'utilisation courants du langage SQL natif

Alors que GraphQL natif offre une sûreté du typage complète et que la directive @view fournit des résultats fortement typés pour les rapports SQL en lecture seule, le langage 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, agrégations, des fonctions de fenêtre et des procédures stockées.
  • Manipulation des données (LMD) : effectuez directement des opérations INSERT, UPDATE, DELETE. (Toutefois, n'utilisez pas le langage SQL natif pour les commandes du langage de définition de données (LDD). Vous devez continuer à apporter des modifications au niveau du schéma à l'aide de GraphQL pour synchroniser votre backend et les SDK généré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 suivants des types query ou mutation :

Champs query

Champ Description
_select

Exécute une requête SQL qui renvoie zéro ligne ou plus.

Arguments:

  • sql : littéral de chaîne de l'instruction SQL. Pour éviter l'injection SQL, utilisez des espaces réservés positionnels ($1, $2, etc.) pour les valeurs des paramètres.
  • params : liste ordonnée de valeurs à lier 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, tels que {_expr: "auth.uid"} (l'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 : littéral de chaîne de l'instruction SQL. Pour éviter l'injection SQL, utilisez des espaces réservés positionnels ($1, $2, etc.) pour les valeurs des paramètres.
  • params : liste ordonnée de valeurs à lier 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, tels que {_expr: "auth.uid"} (l'ID de l'utilisateur authentifié).

Renvoie : un objet JSON (Any) ou null.

Champs mutation

Champ Description
_execute

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

Arguments:

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

    Vous pouvez utiliser ici des expressions de table communes modifiant les données (for example, WITH new_row AS (INSERT...)), car ce champ ne renvoie que le nombre de lignes. Seul _execute est compatible avec les expressions de table communes.

  • params : liste ordonnée de valeurs à lier 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, tels que {_expr: "auth.uid"} (l'ID de l'utilisateur authentifié).

Renvoie : un Int (nombre de lignes affectées).

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

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

Arguments:

  • sql : littéral de chaîne de l'instruction SQL. Pour éviter l'injection SQL, utilisez des espaces réservés positionnels ($1, $2, etc.) pour les valeurs des paramètres. Les expressions de table communes modifiant les données ne sont pas compatibles.
  • params : liste ordonnée de valeurs à lier 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, tels que {_expr: "auth.uid"} (l'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 : littéral de chaîne de l'instruction SQL. Pour éviter l'injection SQL, utilisez des espaces réservés positionnels ($1, $2, etc.) pour les valeurs des paramètres. Les expressions de table communes modifiant les données ne sont pas compatibles.
  • params : liste ordonnée de valeurs à lier 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, tels que {_expr: "auth.uid"} (l'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 SQL Connect compte de service.

Règles de syntaxe et limites

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

  • Commentaires : utilisez des commentaires de bloc (/* ... */). Les commentaires de ligne (--) sont interdits, car ils peuvent tronquer les clauses suivantes (telles que 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 compatibles.
  • Chaînes : les littéraux de chaîne étendus (E'...') et les chaînes entre guillemets dollar ($$...$$) sont compatibles. Les séquences d'échappement Unicode PostgreSQL (U&'...') ne sont pas compatibles.

Paramètres dans les commentaires

L'analyseur ignore tout ce qui se trouve dans un commentaire de bloc. Si vous commentez 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 avec l'erreur unused parameter: $1.

Conventions d'attribution de noms

Lorsque vous écrivez du code SQL natif, vous interagissez directement avec votre base de données PostgreSQL. Vous devez donc utiliser les noms de base de données réels pour les tables et les colonnes. Par défaut, SQL Connect mappe automatiquement les noms de votre schéma GraphQL au format snake_case dans la base de données, sauf si vous personnalisez explicitement les identifiants PostgreSQL à l'aide des @table(name) et @col(name) directives.

Si vous définissez un type sans directives, les noms de table et de champ GraphQL sont mappés aux identifiants PostgreSQL snake_case par défaut :

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

Par défaut, les identifiants PostgreSQL ne sont pas sensibles à la casse. Si vous utilisez des directives telles que @table ou @col pour spécifier un nom contenant des lettres majuscules ou mixtes, vous devez placer cet identifiant entre guillemets doubles dans vos instructions SQL.

Dans l'exemple suivant, vous devez utiliser "UserProfiles" pour le nom de la table et "profileId" pour la colonne userId. Le champ displayName suit la conversion par défaut en 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]
  )
}

Exemples d'utilisation

Exemple 1 : Instruction 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 propre (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 trouvera 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 affecté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 trouvera 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 trouvera dans data._select.

Exemple 5 : Instruction UPDATE avec RETURNING et 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 : Expression de table commune avancée avec upserts (get-or-create atomique)

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

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 et _executeReturningFirst encapsulent votre requête dans une expression de table commune parente pour mettre en forme la sortie au format JSON. PostgreSQL n'autorise pas l'imbrication d'une expression de table commune modifiant les données dans une autre instruction modifiant les données, ce qui entraîne l'échec de la requête.

Exemple 7 : Utiliser des extensions PostgreSQL

Le langage SQL natif vous permet d'utiliser des extensions PostgreSQL, 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 une table 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 PostgreSQL standards (->>) pour extraire ces valeurs à la volée et les transmettre à la fonction ST_MakePoint de 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]
  )
}

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

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

SQL Connect paramétrise 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 stockées ou des fonctions PostgreSQL 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 ces bonnes pratiques :

  • 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 sécurisée d'identifiants de base de données (tels que les noms de tables).
  • Autorisez strictement les identifiants : ne laissez 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;
$$;