/otelsql

OpenTelemetry SQL database driver wrapper for Go

Primary LanguageGoApache License 2.0Apache-2.0

OpenTelemetry SQL database driver wrapper for Go

GitHub Releases Build Status codecov Go Report Card GoDevDoc Donate

Add a OpenTelemetry wrapper to your existing database code to instrument the interactions with the database. The wrapper supports both traces and metrics.

Table of Contents

Prerequisites

  • Go >= 1.22

[table of contents]

Install

⚠️ From v0.5.0, the project is rebranded to go.nhat.io/otelsql. v0.4.x is the last version with github.com/nhatthm/otelsql.

go get go.nhat.io/otelsql

Compatibility

otelsql go.opentelemetry.io/otel/trace go.opentelemetry.io/otel/metric
v0.14.*   v1.30.0 ~> latest v1.30.0 ~> latest
v0.13.*   v1.24.0 ~> latest v1.24.0 ~> latest
v0.12.*   v1.17.0 ~> latest v0.40.0 ~> latest
v0.11.*   v1.16.0 ~> latest v0.39.0 ~> latest
v0.10.*   v1.15.0 ~> latest v0.38.*
v0.9.*   v1.14.0 ~> latest v0.37.*
v0.8.*   v1.12.0 ~> latest v0.35.0 ~> v0.36.*
v0.7.*   v1.11.1 ~> latest v0.33.0 ~> v0.34.*
v0.6.*   v1.10.0 ~> latest v0.32.*
v0.5.*   v1.10.0 ~> latest v0.31.*
v0.4.* 1 v1.9.0 ~> latest v0.31.*
v0.3.* 1 v1.7.0 ~> latest v0.28.0 ~> v0.30.*
v0.2.* 1 v1.6.2 ~> latest v0.28.0 ~> v0.30.*
v0.1.* 1 v1.4.1 ~> latest v0.26.0 ~> v0.27.*

1 Old versions were shipped under github.com/nhatthm/otelsql. Use go get github.com/nhatthm/otelsql instead.

[table of contents]

Usage

To use otelsql with your application, register the otelsql wrapper by using otelsql.Register(driverName string, opts ...otelsql.DriverOption). For example:

package example

import (
	"database/sql"

	"go.nhat.io/otelsql"
	semconv "go.opentelemetry.io/otel/semconv/v1.20.0"
)

func openDB(dsn string) (*sql.DB, error) {
	// Register the otelsql wrapper for the provided postgres driver.
	driverName, err := otelsql.Register("postgres",
		otelsql.AllowRoot(),
		otelsql.TraceQueryWithoutArgs(),
		otelsql.TraceRowsClose(),
		otelsql.TraceRowsAffected(),
		otelsql.WithDatabaseName("my_database"),        // Optional.
		otelsql.WithSystem(semconv.DBSystemPostgreSQL), // Optional.
	)
	if err != nil {
		return nil, err
	}

	// Connect to a Postgres database using the postgres driver wrapper.
	return sql.Open(driverName, dsn)
}

The wrapper will automatically instrument the interactions with the database.

Optionally, you could record database connection metrics using the otelsql.RecordStats(). For example:

package example

import (
	"database/sql"

	"go.nhat.io/otelsql"
	semconv "go.opentelemetry.io/otel/semconv/v1.20.0"
)

func openDB(dsn string) (*sql.DB, error) {
	// Register the otelsql wrapper for the provided postgres driver.
	driverName, err := otelsql.Register("postgres",
		otelsql.AllowRoot(),
		otelsql.TraceQueryWithoutArgs(),
		otelsql.TraceRowsClose(),
		otelsql.TraceRowsAffected(),
		otelsql.WithDatabaseName("my_database"),        // Optional.
		otelsql.WithSystem(semconv.DBSystemPostgreSQL), // Optional.
	)
	if err != nil {
		return nil, err
	}

	// Connect to a Postgres database using the postgres driver wrapper.
	db, err := sql.Open(driverName, dsn)
	if err != nil {
		return nil, err
	}

	if err := otelsql.RecordStats(db); err != nil {
		return nil, err
	}

	return db, nil
}

[table of contents]

Options

Driver Options

Option Description
WithMeterProvider(metric.MeterProvider) Specify a meter provider
WithTracerProvider(trace.TracerProvider) Specify a tracer provider
WithDefaultAttributes(...attribute.KeyValue) Add extra attributes for the recorded spans and metrics
WithInstanceName(string) Add an extra attribute for annotating the instance name
WithSystem(attribute.KeyValue) Add an extra attribute for annotating the type of database server.
The value is set by using the well-known identifiers in semconv. For example: semconv.DBSystemPostgreSQL. See more
WithDatabaseName(string) Add an extra attribute for annotating the database name
WithSpanNameFormatter(spanNameFormatter) Set a custom span name formatter
ConvertErrorToSpanStatus(errorToSpanStatus) Set a custom converter for span status
DisableErrSkip() sql.ErrSkip is considered as OK in span status
TraceQuery() Set a custom function for tracing query
TraceQueryWithArgs() Trace query and all arguments
TraceQueryWithoutArgs() Trace query without the arguments
AllowRoot() Create root spans in absence of existing spans or even context
TracePing() Enable the creation of spans on Ping requests
TraceRowsNext() Enable the creation of spans on RowsNext calls. (This can result in many spans)
TraceRowsClose() Enable the creation of spans on RowsClose calls
TraceRowsAffected() Enable the creation of spans on RowsAffected calls
TraceLastInsertID() Enable the creation of spans on LastInsertId call
TraceAll() Turn on all tracing options, including AllowRoot() and TraceQueryWithArgs()

Record Stats Options

Option Description
WithMeterProvider(metric.MeterProvider) Specify a meter provider
WithMinimumReadDBStatsInterval(time.Duration) The minimum interval between calls to db.Stats(). Negative values are ignored.
WithDefaultAttributes(...attribute.KeyValue) Add extra attributes for the recorded metrics
WithInstanceName(string) Add an extra attribute for annotating the instance name
WithSystem(attribute.KeyValue) Add an extra attribute for annotating the type of database server.
The value is set by using the well-known identifiers in semconv. For example: semconv.DBSystemPostgreSQL. See more
WithDatabaseName(string) Add an extra attribute for annotating the database name

[table of contents]

Extras

[table of contents]

Span Name Formatter

By default, spans will be created with the sql:METHOD format, like sql:exec or sql:query. You could change this behavior by using the WithSpanNameFormatter() option and set your own logic.

For example

package example

import (
	"context"
	"database/sql"

	"go.nhat.io/otelsql"
)

func openDB(dsn string) (*sql.DB, error) {
	driverName, err := otelsql.Register("my-driver",
		otelsql.WithSpanNameFormatter(func(_ context.Context, op string) string {
			return "main-db:" + op
		}),
	)
	if err != nil {
		return nil, err
	}

	return sql.Open(driverName, dsn)
}

With traces of ExecContext() and QueryContext() (either DB, Stmt, or Tx), you could get the SQL query from the context using otelsql.QueryFromContext(). For example:

package example

import (
	"context"
	"database/sql"

	"go.nhat.io/otelsql"
)

func openDB(dsn string) (*sql.DB, error) {
	driverName, err := otelsql.Register("my-driver",
		otelsql.WithSpanNameFormatter(func(ctx context.Context, op string) string {
			if op != "exec" {
				return "main-db:" + op
			}

			query := otelsql.QueryFromContext(ctx)

			// Make span name from the query here and return.
		}),
	)
	if err != nil {
		return nil, err
	}

	return sql.Open(driverName, dsn)
}

[table of contents]

Convert Error to Span Status

By default, all errors are considered as ERROR while setting span status, except io.EOF on RowsNext calls (which is OK). otelsql also provides an extra option DisableErrSkip() if you want to ignore the sql.ErrSkip.

You can write your own conversion by using the ConvertErrorToSpanStatus() option. For example

package example

import (
	"database/sql"
	"errors"

	"go.nhat.io/otelsql"
	"go.opentelemetry.io/otel/codes"
)

func openDB(dsn string) (*sql.DB, error) {
	driverName, err := otelsql.Register("my-driver",
		otelsql.ConvertErrorToSpanStatus(func(err error) (codes.Code, string) {
			if err == nil || errors.Is(err, ignoredError) {
				return codes.Ok, ""
			}

			return codes.Error, err.Error()
		}),
	)
	if err != nil {
		return nil, err
	}

	return sql.Open(driverName, dsn)
}

[table of contents]

Trace Query

By default, otelsql does not trace query and arguments. When you use these options:

  • TraceQueryWithArgs(): Trace the query and all arguments.
  • TraceQueryWithoutArgs(): Trace only the query, without the arguments.

The traced query will be set in the semconv.DBStatementKey attribute (db.statement) and the arguments are set as follows:

  • db.sql.args.NAME: if the arguments are named.
  • db.sql.args.ORDINAL: Otherwise.

Example #1:

SELECT *
FROM data
WHERE country = :country

The argument attribute will be db.sql.args.country

Example #2:

SELECT *
FROM data
WHERE country = $1

The argument attribute will be db.sql.args.1

You can change this behavior for your own purpose (like, redaction or stripping out sensitive information) by using the TraceQuery() option. For example:

package example

import (
	"context"
	"database/sql"
	"database/sql/driver"

	"go.nhat.io/otelsql"
	"go.opentelemetry.io/otel/attribute"
	"go.opentelemetry.io/otel/codes"
	semconv "go.opentelemetry.io/otel/semconv/v1.20.0"
)

func openDB(dsn string) (*sql.DB, error) {
	driverName, err := otelsql.Register("my-driver",
		otelsql.TraceQuery(func(_ context.Context, query string, args []driver.NamedValue) []attribute.KeyValue {
			attrs := make([]attribute.KeyValue, 0, 1+len(args))

			attrs = append(attrs, semconv.DBStatementKey.String(query))

			// Your redaction goes here.

			return attrs
		}),
	)
	if err != nil {
		return nil, err
	}

	return sql.Open(driverName, dsn)
}

[table of contents]

AllowRoot() and Span Context

To fully take advantage of otelsql, all database calls should be made using the *Context methods. Failing to do so will result in many orphaned traces if the AllowRoot() is used. By default, AllowRoot() is disabled and will result in otelsql not tracing the database calls if context or parent spans are missing.

Old New
*DB.Begin *DB.BeginTx
*DB.Exec *DB.ExecContext
*DB.Ping *DB.PingContext
*DB.Prepare *DB.PrepareContext
*DB.Query *DB.QueryContext
*DB.QueryRow *DB.QueryRowContext
*Stmt.Exec *Stmt.ExecContext
*Stmt.Query *Stmt.QueryContext
*Stmt.QueryRow *Stmt.QueryRowContext
*Tx.Exec *Tx.ExecContext
*Tx.Prepare *Tx.PrepareContext
*Tx.Query *Tx.QueryContext
*Tx.QueryRow *Tx.QueryRowContext

[table of contents]

jmoiron/sqlx

If using the jmoiron/sqlx library with named queries you will need to use the sqlx.NewDb function to wrap an existing *sql.DB connection. Do not use the sqlx.Open and sqlx.Connect methods. jmoiron/sqlx uses the driver name to figure out which database is being used. It uses this knowledge to convert named queries to the correct bind type (dollar sign, question mark) if named queries are not supported natively by the database. Since otelsql creates a new driver name it will not be recognized by jmoiron/sqlx and named queries will fail.

For example:

package example

import (
	"database/sql"

	"github.com/jmoiron/sqlx"
	"go.nhat.io/otelsql"
)

