The world's simplest database migrations

February 22, 2024
SQL

For a small side project, I was looking into the easiest way to create and keep track of database migrations. That’s when I came across a post from the Clojure forums, where pesterhazy uses a SQL function to manage migrations.

You start with you have a single migrations.sql file in the project directory, and you manage all your migrations in that file. I made some minor tweaks to his script, and my migrations file starts with the following:

-- Create the migration table
create table if not exists migrations (
  key text CONSTRAINT pkey PRIMARY KEY,
  migrated_at timestamp NOT NULL DEFAULT NOW()
);

-- Create the migration function
create or replace function idempotent(migration_name text,code text) returns void as $$
begin
  if exists (select key from migrations where key=migration_name) then
    raise notice 'Migration already applied: %', migration_name;
  else
    raise notice 'Running migration: %', migration_name;
    begin
      execute code;
      insert into migrations (key) VALUES (migration_name);
      exception when others then
      raise warning 'Failed to apply migration: %s', migration_name;
    end;
  end if;
end;
$$ language plpgsql strict;

First, we create a migrations table to keep track of the migrations that have already run. Then it creates an idempotent function that runs supplied migration or skips it if it has already run.

To run a migration, you supply the idempotent function with the migration in SQL. As follows:

-- Create the users table
do $do$ begin perform idempotent('V0001__users_table', $$
CREATE TABLE users (
  id        uuid PRIMARY KEY,
  email     text NOT NULL UNIQUE,
  joined_at timestamp DEFAULT NOW(),
  foo       text,
  bar       text
)
$$); end $do$;

It’s delightfully simple.

You can run the migrations with psql:

psql -d database_name -f migrations.sql and you would see:

> psql -d database_name -f resources/migrations.sql
psql:resources/migrations.sql:5: NOTICE:  relation "migrations" already exists, skipping
CREATE TABLE
CREATE FUNCTION
psql:resources/migrations.sql:33: NOTICE:  Migration already applied: V0001__users_table
DO

Or run it when your application starts up because it’s so fast!

Running to think