Pure SQL migrations

migrations.sql

--------------------------------------------------
--- SETUP
--------------------------------------------------

\set ON_ERROR_STOP true

CREATE TABLE IF NOT EXISTS migrations (
    name CHAR VARYING PRIMARY KEY,
    timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION EXECUTE(TEXT) RETURNS VOID AS $$
    BEGIN EXECUTE $1; END;
$$ LANGUAGE plpgsql STRICT;

CREATE OR REPLACE FUNCTION migration_exists(TEXT) RETURNS bool AS $$
    SELECT EXISTS (SELECT FROM migrations WHERE name = $1);
$$ LANGUAGE SQL STRICT;

CREATE OR REPLACE FUNCTION migrate(TEXT, TEXT) RETURNS VOID AS $$
    SELECT EXECUTE($2) WHERE NOT migration_exists($1);
    INSERT INTO migrations (name) SELECT ($1) WHERE NOT migration_exists($1);
$$ LANGUAGE SQL STRICT;

--------------------------------------------------
--- MIGRATIONS
--------------------------------------------------

SELECT migrate('create_examples', $$
    CREATE TABLE IF NOT EXISTS examples (
        id SERIAL PRIMARY KEY,
        other TEXT
    );
$$);

SELECT migrate('add_foo_to_examples', $$
    ALTER TABLE examples
    ADD COLUMN foo TEXT NOT NULL;
$$);