MigrationOps is a database schema versioning and source control tool designed to bring structure and traceability to SQL changes.

It combines concepts from Entity Framework migrations and SQL source control tools (such as Redgate SQL Source Control), enabling teams to manage database updates through versioned scripts, checksum validation, and automated execution ordering.

• SQL Source Control: Organize your SQL scripts (stored procedures, views, functions, and triggers) in dedicated folders.
• Automatic Checksum Calculation: Inserts unique checksums into your SQL scripts to easily manage changes.
• Migration Management: Easily handle database migrations with a structured approach, using timestamped filenames to ensure proper execution order.
• Git Integration: Git hooks to automate checksum calculation and ensure valid script tags exist.

• Git: Ensure Git is installed on your machine. You can download it from Git for Windows.
• Git Bash: Git Bash should be installed, as the Git hooks are written in shell script format (.sh).

To get started, clone the project repository to your local machine:

git clone <repository-url>

To ensure that our custom Git hooks are correctly set up on your local environment, please follow these steps:

1. Navigate to the root of the repository

2. Execute the GitHook setup script in PowerShell

.\setup_hooks.ps1

3. Verify the Hook Make a change in the repository, stage it, and attempt to commit. The pre-commit hook should automatically run and insert/update the checksum in your SQL files.

The pre-commit hook script uses PowerShell's Get-FileHash cmdlet to calculate SHA-256 checksums. This makes the script fully compatible with Windows environments, including GitHub Desktop and Git Bash.

If you're working in a macOS or Linux environment, you may need to modify the script to use a Unix-compatible command like sha256sum. Here’s a basic example:

#!/bin/sh
# Function to calculate SHA-256 checksum using sha256sum
calculate_checksum() {
    sha256sum "$1" | awk '{ print $1 }'
}

# Rest of the script...

dbconfig.json is used to configure the database connections and migration settings for MigrationOps.

{
  "Databases": {
    "Db1": {
      "ConnectionString": "Server=myServerAddress;Database=db1;User Id=myUsername;Password=myPassword;"
    },
    "Db2": {
      "ConnectionString": "Server=myServerAddress;Database=db2;User Id=myUsername;Password=myPassword;"
    }
  },
  "MigrationSettings": {
    "MigrationDirectory": "Migrations",
    "ScriptDirectory": "Scripts",
    "DefaultDatabase": "Db1"
  }
}

Place your SQL scripts into the appropriate folders:

• StoredProcedures/
• Views/
• Functions/
• Triggers/

Ensure that all scripts are written using the CREATE OR ALTER statement to simplify deployment.

Scripts will be applied in the order determined by their filenames.

Migrations will be executed in order by datetime, then script number.

Example: Migrations/20240805-001-CreateNewTestSchema.sql Migrations/20240805-002-CreateEntityTable.sql Migrations/20240806-001-CreateHappyCustomerTable.sql Migrations/20240806-002-CreateCustomerTestTable.sql

Add your migration scripts to the Migrations folder with a filename format that includes a timestamp and a brief description:

Example: Migrations/20240805-001-CreateEntityTable.sql

When you deploy the project, the migration scripts will be applied to the databases in the Tags comment. Tags should be included as a comment at the top of each SQL script. The githook runs validation logic to ensure the Tags comment is properly added to each sql file.

Example:

-- Tags: db1, staging
CREATE OR ALTER PROCEDURE dbo.MyProcedure
AS
BEGIN
    -- Procedure logic here
END

I welcome contributions! Please fork the repository and submit a pull request for any enhancements or bug fixes.

This project is licensed under the MIT License - see the LICENSE file for details.

For any questions or suggestions, please open an issue or reach out to Cat Fortman.