RenderSQL

Rails Engine to treat SQL files as user-facing views.

Often times —usually in an "admin" context— one needs to display data to users. SQL is a quick way to retrieve the data but your users need (semi)pretty HTML displays. This means you have to create a view file, maybe convert it to an ActiveRecord query or paste it into a .find_by_sql call etc... Sometimes in lieu of writing an HTML view you'll "compromise" and export to CSV.

With RenderSQL just create an SQL file. The result will be automatically displayed in an HTML table.

Usage

In your Gemfile:

gem "render_sql"

Define an action and route in a controller:

class UsersController
  def must_upgrade
  end
end

Create an SQL file named must_upgrade.sql with a query:

select id, name, monthly_payment from users where monthly_payment < 5 order by created_at desc

Now when you visit the #must_upgrade action the query will be executed and its result placed in an HTML table using the current controller's layout.

The selected column names will be used for the HTML table's headers. If you need something pretty create column aliases:

select id, name 'Full Name', monthly_payment 'Monthly Payment' from users where monthly_payment < 5 order by created_at desc

If you want to use a single action for multiple SQL files:

class UsersController
  KNOWN_REPORTS = %w[monthly weekly yearly].freeze

  # Don't forget to define the route
  def reports
    if KNOWN_REPORTS.include?(params[:report])
      render params[:report]
    else
      render :plain => "Unknown report", :status => 400
    end
  end
end

Then create 3 SQL files monthly.sql, weekly.sql, yearly.sql containing the respective queries.

The page's heading will be the SQL file's basename, titleized.

Query Placeholders

Supported only for where clauses. For example:

select id, name from users where created_at < :created_at limit 50

Now when the associated controller action is accessed one must provide a value for the created_at parameter.

If a required placeholder is missing a RenderSQL::MissingParameter error will be raised.

View Customization

You can override some things in your app's application.rb file:

module YourApp
  class Application < Rails::Application
    config.render_sql.css_classes[:table] = "table"
    config.render_sql.css_classes[:container] = "container py-4"
    config.render_sql.css_classes[:heading] = "mb-2"
    config.render_sql.partial = "your-partial"
  end
end

The partial will be provided with a result variable:

<table>
  <tr>
    <% result.headers.each do |header| %>
      <th><%= h(header) %></th>
    <% end %>
  </tr>
  <% result.each do |row| %>
    <tr>
      <% row.each do |cell| %>
        <td><%= h(cell) %></td>
      <% end %>
    </tr>
  <% end %>
</table>

Limitations

• No pagination
• Placeholders only for where criteria
• Rails >= 6.1

Author

Skye Shaw [skye.shaw -AT- gmail.com]

License

Released under the MIT License: http://www.opensource.org/licenses/MIT