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

Why and How to Keep Your Decryption Keys Off Web Servers

Argon2 with Devise

Hybrid Cryptography on Rails


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