about_Rivet_migrations

Explains how to write Rivet migrations.

Description

Variables

You can use the following special variables in your Push-Migration and Pop-Migration functions:

$DBScriptRoot: the path to the script root directory of the database you're migrating.

Tables

The following functions are available for managing tables:

Add-Table
Remove-Table

The Add-(DataType)Column's Column parameter is a script block that should return columns as column objects, e.g.,

Add-Table Customer {
    Int 'ID' -Identity
    NVarChar 'Name' -NotNull
    Int 'ZipCode' -NotNull
}

Columns

Update-Table
Remove-Column

Code Objects

The following functions will remove objects of the specified type. Use the IfExists flag to only delete the object if it exists.

Remove-StoredProcedure
Remove-UserDefinedFunction
Remove-View

Executing Code Object Scripts

Stored procedures, user-defined functions, views and other database objects are usually stored in external script files, which are executed against the database to create/update the object. In some cases, it can take a long time to run all your code object scripts, so it can be useful to included updated/new scripts in a migration. Rivet assumes scripts for object types are stored under $DBScriptRoot in directories with the following names:

Stored Procedures
User-Defined Functions
Views

Under these directories, scripts should be stored per-object in files named after the object. For example, if you have stored procedure InsertIntoFoo, it should be saved in $DBScriptRoot\Stored Procedures\InsertIntoFoo.sql. If your script is in a schema other than dbo, the file's name should be prefixed with the schema. For example, if your stored procedure InsertIntoFoo is in the bar schema, it should be saved in $DBScriptRoot\Stored Procedures\bar.InsertIntoFoo.sql.

Use these functions to run the script for a code object:

Set-StoredProcedure -Name <string> [-Schema <string>]

Set-UserDefinedFunction -Name <string> [-Schema <string>]

Set-View -Name <string> [-Schema <string>]

To execute an arbitrary SQL script, use Invoke-SqlScript:

Invoke-SqlScript -Path <string>

If the Path argument is a relative path, the full path to the SQL script is resolved from the directory of the migration script. For example, if your database's migrations directory is C:\Projects\Rivet\Databases\RivetTest\Migrations, this path:

Invoke-SqlScript -Path ..\Miscellaneous\CreateDesertedIsland.sql

would resolve to C:\Projects\Rivet\Databases\RivetTest\Miscellaneous\CreatedDesertedIsland.sql.

Raw SQL

When none of the functions above will do the trick, use the Invoke-Query function to execute raw SQL:

Invoke-Query -Query <string>

You can use a PowerShell here string for longer queries and so you don't have to escape quotes:

Invoke-Query -Query @'
    -- SQL goes here.  You don't have to escape single quotes.
'@  # '@ must be the first two characters on the line to close the string.