What is the best migration strategy for GAE CloudSQL

Question

I can't, for the life of me, find documentation on how to handle migrations using Google App Engine and CloudSQL.I am using the Go runtime.

Clearly an application's schema will change and evolve as time goes by, and migrations will need to be run. Currently I run the migrations by hand. This is not scalable.

Is there anyone who has a solution?

I see some specific challenges:

• I can get the version of the current app.yaml deployed version using VersionID. However, how do I check if a migration happened for this version? I would have to keep a version number in a db table and check against that in the init() function?

• However, when you upload a new version of the app, with the new schema GAE will slowly migrate your traffic which means once the first instance of init() in your new version runs, and the migration completes, traffic to the old version will fail in those db transactions.

• I could somewhat mitigate the issue above by versioning my API. But this limits the migration strategies, such as dropping tables, etc.

Lastly, I am disappointed there is no documentation on this as far as I can tell.

Answer 1

I have to agree with Robert, that while this is a challenging situation, it has very little to do with CloudSQL. Pretty much any situation where you need to migrate two versions of app with different SQL schemas will create this situation.

You basically have two options

• Make all your changes at least temporarily backwards compatible. This might involve an intermediate version of your application that can gracefully handle either versions of the schema.

• Version your app/API like you describe, associate a given version with a given schema and use a different database, which might require copying data between the two databases, which might use a lot more storage than you'd like.

The backwards compatible approach is usually the best although you get some ugly code to handle the different schemas. But it can usually be done.

You're asking about how do you get whether a given version has a given migration, but keep in mind the migration is an attribute of the database and the version is an attribute of the application. So the question is really just which database the version is talking to and what the schema is for that database. Your idea for a "version" number for the migrations in the database is actually pretty reasonable and many ORMs have some sort of feature for that.

The fact that this issue exists is why your usually better off with more flexible data modeling approaches when first prototyping your app (allow more NULLs, don't use foreign keys, or just use a NoSQL approach like Datastore), and implement more data integrity once you have more confidence in your data model.

Finally, I work on Google Cloud documentation and I'm sorry if you're disappointed we don't address this more clearly, but hopefully you understand the perspective that it's a general database operations question rather than a question specific to Google Cloud or App Engine. If you do come up with a solution you like, you should consider blogging about it and we'll be happy to help promote your solution!