Girders Blog
Notes on building internet applications

Modern Rails Migrations

Jan 31, 2018

Active Record Migrations, a feature of Ruby on Rails, creates and changes database tables from within your app. These migrations assist in setting up development and test environments. When you deploy your app, the tables can be automatically created.

Generate a new table

The rails generate command creates the migration and optionally, other application code stubs for you. You can use on of these commands:

rails g migration CreateWidget [columns...] [options]
rails g model     widget  [columns...] [options]
rails g scaffold  widget  [columns...] [options]

Useful options are:

-p  --pretend            "Pretend" to run to check for errors
--primary-key-type=uuid  USe a different Type for your "id" column

You can name your migration anything you need, but naming conventions like “CreateWidget” will set up the migration to create a widgets table.

After generating, you’ll likely want to edit the resulting migration file. Migrations are prefixed with the current timestamp to help specify creation and revision order, so yyyymmddhhmmss refers to whatever filename was generated by the command.

vim db/migrate/yyyymmddhhmmss_create_widgets.rb

Then execute the migration in devopment (default) and test environments.

rake db:migrate
rake db:migrate RAILS_ENV=test

Rolling Back

If you make a mistake and want to start over, run the destroy command.

rails destroy migration CreateWidget

If you created the table already with rails db:migrate, remember to run rails db:rollback first. (Before Rails 5.0, these tasks should be invoked with the rake command instead.) Use the VERSION or STEP arguments to move forward/backward to a timestamp or number of migrations:

rails db:rollback              # reverts last migration only
rails db:rollback STEP=3       # reverts last 3 migrations
rails db:rollback VERSION=yyyymmddhhmmss

Revert a previously committed migration with a new migration

def change
  revert MigrationName
end

Migration File Format

You can also create a migration file manually. They look like this. Migrations use a Domain-Specific-Language (DSL) to generate the required SQL statements for your database.

class CreateWidgets < ActiveRecord::Migration[5.1]
  def change
    # ... migration commands
    reversible do |dir| # Optional
      dir.up do
        # ... change commands
      end
      dir.down do
        # ... optional undo change commands
      end
    end
  end
end

Specify the version of Rails used to define the migration usage as an index to the Migration class name ([5.1]).

Most migration commands can reverse themselves intelligently. Raw changes such as running SQL statements directly need to have an “up” and “down” section of a reversible block.

Modeling Your Data

I like to create my data models using a script containing the migration parameters so I can edit and tweak them to get it close (rarely perfect) the first time.

#!/bin/sh
bundle exec rails generate model Widget \
  "name:string{40}:index" \
  price:integer \
  description:text \
  --pretend

This is useful when creating scaffolds or auto-generated column lists for forms, controllers, factories, etc. and when thinking through relationships between related tables. I don’t commit this file; it is just a helper. The resulting migration still needs editing to add multi-column indexes and other constraints.

Defining Your Primary Key

The rails migration will automatically add a primary key column named id onto your table with the configured datatype. Primary keys have a unique constraint, cannot be null, and an index is created for it. For integer/bigint types, a sequence is generated for the column as well with serial or ‘bigserial’ datatypes (PostgreSQL).

By default, rails generates id columns with 32-bit integer type. In Rails 5.1, that has been changed to Bigint (64-bit integer). In PostgreSQL, the serial or bigserial types are used which are unsigned integers of 4 or 8 bytes respectively.

Use the --primary-key-type=xxx option to override the primary key type. If you expect to use the same type for all tables, configure the default primary key type by creating an initializer:

# config/initializers/generators.rb
Rails.application.config.generators do |g|
  g.orm :active_record, primary_key_type: :uuid
end

You may also handle this yourself with the :id option on the create_table statement in the migration.

create_table :widgets, id: :uuid do |t|
  # ...
end

An alternate way can also be used if you need to override the name of the primary key column.

create_table :widgets, id:false do |t|
  t.primary_key :ident, :uuid, default:'gen_random_uuid()'
  # or
  t.uuid :ident, primary_key: true
  # ...
end

UUID Primary Keys

Note for UUID primary key types (PostreSQL): the migration will configure the id column default to the gen_random_uuid() function. If you get an error telling you that function is not defined, you need to first enable the pgcrypto and also uuid-ossp extensions. Do this before migrating your first table, preferably in an initial migration.

# rails g migration CreateUuidExtensions
# db/migrate/yyyymmddhhmmss\_create\_uuid\_extensions.rb
 def change
   enable_extension 'uuid-ossp' unless extension_enabled?('uuid-ossp')
   enable_extension 'pgcrypto'  unless extension_enabled?('pgcrypto')
 end

This is the same as running these SQL commands.

