Here's the fastest way to get raw SQL into an API endpoint, with minimal intervention from the model layer of the Rails framework.

explanation.md

To distill the essence of turning a raw SQL DB query into an API endpoint in Rails, we can abstract away the domain-specific details and focus on the general process. Here's a step-by-step guide that captures the technique:

Step 1: Define the SQL Query

Create a module with a method that encapsulates the raw SQL query. This method should accept parameters to filter or manipulate the data as needed and return the result set.

module Queries
  module CustomData
    def self.perform(params:)
      query = <<-SQL
        SELECT column1, column2, ...
        FROM your_table
        WHERE conditions
        -- Other SQL clauses
      SQL
      rows = ActiveRecord::Base.connection.select_all(query)
      rows.to_a.map do |row|
        row.keys.each do |key|
          row[key] = rows.column_types[key].type_cast_from_database(row[key])
        end
        row
      end
    end
  end
end

Step 2: Fetch and Process Data

Create a service class that uses the query module to fetch data. This class can also include methods to serialize the data into the desired format.

module DataFetcher
  class FetchData
    attr_reader :params

    def initialize(params:)
      @params = params
    end

    def perform
      query.map do |row|
        serialize_data(row)
      end
    end

    private

    def query
      @query ||= Queries::CustomData.perform(params: params)
    end

    def serialize_data(row)
      # Transform the row data into the desired format
      row.slice(*%w[column1 column2 ...])
    end
  end
end

Step 3: Create the API Endpoint

In your controller, create an action that instantiates the service class with the necessary parameters (e.g., from the request) and returns the serialized data as a JSON response.

class Api::V1::DataController < ApplicationController
  def fetch_data
    fetcher = DataFetcher::FetchData.new(
      params: params_to_pass_to_query
    )
    render json: {
      data: fetcher.perform,
      meta: additional_metadata
    }
  end

  private

  def params_to_pass_to_query
    # Extract and process parameters from the request
  end

  def additional_metadata
    # Any additional metadata you want to include in the response
  end
end

Step 4: Handle Exceptions

Use rescue handlers in your controller to manage exceptions that may arise from policy violations or other errors, and return appropriate HTTP status codes and error messages.

class Api::V1::DataController < ApplicationController
  rescue_from CustomPolicy::Denied do |exception|
    render_error(:unauthorized, exception.message)
  end

  # Other rescue_from handlers as needed

  # ... (rest of the controller)
end

Step 5: Route the Request

Define a route in your config/routes.rb file that maps the desired URL to the controller action.

Rails.application.routes.draw do
  namespace :api do
    namespace :v1 do
      get 'data/fetch', to: 'data#fetch_data'
    end
  end
end

By following these steps, you can create a minimal and generic representation of the process to turn a raw SQL DB query into an API endpoint in a Rails application. This approach separates concerns, allowing for modular, maintainable, and testable code.