Edit - Stack Overflow

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

Unspecific error for Firebase Data Connect nested SQL inserts with _execute

I need to insert data in multiple tables at once in my Firebase Data Connect db with native SQL. When calling the mutation from my cloud function it fails and Data Connect will always throw the same Invalid SQL statement error without any further information, making it really difficult to find the cause.

I cannot find documentation on how to debug this Firebase Data Connect error further.

When extracting each insert and testing it by individually calling it from my app sequentially via the standard mutations.gql, everything works, however calling the inserts together in a nested SQL statement from my cloud function does not and I just get the error "Invalid SQL statement", even though the SQL and input data is definitely correct as each insert individually works.

Does anyone else have experience with nested raw SQL inserts in Firebase Data Connect or knows how to further debug such statements?

Here is my cloud function:

await dataConnect.executeGraphql(
 `
 mutation InsertRecipe(
 $param1: String!,
 $param2: String,
 $param3: String!,
 $param4: String!,
 $param5: [String!]!,
 $param6: String!,
 $param7: Int!,
 $param8: Boolean!
 ) {
 _execute(
 sql: """
 WITH ensure_user AS (
 INSERT INTO "User" (id, displayName)
 VALUES (2,ドル null)
 ON CONFLICT (id) DO NOTHING
 ),
 new_recipe AS (
 INSERT INTO Recipe (
 title,
 authorId,
 subtitle,
 description,
 foodTypes,
 imageUrl,
 estimatedMinutes,
 isTest
 )
 VALUES (1,ドル2,ドル3,ドル4,ドル5,ドル6,ドル7,ドル8ドル)
 RETURNING id
 )
 SELECT id FROM new_recipe;
 """,
 params: [
 $param1,
 $param2,
 $param3,
 $param4,
 $param5,
 $param6,
 $param7,
 $param8
 ]
 )
 }
 `,
 {
 variables: {
 param1: draft.param1,
 param2: draft.param2,
 param3: draft.param3,
 param4: draft.param4,
 param5: draft.param5,
 param6: param6,
 param7: draft.param7,
 param8: draft.param8,
 },
 }
);

Edit: When removing the SELECT id FROM new_recipe - as Pedro correctly mentioned - the same error still gets thrown.

I tried different table names like user, User, ~~(削除) "user" (削除ここまで)~~, "User" etc. [Edit: Turns out I tried all except "user", which is the correct name], but as the error is always the same I do not know what the cause is. Simple lookups as SELECT 1; do work so the access to Data Connect is not the problem, which I believe would result in a different error anyway.

Wrong data formats or null/undefined values are also not the cause as the same individual queries with the same input data called from my app directly do work, only nested calls do not work.

So the problem has to be either:

Data Connect does not support native SQL in the way I am trying to do, or
Setting and calling mutations with executeGraphgl does not work the way I am doing it.

However the error message and the documentation do not give me more to work with.

Here is the error:

FirebaseDataConnectError: Invalid SQL statement
 at DataConnectApiClient.makeGqlRequest (/workspace/node_modules/firebase-admin/lib/data-connect/data-connect-api-client-internal.js:270:19)
 at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
 at async DataConnectApiClient.executeGraphqlHelper (/workspace/node_modules/firebase-admin/lib/data-connect/data-connect-api-client-internal.js:129:26) {
 errorInfo: {
 code: 'data-connect/query-error',
 message: 'Invalid SQL statement'
 },
 codePrefix: 'data-connect'
}

When replacing the _execute SQL statement with a gql mutation the query works as expected:

await dataConnect.executeGraphql(
 `
 mutation InsertRecipe(
 $param1: String!,
 $param2: String,
 $param3: String!,
 $param4: String!,
 $param5: [String!]!,
 $param6: String!,
 $param7: Int!,
 $param8: Boolean!
 ) {
 recipe_insert(data: {
 title: $param1
 authorId: $param2
 subtitle: $param3
 description: $param4
 foodTypes: $param5
 imageUrl: $param6
 estimatedMinutes: $param7
 isTest: $param8
 })
 }
 `,
 {
 variables: {
 param1: draft.param1,
 param2: draft.param2,
 param3: draft.param3,
 param4: draft.param4,
 param5: draft.param5,
 param6: param6,
 param7: draft.param7,
 param8: draft.param8,
 },
 }
);

Answer*

I'm a Firebaser on the Data Connect team. This is a great question. You actually ran into a few different constraints overlapping at the same time here, it's a super common gotcha when writing Native SQL. Let's break down exactly what's happening. 
 
