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:

[Type][Version]__script_name.[yaml|csv|sql]

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.

Script Types#

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:

Type	Format	Description
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.

Script examples:

P1__prepare.sql

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";

S1_0__schema.yaml

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()

V1_0__indexes.sql

CREATE INDEX emails_email_gin ON Emails USING gin (email gin_trgm_ops);

R1_0_1__random.sql

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 Versions#

Script version can be a one number like 1,2,3..., but in general it has the following syntax:

[[Major]_[Minor]_[Patch]]

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.