How to enable automatic logging of SQL statements with Persistent

Question

I have searched for a clear answer to this question but haven't been able to find one yet - How do I enable automatic logging of SQL statements being executed by persistent? Can someone give me a small example program for this?

Following is an example program that currently does not have logging. How do I enable logging in it?

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
    Person
    name Text
    status Text Maybe
    deriving Show
|]

main :: IO ()
main = runSqlite ":memory:" $ do
    runMigration migrateAll
    insert (Person "Oliver Charles" Nothing)
    insert (Person "Jon Snow" Nothing)
    insert (Person "Marky Mark" (Just "helloo helloo"))
    noStatusPeople >>= mapM_ (liftIO . print)
    where
        noStatusPeople =
            select $ from $ \person -> do
                where_ (person ^. PersonStatus ==. val Nothing)
                return (person ^. PersonName)

score 5 · Accepted Answer · edited Apr 16 '17 at 16:33

You need to call your SQL code in a Monad that implements MonadLogger and not just IO. (see http://hackage.haskell.org/package/monad-logger-0.3.13.1/docs/Control-Monad-Logger.html#v:runStdoutLoggingT). However, runSqlite already sets up the logging (to none...) for you, so you need to use the lower level function withSqliteConn. So for example, if you change your code to:

import Control.Monad.Logger
import Control.Monad.Trans.Resource

runResourceT $ runStdoutLoggingT $ withSqliteConn ":memory:" . runSqlConn  $ do...

(with the proper dependencies on resourcet and monad-logger), you can have your SQL statement written to standard out.

As a real-life example, have a look at my scion-class-browser project: in https://github.com/JPMoresmau/scion-class-browser/blob/5ab9c7576f8faf93299826e72defe70dd5b6dd6f/src/Server/PersistentCommands.hs#L93 you see the call to runSqlite. runLogging is a helper function, to switch between logging or no logging, defined in https://github.com/JPMoresmau/scion-class-browser/blob/f7f2ab0de4f4edb01b307411abf0aa951a3c7c48/src/Scion/PersistentBrowser/DbTypes.hs#L16 (the currently build version does not log, replace by the commented out code).

Of course, you can instead of using a simple dump to stdout or stderr, write your own implementation of MonadLogger that does what you want.

As a side note, your code doesn't print out the matching records because you shouldn't compare with val Nothing but instead use isNothing:

where_ (isNothing $ person ^. PersonStatus)

I may be missing something. I tried putting in your code but it doesn't seem to have an effect. Is the following correct? `main :: IO ()` `main = runResourceT $ runStdoutLoggingT $ runSqlite ":memory:" $ do` The output is still only - `Migrating: CREATE TABLE "person"("id" INTEGER PRIMARY KEY,"name" VARCHAR NOT NULL,"status" VARCHAR NULL)` — Anupam Jain, May 13 '15 at 16:47
Sorry, my mistake, runSqlLite already sets up the logging (to none), so you need to use the lower level functions. I've updated my answer. — JP Moresmau, May 13 '15 at 19:26
That worked! Thanks! Yes I realised that I should have used `isNothing`, that's the bug that took me a while to track down without SQL logging and prompted this question :) — Anupam Jain, May 14 '15 at 05:14
@JPMoresmau would you know how to to this in a yesod project? It seems like I should change this line `logFunc = messageLoggerSource tempFoundation appLogger` — mb21, Jul 11 '17 at 13:55

How to enable automatic logging of SQL statements with Persistent

1 Answers1