Script Versions and Types#
Schema Guard gives flexible control over script execution order. Besides sequential migration number or version, each database migration can be split into several stages.
Hence, each script or schema definition file can have version, type and descriptive incoming database changes name. In general, file name format can be described in the following way:
Order of Execution#
On application start, Schema Guard scans the target directory. All found scripts and schema definition files are sorted by version, then by type. After that, Schema Guard analyzes current target database state and history of already applied changes too find the starting point for incoming changes.
Each migration of particular version number can be split into five stages by types. In the next table the file types are listed by execution order, please read the notes below the table.
Script types sorted by the execution order:
|P||SQL||Immutable. Before schema generation scripts.Example: P1__prepare.sql|
|S||YAML||Mutable. Schema generation scripts.Example: S1_0__schema.yaml|
|D||CSV||Mutable. Table data. The schema file must refer to this file in etl/file. Example: ETL|
|V||SQL||Immutable. Post schema generation scripts.Example: V1_0__indexes.sql|
|R||SQL||Mutable. Repeatable scripts, run when changes are detected.Example: R1_0_1__random.sql|
|A||SQL||Mutable. After migration scripts. Allowed names are after.sql and after_each.sql. Run after all scripts of the same version or at the end of all migrations.|
Results of the After Migration Scripts are not tracked in the history table and do not affect the success of the migration execution.
Immutable files are run only once. If the already applied immutable file is changed, the next migration attempt will fail with an error.
Mutable files run if they were not applied before or if rdbm detects changes in such file.
CREATE OR REPLACE FUNCTION make_lower_email() returns trigger as $$ begin new.email := lower(new.email); return new; end; $$ language plpgsql; CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
database: - schema: schemaName: public tables: - table: tableName: emails columns: - column: name: id type: UUID defaultValue: uuid_generate_v4() constraint: primaryKey: true nullable: false - column: name: email type: VARCHAR(256) constraint: nullable: false triggers: - trigger: name: ensure_lower_email_trg event: before update or insert when: for each row proc: make_lower_email()
CREATE INDEX emails_email_gin ON Emails USING gin (email gin_trgm_ops);
CREATE OR REPLACE FUNCTION random_between(low INT, high INT) RETURNS INT AS $$ BEGIN RETURN floor(random()* (high-low + 1) + low); END; $$ language plpgsql;
Script version can be a one number like 1,2,3..., but in general it has the following syntax:
The version number parts are separated by the underscore symbol and as it follows from the description above: after the major version number comes minor version number and then the patch version number.
If some version number parts are missing, rdbm assumes that they equal zero.