Pretty Print SQL in Ruby

pretty-print, ruby, ruby-on-rails, sql

Solution

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 :-) )

Problem

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.

Original source

Related problems