Performing database migrations with Django on Google App Engine and Cloud SQL

Mr Data Insight

Google App Engine is great. It has a free tier that is sufficient for most small businesses and hobby websites, and is very easy to deploy and maintain. Some of those websites are written in Django, a recommended Python web framework.

Usually the first step is deploying your website to Google App Engine. There are great tutorials online already, but I feel they are missing one very important piece of inforation: django migrations. This blog post will help you to perform the manage.py migrate command against your App Engine Standard instance, with a Cloud SQL database

App Engine flexible is different from the standard environment and is well documented. But we are interested in App Engine Standard, the managed solution.

Typical Django deployment steps

What you want to do in a typical workflow is the following

  • Write your code, test it locally until satisfied
  • Upload your code to source control and your servers
  • Update your database tables if necessary
  • Deploy

I couldn’t find the update part in the online documentation, so I decided to write this blogpost so I can find it later when I need it again.

How Django performs database migrations

Database migrations where invented to make your life easier when changing your models in Django. In the early days, you had to write sql yourself to change the database structure before you could deploy a new instance of your codebase. A few years ago our life was made easier when Django started supporting migrations.

Updating your database happens in two steps:

  • First you ask Django to generate the update scripts for you. Django will then check your models and look for any updates it can find. This happens with the manage.py makemigrations command.
  • Secondly, you need to apply your migrations to your database to actually make the changes. This happens with the manage.py migrate command.

The reason there are two steps is that typically you want to create the migrations once on your development machine, but execute them multiple times: on your local environment, testing environment, production databases, … Splitting it into two steps also allows us to save the migrations in our code repository version control.

How to perform migrations on Google Cloud SQL

Let me explain how App Engine works: In essence, you copy paste your code to a cloud storage area with thegcloud app deploy command. The problem is that you can’t run scripts on the server, all you can do is copy paste your local environment to the server. This is not a standard way of working outside of App Engine, and Django typically assumes you can execute scripts on your production server.

So in theory, we can’t perform migrations at all. The advice I found online was to migrate my instance to the flexible environment, but I didn’t want to go through all the hassle and have the cumbersome maintenance added on top.

There is a solution/workaround: you can connect to Cloud SQL from your local machine and execute the manage.py migrate on your local machine but have the changes be made to your cloud instance.

To do this, you need to add your Cloud SQL database to your settings file. Your production instance is probably using sockets, but from your local machine you need to use the IP adress

if False and DEBUG: 
# Running in development, but want to access the Google Cloud SQL instance in production.
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'cloud-database-name',
'USER': 'dbuser',
'PASSWORD': 'dbpass',
'HOST': '35.242.xxx.xxx', # Set to IP address
'PORT': '', # Set to empty string for default.
}
}
else if DEBUG:
#Sqlite database
else:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'cloud-database-name',
'USER': 'dbuser',
'PASSWORD': 'dbpass',
'HOST': '/cloudsql/instancename:zone:dbname', #Use socket
'PORT': '', # Set to empty string for default.
}

This way, you can’t accidentally perform a migration without explicitly changing the if False to if True.

Now this alone won’t work, you also need to configure your SQL instance to allow connections from outside Google Cloud. Go to your console and open the Connections tab

There you can add a public IP address by clicking the Add network button and give it a name to remember which machine this belongs to.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade