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 ?

I do not think `StatisticsEnabled` is implemented/supported by `NpgsqlConnection`; not sure though. See if logging [this](https://stackoverflow.com/a/50875558/5779732) helps you. — Amit Joshi, Aug 19 '19 at 10:35
Note also https://github.com/npgsql/npgsql/issues/1725, which introduces .NET Core 3.0 performance counters - this would provide some statistics and also information on the queries. I'll be writing some docs about it soon. — Shay Rojansky, Aug 20 '19 at 15:05

score 5 · Accepted Answer · answered Aug 19 '19 at 12:05

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

Dapper and Postgres - print queries

1 Answers1