Automating Django Database Re-Creation on PostgreSQL

border_wall

photo: Oren Ziv/Activestills.org

The Django database migration system is a mess. This mess is made more difficult when using PostgreSQL.

Schemas go unrecognized without a work around, indices can break when using the work around, and processes are very difficult to automate with the existing code base.

The authors of Django, or someone such as myself who occasionally finds the time to dip in and unclutter a mess or two, will eventually resolve this. However, the migration system today is a train wreck.

My current solution to migrating a Postgres based Django application is presented in this article.

Benefits of Getting Down and Dirty

The benefits of taking the time to master this process for your own projects are not limited to the ability to automate Django. They include the ability to easily manage migrations and even automate builds.

Imagine having a DDL that is defined in your application and changed with one line of code without ever touching your database.

Django is a powerful and concurrent web framework with an enormous amount of add-ons and features. Mastering this task can open your code base to amazing amounts of abstraction and potential.

Well Defined Problems

While Django is powerful, integration with Postgres is horrible. A schema detection problem can cause a multitude of problems as can issues related to using multiple databases. When combined, these problems sap hours of valuable time.

The most major issues are:

  • lack of migration support for multiple databases
  • lack of schema support (an issue recognized over 13 years ago)
  • some indices (PostGIS polygon ids here) break when using the schema workaround

Luckily, the solutions for these problems are simple and require hacking only a few lines of your configuration.

Solving the Multiple Database Issue

This issue is more annoying than a major problem. Simply obtain a list of your applications and use your database names as defined by your database router in migration.

If there are migrations you want to reset, use the following article instead of the migrate command to delete your migrations and make sure to drop tables from your database without deleting any required schemas.

Using python[version] manage.py migrate [app] zero  did not actually drop and recreate my tables for some reason.

For thoroughness, discover your applications using:

python[verion] manage.py showmigrations

With your applications defined, make the migrations and run the second line of code in an appropriate order for each application:

python[version] manage.py makemigrations [--settings=my_settings.py]
python[version] manage.py migrate [app] --database= [--settings=my_settings.py]
....

As promised, the solution is simple. The –database switch matches the database name in your configuration and must be present as Django only recognizes your default configuration if it is not. This can complete migrations without actually performing them.

Solving the Schema Problem without Breaking PostGIS Indices

Django offers terrific Postgres support, including support for PostGIS. However, Postgres schemas are not supported. Tickets were opened over 13 years ago but were merged and forgotten.

To ensure that schemas are read properly, setup a database router and add a database configuration utilizing the following template:

"default": {
        "ENGINE": "django.contrib.gis.db.backends.postgis",
        "NAME": "test",
        "USER": "test",
        "PASSWORD": "test",
        "HOST": "127.0.0.1",
        "PORT": "5432",
        "OPTIONS": {
            "options": "-csearch_path=my_schema,public"
        }
 }

An set of options append to your dsn including a schema and a backup schema, used if the first schema is not present, are added via the configuration. Note the lack of spacing.

Now that settings.py is configured and a database router is established, add the following Meta class to each Model:

    class Meta():
        db_table=u'schema\".\"table'

Notice the awkward value for db_table. This is due to the way that tables are specified in Django. It is possible to leave managed as True, allowing Django to perform migrations, as long as the database is cleaned up a bit.

If there are any indices breaking on migration at this point, simply drop the table definition and use whichever schema this table ends up in. There is no apparent work around for this.

Now Your Migration Can Be Run In a Script, Even in a CI Tool

After quite a bit of fiddling, I found that it is possible to script and thus automate database builds. This is incredibly useful for testing.

My script, written to recreate a test environment, included a few direct SQL statements as well as calls to manage.py:

