relocant

A PostgreSQL migration CLI tool and library.

Synopsis

• The user tells relocant that there is a directory containing migration scripts;
• relocant (advisory-)locks the database;
• relocant figures out which scripts haven't yet been applied;
• relocant sorts unapplied scripts lexicographically, creating a migration plan;
• relocant runs each script in a separate transaction;
• relocant either records a successfully applied migration or aborts the migration plan on a failure;
• relocant (advisory-)unlocks the database.

CLI Workflow

The relocant CLI tool attempts to provide a simple and reliable way to apply migration scripts and track them. A migration script is any file with an sql extension. The longest alpha-numeric prefix of the migration script is its ID. The tool requires all migration scripts to reside inside a single directory.

[!NOTE] The path to the scripts directory can be configured by

• either setting the RELOCANT_SCRIPTS_DIR environment variable,
• or providing the path to the --scripts option

If we run relocant list-unapplied we'll get back a migration plan, listing those scripts that haven't yet been recorded as applied in the database, in the order they will be applied:

% ls ./migration
001-initial-schema.sql
002-description.sql
% relocant list-unapplied --scripts ./migration
001     001-initial-schema      dfde7438
002     002-description         74f8a76e

Now, if we like this plan, we can run relocant apply to actually apply the scripts to our database.

[!IMPORTANT] Each script is run in a separate transaction and recorded as part of it. Unfortunately, this means that there are restrictions on what you can put in a migration script, including these:

1. Using BEGIN, ROLLBACK, and COMMIT will likely break relocant.
2. Running CREATE INDEX CONCURRENTLY will cause the migration script to fail immediately.

[!NOTE] Successfully running relocant commands needing access to the DB would require

• either setting PG* variables up,
• or providing a connection string to the --connection-string option

Here, I'm running relocant inside a nix-shell where the environment has been already set up correctly.

% relocant apply --scripts ./migration
001     001-initial-schema      dfde7438
001     001-initial-schema      dfde7438        2024-10-24 15:04:09 +0000       0.01
002     002-description         74f8a76e
002     002-description         74f8a76e        2024-10-24 15:04:09 +0000       0.01

By running relocant list-applied, we can check that the applied scripts have been recorded correctly. Naturally, the recorded migrations will be sorted in the order of application:

% relocant list-applied
001     001-initial-schema      dfde7438        2024-10-24 15:04:09 +0000       0.01s
002     002-description         74f8a76e        2024-10-24 15:04:09 +0000       0.00s

Once we have another migration script to apply, we put it in the migration directory and re-run relocant apply:

% relocant list-unapplied --scripts ./migration
003     003-fix-typo            a7032e4f
% relocant apply
003     003-fix-typo            a3582319
003     003-fix-typo            a3582319        2024-10-24 15:24:09 +0000       0.00s
% relocant list-applied --scripts ./migration
001     001-initial-schema      dfde7438        2024-10-24 15:04:09 +0000       0.01s
002     002-description         74f8a76e        2024-10-24 15:04:09 +0000       0.00s
003     003-fix-typo            a3582319        2024-10-24 15:24:09 +0000       0.00s

Library docs

See https://supki.github.io/relocant

Acknowledgements

I've been using a (very) similar workflow for a while now and it worked great for me, but I wouldn't have bothered to make it a standalone tool if I haven't stumbled upon pgmigrate by Peter Downs.