Raw SQL Migrations

Sometimes a more custom migration is required, than is possible using Expression Migrations. In these cases Raw SQL Migrations are available, that are able to run provided SQL script. The using property can be used to reference other depot entities, just like in views.

Migrations that need to be run are determined using a combination of target, name and dialect properties - migrations that didn't exist in the previous dataset version with those values will be run after other deployment steps.

• type Required - Must be 'rawSql'
• target Required - name of the schema that will be modified
• name Required - name of the migration.
• dialect Required - must be the exact same as the dataset location type, otherwise it will not run. Allowed values: snowflake or aurora
• sql Required - the sql that should run. It can include other references to other depot entities defined in the using or Migration Context values.
• using Required - map of depot entities referenced in the sql. The target must also be included, if it's referenced.

Usage

Example migration:

{
    type: 'rawSql',
    target: 'schema.Table1',
    dialect: 'snowflake',
    name: 'schema.Table1.ChangeName',
    sql: `
        UPDATE @{SCHEMA.TABLE1} SET
            ID = '{{MIGRATION_ID}}',
            TIME = '{{MIGRATION_TIME}}',
            NAME = '{{MIGRATION_NAME}}'
        WHERE NAME IN (
            SELECT NAME FROM @{SCHEMA.TABLE2}
        )
    `
    using: {
        'SCHEMA.TABLE1': 'schema.Table1',
        'SCHEMA.TABLE2': 'schema.Table2'
    }
}

will result in this SQL being run:

        UPDATE "ENV_DATABASE"."SCHEMA"."TABLE_1" SET
            ID = 'mig1',
            TIME = '2024-03-25T10:07:12.795Z',
            NAME = 'migration mig1 at 2024-03-25T10:07:12.795Z'
        WHERE NAME IN (
            SELECT NAME FROM "ENV_DATABASE"."SCHEMA"."TABLE_2"
        )

Usage caveats

Because target, name and dialect are used to determine if the migration should be run, changes to any of these properties might result in the migration being run, even if it has run in the past, so they shouldn't be changed.

The SQL script should perform it's own cleanup, as depot doesn't perform any additional actions besides running the script.

Migration Context

Migration context provides additional migration values that can be referenced in the generated sql - the values are:

• MIGRATION_ID
• MIGRATION_TIME
• MIGRATION_NAME

Example of usage in the sql:

UPDATE @{SCHEMA.TABLE1} SET
    ID = '{{MIGRATION_ID}}',
    TIME = '{{MIGRATION_TIME}}',
    NAME = '{{MIGRATION_NAME}}'

PostgreSQL Functions with DECLARE Blocks

Since Depot 9.7.3, Raw SQL Migrations support PostgreSQL functions that use DECLARE blocks. This allows you to define local variables and use more complex procedural logic within your migration scripts.

Example

{
    type: 'rawSql',
    target: 'schema.Table1',
    dialect: 'aurora',
    name: 'schema.Table1.UpdateWithDeclare',
    sql: `
        CREATE OR REPLACE FUNCTION update_records()
        RETURNS void AS $$
        DECLARE
            row_count INTEGER;
            update_timestamp TIMESTAMP;
        BEGIN
            update_timestamp := NOW();
            
            UPDATE @{SCHEMA.TABLE1}
            SET updated_at = update_timestamp
            WHERE status = 'pending';
            
            GET DIAGNOSTICS row_count = ROW_COUNT;
            RAISE NOTICE 'Updated % rows at %', row_count, update_timestamp;
        END;
        $$ LANGUAGE plpgsql;
        
        SELECT update_records();
        DROP FUNCTION update_records();
    `,
    using: {
        'SCHEMA.TABLE1': 'schema.Table1'
    }
}

This feature enables you to write more sophisticated migration scripts that require temporary variables, conditional logic, or iterative processing within PostgreSQL and Aurora databases.