Содержание статьи

Структура скрипта миграции
Таблица истории миграций
Добавление not null столбца
Изменение типа столбца
Добавление индекса
Удаление индекса
Добавление внешнего ключа в таблицу
Добавление родительской таблицы с созданием not null ключа в дочерней таблице
Изменение первичного ключа
Добавление вьюхи

При промышленной эксплуатации клиент-серверных систем остро встает вопрос как обновить базу данных до новой структуры. Уже много лет для этого широко используется подход последовательных миграций, который был популяризирован платформой Ruby on Rails. Миграции представляют собой sql-скрипты, содержащие выражения DML для изменения структуры БД, а также логику, обеспечивающую безопасное применение миграции. Есть множество способов автоматической генерации миграций, в этой статье будет описан подход для ручного создания таких скриптов для баз данных под управлением Microsoft Sql Server.

Структура скрипта миграции

Все миграции содержат sql-скрипт вида

if условие  применимости миграции
begin
    операторы изменения БД
    вставка строки в таблицу истории миграций
    print 'Миграция n успешно применена'
end
else begin
    print 'Миграция n не может быть применена'
end

Файлу миграции дается имя вида nnnn_НазваниеМиграции.sql, где nnnn – порядковый номер миграции, НазваниеМиграции ее осмысленное имя. Хорошим правилом является вместе со скриптом миграции создавать скрипт отмены миграции, чтобы вернуть БД в состояние «до обновления».

Таблица истории миграций

Если при разработке БД не была предусмотрена такая таблица, то ее надо создать первой миграцией. Она хранит историю примененных миграций, вставка строк делается в скрипте миграции после успешного выполенения. Если требуется перенос базы данных на новый сервер и она создастся sql-скриптом «с нуля», то этот скрипт должен содержать вставку всей истории миграций, чтобы она была одинаковой в БД, созданной «с нуля», и в БД, которая апгрейдилась миграциями.

Сверяя содержимое истории с каталогом актуальных скриптов миграций можно быстро выбрать и применить отсутствующие обновления или наоборот, откатить БД на несколько версий назад.
Итак, первая миграция – создание таблицы миграций, которая также иллюстрирует добавление в БД новой таблицы:
0001_CreateMigrationsTable.sql

-- проверка отсутствия таблицы Migrations
if not exists (select 1
                from sysobjects
                where id = object_id('Migrations') and type = 'U')
begin
    create table Migrations (
        Number int not null,
        Name nvarchar(max) not null,
        RunDate datetime not null default (getdate()),
        constraint PK_MIGRATIONS primary key (Number))

    insert into Migrations (Number, Name) values (1, '0001_CreateMigrationsTable')
    
    print 'Миграция 1 успешно применена'
end
else begin
    print 'Миграция 1 не может быть применена'
end

Далее рассмотрим на примерах последовательные миграции базы с таблицей MyTable и одним столбцом Id типа int, который является первичным ключом.

create table MyTable (
	Id int not null
	constraint Pk_MyTable primary key (Id)
)

Добавление not null столбца

Проблема может возникнуть, если попытаться добавить not null столбец к не пустой таблице. Для обхода этого момента использует временный констреинт для задания значения по-умолчанию.
0002_AddNotNullColumn.sql

-- проверка отсутствия столбца MyTable.MyColumn
if not exists (select 1
                from sys.columns
                where object_id = object_id('MyTable') and name = 'MyColumn')
begin
    alter table MyTable
    add MyColumn varchar(32) not null 
    constraint tmp_constraint default('foo') -- временное ограничение tmp_constraint позволяет добавить в таблицу с данными not null столбец
    
    -- после добавления столбца удаляем временное ограничение
    alter table MyTable
    drop constraint tmp_constraint

    insert into Migrations(Number, Name) values(2, '0002_AddNotNullColumn')
    
    print 'Миграция 2 успешно применена'
