Interpolating Query with Cloud Spanner in Node.js

Question

I'm trying to dynamically add a LIMIT and an ORDER BY clause to my spanner query using the Cloud Spanner lib in Node.js:

function getPosts (query) {
  const { limit, start, sortBy, sortOrder } = query;

  delete query.start;
  delete query.limit;
  delete query.sortBy;
  delete query.sortOrder;

  const meta = {};

  let limitClause = '';
  let sortClause = '';

  if (limit) {
    limitClause = `
    LIMIT @limit
    OFFSET @start
    `;

    meta.limit = limit && parseInt(queryObj.limit, 10);
    meta.start = start ? parseInt(start, 10) : 0;
 }

  if (sortBy) {
    sortClause = `ORDER BY @sortBy ${(sortOrder && sortOrder.match(/^desc/i)) ? 'DESC' : 'ASC'}`;

    meta.sortBy = sortBy;
  }

  const [postsRows] = await myDatabase.run({
    sql: `
      SELECT *
      FROM   posts@{FORCE_INDEX=posts_userId}
      WHERE  userId = @userId
      ${sortClause}
      ${limitClause}
    `,
    params: { userId, ...meta },
  });

   return orderRows;
}

Note that myDatabase is an instance of Spanner. (https://cloud.google.com/nodejs/docs/reference/spanner/2.0.x/Spanner)

At the moment, the LIMIT clause works as expected, but the response appears as if the ORDER BY clause is being ignored.

If I replace the 2nd if statement with

if (sortBy) {
    sortClause = `ORDER BY ${sortBy} ${(sortOrder && sortOrder.match(/^desc/i)) ? 'DESC' : 'ASC'}`;
  }

Then it works as expected, but I don't want to interpolate raw strings into my query like this.

I'm not sure exactly how params are interpolated into the query, but it works for the userId, limit, and start variables.

It would also be helpful to see the result of the interpolation that the Spanner instance's run method produces, I'm not sure if/how I can view that.

Answer 1

From Cloud Spanner's documentation on query parameters:

Query parameters can be used in substitution of arbitrary expressions. They cannot, however, be used in substitution of identifiers, column names, table names, or other parts of the query itself.

The ORDER BY expression is a column name, which is not a supported type of query parameter. You'll need to interpolate into the query string in this case.