Posts Tagged ‘myql’

Renaming a database in Rails

December 14th, 2009

Hooray for a non-rant post!

The heart of this method works outside of Rails, but there’s one particular convenience method that Rails provides.  Find some way to mimic that behavior, and the rest would apply.  Also, this is a MySQL-specific solution.  I haven’t tried it with any other DB systems.

We “rename” a database by moving all of its tables into a new database.  We do so via MySQL’s RENAME TABLE command.  Basically, create an empty database with the new name.  We then use our ActiveRecord connection’s “tables” method to loop over all the tables in our database, executing a RENAME TABLE from the old to the new with each iteration. “Tables” is the convenience method I mentioned above. Encapsulating this in a method we get:

def rename_database(old_db_name, new_db_name)
    ActiveRecord::Base.connection.execute "CREATE DATABASE #{new_db_name}"
    ActiveRecord::Base.connection.tables.each do |table|
            "RENAME TABLE #{old_db_name}.#{table} TO #{new_db_name}.#{table}"
    ActiveRecord::Base.connection.execute "DROP DATABASE #{old_db_name}"

Error checking is left as an (important!) exercise to the reader.  Also, you may prefer to monkey patch this into ActiveRecord itself as opposed to some odd method floating around in global space.

This presupposes that the new database destination will be on the same server.

Another way to rename a database is to mysqldump the old one to a file and then reimport it to the renamed database.  The method presented in this article is advantageous if your database has a large amount of data.  Renaming a table is almost instant.  Dumping millions of rows and reimporting them is not so instant.