StrictPagination

Keywords: pagination, rails, ruby, activerecord, api, validation, params, database, sql, query-validation

A Ruby gem that adds strict pagination validation to ActiveRecord, preventing unsafe JOINs that can cause inconsistent pagination results.

Problem

When using LIMIT/OFFSET with DISTINCT and JOINs to associations that multiply rows (like has_many or unsafe has_one), pagination can become inconsistent. This happens because:

1. JOINs multiply rows (e.g., a User with 3 posts becomes 3 rows)
2. DISTINCT collapses them back to 1 row
3. But LIMIT is applied BEFORE DISTINCT, not after
4. Result: inconsistent page sizes and missing records

Solution

This gem provides strict_pagination mode that validates queries before execution, ensuring they don't use JOINs that multiply rows when using pagination.

Requirements

• Ruby 3.1.0 or higher
• ActiveRecord 6.1 or higher (Rails 6.1+)

Installation

Add this line to your application's Gemfile:

gem 'strict_pagination'

And then execute:

bundle install

Or install it yourself as:

gem install strict_pagination

Usage

Basic Usage

Simply add .strict_pagination to your ActiveRecord queries with DISTINCT:

# Safe: no associations
User.strict_pagination.distinct.limit(10).offset(0)

# Safe: belongs_to associations don't multiply rows
Article.strict_pagination
       .includes(:author)
       .distinct
       .limit(10).offset(0)

# Safe: view-backed has_one associations
Order.strict_pagination
     .includes(:latest_payment)  # backed by a database view
     .distinct
     .limit(10).offset(0)

# ERROR: has_many multiplies rows
User.strict_pagination
    .includes(:posts)
    .distinct
    .limit(10).offset(0)
# => StrictPagination::ViolationError

Note: Validation only runs when both LIMIT (from .limit()) and DISTINCT are present. This is because the pagination issue only occurs with DISTINCT queries.

When is Validation Triggered?

The validation only runs when BOTH conditions are met:

• Query has LIMIT (pagination)
• Query has DISTINCT

This means you can use strict_pagination safely on all queries, and it will only validate when necessary.

Safe Associations

The following associations are considered safe:

1. belongs_to - Never multiplies rows
2. has_one backed by database views - Views starting with Views:: or table names starting with views_
3. has_one with unique constraint - When the foreign key has a unique index

Unsafe Associations

The following associations will trigger an error:

1. has_many - Always multiplies rows
2. has_and_belongs_to_many - Always multiplies rows
3. has_one without unique constraint - Can multiply rows

Error Messages

When a violation is detected, you'll get a detailed error message:

StrictPagination::ViolationError: Strict pagination violation: The query includes unsafe
associations `posts` that can multiply rows, causing inconsistent pagination with
LIMIT/OFFSET + DISTINCT.

Unsafe associations detected:
  - posts: has_many (always multiplies rows)

Solutions:
1. Remove `posts` from the query's includes/preload/eager_load/joins
2. Replace has_many with has_one backed by a database view (automatically safe)
3. Add a unique constraint to the foreign key (for has_one associations)
4. Remove .strict_pagination if you understand the pagination risks

Configuration

You can configure the gem's behavior using an initializer:

# config/initializers/strict_pagination.rb

StrictPagination.configure do |config|
  # Validate all paginated queries, even without DISTINCT
  # Default: false (only validates with DISTINCT)
  config.validate_on_all_queries = false

  # Add custom view prefixes to consider as safe
  # Default: [] (uses "Views::" and "views_" by default)
  config.safe_view_prefixes = ['Reports::', 'Analytics::']
end

Configuration Options

validate_on_all_queries

By default, validation only runs when a query has both LIMIT and DISTINCT. Set this to true to validate all paginated queries regardless of DISTINCT:

StrictPagination.configure do |config|
  config.validate_on_all_queries = true
end

# Now this will be validated even without DISTINCT
User.strict_pagination.includes(:posts).limit(10)
# => StrictPagination::ViolationError

safe_view_prefixes

Add custom prefixes for view classes or table names that should be considered safe:

StrictPagination.configure do |config|
  config.safe_view_prefixes = ['Reports::', 'Analytics::']
end

# Now these are considered safe
class Reports::UserSummary < ApplicationRecord; end
Order.strict_pagination.includes(:user_summary).limit(20)  # Safe

Best Practices

For API Endpoints

Use strict_pagination on all paginated API endpoints with DISTINCT:

# app/controllers/api/v1/users_controller.rb
def index
  page = (params[:page] || 1).to_i
  per_page = (params[:per_page] || 20).to_i

  @users = User.strict_pagination
               .includes(:profile)  # Safe: belongs_to
               .distinct
               .limit(per_page)
               .offset((page - 1) * per_page)

  render json: @users
end

Using Database Views for Safe has_one

Replace has_many with has_one backed by a database view:

# Instead of:
class Author < ApplicationRecord
  has_many :books
end

# Create a view for the latest book:
# CREATE VIEW views_latest_books AS
#   SELECT DISTINCT ON (author_id) *
#   FROM books
#   ORDER BY author_id, published_at DESC

class Author < ApplicationRecord
  has_one :latest_book, class_name: 'Views::LatestBook'
end

# Now safe to use with strict_pagination:
Author.strict_pagination
      .includes(:latest_book)
      .distinct
      .limit(10).offset(0)

Gradual Adoption

You can gradually adopt strict pagination in your codebase:

# Start with critical endpoints
class Api::V1::OrdersController < ApplicationController
  def index
    page = (params[:page] || 1).to_i
    per_page = 20

    @orders = Order.strict_pagination
                   .distinct
                   .limit(per_page)
                   .offset((page - 1) * per_page)
  end
end

# Once confident, use it everywhere
class ApplicationRecord < ActiveRecord::Base
  # Helper method for pagination
  def self.paginated(page: 1, per_page: 20)
    page = page.to_i
    per_page = per_page.to_i

    strict_pagination
      .distinct
      .limit(per_page)
      .offset((page - 1) * per_page)
  end
end

How It Works

The gem:

1. Adds a strict_pagination method to ActiveRecord::Relation
2. Uses prepend to hook into query execution (exec_queries)
3. Before executing, validates that no unsafe associations are included/joined
4. Only validates when the query has LIMIT (and optionally DISTINCT)
5. Raises StrictPagination::ViolationError if unsafe associations are detected

Architecture

The gem follows Rails best practices:

• ActiveSupport::Concern - For clean module inclusion
• ActiveSupport.on_load - For proper Rails initialization
• Railtie - For Rails lifecycle integration
• Prepend pattern - For clean method overriding

Examples

Safe Queries

# No associations
User.strict_pagination.distinct.limit(20).offset(0)

# belongs_to only
Comment.strict_pagination
       .includes(:author, :post)
       .distinct
       .limit(20).offset(0)

# View-backed has_one
Invoice.strict_pagination
       .includes(:latest_payment)
       .distinct
       .limit(20).offset(0)

# has_one with unique constraint
User.strict_pagination
    .includes(:profile)  # profiles.user_id has unique index
    .distinct
    .limit(20).offset(0)

Unsafe Queries

# has_many association
User.strict_pagination
    .includes(:posts)  # Error: posts multiplies rows
    .distinct
    .limit(20).offset(0)

# has_and_belongs_to_many
Article.strict_pagination
       .includes(:tags)  # Error: tags multiplies rows
       .distinct
       .limit(20).offset(0)

# has_one without unique constraint
Account.strict_pagination
       .includes(:primary_contact)  # Error: no unique constraint
       .distinct
       .limit(20).offset(0)

Development

After checking out the repo, run:

bundle install

To run tests:

bundle exec rspec

To build the gem:

gem build strict_pagination.gemspec

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/hdamico/strict_pagination.

Please read CONTRIBUTING.md for details on our code of conduct and the process for submitting pull requests.

Roadmap

• Add helper methods for easier pagination parameter handling
• Performance benchmarks and optimization guide
• Rails generator for adding strict_pagination to existing controllers
• Automated migration helper for converting has_many to view-backed has_one
• Support for custom validation rules

Community & Support

• Issues: Report bugs or request features at GitHub Issues
• Discussions: Ask questions and share ideas at GitHub Discussions
• Contributing: See CONTRIBUTING.md for guidelines

License

The gem is available as open source under the terms of the MIT License.

---

Made with ❤️ for the Ruby and Rails community

If you find this gem useful, please ⭐ star the repository and share it with others!