Enable Observability

[kyuff]

What do you want to change?

I propose a mechanism that allows users of the generated code to instrument the Querier.

Use-cases could be, but are not limited to:

• Start Spans for OpenTelemetry tracing
• Create Histograms in a Prometheus metric
• Log that a SQL command was executed with it's name

Proposed Solution

Add a new method on the Queries struct like this:

func (q *Queries) WithObserver(o func(ctx context.Context, methodName string) (context.Context, func(err error) error)) *Queries {
  return &Queries{
    db: q.db,
    observer: o,
  }
}

In addition the generated methods will be updated to this pattern:

func (q *Queries) InsertData(ctx context.Context, arg InsertDataParams) error {
  ctx, done = q.observer(ctx, "InsertData")
  _, err := q.db.ExecContext(ctx, insertData,
        arg.ID,
                arg.Data,
    )  
   return done(err)
}

Example usage

type DataRepository struct {
  db data.Querier
}

func NewDataRepository(db *sql.DB) *DataRepository {
  return &DataRepository{
    db: data.New(db).WithObserver(observer),
  }
}

func observer(ctx context.Context, methodName string) (context.Context, func(err error) error) {
      spanCtx, span := tracer.Start(ctx, methodName)
      return spanCtx, func(err error) error {
         span.End()
         return err
      }
}

If the proposal is acceptable to the direction of sqlc, I would be happy to provide a PR - or look forward to use the functionality.

What database engines need to be changed?

No response

What programming language backends need to be changed?

Go

[kyuff]

@kyleconroy is the suggested design ok for you? And would you like a PR on it? :)

[kindermoumoute]

Would be happy to review a PR on this feature 🙏

[owenhaynes]

I would suggest that opentelemetry should just be generated if enabled. Lots more of extra fields can be set more then just method name.

The generated code should try and populate all the semconv fields for database https://github.com/open-telemetry/opentelemetry-specification/blob/main/specification/trace/semantic_conventions/database.md

[kyuff]

Would be happy to review a PR on this feature 🙏

@owenhaynes Please do in #1689 :)

[Yamakaky]

For the record, I ended up doing a wrapper around the pgx pool that implements DBTX. Simple stuff that starts a span and defers to the pool. I get the query name by parsing the query string for -- name:, which is not ideal but it works.

[wyattanderson]

Just chiming in to voice additional desire for a solution to this problem. Two use cases where this would be extremely valuable for us:

• setting the operation name of trace spans to the actual query name
• being able to track query performance with Prometheus metrics by adding a metric tag corresponding to the query name

As @Yamakaky mentioned, I think both of these can be approximated with a wrapper, but needing to parse out the query name from the query is a bummer. Additionally, it'd be great to have access to the query parameter struct as well as the context so that we could build integrations that—for example—set span attributes that correspond to query parameters, if desired.

It looks like the current plugin interface is additive and only allows writing of new files as opposed to modifying generated code, so I'm not sure how plugins can fulfill these use cases or any of the use cases in the plugin design document, though it's entirely possible I'm missing something about the design of the plugin API.

[paambaati]

I see that the original PR was closed in favor of plugins; were you folks able to do this via plugins?

[craigpastro]

Came here looking for this, noticed the state of affairs, and ended up going with the following alternative (hopefully it helps someone):

If you are using Postgres and generating pgx code then you can implement the QueryTracer interface, and add that to your pgx.Config. This worked out pretty well for me.

[sgielen]

I wrote a small tool to do this based on the gendb.Querier interface generated by sqlc automatically. I run it in a Makefile automatically after running sqlc generate. The generated object then implements the gendb.Querier interface and can be used in its place.

Please feel free to do with this as you like.

package main

import (
    "bytes"
    "fmt"
    "go/ast"
    "go/format"
    "go/parser"
    "go/token"
    "go/types"
    "log"
    "os"
    "strings"
)

func main() {
    if len(os.Args) != 3 {
        fmt.Printf("Usage: %s gendb/querier.go gendb/otelwrap.go", os.Args[0])
    }

    input := os.Args[1]
    output := os.Args[2]

    fileSet := token.NewFileSet()
    file, err := parser.ParseFile(fileSet, input, nil, parser.ParseComments)
    if err != nil {
        log.Fatal(err)
    }

    var buf bytes.Buffer

    buf.WriteString(fmt.Sprintf("package %s\n\n", file.Name.Name))
    for _, importSpec := range file.Imports {
        buf.WriteString(fmt.Sprintf("import %s\n", importSpec.Path.Value))
    }
    buf.WriteString("import \"go.opentelemetry.io/otel\"\n")
    buf.WriteString("import \"go.opentelemetry.io/otel/trace\"\n\n")

    for _, decl := range file.Decls {
        iface, ok := decl.(*ast.GenDecl)
        if !ok || iface.Tok != token.TYPE {
            continue
        }

        for _, declSpec := range iface.Specs {
            typeSpec, ok := declSpec.(*ast.TypeSpec)
            if !ok {
                continue
            }

            ifaceType, ok := typeSpec.Type.(*ast.InterfaceType)
            if !ok {
                continue
            }

            typeName := typeSpec.Name.Name
            variable := strings.ToLower(typeName[:1]) + typeName[1:]
            wrapperName := typeName + "OtelWrapper"

            buf.WriteString(fmt.Sprintf("type %s struct {\n", wrapperName))
            buf.WriteString(fmt.Sprintf("\t%s %s\n", variable, typeName))
            buf.WriteString("}\n\n")

            buf.WriteString(fmt.Sprintf("func NewOtelWrapper(%s %s) %s {\n", variable, typeName, typeName))
            buf.WriteString(fmt.Sprintf("\treturn &%s{%s: %s}\n", wrapperName, variable, variable))
            buf.WriteString("}\n\n")

            buf.WriteString(fmt.Sprintf("func (w *%s) GetQuerier() Querier {\n", wrapperName))
            buf.WriteString("\treturn w.querier\n")
            buf.WriteString("}\n\n")

            for _, methodNode := range ifaceType.Methods.List {
                methodName := methodNode.Names[0].Name
                buf.WriteString(fmt.Sprintf("func (w *%s) %s(", wrapperName, methodName))

                method, ok := methodNode.Type.(*ast.FuncType)
                if !ok {
                    continue
                }

                for i, param := range method.Params.List {
                    if i > 0 {
                        buf.WriteString(", ")
                    }
                    for _, paramName := range param.Names {
                        buf.WriteString(fmt.Sprintf("%s %s", paramName.Name, types.ExprString(param.Type)))
                    }
                }
                buf.WriteString(")")

                if method.Results != nil {
                    buf.WriteString(" (")
                    for i, result := range method.Results.List {
                        if i > 0 {
                            buf.WriteString(", ")
                        }
                        buf.WriteString(types.ExprString(result.Type))
                    }
                    buf.WriteString(")")
                }

                // Write method body with OpenTelemetry span
                buf.WriteString(" {\n")
                buf.WriteString("\tctx, span := otel.Tracer(\"db\").Start(ctx, \"" + methodName + "\", trace.WithSpanKind(trace.SpanKindClient))\n")
                buf.WriteString("\tdefer span.End()\n\n")
                buf.WriteString("\treturn w." + variable + "." + methodName + "(")

                // Write method parameters for the wrapped method call
                for i, param := range method.Params.List {
                    if i > 0 {
                        buf.WriteString(", ")
                    }
                    for _, paramName := range param.Names {
                        buf.WriteString(paramName.Name)
                    }
                }
                buf.WriteString(")\n")
                buf.WriteString("}\n\n")
            }
        }
    }

    gofmt, err := format.Source(buf.Bytes())
    if err != nil {
        // Write the unformatted source to file and fail afterwards
        gofmt = buf.Bytes()
        defer log.Fatal(err)
    }

    if err := os.WriteFile(output, gofmt, 0644); err != nil {
        log.Fatal(err)
    }
}

[sysulq]

Maybe https://github.com/XSAM/otelsql is a good choice :-)