ActiveRecord Migrations provides neat ways to handle migrations in databases. This includes adding, updating, and removing columns in databases. Some of these operations can take a lot of time, depending on the translated SQL queries.
One of these slow operations is the
remove_columns
operations with multiple columns.
It is because individual SQL queries are triggered to delete each column.
Since,
both MySQL and PostgreSQL support dropping multiple columns in a single SQL statement,
Rails 7 will now generate a single SQL statement to remove all the columns together if the database supports it.
Since SQLite3 doesn’t support dropping multiple columns, it will still generate multiple SQL queries.
Let’s understand this with the help of an example.
Let’s assume we have a Post
model with attributes :content
,
:description
,
:date_created_at
and
:date_published_at
.
Due to requirement changes,
we now want to remove :description
,
:date_created_at
and
:date_published_at
To do this, we would generate a migration, which would look something like this:
class RemoveColumnsFromPosts < ActiveRecord::Migration[6.1]
def change
remove_columns :posts, :description, :date_created_at, :date_published_at
end
end
Before
Before Rails 7, the given migration will generate three separate SQL statements.
PostgreSQL example for the above migration:
ALTER TABLE "posts" DROP COLUMN "description"
ALTER TABLE "posts" DROP COLUMN "date_created_at"
ALTER TABLE "posts" DROP COLUMN "date_published_at"
After
In Rails 7, the above migration will generate only one SQL statement
ALTER TABLE "posts" DROP COLUMN "description", DROP COLUMN "date_created_at", DROP COLUMN "date_published_at"
It is a good improvement,
as regardless of the number of columns to be dropped,
we will only be executing one ALTER_TABLE
query instead of multiple ALTER_TABLE
queries.
There is also a performance improvement to it, as there is only one call to the database. But more importantly, we’ll be making all the changes in one copy operation in the database.