Accessing outoing query as a Query, for tracing

[jwoudenberg]

Is there a way I can get my hands on outgoing queries as a String/ByteString/Text. I'd like to be able to send the queries we make to a tracing service such as New Relic.

I see I can get my hand on the raw query in unsafeModifyQuery, but I can't use that function to do something effectful like sending a request to a tracing service.

Love this library by the way. Thank you so much for your work on it!

[dylex]

Do you need something that's a valid SQL query, or just something you can log? Are you looking for a hook that will run for every query or just a way you can add your own given the query? I believe all query objects have a Show instance that should include the query in some form, from which you could parse out the query if necessary. It would be possible to add some function to extract the query itself, but, depending on the type of query, it may or may not include placeholders, literal values, etc, so you'd have to be prepared for that.

[jwoudenberg]

Thank you getting back to me, and so fast!

Do you need something that's a valid SQL query, or just something you can log?

A valid SQL query. I'd like to pass it to a service like NewRelic, which will parse the query to do grouping, automatically run EXPLAIN on slow queries, and other fancy things. That requires a valid SQL query.

I don't know how feasible this is, but I guess I'm looking for a function like this:

pgQuery' ::  PGQuery q a => PGConnection -> q -> (ByteString, IO [a])

ByteString being the query that would be sent to the database, not wrapped in the IO because it would be nice to see the query even (or specifically) in the case of a failure.

Are you looking for a hook that will run for every query or just a way you can add your own given the query?

A hook is great, but I'm also happy to write my own hook if that's possible. We're already wrapping the pgQuery function and pgSQL quasiquoter so we can access the quasi-quoted string, and the query object, but the query object I don't think we can introspect (it's wrapped in an unexposed QueryParser type) and the quasi-quoted string contains placeholders.

I believe all query objects have a Show instance that should include the query in some form, from which you could parse out the query if necessary.

I took a look at this, and it looks something like this:

QueryParser (SimpleQuery ("<query here>"))

So it looks like we could parse the query out of the central part. I'm a bit nervous about building logic on top of Show, although for logging/tracing it might be okay.

It would be possible to add some function to extract the query itself, but, depending on the type of query, it may or may not include placeholders, literal values, etc, so you'd have to be prepared for that.

Yeah, that makes sense. In our case we only use pgQuery in combination with the pgSQL quasi quoter, so I think for those it should always be possible to resolve placeholders?

[dylex]

Okay, I think what I'd propose then is a new function:

pgGetQueryString :: PGQuery q => PGConnection -> q -> ByteString

You could then use this in a wrapper to get the query string and execute it. Not completely set on the name, but will think about it. As it stands, the PGConnection argument is necessary, but there's some refactoring I'm thinking about that may be able to eliminate it. I'll work on this, if that makes sense.

[dylex]

As long as you only use simple queries (as opposed to prepared queries with $), there won't be any placeholders.

[jwoudenberg]

That looks like exactly what we need, thank you so much for picking this up!

[dylex]

Released 0.6.1.0 with PGQuery.getQueryString.