***1. Schema Casing & Type Casting*** 
The main reason your query initially failed comes down to how Data Connect maps GraphQL to Postgres. Even though you define things like `User` and `authorId` in your GraphQL schema, Data Connect automatically creates the underlying Postgres tables and columns in **snake_case** (`"user"` and `author_id`). 
When you drop down into Native SQL, you have to use those actual database names, not the GraphQL camelCase names. You can read more about this mapping in the [Data Connect Schemas Guide](https://firebase.google.com/docs/data-connect/schemas-guide#movie-table:~:text=%40table%28name%29,values%20during%20insert%2E). As @Pedro mentioned, the `_execute` operation doesn't respect trailing `RETURNING` or `SELECT` clauses. The database will still execute the statement, but the expected data won't be passed back to the client—you will just get the standard `_execute` acknowledgment instead. However, that wouldn't cause in an error. 
 
***2. Why the SDK hides the real error*** 
You mentioned only seeing a generic error message, which makes debugging really frustrating. Our backend actually caught the exact syntax error, but all of our generated SDKs (including `firebase-admin`) intentionally mask database-level errors. This is a security best practice (CWE-209) to ensure your app doesn't accidentally leak internal database schema details to the client runtime. 
**How to see the real errors:** 
To see exactly what Postgres is complaining about, you just need to bypass the SDK and check the backend logs directly:

- **Locally:** Check the `dataconnect-debug.log` file generated by the emulator in your project folder.

- **In Production:** Check **Google Cloud Logging (Logs Explorer)** for the Data Connect service in your Google Cloud console.

3\. The Workaround for CTEs and `SELECT` 
As Pedro correctly pointed out, `execute` strictly performs DML operations and will just ignore any trailing `SELECT` statements. Furthermore, as noted in our [Native SQL docs](https://firebase.google.com/docs/data-connect/native-sql#:~:text=Note%3A%20Data%2Dmodifying%20CTEs%20%28using%20INSERT%2C%20UPDATE%2C%20or%20DELETE%20inside%20a%20WITH%20clause%29%20are%20only%20supported%20by%20_execute%2E), data-modifying CTEs (like your `INSERT` inside a `WITH` block) are only supported by `execute`, not `executeReturning`. 
Because of these rules, you can't run a complex data-modifying CTE and return the ID in a single parameterized request. 
One alternative is to wrap your CTE in a custom Postgres function that returns the ID, which you can then call using `_executeReturning`*. * 
The cleanest workaround in your case is to split the logic into two sequential queries in your Node function: 
JavaScript

```
// Step 1: Perform the inserts using _execute (Note the snake_case and ::uuid casts)
await dataConnect.executeGraphql(`
 mutation InsertRecipeCTE(
 $param1: String!
 $param2: String!
 $param3: String!
 $param4: String!
 $param6: String!
 $param7: Int!
 $param8: Boolean!
 ) {
 _execute(
 sql: """
 WITH ensure_user AS (
 INSERT INTO "user" (id, display_name)
 VALUES (2ドル::uuid, 'Default Name')
 ON CONFLICT (id) DO NOTHING
 )
 INSERT INTO "recipe" (
 title, author_id, subtitle, description, image_url, estimated_minutes, is_test
 )
 VALUES (1,ドル 2ドル::uuid, 3,ドル 4,ドル 5,ドル 6,ドル 7ドル);
 """,
 params: [$param1, $param2, $param3, $param4, $param6, $param7, $param8]
 )
 }
`, variables);

// Step 2: Fetch the generated ID using a separate _select query
const result = await dataConnect.executeGraphql(`
 query GetRecipeId($title: String!, $authorId: String!) {
 _select(
 sql: """
 SELECT id FROM "recipe" 
 WHERE title = 1ドル AND author_id = 2ドル::uuid
 """,
 params: [$title, $authorId]
 )
 }
`, { variables: { title: variables.param1, authorId: variables.param2 } });

const recipeId = result.data._select[0].id;
```

Hope this helps clear up the Native SQL debugging process!

Draft saved

Draft discarded

Required fields are marked with *

Edit Summary*

Cancel

Add a comment |

How to Edit

Correct minor typos or mistakes
Clarify meaning without changing it
Add related resources or links
Always respect the author’s intent
Don’t use edits to reply to the author

How to Format

create code fences with backticks ` or tildes ~
```
like so
```
add language identifier to highlight code
```python
def function(foo):
print(foo)
```
put returns between paragraphs
for linebreak add 2 spaces at end
_italic_ or **bold**
indent code by 4 spaces
backtick escapes `like _so_`
quote by placing > at start of line
to make links (use https whenever possible)

<https://example.com>

[example](https://example.com)

<a href="https://example.com">example</a>

formatting help »
answering help »

How to Tag

A tag is a keyword or label that categorizes your question with other, similar questions. Choose one or more (up to 5) tags that will help answerers to find and interpret your question.

complete the sentence: my question is about...
use tags that describe things or concepts that are essential, not incidental to your question
favor using existing popular tags
read the descriptions that appear below the tag

If your question is primarily about a topic for which you can't find a tag:

combine multiple words into single-words with hyphens (e.g. python-3.x), up to a maximum of 35 characters
creating new tags is a privilege; if you can't yet create a tag you need, then post this question without it, then ask the community to create it for you

popular tags »

lang-sql

CollectivesTM on Stack Overflow

Unspecific error for Firebase Data Connect nested SQL inserts with _execute

Answer*