--- title: "Writing SQL in SQL Connect" description: "Writing SQL in SQL Connect" published: "2026-03-24" last_updated: "2026-03-24" authors: - "Cynthia Wang (Developer Relations Engineer)" categories: - "SQL Connect" - "PostgreSQL" - "GraphQL" canonical_url: "https://firebase.blog/posts/2026/03/fdc-native-sql" --- # Writing SQL in SQL Connect *Published March 24, 2026 by Cynthia Wang (Developer Relations Engineer)* --- > **Note:** Firebase Data Connect has been renamed to SQL Connect. Learn more in the [SQL Connect announcement](https://firebase.blog/posts/2026/04/whats-new-sql-connect). Data Connect (now SQL 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](https://g.dev/firebase/data-connect-native-sql-guide). 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](https://g.dev/firebase/data-connect-native-sql-guide) 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. **`queries.gql`** ```graphql # 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. **`mutations.gql`** ```graphql # 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](https://g.dev/firebase/data-connect-native-sql-guide). ### 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. **`schema.gql`** ```graphql 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). **`queries.gql`** ```graphql 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. **`schema.gql`** ```graphql 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. **`queries.gql`** ```graphql 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. **`mutations.gql`** ```graphql 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. **`mutations.gql`** ```graphql 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. **`mutations.gql`** ```graphql 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. **`mutations.gql`** ```graphql 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](https://g.dev/cloud/sql-query-insights-fdc-native-sql) (`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](https://g.dev/firebase/console-fdc-native-sql) and spin up a [no-cost trial](https://g.dev/firebase/data-connect-spark-trial). If you want to get hands-on right away, jump into the [codelab](https://g.dev/firebase/data-connect-web-codelab), or check out the [docs](https://g.dev/firebase/data-connect-native-sql-guide) 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](https://firebase.uservoice.com/forums/948424-general?category_id=501596) with the Data Connect tag!