Migrations

In the SQL\Migrations folder, you will find the SQL scripts used to maintain the database. It is broken into the following subdirectories:

Tables - Contains the scripts to create and maintain the tables in the database. Every change to a table should be done in the same file to maintain the history of the table and a single source of truth for changes to the table.
Procedures - Contains the scripts to create/alter the stored procedures in the database.
Views - Contains the scripts to create/alter the views in the database.

Running Migrations

You should not need to run the migrations manually. The API project has a MigrationsRunner that will automatically run the migrations when the application starts. See the section Basic File Format below for information on how the runner detects which files to run and the order to run them.

Idempotent Scripts

Although the file headers (described below) should guarantee that scripts do not get executed more than once, it is still important to ensure that the scripts are idempotent. Each script section should check to ensure that it needs to execute before it actually does. See the Create Employee Table example in the Basic File Format.

Basic File Format

Each file can be broken into sections, each section delimited by a comment block with a format similar to this:

--::20240604160100 Some Description

The --:: is required, followed by a timestamp in the format yyyyMMddHHmmss and then a description of the change. The timestamp must be exactly 14 characters long; the migrations runner will error if it is not.

IMPORTANT: The timestamp determines the order the scripts will run and is also the key by which the migration history is tracked. DO NOT change the timestamp once it has been committed.

A file may, and should, contain multiple sections. For example, if a script creates a table, then adds a column a month later, it should look something like this:

--::20240101000000 Create the Employee table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Employee')
BEGIN
    CREATE TABLE Employee (
        EmployeeID INT PRIMARY KEY,
        FirstName NVARCHAR(50),
        LastName NVARCHAR(50)
    );
END;

--::20240201000000 Add MiddleName to Employee
IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'MiddleName' AND object_id = OBJECT_ID('Employee'))
BEGIN
    ALTER TABLE Employee ADD MiddleName NVARCHAR(50);
END;

This allows all changes related to a single object to be contained in a single file, making it easier to track changes.

Managing Dependencies

If a script depends on objects/changes in another script, the timestamp portion of the comment block MUST be after the timestamp of the other script. For example, if a script adds a column to a table, and another script adds a view that uses that column, the view script must have a timestamp later than the column script.

Employee.sql:

--::20240101000000 Create the Employee table
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Employee')
BEGIN
END

vwEmployee.sql:

--::20240101000001 Create the vwEmployee view
IF NOT EXISTS (SELECT * FROM sys.views WHERE name = 'vwEmployee')
BEGIN
END

In this case, the vwEmployee will require the Employee table to exist, so it must have a timestamp later than the Employee table.

NOTE: It is easy to create these timestamps correctly if you always use the date/time of when you are actually setting up file. Most editors (including VS Code and Rider), allow templates that will automatically put the current date/time in the proper format. Reach out to Michael Koss if you need help setting this up.

Managing Procedures and Views

Procedures and views are handled a bit differently than tables. When a change is made to a procedure, a new section comment should be added between the last section comment and the CREATE OR ALTER statement.

Altering `vwEmployee` example

NOTE: Stored procedures are handled the same way as views.

Assuming this migration currently exists: