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.
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
Due to requirement changes,
we now want to remove
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 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"
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
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.