Using Powershell script in Github Actions to execute T-SQL script files against a database

1 - ExecuteUpdateScripts.ps1

# Description: This script will execute all SQL files in the current directory against a specified SQL Server.
# git mv to ./Archive scripts once they've successfully executed through the environment pipeline.

param (
    [Parameter(Mandatory = $true)]
    [string]$ConnectionString,
    [Parameter(Mandatory = $false)]
    [bool]$VerboseOutput = $false
)

# Get the current directory of this script
$CurrentDir = Split-Path $PSCommandPath
Write-Host("Executing scripts in $CurrentDir")

# Get all SQL files in the current directory
$SqlFiles = Get-ChildItem -Path $CurrentDir -File -Filter *.sql

# Loop through and execute each SQL file
for ($i = 0; $i -lt $SqlFiles.Count; $i++) {
    $SqlFile = $SqlFiles[$i]
    $SqlFilePath = Join-Path $CurrentDir $SqlFile.Name
    try {
        # https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps
        # -Verbose is optional, but it will print the SQL Server messages to the console
        # -IncludeSqlUserErrors is undocumented, but it is required to get the actual error message from SQL Server
        # -ErrorAction 'Stop' is a global cmdlet flag required to stop the script execution if there is an error
        $params = @{
            ConnectionString = $ConnectionString
            InputFile = $SqlFilePath
            IncludeSqlUserErrors = $true
            ErrorAction = 'Stop'
        }

        # Add Verbose flag if requested
        if ($VerboseOutput) {
            $params.Add("Verbose", $true)
        }

        # Invoke-Sqlcmd will throw an error if the script fails
        $OutputResult = Invoke-Sqlcmd @params
        Write-Debug($OutputResult) # this isn't used but we need to swallow the output to prevent it from being displayed
        Write-Host($SqlFile.Name + " Executed Successfully.")
    }
    catch {
        Write-Host("Error executing $($SqlFile.Name): $_")
        exit 1 # Exit with error code for GitHub actions to stop
    }
}

Write-Host("All scripts executed successfully.")
exit 0 # Exit with success code for GitHub actions to continue

2 - update-action.yml

# Example step to add to Github action build and deploy process

  update-database:
    runs-on: windows-latest

    steps:
      - uses: actions/checkout@v4
        with:
          sparse-checkout: 'SourceCode/Database/UpdateScripts'

      - name: Run Update Script
        shell: pwsh
        run: ./SourceCode/Database/UpdateScripts/ExecuteUpdateScripts.ps1 -ConnectionString "${{ secrets.DATABASE_CONNECTION_STRING }}"

3 - 2024.01.01-1.TemplateUpdateScript.sql

-- Example Script with tracking and rollback on failure
-- Ideally you should have one script per deployment so if the script fails you can rollback and stop deployment
DECLARE @ScriptCode nvarchar(100) ='2024.01.01-1.TemplateUpdateScript' --This should be the same as script file name (without .sql extension)

PRINT '-------------------------------------------------------------------------------'
PRINT @ScriptCode
PRINT '-------------------------------------------------------------------------------'

-- Check if we've already run this script
IF(EXISTS(SELECT * FROM dbo.UpdateScript WHERE ScriptCode=@ScriptCode))
  BEGIN
    PRINT N'Script already executed'
    SELECT 'Result' = 'Script already executed' -- Output is required for the script to be considered successful
  END
ELSE
  BEGIN TRY
    BEGIN TRANSACTION

    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    
----------SCRIPT GOES BELOW THIS LINE---------

-- Write your scripts here.
-- This script does nothing.

----------SCRIPT GOES ABOVE THIS LINE---------

    -- We are successful so insert the script code into the UpdateScript table
    INSERT INTO dbo.UpdateScript(ScriptCode, UpdateDate) VALUES(@ScriptCode, GETDATE())
    -- Commit the Transaction to the database
    COMMIT
    PRINT N'Script executed'
    SELECT 'Result' = 'Script executed' -- Output is required for the script to be considered successful
  END TRY
  BEGIN CATCH
    ROLLBACK
    PRINT ERROR_MESSAGE()
	  DECLARE @ErrorMessage NVARCHAR(MAX) = CONCAT(N'Rolling back script - ', ERROR_MESSAGE())
    RAISERROR(@ErrorMessage,11,1);
  END CATCH
PRINT '-------------------------------------------------------------------------------'
PRINT ''