Pensieri di un lunatico minore

Rails schema migrations

Rails has developed a bit more maturity now, and has the concept of “schema migrations” in the ActiveRecord component. I decided that rather than even try and “jump-start” my system with a normal initial schema, that I’d try and use the migration facilities. Some observations follow.

First, ActiveRecord::Migration does not implicitly wrap it’s DDL in a transaction as far as I can determine. This means that if anything goes wrong, you could leave your schema in an unusable state. For PostgreSQL at least, DDL can exist in a transaction, so I decided to wrap it up in: BEGIN and END so that if something goes wrong, things get rolled back. Not doing this by default is a bad idea¹.

Next, data types supported by ActiveRecord are woefully simplistic, at least for automatic schema creation. Your choices are:

• :string becomes a VARCHAR
• :text becomes a TEXT
• :integer becomes a INTEGER (32-bit signed)
• :float becomes a FLOAT (likely single-precision)
• :datetime becomes a TIMESTAMP
• :timestamp becomes a TIMESTAMP
• :time becomes a TIME
• :date becomes a DATE
• :binary becomes a BYTEA or BLOG
• :boolean becomes a BOOLEAN

For those of us who depend on our databases not to make huge mistakes, this leaves out the NUMERIC fixed-point representation—for which the recommendation is to multiply by the decimal places and store it as a flat INTEGER, which is little more than a kludge. For me, especially, I use INET for storing IP addresses. The other “options,” such as they are, are to store it either as a string (useless for querying), or as a 32-bit packed number (using inet_ntoa or others), which requires using a NUMERIC column in standard SQL databases.

In initial tests, INET, at least, comes back as a string, and so long as you hand the database a properly formatted string, it will “do the right thing,” but this makes me uncomfortable. The first thing is to wrap it in a custom class, but after that I’m contemplating patching up the ActiveRecord behavior to allow for proper behavior. Regardless, it basically requires that I do my migrations by simply using execute and raw SQL, which defeats a lot of the purpose of the migration system. While platform-neutral SQL might be interesting in some situations, such as widely distributed plug-ins, it is universally deplorable for in-house applications, or those distributed with their database.

Overall, however, the migration system now works reasonably well, but a lot of the magic disappears if you care to work above the least common denominator. This is frustrating, but I understand how it might fit in with some people’s views of the Right Thing.

¹ I suspect this is another latent sloppiness inherited from the MySQL brain-damage. MySQL, at least as late as 4.1, doesn’t really integrate transactions and DDL, and never will outside the InnoDB engine. This means you can’t actually have atomic schema migrations. Bad juju if you ask me.