Setting the file. One moment.
Chapter 07 · Firebase Data Connect
Subchapter 7.4
reference/native_sql.mdMarkdown7 KBView on GitHub
Always default to Native GraphQL. Use Native SQL only when you need database-specific features not available in GraphQL (e.g., PostGIS, Window Functions, Complex Aggregations, or specific DML CTEs).
When generating Native SQL operations, you are bypassing GraphQL and talking directly to PostgreSQL. You MUST adhere to these strict constraints:
$1) with standard GraphQL named variables ($id). The sql: argument
MUST be a hardcoded string literal block ("""SELECT..."""), not a GraphQL
variable.snake_case Conversion: By default, SQL Connect converts
PascalCase types and camelCase fields to snake_case in the database.
type UserProfile { releaseYear: Int } -> Native SQL:
SELECT release_year FROM user_profile@table(name: "ExactName") or @col(name: "ExactCol"), you MUST wrap
the identifier in double quotes if it contains capital letters (e.g.,
SELECT * FROM "ExactName"). Without quotes, Postgres folds it to
lowercase and fails validation.Native SQL enforces strict parsing rules to ensure security and prevent SQL injection:
sql argument must be a hardcoded string
literal block ("""SELECT...""") directly in the .gql file. It cannot
be a GraphQL variable.schema.gql
file instead for table/column changes). Furthermore, query operations cannot
contain DML and must start with SELECT, TABLE, or WITH.$1, $2) that match the
params array order. Named parameters ($id, :name) are forbidden./* ... */). Line comments (--) are
forbidden because they can truncate subsequent clauses during query
compilation. If you comment out a line containing a parameter (e.g.,
/* WHERE id = $1 */), you must also remove that parameter from the params
list, or it will fail with unused parameter: $1.E'...') and dollar-quoted strings
($$...$$) are supported._expr): Variables cannot be used inside _expr
fields; to ensure security, _expr must be a static string (e.g.,
{_expr: "auth.uid"}, not {_expr: $uidVar}).Operations are executed using the permissions granted to the SQL Connect service
account. You can alias the root field (e.g., movies: _select) to make the
client response cleaner (data.movies instead of data._select).
Note on
AnyReturn Types: Because Native SQL completely bypasses GraphQL’s strong typing, queries like_selectand_executeReturningreturn the genericAnyscalar type. The generated client SDKs (TypeScript, Swift, Kotlin, Dart) will type this asany(or equivalent). AGENT INSTRUCTION: When you generate client-side code that consumes these operations, you MUST manually cast or validate the shape of the data, as the typical type safety of SQL Connect will not be present.
Use these root fields in query or mutation operations:
_select: Executes a SQL query returning zero or more rows. Returns [Any].
query GetMovies($genre: String!) @auth(level: PUBLIC) {
movies: _select(
sql: "SELECT id, title FROM movie WHERE genre = $1",
params: [$genre]
)
}_selectFirst: Executes a SQL query expected to return zero or one row.
Returns Any or null.
query GetTotalReviews @auth(level: PUBLIC) {
stats: _selectFirst(
sql: "SELECT COUNT(*) as total_reviews FROM review"
) # params can be omitted if empty
}_execute: Executes DML (INSERT, UPDATE, DELETE). Returns Int (number
of rows affected).
RETURNING clauses are ignored in the result._execute supports Data-Modifying Common Table Expressions
(e.g., WITH new_row AS (INSERT...)).mutation UpdateRating($id: UUID!, $rating: Float!) @auth(level: USER) {
_execute(
sql: "UPDATE movie SET rating = $2 WHERE id = $1",
params: [$id, $rating]
)
}_executeReturning: Executes DML with a RETURNING clause. Returns [Any].
Data-Modifying CTEs are not supported.
mutation DeleteUserReviews($uid: String!) @auth(level: USER) {
deletedReviews: _executeReturning(
sql: "DELETE FROM review WHERE user_id = $1 RETURNING id, rating",
params: [{_expr: "auth.uid"}]
)
}_executeReturningFirst: Executes DML with RETURNING, expecting zero or one
row. Returns Any or null. Data-Modifying CTEs are not supported.
mutation UpdateMyReview($movieId: UUID!, $text: String!) @auth(level: USER) {
updatedReview: _executeReturningFirst(
sql: """
UPDATE review SET text = $2
WHERE movie_id = $1 AND user_id = $3
RETURNING id, text
Native SQL allows you to directly query and utilize PostgreSQL extensions, such
as PostGIS, without needing to map complex geometry types into your GraphQL
schema or alter your underlying tables (e.g., using JSON operators to extract
values and pass them into ST_MakePoint).
Note: You must enable the extension on your underlying Cloud SQL instance by
connecting as the postgres user and running
CREATE EXTENSION IF NOT EXISTS ...;
(See examples.md for a full GetNearbyActiveRestaurants implementation).
SQL Connect parameterizes inputs at the GraphQL boundary automatically. However, if your Native SQL calls custom PL/pgSQL stored procedures, you must manually prevent 2nd-order SQL injection:
EXECUTE string
(EXECUTE 'UPDATE ' || table || ' SET x=' || val;).USING clause to bind data values safely.format('%I') for safe database identifier injection.Secure PL/pgSQL Pattern:
CREATE OR REPLACE PROCEDURE secure_update(target_table TEXT, new_value TEXT, row_id INT)
LANGUAGE plpgsql AS $$
BEGIN
-- 1. Strict Allowlist for Identifiers
IF target_table NOT IN ('orders', 'users', 'inventory') THEN
RAISE EXCEPTION 'Invalid table name';
END IF;
-- 2. format(%I) for Identifiers, USING for Data
EXECUTE format('UPDATE %I SET status = $1 WHERE id = $2', target_table)
USING new_value, row_id;
END;
$$;