Multiple Environments per run

[JackNWeems]

Guess I get to be the one to request this. Being able to specify multiple environment per run as referenced in the missing RoundhousE features document would be a big help to us. We have a complex set of environment spread across our datacenter and Azure supporting multiple customers. Being able to specify multiple environments would let us use the environment names like tags and be able to run scripts against any environment matching that tag. We have a lot of environments that are very similar except for a couple of settings. It would be nice to be able to run a a script across all the UAT environments (we have 1 per customer) without having to name each UAT environment individually in the script. We have actually hit the windows file path limit a few times trying to get every environment named when using RoundhousE.

[hoangthanh28]

Currently, grate only supports a single environment per run, but you can create multiple runs, each per environment.
My initial idea is to use the script (bash, powershell) to trigger multiple runs depending on the input variable. See the example below.
My script

#!/bin/sh
# https://erikbra.github.io/grate/getting-started/
# https://erikbra.github.io/grate/configuration-options/
# https://github.com/erikbra/grate/tree/main/examples/k8s/multitenancy
# databasetype <mariadb | oracle | postgresql | sqlite | sqlserver
for i in $(echo ${Database__Env:-DEV} | tr "," "\n")
do
  echo "Migrating to environment $i"
  ./grate --connectionstring "$Database__ConnectionString" \
        --sqlfilesdirectory /db \
        --environment ${i:-DEV} \
        --databasetype ${Database__Type:-sqlserver} \
        --schema ${Database__Schema:-grate} \
        --silent \
        --version ${VERSION:-1.0.0-beta1} \
        --createdatabase ${Database__CreateDatabase:-true} \
        --transaction ${Database__Transaction:-true} || exit 1
done
echo "Migration database completed."

Grate Dockerfile build file ( I use docker, but you can choose which fit in your use case):

FROM ubuntu:jammy as grate-downloader
WORKDIR /app
ARG GRATE_VERSION=1.6.2
ADD https://github.com/erikbra/grate/releases/download/$GRATE_VERSION/grate-linux-musl-x64-self-contained-$GRATE_VERSION.zip /tmp/grate.zip
RUN apt update && apt install unzip -y
# extract grate to current folder /app
RUN unzip -d ./ /tmp/grate.zip

FROM alpine:3 as grate-migrator
WORKDIR /app
# COPY the sql script here or you can specify the mount volume at runtime
# COPY /sql /db

COPY --chmod=0755 --from=grate-downloader /app .
# migrate script
COPY --chmod=0755 migrate.sh .
# Add globalization support to the OS so .Net can use cultures
RUN apk add icu-libs
ENV DOTNET_SYSTEM_GLOBALIZATION_INVARIANT=false

ENTRYPOINT [ "./migrate.sh" ]

Folder setup:

Run the migration script:

docker run -e Database__ConnectionString="connection_string_here" \
                   -e Database__CreateDatabase=true \
                   -v ./sql:/db \
                   -e Database__Env=DEV,TEST,CUSTOMER_A \
                   --network=host \
                   grate:latest

Grate output:

[hoangthanh28]

The main reason of choosing docker in my case:

• Using grate as init container in k8s
• Able to mount the migration script from storage (by using volume mount with migration script stored in blob storage, file share..etc without any concerns the security leakage)
• Nicely support Argo-CD with GitOps, let's say you want to deploy the new version of your database, create the PR and then merge to the repository. Grab some coffee and everything just works.

[JackNWeems]

This is a very elegant approach but I don't think it will work in our environment. At any given time I've got about 75, environments I'm managing all with data of various ages. The fact I can keep this running at all is a testament to how good roundhouse is., Being able to control the order script run in is a major issue for us. In the example above all the scripts for the Dev environment would always run before any of the scripts for Customer environment. I can see that causing issues for us.

Another lesser issue is the fact that roundhouse is by far the longest step in our deployment process, we have a lot of DBs so it can take up to 10 minutes. I'd like to avoid doubling or tripling that.

It would really be helpful to be able to specify multiple environments on the command line so the script order can be preserved and to avoid the overhead associated with multiple runs.

[hoangthanh28]

In the example above all the scripts for the Dev environment would always run before any of the scripts for Customer environment. I can see that causing issues for us.

You can control the order of environment in the command line mate.

-e Database__Env=DEV,TEST,CUSTOMER_A

with this command, you can specify the order, the script just splits by ',' and runs the environment from left to right.

Another lesser issue is the fact that roundhouse is by far the longest step in our deployment process, we have a lot of DBs so it can take up to 10 minutes. I'd like to avoid doubling or tripling that.

That is true, making the connection to every environment shouldn't be a good option, but by far I can see in my application, that not a deal breaker. My application is 20 years old with a thousand migration scripts but takes little time to migrate with grate. With RoundhousE, our migration takes 30 minutes or more, grate re-written in the way to have a good performance then. Give it a try and you will see how good grate is.

It would really be helpful to be able to specify multiple environments on the command line so the script order can be preserved and to avoid the overhead associated with multiple runs.

That is a good suggestion, let Erik (@erikbra) decide whether he wants grate to support that option. If yes, I'm happy to take the implementation.

[JackNWeems]

Sorry for the late response, I have noticed grate seems to be faster. I'm working through some issues where scripts ran under roundhouse but are erroring in grate so I don't have good numbers yet. Circling back around to the ordering of scripts being able to specify the order of environment doesn't completely solve the issue. Let's say I have these scripts

01.ENV.DEV.SQL
02.ENV.CUST.SQL
03.ENV.DEV.SQL

By specifying the environments I can get dev to run first the cust, so they would run in the order 01,03,02
or I could run cust then dev and get 02,01,03
but there is no way to get the scripts to run in 01,02,03 order.

[erikbra]

I created a PR: #495 with a suggestion, would this solve your needs, @JackNWeems ?

https://github.com/erikbra/grate/pull/495/files#r1561531618

Would this also be able to solve your scenario without running grate multiple times, @hoangthanh28 ?

[hoangthanh28]

I'm ok with this PR, however we need to write more test cases.

01.ENV.DEV.SQL
02.ENV.CUST.SQL
03.ENV.DEV.SQL

This looks not straightforward, especially when the customer script interferes with another environment.
My views:

• The script in the same environment must run sequentially.
• Can run multiple environments.

[erikbra]

What do you mean, that this is not straightforward, @hoangthanh28 ? The scripts will be run in the order they are named. So, if these three scripts are run with environments "DEV, CUST", they will be run in the order listed:

01.ENV.DEV.SQL
02.ENV.CUST.SQL
03.ENV.DEV.SQL

If run for "DEV", this order will be run:
01.ENV.DEV.SQL
03.ENV.DEV.SQL

If run for "CUST", only this will be run:
02.ENV.CUST.SQL

Would you expect it to be differently? If so, how?

[erikbra]

Wasn't your problem, @JackNWeems , that the order would be wrong if you had to run grate twice, once first for environment "DEV", and one for environment "CUST"? But, now, when you can specify both environments at once, won't it be correct? Or did I misunderstand?

[erikbra]

Solved by #495