How to write SQL in a migration in Rails

sql

database-migration

rails-migration

sql-queries

byNikita Barsukov·Sep 22, 2024

Implement raw SQL in your Rails migration using the execute method:

class AddUsersTable < ActiveRecord::Migration[6.0]
  def change
    execute 'CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));'
  end
end

Remember:

The change method handles both up and down migrations—Rails can intuit how to reverse it.
For clarity, use a single-line string for short SQL statements.

Advanced scenarios: executing raw SQL

An SQL migration isn't restricted to the execute method. Based on the complexity and requirements, there are different approaches.

Table renaming and data transformation

When a simple table rename doesn't cut the mustard, and you need to perform some data wizardry:

# Anyone said magic? Nope. This is pure SQL.
class RenameAndTransformUsersTable < ActiveRecord::Migration[6.0]
  def up
    execute <<-SQL
      CREATE TABLE new_users AS
      SELECT id, name, 'unknown' as email FROM users;
    SQL
    drop_table :users
    rename_table :new_users, :users
  end

  def down
    raise ActiveRecord::IrreversibleMigration
  end
end

Reversible changes with style

Rails 4+ introduced reversible blocks, a swanky way to handle both up and down methods in one go:

# Bet you didn't see this coming!
class AddPartNumberToProducts < ActiveRecord::Migration[6.0]
  def change
    reversible do |dir|
      dir.up { execute 'ALTER TABLE products ADD COLUMN part_number STRING' }
      dir.down { execute 'ALTER TABLE products DROP COLUMN part_number' }
    end
  end
end

Elegance with multi-statement SQL

Long live here-docs! They enhance readability of complex multi-step SQL statements:

# Go big or go home, right?
class MultiStepSQL < ActiveRecord::Migration[6.0]
  def change
    execute <<-SQL
      DROP TABLE IF EXISTS old_users;
      CREATE TABLE new_users (LIKE users INCLUDING ALL);
      INSERT INTO new_users SELECT * FROM users;
    SQL
  end
end

Mastering migrations: versatility is key

Crafting reversible changes

Write migrations where you manipulate the database schema using SQL? It's crucial you know how to construct reversible changes:

# "reversibility", is there anything you can't do?
class ChangeUsersTableName < ActiveRecord::Migration[6.0]
  def change
    rename_table :users, :accounts
  rescue
    raise ActiveRecord::IrreversibleMigration
  end
end

Data safety vs. structural changes

When you meddle with table structures, consider the potential impact on your precious data:

# Let's not ruin the party by deleting everything accidentally
class AddDefaultValue < ActiveRecord::Migration[6.0]
  def change
    add_column :users, :active, :boolean, default: true
    User.update_all(active: true) # start the backfill process
  end
end