Actual resulting query?

[Gys]

I was hoping the after hook would include the resulting query, as it is actually passed to the database.

So instead of showing the original query like

'select id, first, last from users where id=?', '12'

It would include

'select id, first, last from users where id=12'

Useful for debugging edge cases, logging of all actual statements (to rebuild a database or specific situation) and other things.

[qustavo]

There are cases where you do want to have the parameters separated from the query, if we do it this way, we would need to provide a helper function to parse the query and split the arguments from the original query (i.e you want to log the user=id from a SELECT * FROM users WHERE id=?'.

What about doing the opposite, providing a method to return the resulting query, so that you could print it whenever you need it?

[Gys]

Yes I understand having the parameters separated from the query has use cases as well. That situation is covered in the 'before' hook. Because that is the 'input' to the driver.

In the same logic the 'after' hook could (should? ;)) have the actual passed query, representing the 'result' or 'output' of the driver.

[qustavo]

The example I wrote above I'd like to log after the query is executed. Anyway, is the resulting query built by the driver, meaning that it depends on each db driver? Could you imagine a tentative implementation of this function?

[keegancsmith]

@Gys I believe the driver doesn't construct a final query (at least for the popular libraries). The over the wire protocol sends the SQL and the arguments separately.

Depending on which DB you use how you translate this into native sql changes. For your specific purpose I would just write some code to do this for you. Given you are using ? you are probably using postgres, which means maybe this little hack works :)

fmt.Sprintf(strings.ReplaceAll(query, "?", "%q"), args...)

[qustavo]

That's a good point, if @keegancsmith is right (which I think he is) we can't be sure we're producing the exact statement that will be executed.

If the example above works for you, you could create a hook that generates the tentative query and inject it into the context so that other hooks can use:

func (h *Hooks) Before(ctx context.Context, query string, args ...interface{}) (context.Context, error) {
    q := fmt.Sprintf(strings.ReplaceAll(query, "?", "%q"), args...)
    return context.WithValue(ctx, "resulting-query", q), nil
}

[Gys]

I looked into the mysql driver and found this interpolateParams() func that transforms the query with arguments into a final query: https:// github.com/go-sql-driver/mysql/blob/b4242bab7dc57d57fef955900943999b6fb34b67/connection.go#L183

This func is called from inside exec() and query(). So the final query is constructed by the driver, but sadly at a low level.

Yes, I can recreate some kind of query with the passed arguments myself. But that limits the applications. I was hoping to get the actually send query.

[qustavo]

But that limits the applications.

Mind to share those limitations?

[Gys]

I wrote before 'Useful for debugging edge cases, logging of all actual statements (to rebuild a database or specific situation)'. A query builder seems to be different for each driver so to copy that reliably seems a bit over the top. On the other hand, a simple one that more or less does the job ok-ish seems less useful.

[qustavo]

Well, this seems to be outside the scope of sqlhooks I'm closing this, but feel free to re-open if you think that there is something that can be done