Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
postgres-only transactional migration script
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.