create extension if not exists pgcrypto;
create extension if not exists "uuid-ossp";

Defining Columns

Definition of each column from the command-line list to the table definition code in a format of:

name                  # Defaults to string type
name:type             # Specify another type
"name:type{modifier}" # Include a size (quotes required)
name:index            # Create an index on this column
name:type:index       # Create an index on this column of this type

Where name is a valid SQL and Ruby identifier, avoiding reserved words from either context.

Suffix the definition with :index to have and index automatically generated for that column. The index line generated is not unique be default, so you’ll have to edit that after the generation.

Types (Migration File)

Replace :type with a known datatype for your database. The modifiers are used to define options such as size (:limit option) and :precision. The default type is a string of the default size.

:primary_key sets the column to be the configured type of the primary key column

String types: :string, :text. Use the :limit option or modifier such as :string{42} to limit the size in characters (not bytes).

Integer types: :int or :integer: (4 bytes), :tinyint (1 byte), :smallint (2 bytes), :bigint (8 bytes). The :limit option used with :integer specifies how many bytes long it should be.

Floating Point: :float (4 bytes). For double, use limit: 23 (any number between 23 and 53) option. Also the :double type creates a “double precision” type (PostgreSQL) Remember: never store money values in float. Instead, store as cents in an integer!

Decimal numbers: :decimal{precision,scale} where precision is the maximum digits, and scale is the digits after the decimal point. Check your database docs for details, as PostgreSQL seems to create a double precision float regardless of any options instead of a numeric BSD type.

Rails doesn’t support the BCD datatype from the PostgreSQL numeric datatype. If you need that, try COBOL On Cogs :-)

Time types: :date, :datetime, :timestamp

Special types: :json, :hstore, :inet, :uuid, etc. Check your database docs for details.

Belongs to Association: :references or :belongs_to with the foreign table name.

Foreign Keys and Associations

With a command-line type of references or a migration code definition of t.references :model\_name, the migration will create a foreign key automatically. The column will be named appropriately with \_id appended. A foreign key (with in implicit index) is created as well. The datatype of the column is the default primary-key type, but can be overridden with the :type option. Options include:

If you are creating a model scaffold, the belongs_to line will be added to the model code.

Column Options

Once your migration is generated, you can use these options for more control:

Timestamps

The t.timestamps line is generated automatically which adds the created_at and updated_at columns.

Additional Indexes

A helper is provided to easily create indexes within the create_table context:

t.index :col
t.index [:col1, :col2,...], unique: true

This is eassier than then default (here with descending order and partial index (PostgreSQL).

add_index :widget, [:col1, :col2],
          unique: true,
          order:  { col2: "DESC NULLS LAST" },
          where:  'col1 = true'

Constraints

We can use SQL commands to add PostgreSQL constraints. When we do this we need to have up and down methods in the migration, or use a reversible block on the change method.

def change
  reversible do |dir|
    dir.up do
      execute %Q(ALTER TABLE widgets ADD CONSTRAINT name ...)
    end
    dir.down do
      execute %Q(ALTER TABLE widgets DROP CONSTRAINT name)
    end
  end
end

Sample constraints:

ALTER TABLE name ...

ADD CONSTRAINT name CHECK (price > 0)
ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses
   (address) MATCH FULL;
DROP CONSTRAINT name
ALTER COLUMN name SET/DROP NOT NULL
ALTER COLUMN name SET/DROP DEFAULT expression

Other Migration commands

These are the Migration DSL commands you can use in your migration files. To be reversible, these command may require settings such as types that would be needed only to undo the change.

Tables

create_table :table do |t| ... end
create_join_table :table1, :table2
change_table :table do |t| ... end
rename_table
drop_join_table
drop_table :table do |t| ... end

Table blocks allow you to run changes to the table instead of full column migration commands.

change_table :table do |t|
  t.rename :prt_number, :part_number
  t.remove :colname
  t.string :new_column
  t.column :another_column, :string, limit:100
  t.index  columns
end

Columns

add_column :table, :col, :type, options
rename_column :table, :from, :to
remove_column :table, :col, :type
add_timestamps
remove_timestamps
change_column_null :table, :col, true||false, default_for_existing_nulls
change_column_default :table, :col, from:a to:b

Foreign Keys and References

add_reference :table, ref_name
remove_reference
add_foreign_key :fromtable, :totable, column: :xxx, name: :xxx, on_delete: :cascade
remove_foreign_key :fromtable, :totable, column: :xxx, name: :xxx

Indexes

add_index  :table, columns, options
remove_index  :table, columns, options

SQL Statements

These are not reversible, so use a reversible block if you expect to run a db:rollback.

execute "ALTER TABLE ..."
execute "UPDATE widgets SET newcolumn = 0"