Dapper and Postgres - print queries

Question

I'm using Dapper with Postgres and I would like to log some diagnostics, especially the actual query being run.

If I was using SQL Server I'd be doing

let connection = new SqlConnection(connectionString)
connection.StatisticsEnabled = true

// run query 

let stats = connection.RetrieveStatistics()

but I can't seem to find something similar for NpgsqlConnection.

What is the best way of retrieving the actual query being run for NpgsqlConnection ?

Answer 1

I was able to print the queries by creating an NpgSql logging provider, as described here.

type SqlLogger (logger: ILogger) =

    inherit NpgsqlLogger() with

        let mapLogLevel level =
            match level with
            | NpgsqlLogLevel.Trace -> LogLevel.Debug
            | NpgsqlLogLevel.Debug -> LogLevel.Debug
            | NpgsqlLogLevel.Info ->  LogLevel.Information
            | NpgsqlLogLevel.Warn ->  LogLevel.Warning
            | NpgsqlLogLevel.Error -> LogLevel.Error
            | NpgsqlLogLevel.Fatal -> LogLevel.Critical
            | _ -> LogLevel.Debug
            
            
        override __.IsEnabled(_level : NpgsqlLogLevel) = true
        override __.Log(level, connectorId, msg, ex) =
            let level = mapLogLevel level               
            logger.Log(level, ex, msg)
            

type SqlLoggerProvider (loggerFactory: ILoggerFactory ) =
    member __.loggerFactory = loggerFactory
    interface INpgsqlLoggingProvider with
        member __.CreateLogger(name) = loggerFactory.CreateLogger(name) |> SqlLogger :> NpgsqlLogger

and then registering it like so:

let configureApp (app : IApplicationBuilder) =    
    NpgsqlLogManager.Provider <- SqlLoggerProvider (app.ApplicationServices.GetService<ILoggerFactory>())
    NpgsqlLogManager.IsParameterLoggingEnabled <- true