Improve SQL-API metrics

[ethervoid]

Include the POST requests in our metrics. The main objective of this change is to have DDL commands included for the new Ghost tables feature

[oleurud]

We will need to add a new object in the logs. The current logs written has the following format :

[:date] :remote-addr :method :req[Host]:url :status :response-time ms -> :res[Content-Type] (:res[X-SQLAPI-Profiler]) (:res[X-SQLAPI-Errors])

My proposal is to add a new JSON object at the end in ther same way as X-SQLAPI-Errors:

[:date] :remote-addr :method :req[Host]:url :status :response-time ms -> :res[Content-Type] (:res[X-SQLAPI-Profiler]) (:res[X-SQLAPI-Errors]) (:res[X-SQLAPI-Log])

The new object will be generic to allow us to add everything in this JSON object in the future. It is not an objective of the current issue to define a future log structure, but the new object should be generic and flexible enough.

{
    request: {
        q: "SELECT * FROM ..."
    }
}

How do you see it @dgaubert ?

This approach will require to make some task with the infrastructure team. I have not reviewed it yet, but if I don't remember badly, I think I will be able to prepare the PRs myself

[oleurud]

We already have something that works: https://github.com/CartoDB/CartoDB-SQL-API/blob/master/app/server.js#L116 But we have the same problem as adding a new object, we need to make some task with the infrastructure team.

In my opinion, I prefer to create a new object as I explained in the previous comment to be more generic and flexible for the future.

But I like a point from it: if for some reason we need to add the queries to the log, it is possible adding :sql to the log format parameter. So, I think it makes sense to add a config parameter to enable/disable the query logging

[oleurud]

The infrastructure part: https://github.com/CartoDB/cartodb-platform/pull/5399

[dgaubert]

Just one thing:

As we are using log4js with the following format:

[:date] :remote-addr :method :req[Host]:url :status :response-time ms -> :res[Content-Type] (:res[X-SQLAPI-Profiler]) (:res[X-SQLAPI-Errors])

That means we also have access to the request, its params and, its body, right?. No sure if it's usefule at all, just thinking out loud, but something like :res[locals][query] could work. Thus, we can avoid to save the query in a response header.

What do you think?

[oleurud]

ummm it will be nice, but I am not sure if it works, let me check it.

[oleurud]

It would be nice, but it doesn't works. Of course, we could add the feature...

From https://github.com/CartoDB/log4js-node/blob/master/lib/connect-logger.js#L18-L28 the paramters that we can use are:

 - `:req[header]` ex: `:req[Accept]`
 - `:res[header]` ex: `:res[Content-Length]`
 - `:http-version`
 - `:response-time`
 - `:remote-addr`
 - `:date`
 - `:method`
 - `:url`
 - `:referrer`
 - `:user-agent`
 - `:status`

[oleurud]

Done 🚀