func openDB(dsn string) (*sql.DB, error) {
	driverName, err := otelsql.Register("my-driver",
		otelsql.AllowRoot(),
		otelsql.TraceQueryWithoutArgs(),
		otelsql.TraceRowsClose(),
		otelsql.TraceRowsAffected(),
	)
	if err != nil {
		return nil, err
	}

	db, err := sql.Open(driverName, dsn)
	if err != nil {
		return nil, err
	}

	return sqlx.NewDb(db, "my-driver"), nil
}

[table of contents]

Metrics

Attributes (applies to all the metrics below)

Attribute Description Note
db_operation The executed sql method For example: exec, query, prepare
db_sql_status The execution status OK if no error, otherwise ERROR
db_sql_error The error message When status is ERROR. The value is the error message
db_instance The instance name Only when using WithInstanceName() option
db_system The system name Only when using WithSystem() option
db_name The database name Only when using WithDatabaseName() option

WithDefaultAttributes(attrs ...attribute.KeyValue) will also add the attrs to the recorded metrics.

[table of contents]

Client Metrics

Metric Description
db_sql_client_calls{db_instance,db_operation,db_sql_status,db_system,db_name} Number of Calls (Counter)
db_sql_client_latency_bucket{db_instance,db_operation,db_sql_status,db_system,db_name,le} Latency in milliseconds (Histogram)
db_sql_client_latency_sum{db_instance,db_operation,db_sql_status,db_system,db_name}
db_sql_client_latency_count{db_instance,db_operation,db_sql_status,db_system,db_name}

[table of contents]

Database Connection Metrics

Metric Description
db_sql_connections_active{db_instance,db_system,db_name} Number of active connections
db_sql_connections_idle{db_instance,db_system,db_name} Number of idle connections
db_sql_connections_idle_closed{db_instance,db_system,db_name} Total number of closed connections by SetMaxIdleConns
db_sql_connections_lifetime_closed{db_instance,db_system,db_name} Total number of closed connections by SetConnMaxLifetime
db_sql_connections_open{db_instance,db_system,db_name} Number of open connections
db_sql_connections_wait_count{db_instance,db_system,db_name} Total number of connections waited for
db_sql_connections_wait_duration{db_instance,db_system,db_name} Total time blocked waiting for new connections

[table of contents]

Traces

Operation Trace
*DB.BeginTx Always
*DB.ExecContext Always
*DB.PingContext Disabled. Use TracePing() to enable
*DB.PrepareContext Always
*DB.QueryContext Always
*DB.QueryRowContext Always
*Stmt.ExecContext Always
*Stmt.QueryContext Always
*Stmt.QueryRowContext Always
*Tx.ExecContext Always
*Tx.PrepareContext Always
*Tx.QueryContext Always
*Tx.QueryRowContext Always
*Rows.Next Disabled. Use TraceRowsNext() to enable
*Rows.Close Disabled. Use TraceRowsClose() to enable
*Result.LastInsertID Disabled. Use TraceLastInsertID() to enable
*Result.RowsAffected Disabled. Use TraceRowsAffected() to enable

ExecContext, QueryContext, QueryRowContext, PrepareContext are always traced without query args unless using TraceQuery(), TraceQueryWithArgs(), or TraceQueryWithoutArgs() option.

Using WithDefaultAttributes(...attribute.KeyValue) will add extra attributes to the recorded spans.

[table of contents]

Migration from ocsql

The migration is easy because the behaviors of otelsql are the same as ocsql, and all options are almost similar.

ocsql otelsql
Register driver wrapper Register(driverName string, options ...TraceOption) Register(driverName string, options ...DriverOption)
Records database statistics RecordStats(db *sql.DB, interval time.Duration) RecordStats(db *sql.DB, opts ...StatsOption)

The interval in RecordStats() is replaced with WithMinimumReadDBStatsInterval(time.Duration) option.

[table of contents]

Options

