Pilgrim: An SQL Migration Tool Worth The Pilgrimage

Usage

$ pilgrim --url 'mariadb://localhost:3306/database' --up
$ pilgrim --url 'driver://user:pass@host:port/database?arg1=val1&arg2=val2' --dir ./migrations --up
$ pilgrim --url 'postgres://localhost:5432/database' --down '2024-09-27/1_CreateTableABC.sql'
$ pilgrim --driver 'driver' --username 'user' --password 'pass' --host 'host' --port 'port' --segments 'database' --args 'arg1=val1&arg2=val2'
$ pilgrim --validate_migration_order --validate_latest

Configuration

The order of precedence for configured values is as follows:

1. Values provided in explicit cli arguments ( --username, --password, --host, --port, etc. )
2. Values provided in cli --url
3. Values provided in PILGRIM_* environment variables

• Values in PILGRIM_URL are of lowest precedence and are overridden by their explicit PILGRIM_* equivalents

By default, pilgrim will search for a .pilgrim file in the directory it has been run in and will use the values within to replace values that have not been provided in a higher precedence form.

CLI Arguments

Argument	Meaning
--url	The database url
--driver	The database driver ( must be one of mysql, mssql, postgres, mariadb, oracle, or sqlite )
--username	The username to use
--password	The password to use
--host	The host/ip of the db server
--port	The port of the db server
--segments	The segments ( typically the db name )
--arguments	The arguments for the db server
--dir	The directory of the db migrations
--down	The migration to downgrade to ( conflicts with --up ). Defaults to false
--up	The migration to upgrade to ( conflicts with --down ). Defaults to false
--script	The migration script to upgrade/downgrade to. Can also be latest. ( Defaults to latest )
--schema	The migration table schema. Must exist prior to running migrations. Default value depends on database driver ( default schema )
--table	The migration table name. Default value is "pilgrim_migrations"
--disable_checksum_validation	Disable checksum validation when running database migration
--strict_ordering	Enforce strict ordering, pilgrim will run a validation of migration execution order before running the migrations
--validate_migration_order	(Special) Runs a validation of the migration directory to ensure there is no uncertainty in migration order of execution.
--validate_latest	(Special) Validate that the provided database contains all migrations in the migration directory
--validate_checksums	(Special) Validate that all current checksums of migration files match the ones stored in the migration table

Conflicts between --down and --up

If both --down and --up are provided, pilgrim will return an error and will not migrate the database. The --script name provided must adhere to the migration naming convention and contain the folder it is located in.

Environment Variables

Variable	Meaning	Example
PILGRIM_URL	The database url	PILGRIM_URL=driver://user:pass@host:port/database?arg1=val1&arg2=val2
PILGRIM_DRIVER	The driver for the database	PILGRIM_DRIVER=postgres
PILGRIM_USERNAME	The username to use	PILGRIM_USERNAME=user
PILGRIM_PASSWORD	The password to use	PILGRIM_PASSWORD=pass
PILGRIM_HOST	The host/ip of the db server	PILGRIM_HOST=localhost
PILGRIM_PORT	The port of the db server	PILGRIM_PORT=9999
PILGRIM_SEGMENTS	The segments ( typically the db name )	PILGRIM_SEGMENTS=database; PILGRIM_SEGMENTS=db/seg1
PILGRIM_ARGUMENTS	The arguments for the db server	PILGRIM_ARGUMENTS="arg1=val1&arg2=val2"
PILGRIM_DIRECTORY	The directory of the db migrations	PILGRIM_DIRECTORY=./migrations
PILGRIM_SCHEMA	The migration table schema. Must exist prior to running migrations.	PILGRIM_SCHEMA="public"
PILGRIM_TABLE	The migration table.	PILGRIM_TABLE="pilgrim_migrations"

Migration Naming Convention & Folder Structure

pilgrim requires that migration files be named uniquely, and can be ordered. To ensure this, migrations must be sorted by date, and then further within each date-named directory, the contained migration files must begin with a number. The numbers need not be unique ( you can have multiple scripts starting with 1_, for example ), but the name of the migration does.

Example migration directory:

migrations
├─2024-09-28
│ ├─1_CreateTableA.sql
│ ├─2_CreateViewA.sql
│ └─3_DropConstraintA.sql
├─2024-10-01
│ ├─1_CreateTableB.sql
│ └─1_CreateTableC.sql
└─2024-10-12
  └─...

Migration Definition

Creating a pilgrim migration involves creating a regular SQL file, with some mandatory comments contained within:

-- PILGRIM::UP

CREATE TABLE ...

-- PILGRIM::DOWN

DROP TABLE ...

Everything between -- PILGRIM::UP and -- PILGRIM::DOWN is run during the --up phase of migration.

Everything after --PILGRIM::DOWN to the end of the file is run during the --down phase of migration.

If a file is missing these comments, the migration will not be run, and the file will be ignored.

Development

See Development Documentation