Реализуйте операции Firebase Data Connect с использованием нативного SQL.

Firebase Data Connect предлагает несколько способов взаимодействия с вашей базой данных Cloud SQL:

  • Встроенный GraphQL : Определите типы в файле schema.gql , и Data Connect преобразует ваши операции GraphQL в SQL. Это стандартный подход, предлагающий строгую типизацию и структуры, обеспечиваемые схемой. Большая часть документации Data Connect за исключением этой страницы, посвящена именно этому варианту. По возможности следует использовать этот метод, чтобы воспользоваться преимуществами полной типобезопасности и поддержки инструментов.
  • Директива @view : определяет тип GraphQL в schema.gql подкрепленный пользовательским SQL-запросом SELECT . Это полезно для создания представлений только для чтения со строгой типизацией, основанных на сложной логике SQL. Эти типы доступны для запросов как обычные типы. См. @view .
  • Встроенный SQL : Встраивание SQL-запросов непосредственно в именованные операции в файлах gql с использованием специальных корневых полей. Это обеспечивает максимальную гибкость и прямой контроль, особенно для операций, которые сложно выразить в стандартном GraphQL, с использованием специфических функций базы данных или расширений PostgreSQL.

В этом руководстве основное внимание уделяется опции Native SQL .

Типичные сценарии использования нативного SQL

Хотя нативный GraphQL обеспечивает полную типобезопасность, а директива @view предлагает строго типизированные результаты для SQL-отчетов только для чтения, нативный SQL обеспечивает гибкость, необходимую для:

  • Расширения PostgreSQL : Позволяет напрямую запрашивать и использовать любые установленные расширения PostgreSQL (например, PostGIS для геопространственных данных) без необходимости сопоставления сложных типов в вашей схеме GraphQL.
  • Сложные запросы : Выполнение сложных SQL-запросов с использованием объединений, подзапросов, агрегаций, оконных функций и хранимых процедур.
  • Манипулирование данными (DML) : Выполняйте операции INSERT, UPDATE, DELETE напрямую. (Однако не используйте нативный SQL для команд языка определения данных (DDL). Необходимо продолжать вносить изменения на уровне схемы с помощью GraphQL, чтобы поддерживать синхронизацию бэкэнда и сгенерированных SDK.)
  • Особенности, специфичные для базы данных : Используйте функции, операторы или типы данных, уникальные для PostgreSQL.
  • Оптимизация производительности : ручная настройка SQL-запросов для критически важных участков кода.

Корневые поля нативного SQL

Для написания операций с использованием SQL применяйте одно из следующих корневых полей типа query или mutation :

поля query

Поле Описание
_select

Выполняет SQL-запрос, возвращающий ноль или более строк.

Аргументы :

  • sql : Строковый литерал SQL-запроса. Для предотвращения SQL-инъекций используйте позиционные заполнители ( $1 , $2 и т. д.) для значений параметров.
  • params : Упорядоченный список значений для привязки к заполнителям. Он может включать литералы, переменные GraphQL и специальные карты контекста, внедряемые сервером, например {_expr: "auth.uid"} (идентификатор аутентифицированного пользователя).

Возвращает : массив JSON ( [Any] ).

_selectFirst

Выполняет SQL-запрос, который, как ожидается, вернет ноль или одну строку.

Аргументы :

  • sql : Строковый литерал SQL-запроса. Для предотвращения SQL-инъекций используйте позиционные заполнители ( $1 , $2 и т. д.) для значений параметров.
  • params : Упорядоченный список значений для привязки к заполнителям. Он может включать литералы, переменные GraphQL и специальные карты контекста, внедряемые сервером, например {_expr: "auth.uid"} (идентификатор аутентифицированного пользователя).

Возвращает : JSON-объект ( Any ) или null .

поля mutation

Поле Описание
_execute

Выполняет оператор DML ( INSERT, UPDATE, DELETE ).

Аргументы :

  • sql : Строковый литерал SQL-запроса. Для предотвращения SQL-инъекций используйте позиционные заполнители ( $1 , $2 и т. д.) для значений параметров.

    Здесь можно использовать общие табличные выражения (CTE) для изменения данных (например, WITH new_row AS (INSERT...) ), поскольку это поле возвращает только количество строк. Только _execute поддерживает CTE.

  • params : Упорядоченный список значений для привязки к заполнителям. Он может включать литералы, переменные GraphQL и специальные карты контекста, внедряемые сервером, например {_expr: "auth.uid"} (идентификатор аутентифицированного пользователя).

Возвращает : Int (количество затронутых строк).

При формировании результата условия RETURNING игнорируются.

_executeReturning

Выполняет оператор DML с предложением RETURNING , возвращая ноль или более строк.

Аргументы :

  • sql : Строковый литерал SQL-запроса. Для предотвращения SQL-инъекций используйте позиционные заполнители ( $1 , $2 и т. д.) для значений параметров. Общие табличные выражения, изменяющие данные, не поддерживаются.
  • params : Упорядоченный список значений для привязки к заполнителям. Он может включать литералы, переменные GraphQL и специальные карты контекста, внедряемые сервером, например {_expr: "auth.uid"} (идентификатор аутентифицированного пользователя).

Возвращает : массив JSON ( [Any] ).

_executeReturningFirst

Выполняет оператор DML с предложением RETURNING , который, как ожидается, вернет ноль или одну строку.

Аргументы :

  • sql : Строковый литерал SQL-запроса. Для предотвращения SQL-инъекций используйте позиционные заполнители ( $1 , $2 и т. д.) для значений параметров. Общие табличные выражения, изменяющие данные, не поддерживаются.
  • params : Упорядоченный список значений для привязки к заполнителям. Он может включать литералы, переменные GraphQL и специальные карты контекста, внедряемые сервером, например {_expr: "auth.uid"} (идентификатор аутентифицированного пользователя).

Возвращает : JSON-объект ( Any ) или null .

Примечания:

  • Операции выполняются с использованием прав доступа, предоставленных учетной записи службы Data Connect.

  • Если вы явно указываете имя таблицы с помощью директивы @table ( @table(name: "ExampleTable") ), вам также необходимо заключить имя таблицы в кавычки в ваших SQL-запросах ( SELECT field FROM "ExampleTable" ... ).

    Без кавычек Data Connect преобразует имя таблицы в формат snake case ( example_table ).

Правила и ограничения синтаксиса

Встроенный SQL применяет строгие правила синтаксического анализа для обеспечения безопасности и предотвращения SQL-инъекций. Следует учитывать следующие ограничения:

  • Комментарии : Используйте блочные комментарии ( /* ... */ ). Строковые комментарии ( -- ) запрещены, поскольку они могут обрезать последующие условия (например, фильтры безопасности) во время конкатенации запросов.
  • Параметры : Используйте позиционные параметры ( $1 , $2 ), соответствующие порядку в массиве params . Именованные параметры ( $id , :name ) не поддерживаются.
  • Строки : Расширенные строковые литералы ( E'...' ) и строки в долларовых кавычках ($$...$$ Поддерживаются. Экранирование символов Unicode в PostgreSQL ( U&'...' ) не поддерживается.

Параметры в комментариях

Парсер игнорирует все, что находится внутри блочного комментария. Если вы закомментируете строку, содержащую параметр (например, /* WHERE id = $1 */ ), вы также должны удалить этот параметр из списка params , иначе операция завершится ошибкой " unused parameter: $1 .

Примеры

Пример 1: Базовый оператор SELECT с псевдонимами полей.

Вы можете присвоить псевдоним корневому полю (например, movies: _select ), чтобы сделать ответ клиента более понятным ( data.movies вместо 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]
  )
}

После выполнения запроса с помощью клиентского SDK результат будет находиться в data.movies .

Пример 2: Базовое обновление

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

После выполнения мутации с помощью клиентского SDK количество затронутых строк будет указано в data._execute .

Пример 3: Базовая агрегация

queries.gql :

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

После выполнения запроса с помощью клиентского SDK результат будет находиться в data.stats.total_reviews .

Пример 4: Расширенная агрегация с использованием 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: []
  )
}

После выполнения запроса с помощью клиентского SDK результат будет находиться в data._select .

Пример 5: ОБНОВЛЕНИЕ с ВОЗВРАЩАЮЩИМСЯ И КОНТЕКСТОМ АВТОРИЗАЦИИ

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

После выполнения мутации с помощью клиентского SDK обновленные данные записи будут находиться в data.updatedReview .

Пример 6: Расширенное CTE с операциями upsert (атомарные get или create)

Этот шаблон полезен для обеспечения существования зависимых записей (например, пользователей или фильмов) перед добавлением дочерней записи (например, отзыва), и все это в рамках одной транзакции с базой данных.

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

Пример 7: Использование расширений Postgres

Использование нативного SQL позволяет применять расширения Postgres, такие как PostGIS, без необходимости сопоставлять сложные типы геометрии со схемой GraphQL или изменять базовые таблицы.

В этом примере предположим, что в вашем приложении для ресторана есть таблица, которая хранит данные о местоположении в столбце метаданных JSON (например, {"latitude": 37.3688, "longitude": -122.0363} ). Если у вас включено расширение PostGIS , вы можете использовать стандартные операторы Postgres JSON ( ->> ) для извлечения этих значений на лету и передачи их в функцию 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]
  )
}

После выполнения запроса с помощью клиентского SDK результат будет находиться в data.nearby .

Рекомендации по обеспечению безопасности: динамический SQL и хранимые процедуры.

Data Connect обеспечивает безопасную параметризацию всех входных данных на границе GraphQL и базы данных, полностью защищая ваши стандартные SQL-запросы от SQL-инъекций первого порядка. Однако, если вы используете SQL для вызова пользовательских хранимых процедур или функций Postgres, выполняющих динамический SQL, вы должны убедиться, что ваш внутренний код PL/pgSQL безопасно обрабатывает эти параметры.

Если ваша хранимая процедура напрямую объединяет пользовательский ввод в строку EXECUTE , она обходит параметризацию и создает уязвимость SQL-инъекции второго порядка: 

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

Чтобы этого избежать, следуйте этим рекомендациям:

  • Используйте предложение USING : При написании динамического SQL-запроса в хранимых процедурах всегда используйте предложение USING для безопасной привязки параметров данных.
  • Используйте функцию format() для идентификаторов: используйте format() с флагом %I для безопасного внедрения идентификаторов в базу данных (например, имен таблиц).
  • Строго разрешайте использование идентификаторов: не позволяйте клиентским приложениям произвольно выбирать идентификаторы базы данных. Если ваша процедура требует динамических идентификаторов, перед выполнением проверяйте входные данные на соответствие жестко заданному списку разрешенных идентификаторов внутри вашей логики PL/pgSQL. 
-- 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;
$$;