Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pretty Print SQL in Ruby

Is there an easy way to pretty print random SQL in the (rails 3) console?

Something similar to awesome_print, or maybe even Pretty Print.

It doesn't have to understand all the dialects possible or be super-advanced.
All I really want is to inspect the SQL generated by ActiveRecord easier.

Currently I just copy the SQL go online to format it which is obviously a productivity killer.

I really want to query.to_sql.pretty_format_sql and see the nicer output.

Thanks.

like image 346
Dmytrii Nagirniak Avatar asked Apr 27 '12 08:04

Dmytrii Nagirniak


1 Answers

Try this:

git clone https://github.com/sonota/anbt-sql-formatter
cd anbt-sql-formatter
rails setup.rb

Then, in a Rails initializer:

# config/initializers/pretty_format_sql.rb
class String
  def pretty_format_sql
    require "anbt-sql-formatter/formatter"
    rule = AnbtSql::Rule.new
    rule.keyword = AnbtSql::Rule::KEYWORD_UPPER_CASE
    %w(count sum substr date).each{|func_name|
      rule.function_names << func_name.upcase
    }
    rule.indent_string = "    "
    formatter = AnbtSql::Formatter.new(rule)
    formatter.format(self)
  end
end

Test:

rails console
# Some complex SQL
puts Recipe.joins(:festivity).where(['? BETWEEN festivities.starts_at AND festivities.ends_at', Time.utc(0,Time.now.month,Time.now.day,12,0,0)]).to_sql.pretty_format_sql
SELECT
        "recipes" . *
    FROM
        "recipes" INNER JOIN "festivities"
            ON "festivities" . "id" = "recipes" . "festivity_id"
    WHERE
        (
            '0000-04-27 12:00:00.000000' BETWEEN festivities.starts_at AND festivities.ends_at
        )
 => nil 

I leave refining to you (refactoring: monkey-patching -> module, customized formatting, etc :-) )

like image 105
mdesantis Avatar answered Oct 19 '22 15:10

mdesantis