Data Connect is our comprehensive PostgreSQL solution, designed to let you build secure, scalable apps backed by Cloud SQL. Instead of writing and maintaining a traditional backend, you define your schema and operations using a declarative syntax based on GraphQL. This approach gives you strict type safety, relation resolution, and custom directives for common features like Firebase Auth right out of the box, without the hassle of having to write your own resolvers.

However, there are times when you really want the flexibility and power of SQL. When you hit advanced use cases—like window functions, geospatial indexing, or complex atomic operations —shoehorning these SQL operations into GraphQL types becomes much more difficult.

That’s why we’re excited to launch Native SQL for Data Connect. It provides direct access to your database, bridging the gap between FDC’s features and raw SQL operations, so you can write the exact statements you need.

What is native SQL?

Native SQL is a feature in Data Connect that lets you define operations using raw SQL syntax while still benefiting from Data Connect’s other features like Firebase integrations and auto-generated SDKs.

Instead of letting Data Connect generate SQL based on your operations, you can provide the SQL commands directly into your standard .gql operation files. Because these statements are predefined in the backend, clients cannot modify them on the fly. This allows you to execute your operations securely without having to worry about SQL injections.

How does it work?

Native SQL is implemented via new root fields on the query and mutation types within Data Connect. You define named operations in your operation .gql files using these fields, passing your raw SQL string and a list of parameters. (See docs for more details).

When you run firebase deploy --only dataconnect, the FDC CLI compiles these operations, validates the SQL string as a static literal, and rejects forbidden commands like Data Definition Language (DDL). At runtime, the client invokes the named operation. The FDC server securely binds the parameters to the pre-deployed SQL template and executes the query against the Cloud SQL PostgreSQL database.

Queries

Used exclusively for Data Query Language (DQL). The deploy-time validation will block DML statements here.

• _select: Executes a query expecting zero or more rows. Returns [Any] (a JSON array).
• _selectFirst: Executes a query expecting exactly one row. Returns Any (a JSON object or null).

Let’s implement these concepts using a social forum app. This first query uses PostgreSQL’s HAVING clause to filter grouped post tags, a complex aggregation standard syntax would have difficulty expressing.

# Finds trending tags that have a high average view count. 
query GetTrendingTags($minAvgViews: Float!) {
_select(
  sql: """
    SELECT tag_name, AVG(views) as avg_views, COUNT(id) as post_count
    FROM posts
    GROUP BY tag_name
    HAVING AVG(views) >= $1
    ORDER BY avg_views DESC
  """,
  params: [$minAvgViews]
)
}

Mutation fields

Used for Data Manipulation Language (DML) such as INSERT, UPDATE, and DELETE.

• _execute: Executes a DML statement returning the result count as an Int.
• _executeReturning: Executes a DML statement with a RETURNING clause for multiple rows, returning [Any].
• _executeReturningFirst: Executes a DML statement returning a single row as Any.

This mutation bulk-updates an author’s drafts in a single command, manipulating massive amounts of data without having to fetch it to the client first.

# Automatically archive drafts that haven't been touched in a year.
mutation ArchiveStaleDrafts($cutoffDate: String!) {
_execute(
  sql: """
    UPDATE posts 
    SET status = 'archived' 
    WHERE status = 'draft' AND last_updated < $1
  """,
  params: [$cutoffDate]
)
}

Security

Native SQL prevents SQL injection and unauthorized access by:

• Server-controlled SQL: SQL statements are hardcoded in the deployed .gql files. The client cannot send arbitrary SQL at runtime.
• Deploy-time validation: FDC validates SQL strings during deployment and rejects destructive commands.
• Strict parameterization: Inputs are passed via a params array and bound to positional placeholders ($1, $2) by the database driver. The SQL string is never concatenated with unvetted inputs.
• Server-side context injection: You can inject trusted server state into your parameters using the _expr directive. For example, {_expr: "auth.uid"} is resolved server-side to the authenticated user’s ID before execution, preventing client-side spoofing.

What can you do with native SQL (that you couldn’t before)

• Direct DML: Run INSERT, UPDATE, and DELETE operations without relying strictly on generated FDC mutations.

• Multi-step execution with Common Table Expressions: The _execute field allows you to write multi-step sql using CTEs (WITH keyword) to chain multiple operations together.

