activerecord-patterns_skill

Overview

This skill is a compact, actionable guide to ActiveRecord query patterns focused on Rails and PostgreSQL. It covers query decision-making, eager loading, scope design, migrations, GROUP BY rules, JSONB usage, and performance checks. Use it to avoid common pitfalls like N+1 queries, incorrect includes/group combinations, and missing indexes. The guidance is practical and ready to apply in production Rails apps.

How this skill works

The skill inspects typical query needs (single vs multiple records, association access, aggregation, complex queries) and maps them to the correct ActiveRecord method (find, where, joins, includes, group, select, CTEs). It highlights Postgres-specific rules (GROUP BY requirements, tsvector/full-text, JSONB queries) and provides templates for models, scopes, and migrations. It also provides a checklist and debugging tips (explain, logging, Bullet) to validate and optimize queries.

When to use it

• Writing or refactoring database queries to improve performance
• Designing model associations and deciding eager loading strategy
• Creating migrations that require indexes, JSONB, or concurrent changes
• Troubleshooting N+1 queries, GROUP BY errors, or slow plans
• Implementing search (pg_search) or complex aggregations

Best practices

• Prefer includes/preload/eager_load based on whether you need filtering or data loading
• Never use includes together with group; run aggregates in separate queries
• Use find_each for large datasets and avoid loading all records into memory
• Index foreign keys, WHERE, and ORDER columns; use GIN for JSONB
• Use exists? for existence checks instead of present? to avoid loading rows

Example use cases

• Count tasks by status: Task.group(:status).count (or Task.select(...).group(:status) for custom aggregates)
• Fix N+1: Task.includes(:carrier).each { |t| t.carrier.name }
• Filter on association attributes: Task.joins(:carrier).where(carriers: { active: true })
• Batch process millions of rows: Task.find_each(batch_size: 1000) { |t| process(t) }
• Store and query JSON metadata: add jsonb column + GIN index; use metadata @> ?