How to Write SQL in a Migration in Rails

How to write SQL in a migration in Rails

For your up migration:

execute "CREATE TABLE cars_users2 AS SELECT DISTINCT * FROM cars_users;" 
drop_table :car_users  
rename_table :car_users2, :car_users  

and for down:

raise ActiveRecord::IrreversibleMigration

Full migration:

class TheMigration < ActiveRecord::Migration
    def up
        execute "CREATE TABLE cars_users2 AS SELECT DISTINCT * from cars_users;" 
        drop_table :car_users  
        rename_table :car_users2, :car_users  
    end

    def down
        raise ActiveRecord::IrreversibleMigration
    end
end

Is it possible to output the SQL change scripts that 'rake db:migrate' produces?

You can create a Rake task in lib/tasks/:

namespace :db do
  desc 'Make migration with output'
  task(:migrate_with_sql => :environment) do
    ActiveRecord::Base.logger = Logger.new(STDOUT)
    Rake::Task['db:migrate'].invoke
  end
end

Then call rake db:migrate_with_sql to log the migration.

Show SQL generated by pending migrations in rails without updating the database

This can be done by monkey-patching the database adapter. This example works for MySQL.

Create a rake task for "fake db:migrate":

desc "Prints all SQL to be executed during pending migrations"
task :fake_db_migrate => :environment do

  module ActiveRecord
    module ConnectionAdapters
      class AbstractMysqlAdapter < AbstractAdapter

        alias_method :real_execute, :execute

        def execute(sql, name = nil)
          if sql =~ /^SHOW/ || sql =~ /^SELECT.*FROM.*schema_migrations/ || sql =~ /^SELECT.*information_schema/m
            real_execute(sql, name)
          else
            puts sql
          end
        end

      end
    end
  end

  Rake::Task["db:migrate"].invoke
end

The rake task monkey-patches the execute method in the connection adapter so that SQL is printed instead of being executed, before actually running the migrations. However, we still have to execute some of the internal SQLs that are used by the db:migrate task to get the database schema and to find out which migrations are pending. That's what the real_execute call does.

Test

Suppose now that and we have a pending migration in db/migrate/20160211212415_create_some_table.rb:

class CreateSomeTable < ActiveRecord::Migration
  def change
    create_table :some_table do |t|
      t.string :string_column, null: false, default: 'ok'
      t.timestamps
    end
  end
end

$ rake db:migrate:status
...
down    20160211212415  Create some table

Now, let's run our fake migrations task:

$ rake fake_db_migrate
== 20160211212415 CreateSomeTable: migrating ==================================
-- create_table(:some_table)
CREATE TABLE `some_table` (`id` int(11) auto_increment PRIMARY KEY, `string_column` varchar(255) DEFAULT 'ok' NOT NULL, `created_at` datetime, `updated_at` datetime) ENGINE=InnoDB
   -> 0.0009s
== 20160211212415 CreateSomeTable: migrated (0.0010s) =========================

BEGIN
INSERT INTO `schema_migrations` (`version`) VALUES ('20160211212415')
COMMIT

The migrations status has not been changed, i.e. the migration is still pending:

$ rake db:migrate:status
...
down    20160211212415  Create some table

Tested on rails 4.2.3 with the mysql2 gem.

Executing sql in rails migration opens to sql injection attacks?

Your example is not likely to be at risk of sql injection. Regardless, it is good practice in Rails to sanitize raw sql fragements: http://api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html

Example:

class MovePlayerToJoinTable < ActiveRecord::Migration
  def change
    games = Game.all
    games.each do |g|
      g.players.each do |p|
        query = sanitize_sql_array(['insert into games_players (player_id, game_id) values (?, ?)', p.id, g.id])
        connection.execute(query)
      end
    end
  end
end

Creating mysql function from rails migration

Rails cannot execute DELIMITER because it is a MYSQL command.

ActiveRecord sets up a different end-of-statement flag (not a semi-colon), so you can just write the straight sql,
Just remove DELIMITTER and $$

How can you get a sql script of changes from Rails migrations (for MySQL)?

I actually ended up creating a rake task which monkey-patched Activerecord's sql execution method to also output all sql to a log file (log/database.log). So, the task could be run right before db:migrate like so: rake db:log db:migrate. After that, you can extract the statements which are relevant and put them into a db/sql_migrations/<migration name>.sql file and have your database admins run that when they're ready.

Thankfully I've changed jobs and don't have to deal with that mess of a process anymore though. :)

---

Related Topics