Google Cloud Spanner Node Library won't accept parameterized SQL-CodePudding

I'm attempting to implement the node Google Spanner library (v6.6.0) and I'm running into an issue with parameterized sql.

const {Spanner} = require('@google-cloud/spanner');

async function doStuff() {
    // Creates a client
    const spanner = new Spanner({
        projectId: "<my_project_id>",
    });

    // Gets a reference to a Cloud Spanner instance and database
    const instance = spanner.instance("<my_instance>");
    const database = instance.database("<my_db>");

    const query = {
        sql: `SELECT id, name, text
        FROM greetings WHERE name = 'Name'`,
    };

    const [results] = await database.run(query);
    console.log("Results: "   JSON.stringify(results));

}

doStuff().then(() => console.log("Complete"));

This is not parameterized, and when I run this I get the following output:

Results: [{"id":"8694b2b9-add0-4024-bb22-bd711bbda9d4","name":"Name","text":"New Message"}]
Complete

as expected.

However, if I update the query object to use parameterization, like so:

    const query = {
        sql: `SELECT id, name, text
        FROM greetings WHERE name = @Name`,
        params: {
           Name: 'Name'
    };

I get the following error:

node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

Error: 3 INVALID_ARGUMENT: Invalid parameter name: name. Expected one of 'p1', 'p2', ..., 'p65535'
    at Object.callErrorFromStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call.js:31:19)
    at Object.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client.js:360:49)
    at <project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:111:35
    at Object.onReceiveStatus (<project_path>\node_modules\grpc-gcp\build\src\index.js:73:29)
    at InterceptingListenerImpl.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:106:23)
    at Object.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client-interceptors.js:328:181)
    at <project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:188:78
    at process.processTicksAndRejections (node:internal/process/task_queues:77:11)
for call at
    at ServiceClientImpl.makeServerStreamRequest (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client.js:343:34)
    at ServiceClientImpl.<anonymous> (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\make-client.js:105:19)
    at <project_path>\node_modules\@google-cloud\spanner\build\src\v1\spanner_client.js:221:29
    at <project_path>\node_modules\google-gax\build\src\streamingCalls\streamingApiCaller.js:38:28
    at <project_path>\node_modules\google-gax\build\src\normalCalls\timeout.js:44:16
    at Object.request (<project_path>\node_modules\google-gax\build\src\streamingCalls\streaming.js:130:40)
    at makeRequest (<project_path>\node_modules\retry-request\index.js:141:28)
    at retryRequest (<project_path>\node_modules\retry-request\index.js:109:5)
    at StreamProxy.setStream (<project_path>\node_modules\google-gax\build\src\streamingCalls\streaming.js:121:37)
    at StreamingApiCaller.call (<project_path>\node_modules\google-gax\build\src\streamingCalls\streamingApiCaller.js:54:16) {
  code: 3,
  details: "Invalid parameter name: name. Expected one of 'p1', 'p2', ..., 'p65535'",
  metadata: Metadata {
    internalRepr: Map(1) {
      'grpc-server-stats-bin' => [
        Buffer(10) [Uint8Array] [
          0, 0, 236, 206, 58,
          2, 0,   0,   0,  0
        ]
      ]
    },
    options: {}
  }
}

Node.js v18.12.1

Process finished with exit code 1

That seems odd that the parameter names must be named p1, p2, etc, because it is not the case in the Google Cloud Spanner docs, but I went ahead and tried it anyway.

So, I convert the query object like so (as instructed by the error):

    const query = {
        sql: `SELECT id, name, text
        FROM greetings WHERE name = @p1`,
        params: {
            p1: "Name"
        }
    };

Now I get the following error:

node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

Error: 3 INVALID_ARGUMENT: [ERROR] column "p1" does not exist
    at Object.callErrorFromStatus (<project_path>\moleculer-gcp-spanner\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call.js:31:19)
    at Object.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client.js:360:49)
    at <project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:111:35
    at Object.onReceiveStatus (<project_path>\node_modules\grpc-gcp\build\src\index.js:73:29)
    at InterceptingListenerImpl.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:106:23)
    at Object.onReceiveStatus (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client-interceptors.js:328:181)
    at <project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\call-stream.js:188:78
    at process.processTicksAndRejections (node:internal/process/task_queues:77:11)
for call at
    at ServiceClientImpl.makeServerStreamRequest (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\client.js:343:34)
    at ServiceClientImpl.<anonymous> (<project_path>\node_modules\google-gax\node_modules\@grpc\grpc-js\build\src\make-client.js:105:19)
    at <project_path>\node_modules\@google-cloud\spanner\build\src\v1\spanner_client.js:221:29
    at <project_path>\node_modules\google-gax\build\src\streamingCalls\streamingApiCaller.js:38:28
    at <project_path>\node_modules\google-gax\build\src\normalCalls\timeout.js:44:16
    at Object.request (<project_path>\node_modules\google-gax\build\src\streamingCalls\streaming.js:130:40)
    at makeRequest (<project_path>\node_modules\retry-request\index.js:141:28)
    at retryRequest (<project_path>\node_modules\retry-request\index.js:109:5)
    at StreamProxy.setStream (<project_path>\node_modules\google-gax\build\src\streamingCalls\streaming.js:121:37)
    at StreamingApiCaller.call (<project_path>\node_modules\google-gax\build\src\streamingCalls\streamingApiCaller.js:54:16) {
  code: 3,
  details: '[ERROR] column "p1" does not exist',
  metadata: Metadata {
    internalRepr: Map(1) {
      'grpc-server-stats-bin' => [
        Buffer(10) [Uint8Array] [
          0, 0, 197, 240, 249,
          3, 0,   0,   0,   0
        ]
      ]
    },
    options: {}
  }
}

Node.js v18.12.1

Process finished with exit code 1

I can't seem to figure out how to get parameterized sql to work on a database.run() function call. Am I doing something wrong or have I set my database up incorrectly? I'm using the postgresql dialect if that makes a difference.

CodePudding user response：

I'm using the postgresql dialect if that makes a difference.

Yes, that makes the difference. PostgreSQL-dialect databases uses the PostgreSQL SQL dialect. That means that queries must use PostgreSQL style query parameters, which is $1, $2, .... So your query should be:

    const query = {
        sql: `SELECT id, name, text
        FROM greetings WHERE name = $1`,
        params: {
           p1: 'Name'
    };

See https://github.com/googleapis/nodejs-spanner/blob/02c6e599a7b744a5c610c8d801229c7299abb668/samples/pg-query-parameter.js#L51 for a full sample.