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

Securing Sensitive Data in Rails

Introducing Dexter, the Automatic Indexer for Postgres

Rails, Meet Data Science


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