• Complex aggregations and window functions: Execute statements using RANK(), PARTITION BY, or deeply nested subqueries.

• Flexible return structures: You no longer need to write boilerplate GraphQL types in schema.gql just to return the result of a custom aggregation or window function.

• Postgres extensions: Directly query extensions like PostGIS for spatial data without mapping complex geometry types into the GraphQL schema.

For a comprehensive list, see the documentation.

Difference from @view

While native SQL provides SQL’s execution flexibility and dynamic JSON returns, there are times when you want the strict type-safety of a standard Data Connect object in addition to the ability to write complex SQL expressions.

For these scenarios, Data Connect also offers the @view directive, which you can use in read-only contexts. By applying @view to an OBJECT type in your schema.gql, you can back that specific type with a custom SELECT statement. This allows you to encapsulate complex database logic on the server while keeping your client-side code strictly typed.

How it works

Data Connect maps the GraphQL fields on your @view type to the columns returned by your SQL statement. Also, your scalar fields (written in camelCase) must match the SQL columns (written in snake_case) in your SELECT statement.

type AuthorStats @view(sql: """
SELECT 
  author_id, 
  COUNT(id) AS total_posts, 
  SUM(views) AS total_network_views
FROM posts
GROUP BY author_id
""") {
authorId: String
totalPosts: Int
totalNetworkViews: Int
}

@view Arguments

• sql: The native SQL SELECT statement used as the basis for this type.
• name: The name of a pre-existing SQL view in your Postgres database (cannot be used alongside the sql argument).
• singular / plural: Configures the naming conventions in your generated API.

Syntax Rules & Limitations

Because views are integrated into your schema, they carry a few specific rules that differ from native SQL:

• Read-only: Only SELECT statements are permitted. Views cannot be mutated directly.
• No primary keys: SQL views do not have primary keys, meaning direct key-based lookups (e.g., authorStats(id: ...)) are not supported.
• Nullable fields: All fields on a @view type should generally be declared as nullable. If a field lacks a corresponding column in the SQL result, it will safely return null.
• No inbound references: Other @table or @view types cannot use @ref to point to a view. (However, a view can contain a @ref pointing out to a base @table).
• Runtime validation: As with native SQL, Data Connect does not parse or validate the SQL string inside a @view during deployment. Meaning that errors in your SQL, as well as mismatches between columns and fields will only surface at runtime, so always test your view queries.

When to use which FDC approach

Query

• When to use native GraphQL: For standard CRUD operations, simple joins, and when you rely heavily on strict end-to-end type safety in your client application (e.g., fetching a basic profile or some list of arbitrary statistics).

query GetAuthorProfile($id: UUID!) {
author(id: $id) {
  id
  username
  published_posts { 
    title
    views
  }
  followers {
    username
  }
}
}

• When to use @view: When you have a stable, read-only SQL query and you want the client to have a strictly typed object for the result. A @view is ideal here because the author leaderboard’s SQL structure is stable and predictable.

type TopAuthors @view(sql: """
SELECT author_id, SUM(views) as total_network_views
FROM posts
GROUP BY author_id
ORDER BY total_network_views DESC
""") {
authorId: UUID!
totalNetworkViews: Int!
}

query GetTopAuthors {
topAuthors { authorId, totalNetworkViews }
}

• When to use Native SQL: For complex aggregations, window functions, specialized PostgreSQL operators, or multi-step SQL using CTEs (WITH clauses). It’s also for any operations that don’t map cleanly to your schema or where defining the exact return type in the schema is unnecessary overhead. For example, the query below uses SQL to calculate a rolling view average on the fly using the AVG() OVER window function.

query GetAuthorRollingViews($authorId: UUID!) {
_select(
  sql: """
    SELECT 
      id, 
      views, 
      AVG(views) OVER (
        PARTITION BY author_id 
        ORDER BY published_at DESC 
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
      ) as rolling_avg_views
    FROM posts
    WHERE author_id = $1
    ORDER BY published_at DESC
    LIMIT 5
  """,
  params: [$authorId]
)
}

Mutation

• When to use native GraphQL: Standard inserts and updates. This is also a more direct path as FDC’s auto-generated resolvers provide a stable, boilerplate-free approach for data manipulation.

