Return to Revisions

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,
 },
 }
);

I tried different table names like user, User, "user", "User" etc., 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'
}