How to run CLI migrations in a Continous Integration pipeline on a private database on AWS RDS

I am currently using a tool that allows you to apply database migrations only using a CLI (Prisma). My database is in a private network in AWS.

To do it manually, I currently do this:

ssh -i $SSH_PATH_TO__MY_IDENTITY_FILE ec2-user@${BASTION_HOSTNAME} \
    -N -f -L $DB_PORT:${DB_HOSTNAME}:5432 &

A bastion, in AWS parlance, is just a VM that has public access but also can reach private networks. This ssh command creates a tunnel through the bastion so that I can reach the private machine in my local $DB_PORT. Then, I apply the migrations locally but, since the database is listening on a local port, I can reach my production database.

Here is the question: how do I move this to a CI/CD pipeline?

I was thinking about doing this

  1. Use a docker image that has ssh and nodejs installed,

  2. Move the identity file to a env variable in the CI/CD.

  3. Install the migrations tool there.

  4. Create a tunnel as I did above.

  5. Modify the configuration file to point to the production database.

And then, finally, apply the migrations.

I think this could work, but it seems a lot of trouble and I was wondering that maybe there was a better, standard way to approach this. Maybe triggering a Lambda function that runs inside the private network?

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum