Advanced Migrations

Most Rails developers use the basic facilities of migrations to create and maintain their database schemas. However, every now and then it’s useful to push migrations just a bit further.

Using native SQL

Migrations give us a database-independent way of maintaining your application’s schema. However, if migrations don’t contain the methods we need, we’ll need to drop down to database-specific code. Rails provides two ways to do this. One is with options arguments to methods like add_column(). The second is the execute() method.

When we use options or execute(), we might well be tying our migration to a specific database engine, since any SQL we provide in these two locations uses our database’s native syntax.

An example of where we might need to use raw SQL is if we’re creating a custom data type inside our database. Postgres, for example, allows us to specify enumerated types. Enumerated types work just fine with Rails, but to create them in migration, we have to use SQL and thus execute(). Suppose we wanted to create an enumerated type for the various payment types we supported in our checkout form (which we created in Check Out!):