Request: Add support for Prisma query tracing

[dickfickling]

I'm using Prisma to manage database connections / queries. Really missing being able to track down slow MySQL queries in DD. Would be great to see support for prisma query tracing added

[3commaDevMJ]

https://www.prisma.io/docs/orm/prisma-client/observability-and-logging/opentelemetry-tracing#step-2-enable-the-feature-flag-in-your-prisma-schema-file

I'm trying this in a NestJS environment based on this page, but tracing doesn't work at all. Is there any additional work I need to do for queries to appear in DataDog?

[milanpajovic]

Is it possible to do prisma tracing without opentelemetry? Just using plain dd-trace ?

[Backend-Changyeon]

I tried the method suggested by @kdawgwilk, but I encountered an error:

TypeError: parentTracer.getSpanLimits is not a function
    at new Span (/Users/yoo-changyeon/repo/timespread-api-nest/node_modules/@opentelemetry/sdk-trace-base/src/Span.ts:122:37)
    at /Users/yoo-changyeon/repo/timespread-api-nest/node_modules/@prisma/instrumentation/dist/chunk-VVAFFO6L.js:59:20
    at Array.forEach (<anonymous>)
    at ActiveTracingHelper.createEngineSpan (/Users/yoo-changyeon/repo/timespread-api-nest/node_modules/@prisma/instrumentation/dist/chunk-VVAFFO6L.js:44:27)
    at eo.createEngineSpan (/Users/yoo-changyeon/repo/timespread-api-nest/node_modules/@prisma/client/runtime/library.js:122:1645)
    at wt.logger (/Users/yoo-changyeon/repo/timespread-api-nest/node_modules/@prisma/client/runtime/library.js:112:1167)
    at /Users/yoo-changyeon/repo/timespread-api-nest/node_modules/@prisma/client/runtime/library.js:112:922

I found that @prisma/instrumentation internally creates a Span from @opentelemetry/sdk-trace-base:

const span = new import_sdk_trace_base.Span(
    tracer,
    import_api.ROOT_CONTEXT,
    engineSpan.name,
    spanContext,
    import_api.SpanKind.INTERNAL,
    engineSpan.parent_span_id,
    links,
    engineSpan.start_time
);

The Span constructor calls parentTracer's getSpanLimits() method:

this._spanLimits = parentTracer.getSpanLimits();

However, dd-trace's TracerProvider does not implement the getSpanLimits() method:

const provider = new ddTracer.TracerProvider();

I think dd-trace did not implement this method because the Tracer interface in @opentelemetry/api does not define getSpanLimits().

On the other hand, Tracer in @opentelemetry/sdk-trace-base defines getSpanLimits(). (In addition to getSpanLimits(), it also defines the getGeneralLimits() method, but we can ignore it because no code is calling getGeneralLimits()).

To resolve this issue, you can add the getSpanLimits() method to Tracer in dd-trace.

Considerations for adding getSpanLimits():

The getSpanLimits() method in sdk-trace-base returns localConfig.spanLimits of the SpanLimits type. localConfig is of the TracerConfig type. The internal objects in the TracerConfig interface and the getSpanLimits() interface are both optional, so we can return empty objects. If dd-trace has a concept similar to spanLimits, it should return that value instead of an empty object.

Here is the modified code:

import { registerInstrumentations } from '@opentelemetry/instrumentation';
import type { SpanLimits } from '@opentelemetry/sdk-trace-base';
import { PrismaInstrumentation } from '@prisma/instrumentation';
import tracer from 'dd-trace';

import Tracer from 'dd-trace/packages/dd-trace/src/opentelemetry/tracer';

const _spanLimits: SpanLimits = {};
Tracer.prototype.getSpanLimits = function getSpanLimits(): SpanLimits {
  return _spanLimits;
};

export const ddTracer = tracer.init();
const provider = new ddTracer.TracerProvider();

registerInstrumentations({
  tracerProvider: provider,
  instrumentations: [new PrismaInstrumentation()],
});

provider.register();

it works

However, even with this approach, the issue mentioned by @georgewitteman about *prisma:engine: spans not appearing** still persists.

[crrobinson14]

Is there any plan to provide an official support path for this feature? This request has been open for 3 years. We've tried some of the workarounds posted above but haven't been able to get consistent tracing in our API layer for Prisma+PG

[rhyek]

I can confirm @zacharyliu's and @alandotcom's solution works and includes the prisma:engine:db_query spans, but I had to change two things:

1. In my schema.prisma file, I had to specify engineType = "binary" for the client generator:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["tracing", "metrics"]
  binaryTargets   = ["native", "linux-musl"]
  engineType      = "binary"
}

2. instead of setting dogstatsd.hostname for dd tracer init options, i just set the OTEL_EXPORTER_OTLP_ENDPOINT environment variable to the datadog agent's otlp endpoint.

[rhyek]

I also made the following adjustments to deal with https://github.com/prisma/prisma/issues/21472 (applies to postgresql):

const provider = new BasicTracerProvider({
  resource: new Resource({
    [SEMRESATTRS_SERVICE_NAME]: 'prisma',
    [SEMRESATTRS_DEPLOYMENT_ENVIRONMENT]: process.env.DD_ENV,
    [SEMRESATTRS_SERVICE_VERSION]: process.env.DD_VERSION,
  }),
});
const sqlResource = new Resource({
  [SEMRESATTRS_SERVICE_NAME]: 'postgres',
  [SEMRESATTRS_DEPLOYMENT_ENVIRONMENT]: process.env.DD_ENV,
  [SEMRESATTRS_SERVICE_VERSION]: process.env.DD_VERSION,
});
class MyOTLPTraceExporter extends OTLPTraceExporter {
  export(spans: ReadableSpan[], resultCallback: (result: ExportResult) => void) {
    for (const span of spans) {
      if (['prisma:engine:connection', 'prisma:engine:db_query'].includes(span.name)) {
        (span as any).kind = api.SpanKind.CLIENT;
        (span as any).resource = sqlResource;
        span.attributes['db.system'] = 'postgresql';
        delete span.attributes['db.type'];
      }
    }
    return super.export(spans, resultCallback);
  }
}
provider.addSpanProcessor(new BatchSpanProcessor(new MyOTLPTraceExporter()));

[ZeroCho]

@rhyek Can you please tell me what OTEL_EXPORTER_OTLP_ENDPOINT should be for datadog?

Oh never mind, I have prisma:engine:db_query spans without setting OTEL_EXPORTER_OTLP_ENDPOINT. I think it's good to let that env variable be default value if I don't use docker.

[crrobinson14]

I'm still really struggling with this one. The OpenTelemetry option looked promising but we couldn't shake it free into a working solution. We were able to get some tracing done by manually adding spans in a custom $allOperations hook but NONE of these tags appears to make a difference in the tracing output. We now see "query" spans in the traces but they're blank, with no context about the SQL query being run. (In the old tracer in TypeORM, we would see the SQL query.) Any thoughts? Here's what makes a span appear, but nothing else:

export const prisma = baseClient.$extends({
  query: {
    $allModels: {
      async $allOperations({operation, model, args, query}) {
        const start = performance.now();
        let span: any;

          span = tracer.startSpan('query', {childOf: tracer.scope().active() || undefined});
          span.setTag('span.service', 'pg');
          span.setTag('span.kind', 'client');
          span.setTag('span.type', 'sql');
          span.setTag('span.resource', query);
          span.setTag('resource', model);
          span.setTag('db.operation', operation);
          span.setTag('db.sql.table', model);

          span.setTag('db.statement', `${model}.${operation}`);
          span.setTag('db.instance', 'api');
          span.setTag('db.user', 'api');
          span.setTag('db.system', 'postgres');
          span.setTag('network.destination', 'test');
          span.setTag('component', 'pg');

        const result = await query(args);
        const end = performance.now();
        const time = end - start;

        if (span) {
          span.finish();
        }

        return result;