Implementa operaciones de Firebase Data Connect con SQL nativo

Firebase Data Connect ofrece varias formas de interactuar con tu base de datos de Cloud SQL:

• GraphQL nativo: Define tipos en tus schema.gql y Data Connect, y traduce tus operaciones de GraphQL a SQL. Este es el enfoque estándar, que ofrece escritura sólida y estructuras reforzadas por el esquema. La mayor parte de la documentación de Data Connect fuera de esta página analiza esta opción. Cuando sea posible, debes usar este método para aprovechar la seguridad de tipos completa y la compatibilidad con herramientas.
• La directiva @view: Define un tipo de GraphQL en schema.gql respaldado por una instrucción SELECT de SQL personalizada. Esto es útil para crear vistas de solo lectura con escritura segura basadas en una lógica de SQL compleja. Estos tipos se pueden consultar como los tipos regulares. Consulta @view.
• SQL nativo: Incorpora instrucciones de SQL directamente en operaciones con nombre en .Archivos gql que usan campos raíz especiales Esto proporciona la máxima flexibilidad y control directo, en especial para las operaciones que no se expresan fácilmente en GraphQL estándar, que aprovechan las funciones específicas de la base de datos o que utilizan extensiones de PostgreSQL.

En esta guía, nos enfocamos en la opción SQL nativo.

Casos de uso habituales de SQL nativo

Si bien GraphQL nativo proporciona seguridad de tipos completa y la directiva @view ofrece resultados con escritura segura para informes de SQL de solo lectura, SQL nativo proporciona la flexibilidad necesaria para lo siguiente:

• Extensiones de PostgreSQL: Consulta y usa directamente cualquier extensión de PostgreSQL instalada (como PostGIS para datos geoespaciales) sin necesidad de asignar tipos complejos en tu esquema de GraphQL.
• Consultas complejas: Ejecuta instrucciones SQL complejas con uniones, subconsultas, agregaciones, funciones analíticas y procedimientos almacenados.
• Manipulación de datos (DML): Realiza operaciones de INSERT, UPDATE, DELETE directamente. (Sin embargo, no uses SQL nativo para los comandos del lenguaje de definición de datos [DDL]). Debes seguir realizando modificaciones a nivel del esquema con GraphQL para mantener sincronizados tu backend y los SDKs generados.
• Funciones específicas de la base de datos: Utiliza funciones, operadores o tipos de datos exclusivos de PostgreSQL.
• Optimización del rendimiento: Ajusta manualmente las sentencias SQL para las rutas críticas.

Campos raíz de SQL nativo

Para escribir operaciones con SQL, usa uno de los siguientes campos raíz de los tipos query o mutation:

Campos query

Campos mutation

Notas:

• Las operaciones se ejecutan con los permisos otorgados a la cuenta de servicio de Data Connect.

• Si estableces explícitamente el nombre de la tabla con la directiva @table (@table(name: "ExampleTable")), también debes incluir el nombre de la tabla entre comillas en tus instrucciones de SQL (SELECT field FROM "ExampleTable" ...).

  Sin las comillas, Data Connect convertirá el nombre de la tabla a snake case (example_table).

Reglas y limitaciones de sintaxis

El SQL nativo aplica reglas de análisis estrictas para garantizar la seguridad y evitar la inyección de SQL. Ten en cuenta las siguientes restricciones:

• Comentarios: Usa comentarios de bloque (/* ... */). Los comentarios de línea (--) están prohibidos porque pueden truncar cláusulas posteriores (como filtros de seguridad) durante la concatenación de consultas.
• Parámetros: Usa parámetros posicionales ($1, $2) que coincidan con el orden del array params. No se admiten los parámetros con nombre ($id, :name).
• Cadenas: Se admiten literales de cadena extendidos (E'...') y cadenas entre comillas con signo de dólar ($$...$$). No se admiten los escapes Unicode de PostgreSQL (U&'...').

Parámetros en los comentarios

El analizador ignora todo lo que se encuentra dentro de un comentario de bloque. Si comentas una línea que contiene un parámetro (por ejemplo, /* WHERE id = $1 */), también debes quitar ese parámetro de la lista params; de lo contrario, la operación fallará con el error unused parameter: $1.

Ejemplos

Ejemplo 1: SELECT básico con alias de campo

Puedes crear un alias para el campo raíz (por ejemplo, movies: _select) para que la respuesta del cliente sea más clara (data.movies en lugar 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]
  )
}

Después de ejecutar la consulta con un SDK de cliente, el resultado estará en data.movies.

Ejemplo 2: UPDATE básico

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

Después de ejecutar la mutación con un SDK de cliente, la cantidad de filas afectadas estará en data._execute.

Ejemplo 3: Agregación básica

queries.gql:

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

Después de ejecutar la consulta con un SDK de cliente, el resultado estará en data.stats.total_reviews.

Ejemplo 4: Agregación avanzada 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: []
  )
}

Después de ejecutar la consulta con un SDK de cliente, el resultado estará en data._select.

Ejemplo 5: UPDATE con RETURNING y contexto de autorización

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

Después de ejecutar la mutación con un SDK del cliente, los datos de la publicación actualizados estarán en data.updatedReview.

Ejemplo 6: CTE avanzada con upserts (obtención o creación atómica)

Este patrón es útil para garantizar que existan registros dependientes (como Usuarios o Películas) antes de insertar un registro secundario (como una Revisión), todo en una sola transacción de base de datos.

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

Ejemplo 7: Uso de extensiones de Postgres

El SQL nativo te permite usar extensiones de Postgres, como PostGIS, sin necesidad de asignar tipos de geometría complejos a tu esquema de GraphQL ni alterar tus tablas subyacentes.

En este ejemplo, supongamos que tu app de restaurantes tiene una tabla que almacena datos de ubicación en una columna JSON de metadatos (por ejemplo, {"latitude": 37.3688, "longitude": -122.0363}). Si habilitaste la extensión de PostGIS, puedes usar operadores JSON de Postgres estándar (->>) para extraer estos valores sobre la marcha y pasarlos a la función 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]
  )
}

Después de ejecutar la consulta con un SDK de cliente, el resultado estará en data.nearby.

Prácticas recomendadas de seguridad: SQL dinámico y procedimientos almacenados

Data Connect parametriza de forma segura todas las entradas en el límite de GraphQL a la base de datos, lo que protege por completo tus consultas de SQL estándar de la inyección de SQL de primer orden. Sin embargo, si usas SQL para llamar a funciones o procedimientos almacenados personalizados de Postgres que ejecutan SQL dinámico, debes asegurarte de que tu código interno de PL/pgSQL controle estos parámetros de forma segura.

Si tu procedimiento almacenado concatena directamente las entradas del usuario en una cadena EXECUTE, omite la parametrización y crea una vulnerabilidad de inyección de SQL de segundo orden:

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

Para evitar esto, sigue estas prácticas recomendadas:

• Usa la cláusula USING: Cuando escribas SQL dinámico en tus procedimientos almacenados, siempre usa la cláusula USING para vincular los parámetros de datos de forma segura.
• Usa format() para los identificadores: Usa format() con la marca %I para la inyección segura de identificadores de bases de datos (como nombres de tablas).
• Permite solo identificadores estrictos: No permitas que las aplicaciones cliente elijan identificadores de bases de datos de forma arbitraria. Si tu procedimiento requiere identificadores dinámicos, valida la entrada con una lista de entidades permitidas codificada dentro de tu lógica de PL/pgSQL antes de la ejecución.

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