Implementare le operazioni di Firebase Data Connect utilizzando SQL nativo

Firebase Data Connect offre diversi modi per interagire con il database Cloud SQL:

• GraphQL nativo: definisci i tipi in schema.gql e Data Connect traduce le operazioni GraphQL in SQL. Si tratta dell'approccio standard, che offre una tipizzazione e strutture con schema applicato. La maggior parte della documentazione di Data Connect al di fuori di questa pagina descrive questa opzione. Se possibile, devi utilizzare questo metodo per sfruttare la sicurezza dei tipi e il supporto degli strumenti.
• Direttiva @view: definisci un tipo GraphQL in schema.gql supportato da un'istruzione SQL SELECT personalizzata. Ciò è utile per creare viste di sola lettura, con tipizzazione forte basate su una logica SQL complessa. Questi tipi sono interrogabili come i tipi normali. Vedi @view.
• SQL nativo: incorpora le istruzioni SQL direttamente nelle operazioni denominate in .gql utilizzando campi radice speciali. Ciò offre la massima flessibilità e il controllo diretto, in particolare per le operazioni non facilmente esprimibili in GraphQL standard, sfruttando funzionalità specifiche del database o utilizzando le estensioni PostgreSQL.

Questa guida si concentra sull'opzione SQL nativo.

Casi d'uso comuni per SQL nativo

Mentre GraphQL nativo fornisce la sicurezza completa dei tipi e la direttiva @view offre risultati con tipizzazione avanzata per i report SQL di sola lettura, SQL nativo offre la flessibilità necessaria per:

• Estensioni PostgreSQL: esegui query e utilizza direttamente qualsiasi estensione PostgreSQL installata (ad esempio PostGIS per i dati geospaziali) senza dover mappare tipi complessi nello schema GraphQL.
• Query complesse: esegui SQL complessi con join, subquery, aggregazioni, funzioni finestra e stored procedure.
• Data Manipulation (DML): esegui direttamente le operazioni INSERT, UPDATE, DELETE. Tuttavia, non utilizzare SQL nativo per i comandi DDL (Data Definition Language). Devi continuare ad apportare modifiche a livello di schema utilizzando GraphQL per mantenere sincronizzati il backend e gli SDK generati.)
• Funzionalità specifiche del database: utilizza funzioni, operatori o tipi di dati univoci di PostgreSQL.
• Ottimizzazione delle prestazioni: ottimizza manualmente le istruzioni SQL per i percorsi critici.

Campi radice SQL nativi

Per scrivere operazioni con SQL, utilizza uno dei seguenti campi radice dei tipi query o mutation:

query campi

mutation campi

Note:

• Le operazioni vengono eseguite utilizzando le autorizzazioni concesse al service account Data Connect.

• Se imposti esplicitamente il nome della tabella utilizzando la direttiva @table (@table(name: "ExampleTable")), devi anche racchiudere il nome della tabella tra virgolette nelle istruzioni SQL (SELECT field FROM "ExampleTable" ...).

  Senza le virgolette, Data Connect convertirà il nome della tabella in snake case (example_table).

Regole e limitazioni della sintassi

SQL nativo applica regole di analisi rigorose per garantire la sicurezza e impedire l'SQL injection. Tieni presente i seguenti vincoli:

• Commenti: utilizza i commenti a blocchi (/* ... */). I commenti di riga (--) sono vietati perché possono troncare le clausole successive (come i filtri di sicurezza) durante la concatenazione delle query.
• Parametri: utilizza parametri posizionali ($1, $2) che corrispondono all'ordine dell'array params. I parametri denominati ($id, :name) non sono supportati.
• Stringhe: sono supportati i valori letterali stringa estesi (E'...') e le stringhe racchiuse tra virgolette e dollaro ($$...$$). Le sequenze di escape Unicode PostgreSQL (U&'...') non sono supportate.

Parametri nei commenti

Il parser ignora tutto ciò che si trova all'interno di un commento a blocchi. Se inserisci un commento in una riga contenente un parametro (ad esempio /* WHERE id = $1 */), devi anche rimuovere il parametro dall'elenco params, altrimenti l'operazione non andrà a buon fine e verrà visualizzato l'errore unused parameter: $1.

Esempi

Esempio 1: SELECT di base con alias dei campi

Puoi creare un alias per il campo principale (ad esempio movies: _select) per rendere più pulita la risposta del client (data.movies anziché 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]
  )
}

Dopo aver eseguito la query utilizzando un SDK client, il risultato sarà in data.movies.

Esempio 2: UPDATE di 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]
  )
}

Dopo aver eseguito la mutazione utilizzando un SDK client, il numero di righe interessate sarà in data._execute.

Esempio 3: aggregazione di base

queries.gql:

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

Dopo aver eseguito la query utilizzando un SDK client, il risultato sarà in data.stats.total_reviews.

Esempio 4: aggregazione avanzata con 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: []
  )
}

Dopo aver eseguito la query utilizzando un SDK client, il risultato sarà in data._select.

Esempio 5: UPDATE con RETURNING e contesto di autenticazione

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

Dopo aver eseguito la mutazione utilizzando un SDK client, i dati del post aggiornati si troveranno in data.updatedReview.

Esempio 6: CTE avanzata con upsert (get-or-create atomico)

Questo pattern è utile per garantire che i record dipendenti (come Utenti o Film) esistano prima di inserire un record secondario (come una Recensione), il tutto in una singola transazione di database.

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

Esempio 7: utilizzo delle estensioni Postgres

SQL nativo ti consente di utilizzare le estensioni Postgres, come PostGIS, senza dover mappare tipi di geometria complessi nello schema GraphQL o modificare le tabelle sottostanti.

In questo esempio, supponiamo che l'app del tuo ristorante abbia una tabella che memorizza i dati sulla posizione in una colonna JSON di metadati (ad esempio, {"latitude": 37.3688, "longitude": -122.0363}). Se hai attivato l'estensione PostGIS, puoi utilizzare gli operatori JSON standard di Postgres (->>) per estrarre questi valori al volo e passarli alla funzione 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]
  )
}

Dopo aver eseguito la query utilizzando un SDK client, il risultato sarà in data.nearby.

Best practice per la sicurezza: SQL dinamico e stored procedure

Data Connect parametrizza in modo sicuro tutti gli input al confine tra GraphQL e il database, proteggendo completamente le query SQL standard dall'SQL injection di primo ordine. Tuttavia, se utilizzi SQL per chiamare procedure o funzioni personalizzate Postgres memorizzate che eseguono SQL dinamico, devi assicurarti che il codice PL/pgSQL interno gestisca questi parametri in modo sicuro.

Se la procedura archiviata concatena direttamente gli input dell'utente in una stringa EXECUTE, ignora la parametrizzazione e crea una vulnerabilità di SQL injection di secondo ordine:

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

Per evitarlo, segui queste best practice:

• Utilizza la clausola USING: quando scrivi SQL dinamico nelle stored procedure, utilizza sempre la clausola USING per associare i parametri dei dati in modo sicuro.
• Utilizza format() per gli identificatori: utilizza format() con il flag %I per l'inserimento sicuro dell'identificatore del database (come i nomi delle tabelle).
• Consenti rigorosamente gli identificatori: non consentire alle applicazioni client di scegliere arbitrariamente gli identificatori di database. Se la tua procedura richiede identificatori dinamici, convalida l'input rispetto a una lista consentita hardcoded all'interno della logica PL/pgSQL prima dell'esecuzione.

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