Purpose

The Database migration tool was developed by Starschema to allow access control of SQL databases through Azure Pipelines. It reads migrations from an Azure Repository, and applies new migrations to the database. It also reverts migrations if they are removed from the repo (or repo is rolled back to a previous commit), therefore keeping the database schema in sync with the SQL schema files in the repo.

Migrations directory/file structure

In order to allow this functionality, the DB scripts that affect the databases schema must follow the following guidelines:

• No DB script should ever be changed once published to the remote git repo!
• Changes should only be done to the databases schema by creating a new migration. Migrations are folders that contain 2 files: Up.sql and Down.sql. These files contain the database scripts that are executed when the migration is applied/reverted.
• The order of the migrations is determined by the alphabetical order of the folders that contain the Up/Down scripts. Please ensure that each newly created migration is the last of the folders alphabetically. The easy way to ensure this, is to include the current datetime as a prefix for the migration, example: 202001011200_my_first_migration. Example directory structure:

-- Migration root directory
----> 202001011200_my_first_migration
-------> Up.sql
-------> Down.sql
----> 202003011200_add_user_table
-------> Up.sql
-------> Down.sql
----> 202003111200_modify_user_table
-------> Up.sql
-------> Down.sql

• The Down script must contain the reverse of the up script. If a table is created in the Up script, it must be droppedin the Down script as the Down script runs when themigration needs to be reverted. Example of Up.sql:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);
ALTER TABLE sales.quotations 
ADD description VARCHAR (255) NOT NULL;

Example of Down.sql:

ALTER TABLE sales.quotations DROP COLUMN description;
DROP TABLE Persons;

Database side

The task needs to be configured to connect to the SQL Server to perform the migrations and keep track of applied migrations (it does so using a special migrations table), so Table create permission is required minimum, but more permissions might be required based on the scripts that are in the migrations.