Microsoft.Data.Sqlite: Feature request: tracing, debugging

[ghost]

I'd like to be able to get some debug output of what's actually being sent to the database, especially when using an ORM like Dapper. I suspect you could add some strategic Trace.Write calls before calling sqlite3.raw.

[ajcvickers]

Putting this on the backlog. This should be done using DiagnosticSource/DiagnosticListener. These are the event APIs that are now being used across all .NET.

[bricelam]

Workaround

You can easily leverage SQLite's native tracing (via SQLitePCL.raw):

SQLitePCL.raw.sqlite3_trace(
    connection.Handle,
    (_, statement) => Trace.WriteLine(statement, "SQLite"),
    null);

[ghost]

Thanks for the workaround - that'll help for now.

[ghost]

Commenting again after hitting another situation where this'd be useful. I can't seem to get the workaround to work in .NET Core - I suspect I'm doing something really silly because I can't get the SQLitePCL type to be found in VS2017.

Edit: I was using Microsoft.Data.Sqlite 1.1.1, it worked after upgrading to some preview (!) release in the 2.1.0 series.

[bricelam]

See also https://github.com/dotnet/runtime/issues/26085

[bricelam]

@roji Which of the dozen logging/telemetry/diagnostics APIs should I use for this?

[roji]

The shiny new thing is definitely OpenTelemetry. For tracing, that means implementing these experimental specs; it's basically just start/stop activity events on command execution (for now), all pretty straightforward.

The Npgsql code implementing this which you can copy is here, here are the Npgsql docs on it. For Npgsql I also did a separate Npgsql.OpenTelemetry nuget just to make the configuration slightly easier (this adds the AddNpgsql extension method over TracerProviderBuilder).

I'd say that's the first/high-priority thing to do... Then there's also OpenTelemetry metrics (experimental database specs) which are like the older event counters; I haven't done this for Npgsql (which does event counters for now) - I know @bgrainger is interested in this.

[bgrainger]

A "specification" for using ActivitySource with ADO.NET is here: https://github.com/mysql-net/MySqlConnector/issues/1036. (The net.* tags may be inapplicable for SQLite.)

[bricelam]

I wonder what we'd need to do to get it to show up in the Visual Studio performance profiler database tool...

[roji]

I never quite figured out how that works - there's a chance it's hard-wired to SqlClient's output diagnostics :/ Would be good to know for sure!

[bricelam]

I vaguely remember the creator saying that it was specific to SqlClient at the time of release, but that it would be "easy" to add other providers. We'd have to track down the current owner to figure out what's possible. Maybe they'd be interested in listening for specific OpenTelemetry events.

[roji]

That sounds like a great feature to add to VS, especially since the OpenTelemetry tracing events are (kinda) standardized.

[bricelam]

Looks like they currently listen for EF Core's CommandExecuting message and SqlClient's WriteCommandBefore. I'll track down the owner so we can talk to them about OpenTelemetry.

[RassK]

@bricelam / @roji any news on this? Looking forward to add this into OOTB features of OpenTelemetry .NET Automatic Instrumentation. If it's going to take another year, we may consider to monkey patch it temporarily.

[bricelam]

It's gonna be a while. I wrote a bunch of code earlier this year for this, but the way you currently configure OpenTelementry assumes there's an application service provider that you can pull the options from. This is not the case for new SqliteConnection().

[RassK]

@bricelam maybe it's simpler to do it in 2 parts? The main packages like SqlClient Instrumentation for OpenTelemetry and HttpClient and HttpWebRequest instrumentation for OpenTelemetry are no different.

[roji]

@bricelam when you get time to focus on this, let's chat - I can show you what I did in Npgsql. It all seems to work pretty well and isn't dependent on an application service provider (or DI).

[RassK]

@bricelam @roji

Happy new year!

Wondering if you happened to have time to sync? This issue has raised up again in Splunk to match our OpenTelemetry offerings. I'm glad to invest some time to this issue and get it done properly for a whole OpenTelemetry Community, instead of going to monkey patching route.