The Origin of SQL Queries

Do you know what part of your application is generating that time-consuming database query? There’s a much simpler way than grep.

Add comments to your queries!!

Turn:

SELECT * FROM pandas WHERE mood = 'sad'

into

SELECT * FROM pandas WHERE mood = 'happy'
/*application:Nature,job:EatBambooJob*/

Whether you use PgHero, pg_stat_statements on its own, or log_min_duration_statement to log slow queries, comments can help!

Ruby on Rails

Marginalia is great. We prefer to customize slightly in config/initializers/marginalia.rb.

module Marginalia
  module Comment
    # add namespace to controller
    def self.controller
      if marginalia_controller.respond_to?(:controller_path)
        marginalia_controller.controller_path
      end
    end
  end
end

# add job
Marginalia::Comment.components << :job

Python

With SQLAlchemy and Flask:

from flask import current_app, request
from sqlalchemy.engine import Engine
from sqlalchemy import event

@event.listens_for(Engine, "before_cursor_execute", retval=True)
def annotate_queries(conn, cursor, statement, parameters, context, executemany):
    comment = ""
    try:
        comment = " /*application:{},endpoint:{}*/".format(current_app.name,
                                                             request.endpoint)
    except RuntimeError:  # running in the CLI
        try:
            comment = " /*application:{}*/".format(current_app.name)
        except RuntimeError:  # running in a REPL
            pass
    return statement + comment, parameters

R

With dbx, use:

options(dbx_comment=TRUE)

Other Languages and Frameworks

Please submit a PR!

Published July 15, 2015 · Tweet


You might also enjoy

Postgres SSLMODE Explained

Active Storage S3 Client-Side Encryption

Securing Sensitive Data in Rails


All code examples are public domain.
Use them however you’d like (licensed under CC0).