Migratonator - Database Migration Tool

Migrate - toe - nater (EM gatt err for short)

CLI Tool for managing MSSQL Database schemas, objects and data.

Built to leverage the excellent DbUp library, Migratonator adds support for managing variables, migration files, running scripts for views and stored procedures, adds support for SQL CLR Assemblies and the use of macros in scripts.

Concepts

The tool is opinionated such that:

• a specific directory structure is assumed,
• the order in which changes are applied is deterministic,
• changes which affect the data, such as to types, tables and indexes, must be made using migrations,
• changes to all other objects, such as views, functions and procedures must be scripted in an idempotent way,
• data seeds must be scripted in an idempotent way,
• a Schema.sql file is generated to represent the most current version of the database schema.

Directory Structure

The directory structure and base files are as follows.

Project Root Directory
  ∟ Assemblies
    - Assembly.sql
    - ...
  ∟ Functions
    - Function.sql
    - ...
  ∟ Jobs
    - Job.sql
    - ...
  ∟ Migrations
    - 0001-Migration1.sql
    - 0002-Migration2.sql
    - ...
    - 9999-Migration999.sql
  ∟ Procedures
    - Procedure.sql
    - ...
  ∟ Seeds
    - Seed.sql
    - ...
  ∟ Views
    - View.sql
    - ...
  - Schema.sql
  - Variables.json
  - Macros.json

Execution

The order in which schema, object and data changes are be made in the following order.

1. Migrations
2. Assemblies
3. Functions
4. Procedures
5. Views
6. Jobs
7. Seeds

Migrations

Scripts within the Migrations directory will only be executed once, whereas all other scripts will be executed each time and therefore must be implemented to be idempotent.

Furthermore, scripts within the Migrations directory must only be used to manage objects which hold or where there are hard dependencies.

Examples include:

• creating types,
• creating tables,
• modifying columns,
• modifying constraints, indexes,
• and dropping existing objects.

By following this convention, dependencies between scripts can be managed deterministically.

Schema.sql Script

The Schema.sql file is generated by the tool each time a command which effects a change is run.

It is used to recreate the database for the current version, and should be checked into source code control after migrations and scripts have been applied in the development environment.

The updated Schema.sql file should be included together with the corresponding migrations as part of a pull request, so that the full extent of the change can understood by the reviewer.

Variables

Scripts can contain variables which get replaced when the migrations are prepared.

Use the format $VariableName$ to denote a variable in the scripts and provide the corresponding named entries in the Variables.json file.

E.g. Given a script which creates a user role:

CREATE LOGIN [$DomainName$\$UserName$]
FROM WINDOWS
;

GO

And the variables JSON file contents:

[
  {
    "Name": "DomainName",
    "Value": "SOMEDOMAIN"
  },
  {
    "Name": "UserName",
    "Value": "Bob"
  }
]

The rendered script would be as follows:

CREATE LOGIN [SOMEDOMAIN\Bob]
FROM WINDOWS
;

GO

SQL CLR Assemblies

To be completed

Macros

Macros are provided via the Macros.json file. Macros are inserted into scripts using the -- %Macro:Name[Parameter1|Parameter2|...|ParameterN]% syntax.

E.g. Psuedo T-SQL code for a hypothetical OBJECT type.

-- %Macro:DropObjectIfExist[ObjectName]%

CREATE OBJECT ObjectName AS
...

When migrations or scripts are applied, the macros are replaced with the respective templated content before execution.

Workflow

The are three common use-cases for the tool, each within a different environment, and where each process is subtly different.

Development

The development workflow commences by creating a local isolated database. Next, migrations are written to define types, tables, constraints, indexes, and other necessary objects, which are then applied to the database. Subsequently, additional scripts are developed to create functions, stored procedures and views, etc, and these changes are also applied to the database.

The application code is then tested against the modified database, and an iterative process begins. During this stage, the developer works to enhance or fix the migrations and scripts, making use of the capability to undo and redo changes as required, until the application functions as desired.

