Sometimes you end up with tables in your database schema that just aren't named what you want anymore. At first glance, renaming them without any downtime isn't feasible because updating the code that uses the table at the same instant that the table is renamed isn't easily accomplished.
We recently came across a few database tables in our schema that were begging to be renamed. Bringing our site down for several minutes to run the migrations and deploy the updated code was unacceptable, so I started investigating to come up with a solution.
In this example, we will be renaming
Using a View
Some views are updatable and references to them can be used to specify tables to be updated in data change statements. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table.
This is super cool because it means that all of the queries, both reads and writes, can be done against the new name. Here we create a view with the new name that we want to end up with:
CREATE VIEW `users` AS SELECT * FROM `people`;
Updating Queries in Code
Because the view acts just like a proper table, we can deploy the code that updates all queries to reference
users instead of
All actions, including writes, proxy through to the underlying table,
Replacing the View With the Table
The last piece of the puzzle is to rename the table and drop the old view. Fortunately, MySQL's rename table command allows you to atomically rename multiple tables:
RENAME TABLE `users` TO `users_old_view`, `people` to `users`; DROP VIEW `users_old_view`;
And just like that we have renamed the table with zero downtime.
This approach has a few caveats that you should be aware of:
While foreign keys are updated when renaming the table, queries in views are not.
In fact, immediately after the
RENAME TABLE command in this example, the query backing
users_old_view is left untouched so it is still referencing
people which doesn't exist anymore.
This doesn't matter in particular because we end up dropping the view in the next statement, but leaving a bad view does cause
mysqldump to fail.
Missing Data in the Information Schema
Also, we discovered one minor difference between the table and the view: the primary key for the view is not available in the
Our ORM uses this information to build its model of the object so it can generate queries to interact with the underlying table.
Fortunately, we could easily provide the information it needed in code while the view was in use so it wouldn't have to look it up in the information schema table at all.
There may be more differences between tables and views that we didn't come across, so exercise caution and do thorough testing before using them in production.
All of the SQL commands were nearly instantaneous and flawlessly renamed one of our most frequently accessed tables. Consider using this approach if you are running MySQL and have some tables that need to be renamed.
Happy table renaming!