mutation CreateNewPost($title: String!, $content: String!) {
post_insert(data: { 
  author_id_expr: "auth.uid", 
  title: $title, 
  content: $content, 
  status: "draft" 
})
}

• When to use @transaction: When executing atomic operations across multiple tables where the updates, inserts, and condition checks can be expressed sequentially using FDC’s generated mutations. FDC handles this multi-table transaction by chaining updates and relying on @check directives to authorize mutations based on query results and prevent invalid states.

mutation SponsorAuthor($authorId: UUID!, $tipAmount: Int!) @auth(level: USER) @transaction {
reader_update(id_expr: "auth.uid", data: {
  wallet_coins_update: { inc: -$tipAmount }
}) @check(expr: "this.wallet_coins >= $tipAmount", message: "Insufficient coins")

author_update(id: $authorId, data: {
  earned_coins_update: { inc: $tipAmount }
})
}

• When to use Native SQL: Complex conditional updates, bulk data manipulation, or enforcing very specific SQL logic that goes beyond standard CRUD capabilities. While native GraphQL offers built-in authorization (like @auth and @check), Native SQL gives more flexibility to write entirely custom conditional logic in your statements with row level security. We use Native SQL here to execute a precise conditional update while still maintaining security via {_expr: "auth.uid"} in the WHERE clause, so that authors can only update their own posts.

mutation UpdatePostTitle($postId: Int!, $newTitle: String!) {
_execute(
  sql: """
    UPDATE posts 
    SET title = $2 
    WHERE id = $1 AND author_id = $3
  """,
  params: [$postId, $newTitle, {_expr: "auth.uid"}]
)
}

Native SQL is also required when an atomic operation requires bulk data manipulation, complex subqueries, or chaining data within the database using Common Table Expressions (CTEs) that standard mutations cannot express. The Native SQL example below atomically deletes a user and reassigns all their posts to a system account within Postgres.

mutation DeleteAccountAndAnonymizePosts() {
_execute(
  sql: """
    WITH deleted_user AS (
      DELETE FROM users 
      WHERE id = $1
      RETURNING id
    )
    UPDATE posts 
    SET author_id = 'system-anon' 
    WHERE author_id IN (SELECT id FROM deleted_user)
  """,
  params: [{_expr: "auth.uid"}]
)
}

Best practices

• Client-side validation: Because data-returning Native SQL fields output the Any type (JSON), the client SDK loses response type safety. You must validate the shape of the returned JSON in your client code.

• Don’t use line comments: Use block comments (/* ... */) in your SQL strings.

• Leverage context injection: Always use {_expr: "auth.uid"} or similar context markers in your params array for authorization, rather than relying on the client to send user IDs.

• Parameterize everything: Never use string interpolation for data values. Always use positional parameters ($1, $2) linked to the params array.

• Please don’t use native SQL just because you don’t like GraphQL: Don’t automatically replace standard FDC queries with Native SQL unless the SQL complexity strictly demands it. You will lose the out-of-the-box benefits of FDC’s automated relation resolution and strict client typing.

What does this mean for my app?

Client-side validation

One of the biggest shifts with Native SQL is the intentional bypass of FDC’s strong typing. Because it returns the Any type (JSON format), the FDC client-side SDK generator cannot infer strict TypeScript, Swift, Kotlin, etc. interfaces. You are trading compile-time safety for execution-time flexibility. It is essential to wrap your Native SQL calls in custom client-side validation logic to safely cast the raw JSON output.

Efficiency

Embedding complex SQL directly means your backend performance depends entirely on your query planner’s efficiency. While standard FDC resolvers optimize lookup paths predictably, Native SQL executes exactly what you dictate. It’s generally a good idea to use Cloud SQL console’s query insights (EXPLAIN ANALYZE) before deploying these raw queries to ensure you aren’t introducing full table scans.

Get started

To get started with native SQL or Data Connect in general, head over to the Firebase console and spin up a no-cost trial. If you want to get hands-on right away, jump into the codelab, or check out the docs for a full breakdown of native SQL.

Don’t forget, we’re always looking for feedback and feature requests, so don’t forget to drop us a note on Firebase UserVoice with the Data Connect tag!