Pretty Print SQL in Ruby

Question

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.

Boutonniere

If you're using JRuby you could consider some answers for a similar question posed for Java, such as Hibernate's org.hibernate.jdbc.util.BasicFormatterImpl – Wedded

Answer 1

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

Francklyn

It should do the job, but I really want to avoid the use of deprecated rails plugins (non gems). – Boutonniere

I felt too that it is not a so "standard" lib... but it shouldn't be hard to extract the good parts and make of them a gem (I tested it and it works pretty well, and seeing that I couldnt find alternatives, why rewrite working things?)... I will try! – Francklyn

Answer 2

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
}

Permute

Answer 3

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

Damato

Answer 4

There is prettier-plugin-sql which has a nice output, e.g.

SELECT
  DISTINCT "events".*
FROM
  "events"
  INNER JOIN "approvals" ON "approvals"."event_id" = "events"."id"
  LEFT OUTER JOIN "attendances" ON "attendances"."event_id" = "events"."id"
WHERE
  (
    "approvals"."status" = ?
    OR "events"."user_id" = ?
  )
  AND (
    "attendances"."user_id" = ?
    OR "events"."user_id" = ?
  )

And if you copy your sql query after doing puts Event.something.to_sql, you can do:

pbpaste | prettier --parser sql

Cookstove