Plip's Weblog

Migrations - they're lovely

We're not talking about swallows (African, European or otherwise) but Database Migrations that ship with Ruby on Rails.

There's a cool bit of functionality which lets you stores database schema and incrimental changes in code files as well as a framework which offers the ability to roll the database forwards or backwards through versions.

The code is really simple, here's some code which creates a Users table: -

class AddUsersTable < ActiveRecord::Migration
  def self.up
    create_table :users, :id => false do |table|
      table.column :UserId, :integer
      table.column :Username, :string
      table.column :Password, :string
      table.column :EmailAddress, :string
    end
  end
  def self.down
    drop_table :users
  end
end

So, what does this do? Well this code is stored in a file inside my migrations folder called "001_add_users_table.rb". Take note of the "001" that indicates that this file is the first inside my Migration, it's the first step to creating my schema.

Notice there's a self.down towards the bottom of the file, this is executed when the database schema is rolled backwards to version 0 (remember that file is version 1 so version 0 is the database before version 1 ran, thus we need to drop the table).

I can tell the framework to change my database to a particular version by executing the following command line: -

rake migration VERSION=1

The framework stores the current database "version number" inside the database in a special single column single row table.

So, when I run that command line the following SQL get's executed against my SQL Server.

set implicit_transactions on 
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
SELECT COLUMN_NAME as ColName, COLUMN_DEFAULT as DefaultValue, 
	DATA_TYPE as ColType, IS_NULLABLE As IsNullable, 
	COL_LENGTH('schema_info', COLUMN_NAME) as Length, 
	COLUMNPROPERTY(OBJECT_ID('schema_info'), COLUMN_NAME, 'IsIdentity') as IsIdentity, 
	NUMERIC_SCALE as Scale 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'schema_info'
go
SELECT TOP 1 version FROM schema_info
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
SELECT TOP 1 version FROM schema_info
go
CREATE TABLE users ([UserId] int, 
			[Username] varchar(255), 
			[Password] varchar(255), 
			[EmailAddress] varchar(255)) 
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
UPDATE schema_info SET version = 1
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
SELECT @@ROWCOUNT AS AffectedRows
go
SELECT TOP 1 * FROM schema_info
go
SELECT table_name from information_schema.tables WHERE table_type = 'BASE TABLE'
go
SELECT COLUMN_NAME as ColName, COLUMN_DEFAULT as DefaultValue, 
	DATA_TYPE as ColType, IS_NULLABLE As IsNullable, 
	COL_LENGTH('users', COLUMN_NAME) as Length, 
	COLUMNPROPERTY(OBJECT_ID('users'), COLUMN_NAME, 'IsIdentity') as IsIdentity, 
	NUMERIC_SCALE as Scale 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'users'
go
EXEC sp_helpindex users
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go

Now, when I run the reverse command and roll the system back I run this: -

rake migration VERSION=0

Which in turn pumps the following SQL into the database: -

set implicit_transactions on 
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
SELECT COLUMN_NAME as ColName, COLUMN_DEFAULT as DefaultValue, 
	DATA_TYPE as ColType, IS_NULLABLE As IsNullable, 
	COL_LENGTH('schema_info', COLUMN_NAME) as Length, 
	COLUMNPROPERTY(OBJECT_ID('schema_info'), COLUMN_NAME, 'IsIdentity') as IsIdentity, 
	NUMERIC_SCALE as Scale 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'schema_info'
go
SELECT TOP 1 version FROM schema_info
go
SELECT TOP 1 version FROM schema_info
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
CREATE TABLE schema_info (version int)
go
select * from CREATE TABLE schema_info (version int)
go
SELECT TOP 1 version FROM schema_info
go
SELECT TOP 1 version FROM schema_info
go
SELECT TOP 1 version FROM schema_info
go
SELECT TOP 1 version FROM schema_info
go
SELECT TOP 1 version FROM schema_info
go
DROP TABLE users
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
UPDATE schema_info SET version = 0
go
IF @@TRANCOUNT > 0 COMMIT TRAN
go
SELECT @@ROWCOUNT AS AffectedRows
go
SELECT TOP 1 * FROM schema_info
go
SELECT table_name from information_schema.tables WHERE table_type = 'BASE TABLE'
go
IF @@TRANCOUNT > 0 ROLLBACK TRAN
go

Pretty cool isn't it? :-)

Well - why does this matter? Just think source control and you'll have the same.