Once satisfied with the changes, the developer commits them to the source code control system and proceeds to create a pull request. The purpose of the pull request is to have the changes reviewed and tested by Continuous Integration process before it is merged into the main codebase.

The tool utilised in this workflow enables the developer to easily tear down the local environment, facilitating the creation of a fresh environment for working from a known state, or for working on a different branch of the code to allow for further testing and development on that branch.

Having the ability to tear down and recreate the local environment, developers can adhere to a more structured and deterministic approach to developing applications. It ensures that the development process is better organized, follows a repeatable process, which leads to more reliable and predictable outcomes.

The tool provides the following commands for this workflow.

• mgatr init - Initialise the project directory structure and configuration for the tool to work.
• mgatr db create - Create a new empty database.
• mgatr schema load - Load the base version of schema into the database.
• mgatr migrate create <Name> - Create a new migration, with a name which describes the purpose of the migration, such as "Add X Column to Table Y", or "Add Unique Index to Table X", etc.
• mgatr migrate up - Apply the migration to the database¹.
• mgatr migrate scripts - Apply script changes to the database¹.
• mgatr schema dump - Generates the Schema.sql file.
• mgatr db drop - Deletes the database, to enable the developer to start again if needed.

Optionally, if the database needs to contain a working set of the data, for developers to work on the application code and to run integration tests, the scripts in the Seeds directory can be applied to the database using the mgatr seeds command.

Continuous Integration

Once the pull request has been submitted, the Continuous Integration process automatically picks up the changes and runs a suite of automated tests to validate the integrity and compatibility of the change with the existing codebase. This helps ensure seamless integration of changes into the project while maintaining overall stability and functionality.

This process consists of two checks. The first step involves verifying that the migrations and scripts work against the existing database version (as in production), since this step will be performed during deployment, and secondly to check that the application works as intended using this new version of the database.

Verifing that the migrations and scripts work involves the following steps.

• Check out the version of the Schema.sql file as at the start² of the pull request.
• Create and configure a database according to the requirements of the CI environment.
• mgatr schema load --confirm - Load the base version of schema into the database.
• mgatr migrate up --confirm - Apply the migrations, with automatic confirmation.
• mgatr migrate scripts --confirm - Apply the script changes, with automatic confirmation.
• Assert that the generated Schema.sql file is the same as that of the pull request³.
• Optionally, run mgatr seeds to seed the database with data required for the execution the applications integration tests.
• Drop the database to free up resources according to the requirements of the CI environment.

Deployment

After pull requests have been merged, and a release has been prepared, the deployment process which runs in the respective deployed environments, such as developer, staging and production, uses the tool to apply the changes to the running databases.

In a deployed environment, the database is typically managed using a DevOps process according to the requirements of the deployed environment. E.g. Databases in a production environment will be configured and managed in a different way than in a Staging environment.

This process involves the following steps.

• mgatr migrate up --confirm - Apply the migrations, with automatic confirmation.
• mgatr migrate scripts --confirm - Apply the script changes, with automatic confirmation.

Pre-requisites

• DotNet Core
• DbUp Library
• System.CommandLine Library
• Microsoft.SqlServer.SqlManagementObjects Library

Usage

See commands for the commandline reference.

Hacking

Building

Build the tool using the dotnet command line tool.

dotnet build

Testing

Test the tool using the dotnet command line tool.

dotnet test

Running

Run the tool with the --help flag to see the available options.

dotnet run -- <command-line-arguments>

Replace <command-line-arguments> with the desired arguments. E.g. --help to view the help.

License

MIT License Copyright (c) Chris Stefano

Footnotes

1. This command automatically generates a revised Scheme.sql script. ↩ ↩²

2. The git merge-base command can be used to determine the branch point commit. ↩

3. Helps to enforce that the developer followed the correct flow. ↩