Implementare le operazioni di Firebase SQL Connect utilizzando SQL nativo

Una guida alla scrittura di operazioni Firebase SQL Connect con SQL anziché GraphQL. page_type: guide announcement: > SQL nativo è disponibile come anteprima della funzionalità, il che significa che non è soggetto a SLA (accordo sul livello del servizio) o a norme sul ritiro e potrebbe essere modificato in modi incompatibili con le versioni precedenti. Se utilizzi questa funzionalità con stored procedure o funzioni che eseguono SQL dinamico, segui le best practice di sicurezza spiegate in fondo a questa pagina.

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

• GraphQL nativo: definisci i tipi in schema.gql e SQL Connect traduce le operazioni GraphQL in SQL. Questo è l'approccio standard, che offre strutture con tipizzazione e schema rigidi. La maggior parte della documentazione di SQL Connect al di fuori di questa pagina descrive questa opzione. Se possibile, devi utilizzare questo metodo per sfruttare la sicurezza completa 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 istruzioni SQL direttamente nelle operazioni denominate nei file .gql utilizzando campi radice speciali. Ciò offre la massima flessibilità e controllo diretto, soprattutto per le operazioni non supportate da GraphQL standard, sfruttando funzionalità specifiche del database o utilizzando le estensioni PostgreSQL. A differenza di GraphQL e della direttiva @view, SQL nativo non fornisce output fortemente tipizzato.

Questa guida si concentra sull'opzione SQL nativo.

Casi d'uso comuni per SQL nativo

Mentre GraphQL nativo fornisce la sicurezza dei tipi completa 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 SQL Connect.

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 tra virgolette con il simbolo del 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.

Convenzioni di denominazione

Quando scrivi SQL nativo, interagisci direttamente con il database PostgreSQL, quindi devi utilizzare i nomi effettivi del database per tabelle e colonne. Per impostazione predefinita, SQL Connect mappa automaticamente i nomi nello schema GraphQL in snake case nel database, a meno che tu non personalizzi esplicitamente gli identificatori Postgres utilizzando le direttive @table(name) e @col(name).

Se definisci un tipo senza direttive, i nomi dei campi e delle tabelle GraphQL vengono mappati agli identificatori Postgres snake_case predefiniti:

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

Per impostazione predefinita, gli identificatori PostgreSQL non distinguono tra maiuscole e minuscole. Se utilizzi direttive come @table o @col per specificare un nome che contiene lettere maiuscole o miste, devi racchiudere l'identificatore tra virgolette doppie nelle istruzioni SQL.

Nell'esempio seguente, devi utilizzare "UserProfiles" per il nome della tabella e "profileId" per la colonna userId. Il campo displayName segue la conversione predefinita in display_name:

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

Esempi di utilizzo

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!) @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 e _executeReturningFirst racchiudono la query in un'espressione di tabella comune (CTE) principale per formattare l'output come JSON. PostgreSQL non consente l'annidamento di una CTE di modifica dei dati all'interno di un'altra istruzione di modifica dei dati, causando l'errore della query.

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

SQL Connect parametrizza in modo sicuro tutti gli input al limite 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 di Postgres 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;
$$;