Implement Firebase SQL Connect operations using native SQL

A guide to writing Firebase SQL Connect operations with SQL instead of GraphQL. page_type: guide announcement: > Native SQL is available as a feature Preview, which means that it isn't subject to any SLA or deprecation policy and could change in backwards-incompatible ways. If you use this feature with stored procedures or functions that execute dynamic SQL, follow the security best practices explained at the bottom of this page.

Firebase SQL Connect offers multiple ways to interact with your Cloud SQL database:

• Native GraphQL: Define types in your schema.gql and SQL Connect translates your GraphQL operations into SQL. This is the standard approach, offering strong typing and schema-enforced structures. Most of the SQL Connect documentation outside of this page discusses this option. When possible, you should use this method to take advantage of full type safety and tooling support.
• The @view directive: Define a GraphQL type in schema.gql backed by a custom SELECT SQL statement. This is useful for creating read-only, strongly-typed views based on complex SQL logic. These types are queryable like regular types. See @view.
• Native SQL: Embed SQL statements directly in named operations in .gql files using special root fields. This provides maximum flexibility and direct control, especially for operations not supported by standard GraphQL, leveraging database-specific features, or utilizing PostgreSQL extensions. Unlike GraphQL and the @view directive, native SQL doesn't provide strongly-typed output.

This guide focuses on the Native SQL option.

Common use cases for native SQL

While native GraphQL provides full type safety, and the @view directive offers strongly-typed results for read-only SQL reports, native SQL provides the flexibility needed for:

• PostgreSQL Extensions: Directly query and use any installed PostgreSQL extension (such as PostGIS for geospatial data) without needing to map complex types in your GraphQL schema.
• Complex Queries: Execute intricate SQL with joins, subqueries, aggregations, window functions, and stored procedures.
• Data Manipulation (DML): Perform INSERT, UPDATE, DELETE operations directly. (However, don't use native SQL for Data Definition Language (DDL) commands. You must continue to make schema-level alterations using GraphQL to keep your backend and generated SDKs in sync.)
• Database-Specific Features: Utilize functions, operators, or data types unique to PostgreSQL.
• Performance Optimization: Hand-tune SQL statements for critical paths.

Native SQL root fields

To write operations with SQL, use one of the following root fields of the query or mutation types:

query fields

mutation fields

Notes:

• Operations are executed using the permissions granted to the SQL Connect service account.

Syntax rules & limitations

Native SQL enforces strict parsing rules to ensure security and prevent SQL injection. Be aware of the following constraints:

• Comments: Use block comments (/* ... */). Line comments (--) are forbidden because they can truncate subsequent clauses (like security filters) during query concatenation.
• Parameters: Use positional parameters ($1, $2) that match the params array order. Named parameters ($id, :name) are not supported.
• Strings: Extended string literals (E'...') and dollar-quoted strings ($$...$$) are supported. PostgreSQL Unicode escapes (U&'...') are not supported.

Parameters in comments

The parser ignores everything inside a block comment. If you comment out a line containing a parameter (for example, /* WHERE id = $1 */), you must also remove that parameter from the params list, or the operation will fail with the error unused parameter: $1.

Naming conventions

When writing native SQL, you are interacting directly with your PostgreSQL database, so you must use the actual database names for tables and columns. By default, SQL Connect automatically maps the names in your GraphQL schema to snake case in the database, unless you explicitly customize the Postgres identifiers using the @table(name) and @col(name) directives.

If you define a type without directives, the GraphQL table and field names map to the default snake_case Postgres identifiers:

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

PostgreSQL identifiers are case-insensitive by default. If you use directives like @table or @col to specify a name that contains uppercase or mixed-case letters, you must enclose that identifier in double quotes in your SQL statements.

In the following example, you must use "UserProfiles" for the table name and "profileId" for the userId column. The displayName field follows the default conversion to 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]
  )
}

Usage examples

Example 1: Basic SELECT with field aliasing

You can alias the root field (for example, movies: _select) to make the client response cleaner (data.movies instead of 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]
  )
}

After running the query using a client SDK, the result will be in data.movies.

Example 2: Basic UPDATE

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

After running the mutation using a client SDK, the number of affected rows will be in data._execute.

Example 3: Basic aggregation

queries.gql:

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

After running the query using a client SDK, the result will be in data.stats.total_reviews.

Example 4: Advanced aggregation with 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: []
  )
}

After running the query using a client SDK, the result will be in data._select.

Example 5: UPDATE with RETURNING and Auth Context

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

After running the mutation using a client SDK, the updated post data will be in data.updatedReview.

Example 6: Advanced CTE with upserts (atomic get-or-create)

This pattern is useful for ensuring dependent records (like Users or Movies) exist before inserting a child record (like a Review), all in a single database transaction.

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 and _executeReturningFirst wrap your query in a parent CTE to format the output as JSON. PostgreSQL does not allow nesting a data-modifying CTE inside another data-modifying statement, causing the query to fail.

Example 7: Using Postgres extensions

Native SQL allows you to use Postgres extensions, such as PostGIS, without needing to map complex geometry types into your GraphQL schema or alter your underlying tables.

In this example, suppose your restaurant app has a table that stores location data in a metadata JSON column (for example, {"latitude": 37.3688, "longitude": -122.0363}). If you have enabled the PostGIS extension, you can use standard Postgres JSON operators (->>) to extract these values on the fly and pass them into the PostGIS ST_MakePoint function.

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

After running the query using a client SDK, the result will be in data.nearby.

Security best practices: dynamic SQL & stored procedures

SQL Connect securely parameterizes all inputs at the GraphQL-to-database boundary, fully protecting your standard SQL queries from first-order SQL injection. However, if you use SQL to call custom Postgres stored procedures or functions that execute dynamic SQL, you must ensure your internal PL/pgSQL code handles these parameters securely.

If your stored procedure directly concatenates user inputs into an EXECUTE string, it bypasses parameterization and creates a second-order SQL injection vulnerability:

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

To avoid this, follow these best practices:

• Use the USING clause: When writing dynamic SQL in your stored procedures, always use the USING clause to bind data parameters safely.
• Use format() for identifiers: Use format() with the %I flag for safe database identifier injection (like table names).
• Strictly allow identifiers: Don't let client applications arbitrarily choose database identifiers. If your procedure requires dynamic identifiers, validate the input against a hardcoded allowlist inside your PL/pgSQL logic before execution.

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