Passing params from a Google Cloud Function GET Request to BigQuery

Question

I have successfully deployed a google cloud function that takes in parameters from a POST request. I am now trying to change it to take in parameters from a GET request since the parameters don't contain any private data.

It seems I am correctly getting the passed in parameters, but when I then try to pass them to bigQuery it tells me I have missing parameters for my query. I know my code is correct because if I hard code the values of the parameters it works correctly e.g.:

bigQuery.createQueryJob({
    query,
    params: {
    "make": "acura",
    "model": "mdx",
    "modelYear": 2005
}
}).then...

I also know I am getting the parameters correctly because if I change my cloud function to just return the passed in query string params, it correctly returns them (commented out lines below). If I change the cloud function to use req.body instead of req.query and make it a POST request, it also works fine.

I'm at a loss as to why "params" is not being passed to createQueryJob correctly. Any help would be much appreciated. Here is the code (I had to remove the actual query for privacy reasons):

package.json:

{
    "name": "sample-http",
    "version": "0.0.1",
    "dependencies": {
        "@google-cloud/bigquery": "^2.0.6"
    }
}

index.js:

const { BigQuery } = require("@google-cloud/bigquery");

/**
 * Responds to any HTTP request.
 *
 * @param {!express:Request} req HTTP request context.
 * @param {!express:Response} res HTTP response context.
 */
exports.getRecallDataByVehicleInfo = (req, res) => {
    res.set('Access-Control-Allow-Origin', "*");
    res.set('Access-Control-Allow-Methods', 'GET, POST');
    res.setHeader(
        "Access-Control-Allow-Headers",
        "X-Requested-With,content-type"
    );

    const params = req.query;
   // res.status(200).send("make is - " + params.make + ", model is - " + params.model + ", model year is -" + params.modelYear);
   // return;

    const bigQuery = new BigQuery();

    const query = `myQuery
        where Make = @make
        and Model = @model
        and ModelYear = @modelYear`

    bigQuery.createQueryJob({
        query,
        params
    }).then(results => {
        const job = results[0];
        return job.getQueryResults({
            autoPaginate: false,
            timeoutMs: 1000000
        }, 
        callback());
    });

    const callback = () => (err, rows) => {
        if (err) {
            res.status(401).send(JSON.stringify(err));
        }
        else {
            res.status(200).send(rows);
        }
    };
}

Answer 1

Thank-you @guillaume blaquiere for the help! I changed my code to the following and it appears to be working now:

const { BigQuery } = require("@google-cloud/bigquery");

/**
 * Responds to any HTTP request.
 *
 * @param {!express:Request} req HTTP request context.
 * @param {!express:Response} res HTTP response context.
 */
exports.getRecallDataByVehicleInfoTest = (req, res) => {
    res.set('Access-Control-Allow-Origin', "*");
    res.set('Access-Control-Allow-Methods', 'GET, POST');
    res.setHeader(
        "Access-Control-Allow-Headers",
        "X-Requested-With,content-type"
    );

    const params = req.query;
    const query = `query
        where lower(Make) = @make
        and lower(Model) = @model
        and CAST(ModelYear as String) = @modelYear`

    const queryObj = {query, params};
    const options = {
        autoPaginate: false,
        timeoutMs: 1000000
    }

    const bigQuery = new BigQuery();
    bigQuery.query(queryObj, options, function(err, rows){
        if (err) {
            res.status(401).send(JSON.stringify(err));
        }
        else {
            res.status(200).send(rows);
        }
    });
}

UPDATE

The issue wasn't me using an old package or me using createQueryJob instead of query. The reason a POST would work and why hard coded params would work was because they would properly send over modelYear as a number. But when you GET the params from the queryString modelYear comes in as a string (obviously) which blows up the bigQuery sql comparison in the whereclause.

The UI for testing cloud functions assumes a POST request and so it would tell me that I wasn't passing in the make param whereas the actual call in the browser would just error out without a helpful error message because I wasn't catching and returning the error properly because I just did a .then and not a .catch on my createQueryJob. So I didn't know what the actual issue was.

Bottom line - both createQueryJob and query can accept params despite the documentation only showing it for query and there is no difference between GET and POST for cloud functions in terms of how they handle passing params into these functions.

Here is the code that shows createQueryJob also handles params: https://github.com/googleapis/nodejs-bigquery/blob/master/src/bigquery.ts#L1139

Answer 2

You don't use the right method. Have a look to the createQueryJob definition

There is no param, it's for creating a query as job. If you have a look to the official (bad) example, you have to use query method. Here its definition

Note: Why the example is bad?

In the official example, the param provided to the query method is named option. In the documentation of the query method, the first (and mandatory) param is named query, and you can optionally add an option param. So, the naming is confusing.