Pretty Print SQL in Ruby

Try this:

git clone https://github.com/sonota/anbt-sql-formattercd anbt-sql-formatterrails setup.rb

Then, in a Rails initializer:

# config/initializers/pretty_format_sql.rbclass 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)  endend

Test:

rails console# Some complex SQLputs 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_sqlSELECT        "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 :-) )

The anbt-sql-formatter of the first answer is available as a gem, you can install it with:

gem install anbt-sql-formatter

Here an example of the usage:

require "anbt-sql-formatter/formatter"rule = AnbtSql::Rule.new  formatter = AnbtSql::Formatter.new(rule)["SELECT `col1`, `col2` FROM `table` WHERE ((`col1` = 1) AND (`col2` = 5))","SELECT `col1`, `col2` FROM `table` WHERE (`col1` = 1) AND (`col2` = 5)","SELECT `col1` FROM `table` WHERE (`col1` IN (SELECT * FROM `table21` WHERE (`col2` = 5)))","SELECT `col1` FROM `table` INNER JOIN `tab2` ON (`tab1`.`id` = `tab2`.`id1`) WHERE ((`id` >= 1) AND (`id` <= 5))",].each{|sql_cmd|   puts "======"  puts sql_cmd  puts formatter.format(sql_cmd)}

The result:

======SELECT `col1`, `col2` FROM `table` WHERE ((`col1` = 1) AND (`col2` = 5))SELECT        `col1`        ,`col2`    FROM        `table`    WHERE        (            (                `col1` = 1            )            AND (                `col2` = 5            )        )======SELECT `col1`, `col2` FROM `table` WHERE (`col1` = 1) AND (`col2` = 5)SELECT        `col1`        ,`col2`    FROM        `table`    WHERE        (            `col1` = 1        )        AND (            `col2` = 5        )======SELECT `col1` FROM `table` WHERE (`col1` IN (SELECT * FROM `table21` WHERE (`col2` = 5)))SELECT        `col1`    FROM        `table`    WHERE        (            `col1` IN (                SELECT                        *                    FROM                        `table21`                    WHERE                        (                            `col2` = 5                        )            )        )======SELECT `col1` FROM `table` INNER JOIN `tab2` ON (`tab1`.`id` = `tab2`.`id1`) WHERE ((`id` >= 1) AND (`id` <= 5))SELECT        `col1`    FROM        `table` INNER JOIN `tab2`            ON (            `tab1`.`id` = `tab2`.`id1`        )    WHERE        (            (                `id` >= 1            )            AND (                `id` <= 5            )        )

There is also the possibility to extend the rules, e.g.

# User defined additional functions:%w(count sum substr date coalesce).each{|func_name|  rule.function_names << func_name.upcase}

Six years later, here's another option: https://github.com/kvokka/pp_sql

"Replace standard ActiveRecord#to_sql method with anbt-sql-formatter gem for pretty SQL code output in console. Rails log will be formatted also."

Uses anbt-sql-formatter under the hood, but makes this the default behavior for .to_sql