postgres-only transactional migration script

migrate.sql

begin;

create table if not exists migration (id bigint primary key, at timestamptz not null default clock_timestamp());


drop function if exists migrate;
create function migrate() returns bigint language plpgsql as $$
declare current_migration bigint;
begin
    select coalesce((select id from migration order by id desc limit 1), 0) into current_migration;
    raise notice 'current_migration "%"', current_migration;

    case when current_migration < 1 then
        create table test (id int);
        insert into migration (id) values (1);
        else null;
    end case;

    case when current_migration < 2 then
        alter table test add col1 text;
        insert into migration (id) values (2);
        else null;
    end case;

    case when current_migration < 3 then
        alter table test add col2 text;
        insert into migration (id) values (3);
        else null;
    end case;

    case when current_migration < 4 then
        alter table test add col3 text;
        insert into migration (id) values (4);
        else null;
    end case;

    case when current_migration < 5 then
        alter table test add col4 text;
        insert into migration (id) values (5);
        else null;
    end case;

    select coalesce((select id from migration order by id desc limit 1), 0) into current_migration;
    return current_migration;
end;
$$;

select migrate() as current_migration;

commit;