echo "DROP SCHEMA IF EXISTS auth CASCADE" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "DROP SCHEMA IF EXISTS filestorage CASCADE" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "DROP SCHEMA IF EXISTS licensing CASCADE" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "DROP SCHEMA IF EXISTS simplred CASCADE" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "DROP SCHEMA IF EXISTS fileauth CASCADE" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "DROP SCHEMA IF EXISTS licenseauth CASCADE" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "DROP OWNED BY simplrdev" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "CREATE SCHEMA IF NOT EXISTS auth" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "CREATE SCHEMA IF NOT EXISTS filestorage" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "CREATE SCHEMA IF NOT EXISTS licensing" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "CREATE SCHEMA IF NOT EXISTS simplred" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "CREATE SCHEMA IF NOT EXISTS licenseauth" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
echo "CREATE SCHEMA IF NOT EXISTS fileauth" | python3 ./app_repo/simplred/manage.py dbshell --settings=test_settings
find ./app_repo -path "*/migrations/*.py" -not -name "__init__.py" -delete
find ./app_repo -path "*/migrations/*.pyc"  -delete
python3 ./app_repo/simplred/manage.py makemigrations --settings=test_settings
python3 ./app_repo/simplred/manage.py migrate auth --settings=test_settings --database=auth
python3 ./app_repo/simplred/manage.py migrate admin --settings=test_settings --database=auth
python3 ./app_repo/simplred/manage.py migrate registration --settings=test_settings --database=auth
python3 ./app_repo/simplred/manage.py migrate sessions --settings=test_settings --database=auth
python3 ./app_repo/simplred/manage.py migrate oauth2_provider --settings=test_settings --database=auth
python3 ./app_repo/simplred/manage.py migrate auth_middleware --settings=test_settings --database=auth
python3 ./app_repo/simplred/manage.py migrate simplredapp --settings=test_settings --database=data
python3 ./app_repo/simplred/manage.py loaddata --settings=test_settings --database=data fixture

Assuming the appropriate security measures are followed, this script works well in a Bamboo job.  My script drops and recreates any necessary database components as well as clears migrations and then creates and performs migrations. Remember, this script recreates a test environment.

The running Django application, which is updated via a webhook, doesn’t actually break when I do this. I now have a completely automated test environment predicated on merely merging pull requests into my test branch and ignoring any migrations folders through .gitignore.

Conclusion

PostgreSQL is powerful, as is Django, but combining the two requires some finagling to achieve maximum efficiency. Here, we examined a few issues encountered when using Django and Postgres together and discussed how to solve them.

We discovered that it is possible to automate database migration without too much effort if you already know the hacks that make this process work.

 

Bamboo and Webhooks: Automating CI and Deployment

process_auto

The name of the game in technology is doing more with less. It is the core element in the drive for profitability. In today’s market, that means finding ways to reduce both the number of employees in an organization and improve the processes that allow for such a reduction.

The days of paying $80,000 for an employee with limited knowledge, only ETL or only XML, are waning or dead. Even more importantly, the cost of software bugs in 2016 reached $1.1 trillion.

Reducing labor costs and reducing the cost of software bugs means producing efficient, clean, easy to follow, and effective systems backed by quality processes (word play with quality assurance intended). This article dives into how to use Bamboo, Flask, and Bitbucket to achieve continuous integration and continuous deployment to a test environment.

The concepts in this article can be ported to a distributed environment using ngrok but hopefully a more secure option is available. The ngrok solution is explained well on the Bitbucket website but promotes non-free software and external providers. This article intends to explain how to do this for free without linking to an external provider.

Environment

An automated test environment can be set up with:

  • Bitbucket
  • Flask
  • Bamboo
  • An Ubuntu or Linux Server for the purposes of this article

Bitbucket is a powerful repository based on git. Part of the repositories power is the ability to add webhooks. These are essentially triggers that perform POST requests to a webserver on specified events.

Webhooks require a web server. Flask is a lightweight and easy to maintain, single threaded framework. This is perfect for most repositories that do not see a significant number of pull requests.

Other hooks, including to Jenkins, are available as well.

Another powerful feature of Bamboo worth noting is the ability to organize repositories into projects. This suits companies with well defined architectures extremely well.

