en-US/about_PSql_Deploy_Migrations.help.txt
|
about_PSql_Deploy_Migrations ============================ SHORT DESCRIPTION A PSql.Deploy migration is a T-SQL script that evolves the schema of a target database from its current state to some new state. The Get-SqlMigrations cmdlet lists migrations, and the Invoke-SqlMigrations cmdlet applies outstanding migrations to one or more target databases, with configurable parallelism to speed deployment in many-database scenarios. PSql.Deploy supports SQL Server, Azure SQL Database, and compatible databases. LONG DESCRIPTION :: Source Directory Structure :: PSql.Deploy expects migrations to have a specific filesystem layout. src\ The source directory: a set of migrations and │ seeds for one database design. The name can vary. │ ├─ Migrations\ Migrations. Required only if there are any migrations. │ │ │ ├─ 0001\ One migration. The name can vary. │ │ │ │ │ ├─ _Main.sql Top-level script for the migration. It can │ │ │ include other files with the :r directive. │ │ ├─ FileA.sql Example file included by _Main.sql. │ │ ├─ FileB.sql Example file included by _Main.sql. │ │ └─ ... More files, subdirectories, etc. │ │ │ └─ ... More migrations. │ ├─ Seeds\ Seeds. Required only if there are any seeds. │ └─ ... See about_PSql_Deploy_Seeds for details. │ └─ ... PSql.Deploy does not care about other files or directories present in the source directory. Given an arbitrary source directory, PSql.Deploy expects to find migrations in a Migrations subdirectory. Within the Migrations directory, each subdirectory containing a _Main.sql file is an individual migration. The name of the subdirectory determines the name of the migration. A migration name must be a valid directory name but is otherwise unrestricted. The _Main.sql file is the entry point for the migration. The file is a T-SQL script with a few extensions to support modularity. :: Migration Script SQLCMD Support :: PSql.Deploy migration scripts support a limited set of SQLCMD directives. Migrations may use these directives to organize code into multiple files, to separate SQL batches, and to replace hard-coded values with variables. GO Ends the current SQL batch and begins a new one. $(<name>) Replaced by the value of the SQLCMD variable <name>. :r <file> Replaced by the contents of the specified file. The path is relative to the current PowerShell directory ($PWD or Get-Location), matching SQLCMD.EXE behavior. To enable inclusion relative to the migration, PSql.Deploy predefines the SQLCMD variable 'Path' as the full path of the directory containing the _Main.sql file. Thus the directive :r $(Path)\Foo.sql includes the file Foo.sql from the same directory as _Main.sql. The path may be enclosed within double quotes. This is mandatory if the path contains spaces, tabs, or double quotes. To include a double quote in the path, use two double quotes. Examples: :r "$(Path)\My Script.sql" :r "$(Path)/My ""Special"" Script.sql" Note that Windows does not support tabs or double quotes in file names. :setvar <name> <value> Sets the SQLCMD variable <name> to the spacified <value>. The value may be enclosed within double quotes. This is mandatory if the value contains spaces, tabs, or double quotes. To include a double quote in the value, use two double quotes. :setvar MyVar MyValue :setvar MyVar "My Value" :setvar MyVar "My ""Special"" Value" :: How Migrations Work :: PSql.Deploy's Invoke-SqlMigrations cmdlet applies (runs) each migration exactly once per target database. After applying a migration, the cmdlet records the migration's state in a table in the target database. Future invocations of the cmdlet for the same target database will read the table and skip any migrations that have already been applied. Thus the Invoke-SqlMigrations cmdlet is idempotent: invoked multiple times against the same target database, the cmdlet applies only new migrations and does nothing to a fully-migrated database. The Get-SqlMigrations cmdlet lists the migrations found in a source directory or applied to a target database. When multiple migrations are present in the source directory but not yet applied to a target database, The Invoke-SqlMigrations cmdlet applies them in a specific order: from least to greatest by case-insensitive ordinal comparison of the migrations' names. It is therefore important to choose a migration naming scheme carefully to fit the development workflow of the database. See below for examples of migration naming schemes. Once applied to a target database, a migration should be considered immutable. The Invoke-SqlMigrations cmdlet computes a hash of all .sql files within the migration directory and subdirectories, recursively. The cmdlet stores this hash as part of the migration state in the target database. If a subsequent invocation finds that the computed hash is different from the stored hash, the cmdlet detects that the migration has been modified after application. The cmdlet then reports a validation error and aborts without applying any migrations. To resolve the error, either revert the changes to the migration, or update the hash value stored in all target databases (not recommended). Migrations named _Begin and _End, if present, are 'pseudo-migrations' that receive special treatment. Invoke-SqlMigrations executes _Begin before any other migrations and _End after all other migrations. Furthermore, the cmdlet does not record the state of these migrations in the target database. They execute every time the cmdlet runs against a target database and has at least one other migration to apply. _Begin is useful for connection setup tasks such as setting the transaction isolation level. _End is a good place to perform cleanup that must occur after every migration session. PSql.Deploy supports 'up', or forward, migrations only. It does not support 'down' migrations that would revert the database to some prior schema. Many migration activities are inherently destructive -- dropping a table, for example. The work to make those activities reversible is often much more complex than the work of the activities themselves, all to enable a feature that in practice is seldom used. Thus PSql.Deploy takes the position that 'down' migrations are not worth the effort. If a database schema must revert to a prior state, then author a new migration that makes the necessary changes, or restore the database from a backup. Use automated testing to find migration problems early, before they reach production. :: Phases :: To facilitate zero- and low-downtime deployments, PSql.Deploy splits deployment into three logical phases: Pre, Core, and Post. Each phase represents a different moment within a deployment process. Each SQL statement within a migration executes in exactly one of these phases. One migration can contain statements for multiple phases. The purposes of the phases are as follows: Pre This phase occurs before deployment of the workloads that use the target database, perhaps while previously deployed workloads are still running. Migration statements in this phase should make the database compatible with the upcoming workload deployment but should retain compatibility with any existing workloads. Core This phase occurs at some arbitrary point between the Pre and Post phases. PSql.Deploy interprets statements within this phase as requiring downtime, explicitly breaking a zero-downtime deployment schenario. Migration statements in this phase should make the database compatible with newly deployed workloads and need not retain compatibility with any previously deployed workloads. This phase is also suitable for deployment scenarios where downtime is not a concern. Post This phase occurs after deployment of the workloads that use the target database, while those workloads are running. Migration statements in this phase should finalize or clean up after the changes made in earlier phases while retaining compatibility with the deployed workloads. :: Dependencies :: PSql.Deploy enables one migration to declare a dependency on another migration. The Invoke-SqlMigrations cmdlet handles a dependency in one of two ways. If the required migration has been applied fully to a target database, then the dependency is satisfied already, and the cmdlet applies the depending migration normally to that target database. If neither the required migration nor the requiring migration has been applied yet to a target database, then the cmdlet moves Pre and Post statements into the Core phase as required to satisfy the dependency and preserve the guarantees that the cmdlet makes about the order of migration statements. Note that this makes dependency resolution incompatible with zero-downtime deployment scenarios, because Core entails downtime. :: Ordering Guarantees :: The Invoke-SqlMigrations cmdlet makes the following guarantees about the order of migration statement execution: - A migration's Pre-phase statements are guaranteed to execute after all previous migrations' Pre-phase statements. - A migration's Core-phase statements are guaranteed to execute after all previous migrations' Core-phase statements. - A migration's Post-phase statements are guaranteed to execute after all previous migrations' Post-phase statements. - If migration B depends on migration A, then A's Post-phase statements are guaranteed to execute before B's Pre-phase statements. Example 1: No dependencies These migrations: 1 │ Pre Core Post 2 │ Pre Core Post 3 │ Pre Core Post 4 │ Pre Core Post 5 │ Pre Core Post Yield this order operations: │ Pre │ Core │ Post │ ══╪════════════════════╪══════════════════════════╪══════════════════════════╡ 1 │ Pre │ Core │ Post │ 2 │ Pre │ Core │ Post │ 3 │ Pre │ Core │ Post │ 4 │ Pre │ Core │ Post │ 5 │ Pre │ Core │ Post │ Time──> Example 2: One dependency These migrations: 1 │ Pre Core Post 2 │ Pre Core Post <──╮ 3 │ Pre Core Post │ 4 │ Pre Core Post ───╯ Migration 4 depends on Migration 2 5 │ Pre Core Post Yield this order operations: │ Pre │ Core │ Post │ ══╪═════════════╪════════════════════════════════════════════╪════════════════╡ 1 │ Pre │ Core Post │ │ 2 │ Pre │ Core ^^^^ Post │ │ 3 │ Pre │ Core ^^^^ │ Post │ 4 │ │ Pre Core │ Post │ 5 │ │ ^^^ Pre Core │ Post │ Time──> ^^^ :: Magic Comments :: A migration may contain 'magic comments', which are special comments that the Invoke-SqlMigrations cmdlet interprets as directives. The cmdlet supports the following magic comments: --# PRE Causes subsequent SQL text to execute in the Pre phase. --# CORE Causes subsequent SQL text to execute in the Core phase. --# POST Causes subsequent SQL text to execute in the Post phase. --# REQUIRES: <migration-name> ... Declares a dependency on one or more migrations. :: Example Migration Naming Schemes :: Here are some example migration naming schemes: - Numeric, zero-padded: 0001, 0002, 0003, ... Pros: simple Cons: requires coordination if multiple people author migrations concurrently; requires new scheme if next number exceeds planned width Best for: solo projects or teams with a single migration author where the versioning scheme is unknown or subject to change - Version, zero-padded: v01.00.00, v01.00.01, v01.01.00, v02.00.00, ... Pros: descriptive; aligned with versioning Cons: requires coordination if multiple people author migrations concurrently; tricky if a version number exceeds planned width Best for: solo projects or teams with a single migration author where the versioning scheme is known and unlikely to change - Date and description: 2025-08-15-Initial, 2025-08-18-AddUsersTable, ... Pros: descriptive; requires less coordination for multiple authors Cons: need to keep migration name up-to-date during authoring Best for: teams with multiple migration authors - Date and work item number: 2025-08-15-00042, 2025-08-18-00123, ... Pros: requires less coordination for multiple authors Cons: need to keep migration name up-to-date during authoring; requires knowing work item numbers Best for: teams with multiple migration authors where it is useful to associate migrations with work items, pull requests, etc. :: Squashes :: A long-lived project with frequent schema changes will accumulate a large number of migrations over time. For such projects, it is sometimes useful to delete old migrations and instead use a create script that creates a database already migrated to that point in time. This is known as a 'squash'. Create scripts are beyond the scope of PSql.Deploy, but PSql.Deploy supports squashes by not caring about completed migrations that are no longer present in the source directory. Nevertheless, there are two rules that must be oserved when performing a squash: 1. The oldest unsquashed migration must be same age or older than the oldest backup that is likely to be restored. 2. The oldest unsquashed migration must be same age or older than the create script. Failure to observe these rules can cause restore-and-migrate or create-and-migrate operations to miss migrations, causing later migrations to fail or leaving the schema in an unexpected state. :: See Also :: - about_PSql_Deploy - Get-SqlMigrations - Invoke-SqlMigrations - https://github.com/sharpjs/PSql.Deploy |