ocsql otelsql
WithAllTraceOptions() TraceAll()
otelsql always set to true
WithOptions(ocsql.TraceOptions) Dropped
WithAllowRoot(bool) AllowRoot()
otelsql always set to true
WithPing(bool) TracePing()
otelsql always set to true
WithRowsNext(bool) TraceRowsNext()
otelsql always set to true
WithRowsClose(bool) TraceRowsClose()
otelsql always set to true
WithRowsAffected(bool) TraceRowsAffected()
otelsql always set to true
WithLastInsertID(bool) TraceLastInsertID()
otelsql always set to true
WithQuery(bool)
WithQueryParams(bool)		 TraceQueryWithArgs()
TraceQueryWithoutArgs()
WithDefaultAttributes(...trace.Attribute) WithDefaultAttributes(...attribute.KeyValue)
WithDisableErrSkip(bool) DisableErrSkip()
WithSampler(trace.Sampler) Dropped
WithInstanceName(string) WithInstanceName(string)

[table of contents]

Metrics

Attributes (applies to all the metrics below)

ocsql otelsql Note
go_sql_instance db_instance Only when using WithInstanceName() option
go_sql_method db_operation
go_sql_status db_sql_status
n/a db_system Only when using WithSystem() option
n/a db_name Only when using WithDatabaseName() option

Client Metrics

ocsql otelsql
go_sql_client_calls{go_sql_instance,go_sql_method,go_sql_status} db_sql_client_calls{db_instance,db_operation,db_sql_status,db_system,db_name}
go_sql_client_latency_bucket{go_sql_instance,go_sql_method,go_sql_status,le} db_sql_client_latency_bucket{db_instance,db_operation,db_sql_status,db_system,db_name,le}
go_sql_client_latency_sum{go_sql_instance,go_sql_method,go_sql_status} db_sql_client_latency_sum{db_instance,db_operation,db_sql_status,db_system,db_name}
go_sql_client_latency_count{go_sql_instance,go_sql_method,go_sql_status} db_sql_client_latency_count{db_instance,db_operation,db_sql_status,db_system,db_name}

Connection Metrics

ocsql otelsql
go_sql_db_connections_active{go_sql_instance} db_sql_connections_active{db_instance,db_system,db_name}
go_sql_db_connections_idle{go_sql_instance} db_sql_connections_idle{db_instance,db_system,db_name}
go_sql_db_connections_idle_closed_count{go_sql_instance} db_sql_connections_idle_closed{db_instance,db_system,db_name}
go_sql_db_connections_lifetime_closed_count{go_sql_instance} db_sql_connections_lifetime_closed{db_instance,db_system,db_name}
go_sql_db_connections_open{go_sql_instance} db_sql_connections_open{db_instance,db_system,db_name}
go_sql_db_connections_wait_count{go_sql_instance} db_sql_connections_wait_count{db_instance,db_system,db_name}
go_sql_db_connections_wait_duration{go_sql_instance} db_sql_connections_wait_duration{db_instance,db_system,db_name}

[table of contents]

Traces

The traces are almost identical with some minor changes:

  1. Named arguments are not just recorder as <NAME> in the span. They are now db.sql.args.<NAME>.
  2. sql.query is now db.statement.

[table of contents]

Compatibility

OS
Driver Database Ubuntu MacOS Windows
go 1.22 go 1.23 go 1.22 go 1.23 go 1.22 go 1.23
DATA-DOG/go-sqlmock Build Status
jmoiron/sqlx Manually Tested
denisenkom/go-mssqldb SQL Server 2019 Build Status
go-sql-driver/mysql MySQL 8 Build Status
jackc/pgx/v4/stdlib Postgres 12, 13, 14, 15, 16 Build Status
jackc/pgx/v5/stdlib Postgres 12, 13, 14, 15, 16 Build Status
lib/pq Postgres 12, 13, 14, 15, 16 Build Status

If you don't see a driver in the list, it doesn't mean the wrapper is incompatible. it's just not tested. Let me know if it works with your driver

[table of contents]

Donation

If this project help you reduce time to develop, you can give me a cup of coffee :)

[table of contents]

Paypal donation

paypal

       or scan this