In Evolve, each sql script is called a migration. Every time you need to change your database schema or data, you have to create a new one. There are two different types:
At startup Evolve will collect all migrations located in Locations, searching recursively for files with a specific file name structure. All migrations starting by a V are then sorted by version in ascending order, regardless their initial directory. Each version of a migration must be unique. If not, the validation phase fails.
The same goes for repeatable migrations, starting by a R, except ordered by description.
Then, each time a migration is applied, its name and checksum is saved into the Evolve metadata table. This table is checked every time Evolve runs, to see if the migration script must be executed.
The versioned migration is the default migration.
It is composed of a version, an informative description and a checksum. For example, the first numbers that make up the version of your migration may be the release version of your application followed by a counter. Versioned migration must follow this file name structure: V1_3_1_1__Create_table.sql.
A versioned migration is executed only once.
The version must be unique.
They are applied in the order of their versions.
The checksum is stored in the Evolve metadata table and used to detect inadvertent changes.
The repeatable migration is applied whenever its content changes. It does not have a version and is executed sorted by name after all pending versioned migrations. You can use them to manage in a single file, database objects you can create or update, such as views and stored procedures, or to insert seed data that can evolve and grow over time. Repeatable migrations must follow this file name structure: R__Create_views.sql:
A repeatable migration is executed each time its content (checksum) changes.
They are applied after versioned migrations.
They are applied in the order of their description.
Evolve has 4 execution commands to interact with your database:
migrate: applies the migrations. It’s the main command.
erase: erases the database schema(s) if Evolve has created it or has found it empty (cf. Metadata table). Otherwise Evolve will not do anything. This command is intended to be use in development to recreate a database from scratch.
repair: updates checksums of previously applied migrations with those of the currently available migration scripts.
info: displays the details and status information about all the migrations.
By default, each migration is executed in a separate database transaction. Thus each script will either succeed or fail completely and Evolve will stop on the first error. If your database supports DDL statements within a transaction, failed migrations will always be rolled back, otherwise you will have to manually fix your database state. And if you want to run a specific migration script outside of a transaction, add – evolve-tx-off at the beginning of the file.
Alternatively, you can also wrap all your migrations in a single transaction using the option TransactionMode with the value CommitAll to commit a group of script if they all succeed, or rollback them all if one fails.
Placeholders are strings enclosed by ${} that will be replaced in sql migrations before their execution. They make it possible to get dynamic migrations depending on the environment.
evolve.Placeholders = new Dictionary<string, string>
{
["${database}"] = "my_db",
["${schema1}"] = "my_schema"
}
-p database:my_db -p schema1:my_schema
-p database:my_db -p schema1:my_schema
SELECT * FROM ${database}.${schema1}.TABLE_1; -- SELECT * FROM my_db.my_schema.TABLE_1;
During its initial execution, Evolve creates a table with a default name of changelog, to keep track of all migrations (applied or failed) and to store their checksums. You can change its default name and schema using these 2 options: MetadataTableName and MetadataTableSchema.
| id | type | version | description | name | checksum | installed_by | installed_on | success |
|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 0 | Empty schema found: dbo. | dbo | sa | 22/02/2019 20:45:15 | True | |
| 2 | 0 | 1.0.0.0 | create table user | V1_0_0_0__create_table_user.sql | D4AAF08FBF70D3B327A9A3… | sa | 22/02/2019 20:45:15 | True |
| 3 | 0 | 1.0.0.1 | create triggers | V1_0_0_1__create_triggers.sql | A4AA367C92B99C56E88132… | sa | 22/02/2019 20:45:16 | True |
| 4 | 4 | Create views | R__Create_views.sql | Z6AA3T7C92B549C56E8813T… | sa | 22/02/2019 20:45:18 | True |
The type column is used to identify which type of metadata is stored:
Command = "erase".Command = "erase".StartVersion).