Deploy database migration to server

,

Hi,

I would like to use Travis to run database schema migration on the server (staging, production) whenever needed as part of deployment. I have actually a few staging environments on AWS. My application is written in PHP and I have already installed lib to DB migration (Phinx).

Basically, I use Travis to run unit tests. When the tests are successful then the next step which I would like to do is deploy code to server e.g. staging or production. It depends if I merged my branch into test or master etc. However, before I pushed the code to the server, I would need to tun DB migration first.

Steps:

  1. Run unit tests.
  2. Run DB migration (so new tables will exist on the DB before I deploy the code on the server).
  3. Deploy code.
  4. Happy days :slight_smile:

I don’t want to run this task separately from my machine as it’s possible to run into a race condition with an automatically run deployment. I don’t want clients to see 500 HTTP code on my web app.

Any idea how can I do it? Should I create e.g. bash script on the servers and then just made Travis do fire that script e.g. by ssh? Or there are any other ways to do it?

Thanks!

If you want zero downtime, you’ll need to deploy the new version of the service (including the database) in parallel with the old one, then shut down the old one.

Since this process is not well-established across web technologies, check if the deployment provider or the underlying deployment technology you’re using supports a built-in DB migration step (and the version switch if you want to do that automatically, too).
If not, you’ll need to do the migration as a custom step – at before_deploy: or after_deploy:, or as part of the deployment script if you’re using the script provider.

My app runs on AWS. Any idea how can I achieve what you did explain above? ^

AFAICS, for AWS CodeDeploy, there is a way to do migration upon deployment via a Lambda in a CodePipeline:


I don’t see any automatic way of switching. The stumbling block seems to be that whenever you have 2 databases and one of them is live, they are bound to get desynchronized as the live one is getting newer data and the one being migrated isn’t – so you’ll have to migrate that new data by hand after the switch anyway.

https://www.reddit.com/r/aws/comments/86qgaq/zerodowntime_rds_migration/ reports to have done a zero-downtime switch with a MySQL replication setup – but I can’t see from the description how the replicated commands would adapt to the new schema :neutral_face: