Why can't I easily get current counts of records in my db?

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----------------------------"
1 Like

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.

3 Likes

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.

I have wondered if something like GitHub - igorkasyanchuk/rails_db: Rails Database Viewer and SQL Query Runner could be added as a development only gem by default. Many of the good database administration tools are OS specific and having something like this would be handy.

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 :man_shrugging:

Edit: @Betsy_Haibel I am looking to contribute here if there is any Todo!

this feels strongly related to Admin framework by default

2 Likes

It looks to me as a subset of what was also brought up here Do we have something similar to Laravel's Telescope, and I agree that would be an interesting internal dashboard to have.

Maybe we are talking about an ActiveAdmin engine of some sort :thinking:

1 Like

I’m not so sure it’s an “admin” thing. I am talking more direct database administation. Maybe there is a way to accomplish both at the same time?

2 Likes

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.

1 Like

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.

1 Like

I’ve been enjoying Administrate, found through Chris Oliver’s Jumpstart Rails.

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).