3

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,
    },
  }
);
Improve this question
0

2 Answers 2

Reset to default
3

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. 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, 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!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

The problem is that _execute in Firebase Data Connect only accepts a DML statement (insert, update, delete) Your query is like this:

SELECT id FROM new_recipe;

So this reject with the invalid SQL statement, Data modified CTEs are supported by _execute but the final statement has to be insert, update or delete.

This pattern here is valid I believe:

WITH ensure_user AS (
  INSERT INTO "User" (id, displayName)
  VALUES ($2, null)
  ON CONFLICT (id) DO NOTHING
)
INSERT INTO Recipe (
  title, authorId, subtitle, description, foodTypes, imageUrl, estimatedMinutes, isTest
)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8);
Improve this answer

4 Comments

Your CTE still has no shape, due to not having a RETURNING clause.
While this is correct, the error is still thrown. As the "Invalid SQL statement" error is always identical and has no further information, it seems impossible to know wether a change resolved the initial cause and created a new problem or if the initial cause persists. I opened a Firebase uservoice idea regarding this.
@dondi Have you tried adding RETURNING id to the CTE? (As per the example on the help page you linked in the OP; firebase.google.com/docs/data-connect/native-sql#advanced-cte .)
Yes, no difference.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.