Observability¶

SQLSpec provides a comprehensive observability layer that integrates with standard tools like OpenTelemetry and Prometheus. It allows you to monitor SQL execution performance, track request correlations, and gather metrics on query duration and rows affected.

Instrumentation¶

To enable observability features, you typically wrap or extend your base configuration. SQLSpec provides helper functions in sqlspec.extensions to make this easier.

OpenTelemetry Tracing¶

SQLSpec can automatically generate OpenTelemetry spans for every SQL query. This is useful for distributed tracing and performance bottlenecks analysis.

To enable tracing, use the enable_tracing helper:

from sqlspec.extensions.otel import enable_tracing
from sqlspec.config import ObservabilityConfig

# Create a configuration with tracing enabled
observability = enable_tracing(
    base_config=ObservabilityConfig(),
    resource_attributes={"service.name": "my-service"}
)

# Use this config when initializing SQLSpec or your session
# ...

This will create spans with attributes like: - db.system (e.g., “postgresql”, “sqlite”) - db.statement (the sanitized SQL query) - db.operation (e.g., “SELECT”, “INSERT”)

Prometheus Metrics¶

You can expose Prometheus metrics for your database interactions, such as query counts and execution time histograms.

To enable metrics, use the enable_metrics helper:

from sqlspec.extensions.prometheus import enable_metrics
from sqlspec.config import ObservabilityConfig

# Enable Prometheus metrics
observability = enable_metrics(
    base_config=ObservabilityConfig(),
    namespace="myapp_sql",  # Prefix for metrics
    label_names=("db_system", "operation")
)

# Use this config...

Metrics exposed: - myapp_sql_query_total: Counter of executed queries. - myapp_sql_query_duration_seconds: Histogram of execution duration. - myapp_sql_query_rows: Histogram of rows affected.

Correlation Tracking¶

SQLSpec can track a correlation ID across your application to link SQL logs with specific requests.

correlation context¶

from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig

spec = SQLSpec()
spec.add_config(
    SqliteConfig(
        connection_config={"database": str(tmp_path / "observability.db")},
        extension_config={"litestar": {"enable_correlation_middleware": True}},
    )
)

with CorrelationContext.context("req-123") as correlation_id:
    print(correlation_id)

Logging & Sampling¶

You can configure detailed SQL logging and sampling to reduce noise in production.

sampling config¶

from sqlspec.observability import ObservabilityConfig, SamplingConfig

sampling = SamplingConfig(sample_rate=0.1, force_sample_on_error=True, deterministic=True)
observability = ObservabilityConfig(sampling=sampling, print_sql=False)

Cloud Log Formatters¶

For cloud environments (like GCP or AWS), structured logging is essential.

cloud formatters¶

from sqlspec.observability import AWSLogFormatter, GCPLogFormatter, ObservabilityConfig

gcp_logs = ObservabilityConfig(cloud_formatter=GCPLogFormatter())
aws_logs = ObservabilityConfig(cloud_formatter=AWSLogFormatter())