Run Alembic migrations on Google App Engine

Question

I have a Flask app that uses SQLAlchemy (Flask-SQLAlchemy) and Alembic (Flask-Migrate). The app runs on Google App Engine. I want to use Google Cloud SQL.

On my machine, I run python manage.py db upgrade to run my migrations against my local database. Since GAE does not allow arbitrary shell commands to be run, how do I run the migrations on it?

Glosseme

Answer 1

• Whitelist your local machine's IP: https://console.cloud.google.com/sql/instances/INSTANCENAME/access-control/authorization?project=PROJECTNAME
• Create an user: https://console.cloud.google.com/sql/instances/INSTANCENAME/access-control/users?project=PROJECTNAME
• Assign an external IP address to the instance: https://console.cloud.google.com/sql/instances/INSTANCENAME/access-control/ip?project=PROJECTNAME
• Use the following SQLAlchemy connection URI: SQLALCHEMY_DATABASE_URI = 'mysql://user:pw@ip:3306/DBNAME'
• Remember to release the IP later as you are charged for every hour it's not used

Glosseme

When you say, "release the IP" in the last bullet, are you referring to the local machine's IP from the first bullet or the assignment of the external IP in the third bullet? – Equity

Answer 2

It's all just code you can run, so you can create an admin endpoint with which to effect an upgrade:

@app.route('/admin/dbupgrade')
def dbupgrade():
    from flask_migrate import upgrade, Migrate
    migrate = Migrate(app, db)
    upgrade(directory=migrate.directory)
    return 'migrated'

(Dropwizard, for instance, caters nicely for such admin things via tasks)

Contradistinguish

Answer 3

You can whitelist the ip of your local machine for the Google Cloud SQL instance, then you run the script on your local machine.

Harsho

This is actually what I ended up doing. However, assigning a IPV4 address to the SQL instance costs $0.01/h = $7.2/mo. If there was a way to automate obtaining the address, running the migrations, and then releasing it, I'd be a happy camper – Glosseme

you can use gcloud sql instances patch --assign-ip, gcloud sql instances patch --no-assign-ip to assign/unassign an IP address – Noise