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: > Native SQL is available as a feature Preview, which means that it isn't subject to any SLA (accordo sul livello del servizio) or norme sul ritiro and could change in backwards-incompatible ways. Se utilizzi questa funzionalità con stored procedure o funzioni che eseguono SQL dinamico, segui le best practice di sicurezza spiegate nella parte inferiore di questa pagina.

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

• GraphQL nativo: definisci i tipi in schema.gql e SQL Connect traduce le operazioni GraphQL in SQL. Questo è l'approccio standard, che offre una tipizzazione forte e strutture con schema applicato. La maggior parte della documentazione SQL Connect al di fuori di questa pagina illustra questa opzione. Quando 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 visualizzazioni 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 .gql file utilizzando campi radice speciali. Questa opzione offre la massima flessibilità e il controllo diretto, soprattutto per le operazioni non supportate da GraphQL standard, sfruttando le funzionalità specifiche del database o utilizzando le estensioni PostgreSQL. A differenza di GraphQL e della direttiva @view, SQL nativo non fornisce output con tipizzazione forte.

Questa guida si concentra sull'opzione SQL nativo.

Casi d'uso comuni per SQL nativo

Mentre GraphQL nativo offre una sicurezza dei tipi completa e la direttiva @view offre risultati con tipizzazione forte 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, sottoquery, aggregazioni, funzioni finestra e stored procedure.
• Data Manipulation (DML): esegui le operazioni INSERT, UPDATE, DELETE direttamente. (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 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 all'account di servizio SQL Connect.

Regole e limitazioni della sintassi

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 stringa estesi (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.

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 esegue automaticamente il mapping dei nomi nello schema GraphQL a snake case nel database, a meno che tu non personalizzi esplicitamente gli identificatori PostgreSQL utilizzando le @table(name) e @col(name) direttive.

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

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 fanno distinzione 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 userId colonna. 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 di campo

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!) @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 una CTE (Common Table Expression) principale per formattare l'output come JSON. PostgreSQL non consente di nidificare una CTE (Common Table Expression) che modifica i dati all'interno di un'altra istruzione che modifica i dati, causando l'errore della query.

Esempio 7: utilizzo delle estensioni PostgreSQL

SQL nativo ti consente di utilizzare le estensioni PostgreSQL, 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 per 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 attivato l'estensione PostGIS, puoi utilizzare gli operatori JSON standard di PostgreSQL (->>) 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 di sicurezza: SQL dinamico e stored procedure

SQL 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 PostgreSQL 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 dell'identificatore del database (ad esempio, i nomi delle tabelle).
• Consenti solo identificatori: non consentire alle applicazioni client di scegliere arbitrariamente gli identificatori del 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;
$$;