Pry command to execute SQL as text or Rails scopes

pry_psql.rb

# Load this file in your `.pryrc`
#
#     load 'path/to/pry_sql.rb'
#
# See how to use this by running: `psql --help`
# Originally created for Rails 3.
# Updated to Rails 6
module Rails
  class DBConsole
    APP_PATH = Rails.root.join('config', 'application')
  end
end

Pry::Commands.create_command 'psql', keep_retval: true, use_shellwords: false do |text|
  description 'Send SQL or scope to `psql`: psql [-x -t]'

  banner <<-BANNER
    Usage: psql [ -x | -t ] SQL or Ruby which evaluates to String or a Rails Model

    Saves SQL to Dir.tmpdir/pry-psql.sql, adds basic formatting, and cleaned SQL in
    `psql` or Rails determined system command.
  BANNER

  def pretty_sql(sql)
    cleaned = sql
      .gsub('"', '')
      .gsub(/\b(SELECT|FROM|WHERE|GROUP|HAVING|LEFT JOIN|LEFT OUTER JOIN|INNER JOIN|JOIN|RIGHT|ORDER|WINDOW)\b/, "\n\\1")
    output.puts CodeRay.scan(cleaned, :sql).terminal
    cleaned
  rescue
    output.puts cleaned
    cleaned
  end

  def options(opt)
    opt.on :x, 'Expand columns vertically'
    opt.on :t, 'Output execution timing in millis', default: true
  end

  def process
    output.puts
    output.puts('Executing SQL:')
    cleaned = pretty_sql(sql_string)
    output.puts('=' * ENV['COLUMNS'].to_i)
    exec_db(cleaned)
  end

  # Parse the Pry args into a SQL statement.
  # The Pry args maybe a Rails statement that generates SQL, so we try
  # called `to_sql` first or `all.to_sql`, otherwise the whole evaled
  # result is considered the SQL.
  def sql_string
    # ghetto argument removal because we want quotes to pass through and
    # args has then stripped out. `use_shellwords: false` doesn't help too much
    # since quotes still need to be escaped.
    #
    # Sample Regex: /\b?\\-[xth]+\s*/
    argless = arg_string.gsub(%r{\b?-[#{opts.options.map(&:short).join}]+\s*}, '')
    evaled = begin
               target.eval(argless)
             rescue Exception
               # If there's any problems evaluating, consider the whole thing as raw SQL
               argless
             end
    evaled.try(:to_sql) || evaled.try(:all).try(:to_sql) || evaled
  end

  # Passes SQL to the configured Rails DB system command.
  # The magic occurs in the following steps:
  #
  # 1. Write the SQL to a file: `Dir.tmpdir/pry-db.sql`.
  # 2. Override `exec` call in `Rails::DBConsole` to call the command with the
  #    file arg instead of normal `Kernel::exec`
  #
  # We want to use Rais::DBConsole since it does a lot of smart stuff to
  # determine the correct DB binary and parse the Rails DB configurtion.
  def exec_db(sql)
    File.join(Dir.tmpdir, 'pry-psql.sql').tap do |tmp_path|
      File.open(tmp_path, 'w') do |f|
        f << "\\x\n" if opts.x?
        f << "\\timing\n" if opts.t?
        f << sql
      end

      require 'rails/command'
      require 'rails/command/base'
      require 'rails/commands/dbconsole/dbconsole_command'
      Rails::DBConsole.class_eval do
        def exec(full_path_command, *args)
          puts `#{full_path_command} #{args.join} < #{File.join(Dir.tmpdir, 'pry-psql.sql')}`
        end
      end
      Rails::DBConsole.start
    end
  end
end

Hey, this is amazing! Had to require a bunch of files and define APP_PATH to make it work for Rails 6:

# Load this file in your `.pryrc`
#
#     load 'path/to/pry_sql.rb'
#
# See how to use this by running: `psql --help`

module Rails
  class DBConsole
    APP_PATH = Rails.root.join('config', 'application')
  end
end

Pry::Commands.create_command 'psql', keep_retval: true, use_shellwords: false do |text|
  description 'Send SQL or scope to `psql`: psql [-x -t]'

  banner <<-BANNER
    Usage: psql [ -x | -t ] SQL or Ruby which evaluates to String or a Rails Model

    Saves SQL to Dir.tmpdir/pry-psql.sql, adds basic formatting, and cleaned SQL in
    `psql` or Rails determined system command.
  BANNER

  def pretty_sql(sql)
    cleaned = sql
      .gsub('"', '')
      .gsub(/\b(SELECT|FROM|WHERE|GROUP|HAVING|LEFT JOIN|LEFT OUTER JOIN|INNER JOIN|JOIN|RIGHT|ORDER|WINDOW)\b/, "\n\\1")
    output.puts CodeRay.scan(cleaned, :sql).terminal
    cleaned
  rescue
    output.puts cleaned
    cleaned
  end

  def options(opt)
    opt.on :x, 'Expand columns vertically'
    opt.on :t, 'Output execution timing in millis', default: true
  end

  def process
    output.puts
    output.puts('Executing SQL:')
    cleaned = pretty_sql(sql_string)
    output.puts('=' * ENV['COLUMNS'].to_i)
    exec_db(cleaned)
  end

  # Parse the Pry args into a SQL statement.
  # The Pry args maybe a Rails statement that generates SQL, so we try
  # called `to_sql` first or `all.to_sql`, otherwise the whole evaled
  # result is considered the SQL.
  def sql_string
    # ghetto argument removal because we want quotes to pass through and
    # args has then stripped out. `use_shellwords: false` doesn't help too much
    # since quotes still need to be escaped.
    #
    # Sample Regex: /\b?\\-[xth]+\s*/
    argless = arg_string.gsub(%r{\b?-[#{opts.options.map(&:short).join}]+\s*}, '')
    evaled = begin
               target.eval(argless)
             rescue Exception
               # If there's any problems evaluating, consider the whole thing as raw SQL
               argless
             end
    evaled.try(:to_sql) || evaled.try(:all).try(:to_sql) || evaled
  end

  # Passes SQL to the configured Rails DB system command.
  # The magic occurs in the following steps:
  #
  # 1. Write the SQL to a file: `Dir.tmpdir/pry-db.sql`.
  # 2. Override `exec` call in `Rails::DBConsole` to call the command with the
  #    file arg instead of normal `Kernel::exec`
  #
  # We want to use Rais::DBConsole since it does a lot of smart stuff to
  # determine the correct DB binary and parse the Rails DB configurtion.
  def exec_db(sql)
    File.join(Dir.tmpdir, 'pry-psql.sql').tap do |tmp_path|
      File.open(tmp_path, 'w') do |f|
        f << "\\x\n" if opts.x?
        f << "\\timing\n" if opts.t?
        f << sql
      end

      require 'rails/command'
      require 'rails/command/base'
      require 'rails/commands/dbconsole/dbconsole_command'
      Rails::DBConsole.class_eval do
        def exec(full_path_command, *args)
          puts `#{full_path_command} #{args.join} < #{File.join(Dir.tmpdir, 'pry-psql.sql')}`
        end
      end
      Rails::DBConsole.start
    end
  end
end

Thanks for the update. I'm more amazed this is still useful 5 years later!
https://gist.github.com/ddrscott/c0e60b8986245aa3b03cc8bbe460e854#gistcomment-3866162