end
else begin
    print 'Миграция 2 не может быть применена'
end

Откат миграции:
0002_AddNotNullColumn_revert.sql

-- проверка существования столбца MyTable.MyColumn
if exists (select 1
                from sys.columns
                where object_id = object_id('MyTable') and name = 'MyColumn')
begin
    alter table MyTable drop column MyColumn

    delete from Migrations where Number = 2

    print 'Миграция 2 успешно отменена'
end
else begin
    print 'Миграция 2 не может быть отменена'
end

Изменение типа столбца

Следующая миграция меняет тип столбца в таблице.
0003_AlterColumn.sql

-- проверка существования столбца MyTable.MyColumn типа varchar
if exists(select 1
			from information_schema.columns 
			where table_name = 'MyTable' and column_name = 'MyColumn' and data_type = 'varchar')
begin				
	alter table MyTable alter column MyColumn nvarchar(1024) not null
	
	insert into Migrations(Number, Name) values(3, '0003_AlterColumn')
	
	print 'Миграция 3 успешно применена';
end
else begin
	print 'Миграция 3 не может быть применена';
end

Откат:
0003_AlterColumn_revert.sql

-- проверка существования столбца MyTable.MyColumn типа nvarchar
if exists(select 1
			from information_schema.columns 
			where table_name = 'MyTable' and column_name = 'MyColumn' and data_type = 'nvarchar')
begin				
	alter table MyTable alter column MyColumn varchar(32) not null
	
	delete from Migrations where Number = 3
	
	print 'Миграция 3 успешно отменена';
end
else begin
	print 'Миграция 3 не может быть отменена';
end

Добавление индекса

0004_AddIndex.sql

-- проверка отсутствия индекса MyTable.Idx_MyTableMyColumn
if not exists (select 1 
			   from sysindexes 
			   where id = object_id('MyTable') and name = 'Idx_MyTableMyColumn')
begin
	create index Idx_MyTableMyColumn on MyTable (MyColumn asc)

	insert into Migrations(Number, Name) values(4, '0004_AddIndex')
	
	print 'Миграция 4 успешно применена'
end
else begin
	print 'Миграция 4 не может быть применена'
end

Откат:
0004_AddIndex_revert.sql

-- проверка cуществования индекса MyTable.Idx_MyTableMyColumn
if exists (select 1 
			   from sysindexes 
			   where id = object_id('MyTable') and name = 'Idx_MyTableMyColumn')
begin
	drop index Idx_MyTableMyColumn on MyTable

	delete from Migrations where Number = 4
	
	print 'Миграция 4 успешно отменена'
end
else begin
	print 'Миграция 4 не может быть отменена'
end

Удаление индекса

0005_DropIndex.sql

-- проверка существования индекса MyTable.Idx_MyTableMyColumn
if exists (select 1 
			from sys.indexes 
			where name = 'Idx_MyTableMyColumn' and object_id = object_id('MyTable'))
begin
	drop index Idx_MyTableMyColumn on MyTable
	
	insert into Migrations(Number, Name) values(5, '0005_DropIndex')
	
	print 'Миграция 5 успешно применена'
end
else  begin
	print 'Миграция 5 не может быть применена'
end

Откат:
0005_DropIndex_revert.sql

-- проверка отсутствия индекса MyTable.Idx_MyTableMyColumn
if not exists (select 1 
			from sys.indexes 
			where name = 'Idx_MyTableMyColumn' and object_id = object_id('MyTable'))
begin
	create index Idx_MyTableMyColumn on MyTable (MyColumn asc)
	
	delete from Migrations where Number = 5
	
	print 'Миграция 5 успешно отменена'
end
else  begin
	print 'Миграция 5 не может быть отменена'
end

Добавление внешнего ключа в таблицу

Эта миграция добавит в таблицу MyTable столбец OtherTableId, который является внешним ключом к таблице OtherTable.
0006_AddFk.sql

