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. Questo è l'approccio standard, che offre una tipizzazione forte e strutture applicate dallo schema. La maggior parte della Data Connect documentazione al di fuori di questa pagina descrive questa opzione. Se possibile, devi utilizzare questo metodo per sfruttare la sicurezza dei tipi completa e il supporto degli strumenti.
• La direttiva @view: definisci un tipo GraphQL in schema.gql supportato da un' istruzione SQL SELECT personalizzata. Questa opzione è utile per creare viste di sola lettura con tipizzazione forte basate su una logica SQL complessa. Questi tipi sono interrogabili come i tipi normali. Consulta @view.
• SQL nativo: incorpora le istruzioni SQL direttamente nelle operazioni denominate nei file .gql utilizzando campi radice speciali. Questa opzione offre la massima flessibilità e il controllo diretto, in particolare per le operazioni non facilmente esprimibili in GraphQL standard, sfruttando le funzionalità specifiche del database o utilizzando le estensioni PostgreSQL. A differenza di GraphQL e della direttiva @view, l'SQL nativo non fornisce output con tipizzazione forte.

Questa guida si concentra sull'opzione SQL nativo.

Casi d'uso comuni per l'SQL nativo

Sebbene GraphQL nativo fornisca una sicurezza dei tipi completa e la direttiva @view offra risultati con tipizzazione forte per i report SQL di sola lettura, l'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, sottoquery, aggregazioni, funzioni finestra e stored procedure.
• Data Manipulation (DML): esegui le operazioni INSERT, UPDATE, DELETE direttamente. (Tuttavia, non utilizzare l'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 per 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:

Campi query

Campi mutation

Note:

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

• Se imposti esplicitamente il nome della tabella utilizzando la @table direttiva (@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

L'SQL nativo applica regole di analisi sintattica rigorose per garantire la sicurezza ed evitare 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 i parametri posizionali ($1, $2) che corrispondono all' params ordine dell'array. I parametri denominati ($id, :name) non sono supportati.
• Stringhe: sono supportati i valori letterali delle stringhe estese (E'...') e le stringhe tra virgolette con il simbolo del dollaro ($$...$$). Le sequenze di escape Unicode di PostgreSQL (U&'...') non sono supportate.

Parametri nei commenti

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

Esempi

Esempio 1: SELECT di base con alias dei campi

Puoi assegnare un alias al campo radice (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 saranno in data.updatedReview.

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

Questo pattern è utile per garantire l'esistenza di record dipendenti (come utenti o film) prima di inserire un record figlio (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

L'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 la tua app di ristoranti abbia una tabella che memorizza i dati sulla località in una colonna JSON di metadati (ad esempio, {"latitude": 37.3688, "longitude": -122.0363}). Se hai abilitato l'estensione PostGIS, puoi utilizzare gli operatori JSON Postgres standard (->>) per estrarre questi valori in tempo reale e passarli alla funzione ST_MakePoint di 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]
  )
}

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 limite tra GraphQL e database, proteggendo completamente le query SQL standard dall'SQL injection di primo ordine. Tuttavia, se utilizzi SQL per chiamare stored procedure o funzioni Postgres personalizzate che eseguono SQL dinamico, devi assicurarti che il codice PL/pgSQL interno gestisca questi parametri in modo sicuro.

Se la stored procedure 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 evitare questo problema, 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'iniezione sicura degli identificatori di database (come i nomi delle tabelle).
• Consenti solo gli identificatori: non consentire alle applicazioni client di scegliere arbitrariamente gli identificatori di database. Se la 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;
$$;