I usually add something like this to projects and then extend the rake db namespace to include a call to it. (This is a postgres-specific example, and also handles tables that don’t have a class defined, e.g. acts_as_taggable_on tables.)
tuple_counts.rb
# run from the command line with: rails runner -e development db/tuple_counts.rb
def check_table_has_id_column(table_name, connection) # that's an integer
sql = "SELECT count(1)
FROM information_schema.columns
WHERE table_schema = 'public'
AND column_name = 'id'
AND table_name = '#{table_name}'"
result = connection.execute(sql).values.flatten.join.to_i
if result == 0
false
else
true
end
end
def check_table_count(table_name, connection, class_name)
# get count of records in table. set to count empty string if result == 0.
sql = "select count(1) from #{table_name}"
result = connection.execute(sql).values.flatten.join.to_i
if result == 0
count = " "
else
count = result
end
# get value of highest id in table. set to "na" if result == 0.
sql = "select max(id) from #{table_name};"
result = connection.execute(sql).values.flatten.join.to_i
if result == 0
max_id = "___"
else
max_id = result
end
puts "#{ count.to_s.rjust(3, " ") } / #{max_id.to_s.ljust(3, "_")}" +
"___" + class_name.to_s
end
puts "--------------------TABLE TOTALS (count/max id)---------------------"
puts "COUNT / MAX ID ___TABLE_NAME"
connection = ActiveRecord::Base.connection
ActiveRecord::Base.connection.tables.each do |table_name|
unless ["ar_internal_metadata", "schema_migrations", "tags", "taggings"].include?(table_name)
begin
table_has_id_column = check_table_has_id_column(table_name, connection)
if table_has_id_column == true
class_name = table_name.singularize.classify.constantize
check_table_count(table_name, connection, class_name)
else
check_table_count(table_name, connection, table_name)
puts "=======#{table_name + ': doesnt have an id column'}======="
end
rescue
puts "=======#{table_name + ': is not able to be constantized'}======="
end
end
end
tables_without_defined_class = ["tags", "taggings"]
tables_without_defined_class.each do |table_name|
check_table_count(table_name, connection, table_name)
end
puts "-----------------------------END----------------------------"
This feels like it could be the seed of some kind of database administration gem. I would use it at work – I feel like I’ve looked up the raw SQL for “how many GB of data does this Postgres table store” ten times in the last few months.
I’ve gotten curious about this a few times, but not frequently enough to save a solution for this. My usual approach is to google how to get all ActiveRecord tables, then call .count on each of them.
The rails console is generally handy enough but I run a postgres administrator for some things. It would be nice to go to /rails/info/db (or something like that) for a good database administrator.
The annoying part about that is your rails server would have to be running
Edit: @Betsy_Haibel I am looking to contribute here if there is any Todo!
Yeah, I’m more interested in database table counts (and max id’s) than records per se.
Even the rails_db is more of a data viewer than a database properties reporter. (Tho I do like rails_db functionality, esp w the export function!, it’s not what I was going for.)
The activeadmin thing is…an interesting idea. Every time I’ve added one of these to a project, I end up ripping it out within the year bc I want to do something the DSL doesn’t support, so, I’m less on board with that.
It doesn’t have to be activeadmin, we could come up with a whole new solution. I read again and again how Django’s admin framework is one of the framework’s selling points. I actually think activeadmin is a bit too much. the dsl is quite huge; it’s doing a lot for you but I would trim down on functionality and get the basics right.
I enjoy Administrate precisely because its first guideline is “No DSLs (domain-specific languages)”. I previously used RailsAdmin in my app and indeed did end up quitting because of its DSL and limited maintenance. I managed to stick with Administrate and was able to submit contributions when I encountered some limitations. Two developers from Thoughtbot seem to be regularly maintaining the library, usually every week, and they are steadily preparing for a 1.0 release (I personally run our prod app using the master branch, as their release speed is a bit too slow for my liking).