Skip to content

Instantly share code, notes, and snippets.

@haggen
Created April 25, 2019 14:31
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save haggen/ff4dda6cffa4ee295c6a27d05b73332b to your computer and use it in GitHub Desktop.
Pure SQL migration procedure for PostgreSQL.
-- Migration script in pure SQL.
-- This script is ran at server boot-up.
--
\set on_error_stop true
-- Migration temporary sequence.
--
create temporary sequence migration_steps;
-- Migrations table.
--
create table if not exists migrations (
id integer primary key,
created_at timestamptz not null default now()
);
-- Migration procedure.
--
drop procedure migrate;
create procedure migrate(migration text) as $$
declare
step numeric := nextval('migration_steps');
begin
if exists(select 1 from migrations where id = step) then
raise notice 'migrations: skipping step %', step;
else
raise notice 'migrations: running step %', step;
execute migration;
insert into migrations (id) values (step);
end if;
end;
$$ language plpgsql;
-- Migration steps.
--
-- 1
call migrate($$
create table sample (
id serial primary key,
created_at timestamptz not null default now(),
name text
);
$$);
-- 2
call migrate($$
alter table sample rename column name to title;
$$);
-- 3
-- ...
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment