Getting a count of the rows in all of your ActiveRecord models


Warning: Going through ActiveRecord is likely not something you want to do in production unless you have a replica database that your script or your model can point at. See the second part for MySQL and PostgreSQL examples in SQL.

How do you enumerate your models?

For apps created under Rails 5 app or later, all of your ActiveRecord models should be descendants ApplicationRecord as well. If this is an option, you’ll be able to filter out some noise from descendants such as SchemaMigration, etc…

Using the descendants of ApplicationRecord you can map the statistics… You’ll need to catch errors on tables that might only be in production-like environments if you’re running locally:

counts = ApplicationRecord.descendants.map do |klass|
  [klass.name, klass.table_name, klass.count]
rescue Mysql2::Error, ActiveRecord::StatementInvalid # if certain tables aren't present in certain environments
  nil
end


=> []

Oops. I’m in development so… no eager loading of the models.


Rails.application.eager_load! # if in development

counts = ApplicationRecord.descendants.map do |klass|
  [klass.name, klass.table_name, klass.count]
rescue Mysql2::Error, ActiveRecord::StatementInvalid # if certain tables aren't present in certain environments
  nil
end

# lots of SELECT COUNT(*) logs if you're on :debug level logging
# followed by an array of name/table name/counts

You might also want to compact that result due to nils:


Rails.application.eager_load!
counts = ApplicationRecord.descendants.map do |klass|
  [klass.name, klass.table_name, klass.count]
rescue Mysql2::Error, ActiveRecord::StatementInvalid # if certain tables aren't present in certain environments
  nil
end

From there, you can export to CSV:

Rails.application.eager_load!

counts = ApplicationRecord.descendants.map do |klass|
  [klass.name, klass.table_name, klass.count]
rescue Mysql2::Error, ActiveRecord::StatementInvalid # if certain tables aren't present in certain environments
  nil
end

output_filename = "/tmp/counts.csv"

CSV.open(output_filename, "wt") do |csv|
  csv << ['class name', 'table name', 'row count']
  counts.compact.each do |row|
    csv << row
  end
end

What about from the database itself?

The below options are *significantly* faster than going through the Rails stack, so unless you need additional information from Rails, you should refine the below options instead (especially for production)

  • MySQL:
select table_name, sum(table_rows) from information_schema.tables where table_schema = 'app_dbname_development' group by table_name;
select table_schema, 
       table_name, 
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
  select table_name, table_schema, 
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public' --<< change here for the schema you want
) t order by 3 DESC


Leave a Reply

%d bloggers like this: