Introspecting your database schema using ActiveRecord (and Rails)

ActiveRecord (with our without Rails) hide away most details from using a database on a lot of common scenarios. However from time to time I have to do something that involves handling the database without using the abstraction of a model, either because it's just simpler not to use an abstraction or because I have to actually use the database schema.

The good thing is that ActiveRecord can also help you writing code that introspect the database itself, so you can easily deal with tables, schemas, index, etc.

It all starts with the actual connection to the database which you can get through ActiveRecord::Base.connection. Querying and manipulating the database is very easy with it:

connection = ActiveRecord::Base.connection

# Getting all tables
tables = connection.tables

# Getting all columns and indexes from the first table
columns = connection.columns(tables.first)
indexes = connection.indexes(tables.first)

# Creating a new table
connection.create_table(:table_name) do |t|
  t.integer :foo
  # etc...
end

# Dropping a table
connection.drop_table(:table_name)

If you would like to know more, the Rails codebase is your friend.

Many of those methods are defined on "activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb" and/or on the specific adapter for you database. Taking PostgreSQL for example, some of those methods are defined on "activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb". Note that those file paths are based on Rails 5.2 but they are either the same for older versions or you can find similar files with those methods on Rails' codebase (I remember using them since at least around Rails 3).

If you don't feel comfortable cloning Rails and navigating around on your computer, you can use your web browser via GitHub - just go to Rails and press t for searching by file name and don't forget you can use GitHub's search box for searching for specific pieces of code. I used GitHub to find the path for the files listed above 😊

Please bear in mind that if you're using the database connection as part of a long-lived process (like a web server), you may have to release the connections you obtain when calling ActiveRecord::Base.connection. You can see more details about that here.

Show Comments