Bamboo is a continuous integration (CI) tool, a tool that allows code to be continually built and tested from a Bitbucket repository with minimal human interaction. This tool integrates directly with Jira, Bitbucket, and nearly every other Atlassian product.

Automating a System

Most companies with a forward facing website have production and test environments. Integrating and automating the environment is made possible with the use of a continuous integration (CI) environment and webhooks.

Bamboo can be set up to automatically synchronize code when a commit or merge is made in the remote repository. However, jobs in this CI run in an isolated way with no access to write to the local machine beyond SCP.

While a CI ensures that code runs correctly and can be set to notify users of failed builds, the synchronization issue outside of the isolated build environment is solved with webhooks. Calls to web servers specified in these hooks are triggered whenever a certain event is performed in the repository, allowing for more automated and instantaneous regression and integration testing.

Step 1: Setting Up SSH in Bitbucket

Any automated pull requires using ssh instead of http protocol as the means to obtain code from the remote repository. This requires setting up an ssh key on Bitbucket and on the local machine where your test environment is configured.

Setting up an rsa key on Ubuntu is simple:

  1. run ssh-keygen -t rsa
  2. enter only the name of the key
  3. copy the public key (.pub) to your clipboard
  4. create  or open the config file in your home’s .ssh directory
  5. add your bitbucket host on a new line Host <host ip address>
  6. on a new tab spaced line enter the name of your private key file generated in steps 1-2 as Identity File <private key file name>
  7. log in to Bitbucket
  8. click on your avatar
  9. click on Manage Account
  10. navigate to SSH Keys on the left hand menu
  11. click on add key towards the center of the page and simply paste your key

Step 2: Creating a Webhook in Flask

A webook can be setup simply in Flask. James Innes of Ogma Development wrote a terrific article on how to setup a webhook in Flask. After creating the project as specified, run the code using:

flask run

It is possible to change the application host by simply modifying app.run() in your hook as follows:

app.run(host=<host>, port=[unreserved port])

Note the host, port, and token if applicable when your application is running and perform a GET request with the appropriate token from the machine or container running your Bitbucket repository.

Step 3: Setting up Webhooks in Bitbucket

Webhooks in Bitbucket can occur at the project or repository level. For the best control, this article explains how to create a hook at the repository level. Imagine triggering a multitude of unnecessary webhooks when modifying a single repository.

Setting up a webhook on a Bitbucket repository is simple:

  1. Log in to Bitbucket
  2. navigate to your target project
  3. Navigate to your repository
  4. Click on the settings gear in the left hand menu
  5. Click on webhooks in the left hand menu
  6. Enter a name for your hook
  7. Enter the url of your webhook server (of the form http[s]://webhook[?verify_token=<token>] in this context)
  8. In the options menus, ensure that push and merged are clicked
  9. Click save at the bottom of the screen

Currently, Bitbucket tests these connections with an empty POST request. This is not handled by our code and empty POST requests are generally discouraged anyway.

It is now possible to simply execute a pull request from a module under the webhook function in your Flask application on every merge in or push to the repository. This can be used to synchronize code and even keep a test site constantly ready for testing if you are using Django. As Flask is single threaded, it is better suited for the task of creating hooks.

Create a Build in Bamboo

With our repository thoroughly connected to the remote server, we can create build plans using Bamboo. Bamboo organizes the build flow  underneath a plan into jobs which maintain tasks.

For an annual fee of just ten dollars, it is possible to obtain ten free jobs with an unlimited number of tasks.

Future articles will explain how to integrate applications built with Django, Celery, and PyTorch. Still, even databases created using Djang are recreatable using the only the models and shell commands from the Script task.

As stated before, Bamboo can be set up to notify an appropriate developer on a build failure.

Conclusion

With these elements in place, it is possible to keep running test code up to date and continue to run builds against this environment with minimal human interaction. This greatly reduces labor and thus cost which can be passed to the end user in the form of savings.