graphile-migrate with GitLab CI (community edition) "post-merge-master" validate, commit and push back to master

.gitlab-ci.yml

stages:
  - pre-build

migrate-test:
  image: node:12
  stage: pre-build
  only:
    - master
  allow_failure: false
  before_script:
    - git remote set-url origin https://gitlab-bot:${GITLAB_BOT_PASS}@${CI_SERVER_HOST}/${CI_PROJECT_PATH_SLUG}.git
    - git config --global user.email 'gitlab-bot@yourdomain.com'
    - git config --global user.name 'gitlab-bot'
  script:
    - cd test/migrations
    - yarn install
    - npx graphile-migrate migrate
    - ./validate-master-on-mr.sh

.gmrc

{
  "connectionString": "postgres://user:pass@server/migration_validator_primary",
  "shadowConnectionString": "postgres://user:pass@server/migration_validator_shadow",
  "rootConnectionString": "postgres://user:pass@server/template1",
  "migrationsFolder": "../../migrations"
}

package.json

{
  "private": true,
  "dependencies": {
    "graphile-migrate": "^0.1.0"
  }
}

validate-master-on-mr.sh

#!/usr/bin/env bash

COMMIT_FILES=(`ls ../../migrations/committed/*.sql`)
COMMIT_FILES_LENGTH=${#COMMIT_FILES[@]}

function debug () {
  echo "validator: $1"
}

echo ""
debug "COMMIT_FILES_LENGTH: $COMMIT_FILES_LENGTH"

CURRENT_FILES=(`ls ../../migrations/current/*.sql`)
CURRENT_FILES_LENGTH=${#CURRENT_FILES[@]}

echo ""
debug "CURRENT_FILES_LENGTH: $CURRENT_FILES_LENGTH"

echo ""
if [[ $CURRENT_FILES_LENGTH -eq 0 ]]
then
  debug "nothing to migrate, all is well and dandy"
  exit 0
fi

debug "npx graphile-migrate commit"
npx graphile-migrate commit

AFTER_COMMIT_FILES=(`ls ../../migrations/committed/*.sql`)
AFTER_COMMIT_FILES_LENGTH=${#AFTER_COMMIT_FILES[@]}

if [[ $COMMIT_FILES_LENGTH -eq $AFTER_COMMIT_FILES_LENGTH ]]
then
  debug "no new migrations created, something went wrong"
  exit 1
fi

debug "git add migrations/committed/*.sql"
git add ../../migrations/committed/*.sql

debug "git remove migrations/current/*.sql"
git rm ../../migrations/current/*.sql

CURRENT_GIT_COMMIT=`git rev-parse --short HEAD`
debug "git commit -m '[skip ci] automatic migrations test and commit ($CURRENT_GIT_COMMIT)'"
git commit -m "[skip ci] automatic migrations test and commit ($CURRENT_GIT_COMMIT)"

debug "git push --follow-tags origin HEAD:master"
git push --follow-tags origin HEAD:master

Based on Discord discussion at http://discord.gg/graphile

Master should always have an empty current.sql and on merge to master you should immediately do a graphile-migrate commit on master to make this stay the same. Rebasing feature branches on master should thus be straightforward (since the changes will only be to current.sql which will always have the same base content - that single comment).
Benjie

Based in this, and that we have a GitLab CE edition without "merge trains" or fancy stuff. I created the following files to automatically test, commit and merge the migration files to our master repository.

Folder structure:

.gitlab-ci.yml
test/migrations/package.json
test/migrations/.gmrc
test/migrations/validate-master-on-mr.sh

Install steps:

1. Create a migration_validator_primary + migration_validator_shadow databases to a server what your GitLab can access.
2. Set the GITLAB_BOT_PASS or GITLAB_BOT_TOKEN in your GitLab variables (set it also to masked)
3. Add above files to your repository and edit the .gmrc settings

Testing:

1. Create MR from master
2. Merge MR to master
3. If it works, good job :thumbs_up: