2

Build servers are generally detached from the VPC running the instance. Be it Cloud Build on GCP, or utilising one of the many CI tools out there (CircleCI, Codeship etc), thus running DB schema updates is particularly challenging.

So, it makes me wonder.... When's the best place to run database schema migrations?

From my perspective, there are four opportunities to automatically run schema migrations or seeds within a CD pipeline:

  1. Within the build phase
  2. On instance startup
  3. Via a warm-up script (synchronously or asynchronously)
  4. Via an endpoint, either automatically or manually called post deployment

The primary issue with option 1 is security. With Google Cloud Sql/Google Cloud Build, it's been possible for me to run (with much struggle), schema migrations/seeds via a build step and a SQL proxy. To be honest, it was a total ball-ache to set up...but it works.

My latest project is utilising MongoDb, for which I've connected in migrate-mongo if I ever need to move some data around/seed some data. Unfortunately there is no such SQL proxy to securely connect MongoDb (atlas) to Cloud Build (or any other CI tools) as it doesn't run in the instance's VPC. Thus, it's a dead-end in my eyes.

I'm therefore warming (no pun intended) to the warm-up script concept.

With App Engine, the warm-up script is called prior to traffic being served, and on the host which would already have access via the VPC. The warmup script is meant to be used for opening up database connections to speed up connectivity, but assuming there are no outstanding migrations, it'd be doing exactly that - a very light-weight select statement.

Can anyone think of any issues with this approach?

Option 4 is also suitable (it's essentially the same thing). There may be a bit more protection required on these endpoints though - especially if a "down" migration script exists(!)

prout.james
  • 99
  • 1
  • 8
  • Hi @prout.james I would like to check with you, how are you planning to implement the startup scripts on App Engine? App Engine doesn't work with startup scripts, even though you can send code in warmup requests. Could you please clarify how you are planning to do it? – gso_gabriel Aug 25 '20 at 13:04
  • Hi @gso_gabriel, a startup script could be added through code, either within the Start NPM script, or within the app itself. I’ve discounted this option now due to poor startup times, especially with serverless technology; cold-starts are already pretty slow. – prout.james Aug 27 '20 at 13:57

1 Answers1

1

It's hard to answer you because it's an opinion based question!

Here my thoughts about your propositions

  1. It's the best solution for me. Of course you have to take care to only add field and not to delete or remove existing schema field. Like this, you can update your schema during the Build phase, then deploy. The new deployment will take the new schema and the obsolete field will no longer be used. On the next schema update, you will be able to delete these obsolete field and clean your schema.
  2. This solution will decrease your cold start performance. It's not a suitable solution
  3. Same remark as before, in addition to be sticky to App Engine infrastructure and way of working.
  4. No real advantage compare to the solution 1.

About security, Cloud Build will be able to work with worker pool soon. Still in alpha but I expect in the next month an alpha release of it.

guillaume blaquiere
  • 66,369
  • 2
  • 47
  • 76
  • 1
    Thanks for answering - your opinion is very much appreciated! I agree that the logical position would be within the build phase, but without the Build server existing within the private network, the inability to connect is a major barrier. The worker pool solution sounds like the missing piece of this puzzle however, excellent future insight. Option 4's primary benefit is the ability to run from within the private network, thus being able to connect to the DB. It could still be called via a cloud-build step, requiring a 200 response prior to promoting traffic to the instance. – prout.james Aug 27 '20 at 14:07
  • Agree for the step 4. However take care to not create a bigbang in your schema update to be sure that a rollback is possible without (or with a minimal) service unavailability! – guillaume blaquiere Aug 27 '20 at 19:49