-- проверка отсутствия столбца MyTable.OtherTableId
if not exists (select 1
				from sys.columns
				where object_id = object_id('MyTable') and name = 'OtherTableId')
begin			
	alter table MyTable add OtherTableId int null

	alter table MyTable
	add constraint Fk_OtherTable_Id foreign key (OtherTableId)
	references OtherTable (Id)
	
	insert into Migrations(Number, Name) values(6, '0006_AddFk')
	
	print 'Миграция 6 успешно применена'
end
else begin
	print 'Миграция 6 не может быть применена'
end

Откат:
0006_AddFk_revert.sql

-- проверка существования столбца MyTable.OtherTableId
if exists (select 1
				from sys.columns
				where object_id = object_id('MyTable') and name = 'OtherTableId')
begin
	-- удаляем ограничение
	alter table MyTable
	drop constraint Fk_OtherTable_Id
	
	-- удаляем столбец
	alter table MyTable 
	drop column OtherTableId
	
	delete from Migrations where Number = 6
	
	print 'Миграция 6 успешно отменена'
end
else begin
	print 'Миграция 6 не может быть отменена'
end

Добавление родительской таблицы с созданием not null ключа в дочерней таблице

Одним скриптом миграции можно создать родительскую таблицу и добавить в дочернюю внешний ключ, причем дочерний внешний ключ будет не нулом. Для добавления к существующим строкам ссылки на внешний ключ по-умолчанию воспользуемся временным констреинтом как в примере «Добавление not null столбца».
0007_AddTableWithFk.sql

-- проверка отсутствия таблицы ParentTable
if not exists (select 1
				from sysobjects
				where id = object_id('ParentTable')	and type = 'U')
begin
	create table ParentTable (
		Id 			int identity(0,1) 	not null,
		Name 		nvarchar(50) 		not null	
		constraint Pk_ParentTable primary key(Id))
	
	-- вставка строки с Id = 0, чтобы можно было сделать ссылку из дочерней таблицы
	insert into ParentTable (Name) 
	values ('Default value')
	
	-- временное ограничение позволЯет добавить в дочернюю таблицу с данными внешний ключ на родительскую таблицу
	alter table MyTable
	add ParentTableId int not null
	constraint tmp_constraint default(0)
	
	-- после добавления внешнего ключа удаляем временное ограничение
	alter table MyTable
	drop constraint tmp_constraint
	
	-- создаем ограничение длЯ внешнего ключа
	alter table MyTable
	add constraint Fk_ParentTable_MyTable
	foreign key (ParentTableId) references ParentTable (Id)
	on update no action on delete no action

	insert into Migrations(Number, Name) values(7, '0007_AddTableWithFk')

	print 'Миграция 7 успешно применена'
end
else 
begin
	print 'Миграция 7 не может быть применена'
end

Откат:
0007_AddTableWithFk_revert.sql

-- проверка существования таблицы ParentTable
if exists (select 1
				from sysobjects
				where id = object_id('ParentTable')	and type = 'U')
begin
	-- удаляем ограничение из дочерней таблицы
	alter table MyTable
	drop constraint Fk_ParentTable_MyTable

	-- удаляем столбец внешнего ключа из дочерней таблицы
	alter table MyTable
	drop column ParentTableId
	
	-- удаляем родительскую таблицу
	drop table ParentTable
	
	delete from Migrations where Number = 7

	print 'Миграция 7 успешно отменена'
end
else 
begin
	print 'Миграция 7 не может быть отменена'
end

Изменение первичного ключа

В этом примере рассмотрим модификацию первичного ключа таблицы. Покажем, как из простого ключа Id сделать составной (Id, MyColumn).
0008_AlterPrimaryKey.sql

-- проверка, что столбец MyTable.Id является первичным ключом 
if exists (select 
					1
				from 
					information_schema.constraint_column_usage
				where 
					table_name = 'MyTable'
					and constraint_name = 'Pk_MyTable'
					and column_name = 'Id')
-- проверка, что столбец MyTable.MyColumn не входит в первичный ключ					
and not exists (select 
					1
				from 
					information_schema.constraint_column_usage
				where 
					table_name = 'MyTable'
					and constraint_name = 'Pk_MyTable'
					and column_name = 'MyColumn')
begin
	alter table MyTable
	drop constraint Pk_MyTable
	
	alter table MyTable
	add constraint Pk_MyTable primary key(Id, MyColumn)
	
	insert into Migrations(Number, Name) values(8, '0008_AlterPrimaryKey')
	
	print 'Миграция 8 успешно применена'
end
else begin
	print 'Миграция 8 не может быть применена'
end

Откат:
0008_AlterPrimaryKey_revert.sql

-- проверка, что столбец MyTable.Id является частью первичного ключа 
if exists (select 
					1
				from 
					information_schema.constraint_column_usage
				where 
					table_name = 'MyTable'
					and constraint_name = 'Pk_MyTable'
					and column_name = 'Id')
-- проверка, что столбец MyTable.MyColumn является частью первичного ключа 				
and  exists (select 
					1
				from 
					information_schema.constraint_column_usage
				where 
					table_name = 'MyTable'
					and constraint_name = 'Pk_MyTable'
					and column_name = 'MyColumn')
begin
	alter table MyTable
	drop constraint Pk_MyTable
	
	alter table MyTable
	add constraint Pk_MyTable primary key(Id)
	
	delete from Migrations where Number = 8
	
	print 'Миграция 8 успешно отменена'
end
else begin
	print 'Миграция 8 не может быть отменена'
end

Добавление вьюхи

Оператор create view нельзя выполнить внутри if-else, но это ограничение можно обойти при помощи функции exec().
0009_CreateView.sql

-- проверка отсутствия вьюхи MyView
if not exists(select 1 
				from sys.views 
				where name = 'MyView')
begin
	-- скрипт создания вьюхи 
	exec('create view MyView as
		select 
			MyColumn
		from 
			MyTable')
		
	insert into Migrations(Number, Name) values(9, '0009_CreateView')
	
	print 'Миграция 9 успешно применена'
end
else begin
	print 'Миграция 9 не может быть применена'
end

Откат:
0009_CreateView_revert.sql

-- проверка существования вьюхи MyView
if exists(select 1 
				from sys.views 
				where name = 'MyView')
begin
	drop view MyView
		
	delete from Migrations where Number = 9
	
	print 'Миграция 9 успешно отменена'
end
else begin
	print 'Миграция 9 не может быть отменена'
end

Добавление ограничения

Покажем добавление ограничения на примере ограничения уникальности.
0010_AddConstraint.sql

-- проверка отсутствия ограничения для столбца MyTable.MyColumn
if not exists (select 1
				from 
					information_schema.constraint_column_usage
				where 
					table_name = 'MyTable'
					and constraint_name = 'Uq_MyColumn'
					and column_name = 'MyColumn')
begin
	alter table MyTable add constraint Uq_MyColumn unique (MyColumn)

	insert into Migrations(Number, Name) values(10, '0010_AddConstraint')
	
	print 'Миграция 10 успешно применена'
end
else begin
	print 'Миграция 10 не может быть применена'
end

Откат:
0010_AddConstraint_revert.sql

-- проверка существования ограничения для столбца MyTable.MyColumn
if exists (select 1
				from 
					information_schema.constraint_column_usage
				where 
					table_name = 'MyTable'
					and constraint_name = 'Uq_MyColumn'
					and column_name = 'MyColumn')
begin
	alter table MyTable drop constraint Uq_MyColumn

	delete from Migrations where Number = 10
	
	print 'Миграция 10 успешно отменена'
end
else begin
	print 'Миграция 10 не может быть отменена'
end