Enable query tagging with Sqlcommenter to understand application impact on database performance
Tags: database, observability, sql, googlecloud
Published: 2021-11-03 15:47:39

Cloud SQL Insights [launched](https://cloud.google.com/blog/products/databases/get-ahead-of-database-performance-issues-with-cloud-sql-insights) earlier this year, giving developers a tool that helps detect, diagnose, and prevent query performance problems for PostgreSQL for Cloud SQL databases. Out of the box, the [Query Insights](https://cloud.google.com/sql/docs/postgres/query-insights-overview) dashboard provides database load graphs, detailed query performance, and built-in query plans. Users can boost the power of Insights by enabling query tagging via an open source library called Sqlcommenter. In this post, we'll share the benefits of using query tagging, what Sqlcommenter is, and two ways you can enable it: using a supported ORM or manually by following the Sqlcommenter spec.

![Query Insights Dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3aqzwb1lc7qcqsysvkst.png)

## Application-centric database monitoring 
One of the most powerful features of Insights is its ability to provide application-centric database monitoring and simplify application troubleshooting. To get this added functionality, you need to enable and set up query tagging.  Query tagging augments your SQL statements with information about your application; you could tag queries by business logic, microservice, route, controller, etc. For example, using payment, inventory, business analytics, or shipping tags. You can then find the queries and database  load that the various services create. This may help you find unexpected events, such as spikes for a business analytics tag at certain times of day, or you might see abnormal growth for a payment service trending over the previous week. Sounds great, but how does it work?

## Overview of Sqlcommenter
Insights uses an open source library called [Sqlcommenter](https://github.com/google/sqlcommenter), which allows application information related to your MVC framework to be sent  to the database along with queries as a SQL comment. Sqlcommenter has [recently](https://cloud.google.com/blog/products/databases/sqlcommenter-merges-with-opentelemetry) merged with [OpenTelemetry](https://opentelemetry.io/) to extend the vision of OpenTelemetry to databases and help grow the observability ecosystem. In addition to application information, Sqlcommenter allows OpenTelemetry trace context information to be propagated to the database, making it possible to find correlations between application traces and database query plans. Developers can view this observability information directly in database logs, the information can be integrated into other tools like Cloud SQL  Insights or APM tools such as those from Datadog, Dynatrace, and Splunk. 

Sqlcommenter supports several ORMs and frameworks including Django, SQLAlchemy Ruby on Rails, Knex.js, Sequelize.js, Spring, Hibernate, and more. If you are using one of these supported ORMs, the application tags are automatically created for you, with little to no change to your application code. If you are not using an ORM, you can manually add Sqlcommenter tags to your SQL queries. Let's look at how to enable Sqlcommenter for query tagging in one of the [supported languages and frameworks](https://google.github.io/sqlcommenter/#support) and how to manually tag queries if you aren't using an ORM.

## Using Python and SQLAlchemy with Sqlcommenter
Each supported ORM has documentation and examples to show you how to install and enable it. Here's the process you would follow to set up Sqlcommenter in a Python application that uses [SQLAlchemy](https://github.com/google/sqlcommenter/blob/master/python/sqlcommenter-python/README.md#sqlalchemy). 


Run the following to install Sqlcommenter with the option to record OpenTelemetry trace context:

```
pip3 install google-cloud-sqlcommenter[opentelemetry]
```

Then, in your application code, attach the event listener shown below to the before_cursor_execute event of the database engine. This will ensure that all queries that are executed with that engine will have the SQL comments included. Once you've installed Sqlcommenter and added the code below, no further code changes are needed to your application.

```
import sqlalchemy
from google.cloud.sqlcommenter.sqlalchemy.executor import BeforeExecuteFactory

engine = sqlalchemy.create_engine(...)
listener = BeforeExecuteFactory(
    with_db_driver=True,
    with_db_framework=True,
    with_opentelemetry=True,
)
sqlalchemy.event.listen(engine, 'before_cursor_execute', listener, retval=True)
engine.execute(...) # comment will be added before execution
```

The output in the database logs would look something like this:

```
2021-08-28 02:33:25.287 PDT [57302] LOG:  statement: SELECT candidate, time_cast FROM
Votes ORDER BY time_cast DESC /*db_driver='pg8000',framework='sqlalchemy%3A1.4.22',controller='index',route='/',
traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01',
tracestate='congo%%3Dt61rcWkgMzE%%2Crojo%%3D00f067aa0ba902b7'*/
```
For other ORMs and frameworks, you can find links to the instructions for setting up Sqlcommenter [here](https://google.github.io/sqlcommenter/#support).

## Manually tagging queries using the Sqlcommenter spec
If you're not using one of the supported ORMs, you can add comments to your SQL statements manually by following the Sqlcommenter [specification](https://google.github.io/sqlcommenter/spec/). The Sqlcommenter algorithm adds a comment containing serialized key value pairs to a SQL statement. An overview of the algorithm is below; to understand the different pieces of the algorithm  in more depth, see the [algorithm documentation](https://google.github.io/sqlcommenter/spec/#sql-commenter).

``` 
sql_commenter(sql, attributes):
    if contains_sql_comment(sql):
        return sql # DO NOT mutate a statement with an already present comment.

    serialized_key_value_pairs := []

    for each attribute in attributes:
        serialized := serialize_key_value_pair(attribute)
        if serialized:
            serialized_key_value_pairs.append(serialized)

    sorted := sort(serialized_key_value_pairs)
    concatenated := concatenate(sorted)
    final := affix_comment(sql, concatenated)

    return final
```

The following keys are supported for use in Cloud SQL Insights: action, controller, framework, route, application, db_driver, [traceparent](https://www.w3.org/TR/trace-context/#traceparent-field), and [tracestate](https://www.w3.org/TR/trace-context/#tracestate-field). However, if you're not using Cloud SQL Insights, you can include any other custom keys as well.

The following information passed to the Sqlcommenter algorithm:

```
sql_commenter('SELECT * FROM POSTS, [
traceparent='00-ff19308b1f17fedc5864e929bed1f44e-6ddace73a9debf63-01',
     route='posts',
     action='index',
     controller='posts',
	application='SqlcommenterBlogDemo'
])
```

Results in this output:

```
SELECT * FROM POSTS/*action='index',application='SqlcommenterBlogDemo',
controller='posts',route='/posts',traceparent='00-ff19308b1f17fedc5864e929bed1f44e-6ddace73a9debf63-01'*/
```

## Next steps
If you're running PostgreSQL for Cloud SQL databases, and you haven't [checked out Insights](https://cloud.google.com/sql/docs/postgres/using-query-insights) yet, now is the time! If you're using one of the ORMs or frameworks that Sqlcommenter supports, you can find instructions in the documentation for [how to enable it for each language and framework](https://google.github.io/sqlcommenter/#support), along with some sample applications in the GitHub repo.

{% github google/sqlcommenter  %}

If you have any feedback or questions, you can raise them in the [issue tracker](https://github.com/google/sqlcommenter/issues) or reach out on [Twitter](https://twitter.com/jankleinert).