Managing Tables

So far we’ve been using migrations to manipulate the columns in existing tables. Now let’s look at creating and dropping tables:

create_table takes the name of a table (remember, table names are plural) and a block. (It also takes some optional parameters that we’ll look at in a minute.) The block is passed a table definition object, which we use to define the columns in the table.

Generally the call to drop_table is not needed, as create_table is reversible. drop_table accepts a single parameter, which is the name of the table to drop.

The calls to the various table definition methods should look familiar—they’re similar to the add_column method we used previously except these methods don’t take the name of the table as the first parameter, and the name of the method itself is the data type desired. This reduces repetition.

Note that we don’t define the id column for our new table. Unless we say otherwise, Rails migrations automatically add a primary key called id to all tables they create. For a deeper discussion of this, see ​Primary Keys​.

The timestamps method creates both the created_at and updated_at columns, with the correct timestamp data type. Although there is no requirement to add these columns to any particular table, this is yet another example of Rails making it easy for a common convention to be implemented easily and consistently.

Options for Creating Tables

You can pass a hash of options as a second parameter to create_table. If you specify force: true, the migration will drop an existing table of the same name before creating the new one. This is a useful option if you want to create a migration that forces a database into a known state, but there’s clearly a potential for data loss.

The temporary: true option creates a temporary table—one that goes away when the application disconnects from the database. This is clearly pointless in the context of a migration, but as we’ll see later, it does have its uses elsewhere.

The options: "xxxx" parameter lets you specify options to your underlying database. They are added to the end of the CREATE TABLE statement, right after the closing parenthesis. Although this is rarely necessary with SQLite 3, it may at times be useful with other database servers. For example, some versions of MySQL allow you to specify the initial value of the autoincrementing id column. We can pass this in through a migration as follows:

Behind the scenes, migrations will generate the following DDL from this table description when configured for MySQL:

Be careful when using the :options parameter with MySQL. The Rails MySQL database adapter sets a default option of ENGINE=InnoDB. This overrides any local defaults you have and forces migrations to use the InnoDB storage engine for new tables. Yet, if you override :options, you’ll lose this setting; new tables will be created using whatever database engine is configured as the default for your site. You may want to add an explicit ENGINE=InnoDB to the options string to force the standard behavior in this case. You probably want to keep using InnoDB if you’re using MySQL, because this engine gives you transaction support. You might need this support in your application, and you’ll definitely need it in your tests if you’re using the default of transactional test fixtures.

Renaming Tables

If refactoring leads us to rename variables and columns, then it’s probably not a surprise that we sometimes find ourselves renaming tables, too. Migrations support the rename_table method:

Rolling back this migration undoes the change by renaming the table back.

Problems with rename_table

There’s a subtle problem when we rename tables in migrations.

For example, let’s assume that in migration 4 we create the order_histories table and populate it with some data:

Later, in migration 7, we rename the table order_histories to order_notes. At this point we’ll also have renamed the model OrderHistory to OrderNote.

Now we decide to drop our development database and reapply all migrations. When we do so, the migrations throw an exception in migration 4: our application no longer contains a class called OrderHistory, so the migration fails.

One solution, proposed by Tim Lucas, is to create local, dummy versions of the model classes needed by a migration within the migration. For example, the following version of the fourth migration will work even if the application no longer has an OrderHistory class:

This works as long as our model classes do not contain any additional functionality that would have been used in the migration—all we’re creating here is a bare-bones version.

Defining Indices

Migrations can (and probably should) define indices for tables. For example, we might notice that once our application has a large number of orders in the database, searching based on the customer’s name takes longer than we’d like. It’s time to add an index using the appropriately named add_index method:

If we give add_index the optional parameter unique: true, a unique index will be created, forcing values in the indexed column to be unique.

By default the index will be given the name index_table_on_column. We can override this using the name: "somename" option. If we use the :name option when adding an index, we’ll also need to specify it when removing the index.

We can create a composite index—an index on multiple columns—by passing an array of column names to add_index.

Indices are removed using the remove_index method.

Primary Keys

Rails assumes every table has a numeric primary key (normally called id) and ensures the value of this column is unique for each new row added to a table. We’ll rephrase that.

Rails doesn’t work too well unless each table has a primary key that Rails can manage. By default, Rails will create numeric primary keys, but you can also use other types such as UUIDs, depending on what your actual database provides. Rails is less fussy about the name of the column. So, for your average Rails application, our strong advice is to go with the flow and let Rails have its id column.

If you decide to be adventurous, you can start by using a different name for the primary key column (but keeping it as an incrementing integer). Do this by specifying a :primary_key option on the create_table call:

This adds the number column to the table and sets it up as the primary key:

The next step in the adventure might be to create a primary key that isn’t an integer. Here’s a clue that the Rails developers don’t think this is a good idea: migrations don’t let you do this (at least not directly).

Tables with No Primary Key

Sometimes we may need to define a table that has no primary key. The most common case in Rails is for join tables—tables with just two columns where each column is a foreign key to another table. To create a join table using migrations, we have to tell Rails not to automatically add an id column:

In this case, you might want to investigate creating one or more indices on this table to speed navigation between books and authors.