Query tracing support

[zyla]

It's helpful to be able to see the exact SQL queries the application executes. Current options are:

a. Wrap every Opaleye function that issues queries (runInsert, runQuery etc.) and use arrange(...)Sql to obtain the query text. b. Look at postgresql logs.

Option a. is not really sustainable. arrange(...)Sql functions are documented as "For internal use only. Do not use. Will be deprecated in version 0.6".

Option b. is not convenient at all.

To enable this, I propose to add a small abstraction over postgresql-simple Connection:

class IsConnection conn where
  execute_ :: conn -> PGS.Query -> IO Int64
  queryWith_ :: PGS.RowParser r -> conn -> PGS.Query -> IO [r]
  foldWith_ :: PGS.RowParser r -> conn -> PGS.Query -> a -> (a -> r -> IO a) -> IO a

and use IsConnection conn => conn instead of PGS.Connection.

If we provide an instance for PGS.Connection, the change will be backwards compatible.

Is it a good idea? If yes, I will be able to submit a pull request soon.

[saurabhnanda]

We had to build a complete set of wrapper functions to log SQL statement, affected rows, and time taken, because something like this isn't available.

[tomjaguarpaw]

@saurabhnanda Have you made them publically available?

[tomjaguarpaw]

Option a. is not really sustainable. arrange(...)Sql functions are documented as "For internal use only. Do not use. Will be deprecated in version 0.6".

True, but if you really want them then stable, supported, sane versions could be produced. See also https://github.com/tomjaguarpaw/haskell-opaleye/issues/311.

Is it a good idea? If yes, I will be able to submit a pull request soon.

Maybe, maybe not. I suggest you first try proposing this to https://github.com/lpsmith/postgresql-simple, because this is not Opaleye specific.

[zyla]

Thanks for the response.

I don't think postgresql-simple is the best place to add this. That said, I'm not entirely convinced opaleye is either.

In case anyone is interested, here's the code I have so far: https://github.com/zyla/haskell-opaleye/commit/c2535b2070b9a13da5f42d5d47f984cb6c723f08

But I think that the ability to easily inspect the executed SQL is necessary for a good debugging experience.

if you really want them then stable, supported, sane versions could be produced.

That would be great! That would make the wrapping approach more appealing.

What's wrong with arrange(...)Sql functions and what is needed to make them "sane"?

[tomjaguarpaw]

What's nice about printing SQL for SELECTs is that there is a Query datatype which represents performing a SELECT. Then you can run it with runQuery or show it with showSql as you wish.

• showSql

• runQuery

The problem with the arrange... functions is that they expect you to plug in the same arguments as you would to the run... functions. They don't go through any consolidating intermediate datatype like Query. Perhaps a nice idea would be to have data Manipulation a = ... which represents any INSERT/UPDATE/DELETE that returns columns of type a.

[zyla]

Perhaps a nice idea would be to have data Manipulation a = ... which represents any INSERT/UPDATE/DELETE that returns columns of type a.

Sounds reasonable. Are you thinking of an API like this?

data Manipulation a = ...

manipulationSql :: Manipulation a -> String
runManipulation :: PGS.Connection -> Manipulation a -> IO a

insertMany
  :: Table columnsW columnsR
  -> [columnsW]
  -> Manipulation ()
insertManyReturning
  :: Default QueryRunner columnsReturned haskells
  => Table columnsW columnsR
  -> [columnsW]
  -> (columnsR -> columnsReturned)
  -> Manipulation [haskells]
update
  :: Table columnsW columnsR
  -> (columnsR -> columnsW)
  -> (columnsR -> Column PGBool)
  -> Manipulation Int64
delete
  :: Table columnsW columnsR
  -> (columnsR -> Column PGBool)
  -> Manipulation Int64

That would cut the number of needed wrapper functions down to two: for runQuery and for runManipulation.

[zyla]

Another idea: Why not integrate Query into this?

data Operation a = ...

showSql :: Operation a -> String
run :: PGS.Connection -> Operation a -> IO a

query :: Query a -> Operation a
insertMany :: ... -> Operation ()
insertManyReturning :: ... -> Operation [haskells]
-- etc.

Not sure how runQueryFold would fit into this, though.

[tomjaguarpaw]

Yeah, something exactly like that. I think I prefer keeping Query and Manipulation separate because Query is "pure" whereas Manipulation has "side effects".