Changing the backend db of a live django app.

2016 Dec 22 at 08:37 » Tagged as :python, sqlite, django, odo, mysql,

I am feeling lucky.

In theory if you use django, changing your RDBS from say sqlite to mysql is a breeze. Simple as generating a dump of your existing data with ./manage.py dumpdata, change database connection string in settings.py and perform  a./manage.py migrate on the new backend. Now you can import the previously exported data through ./manage.py loaddata . Well that's the theory but in practice there's always a glitch.

Squash Migrations

The ./manage.py migrate step on the new database invariably throws an error or two dozen. Squashing migrations often works. The idea behind squash is to take a series of create and alter statements and combine them together into one single create statement. That doesn't always work either. Then the simplest fix is to delete all the migrations from each app's migrations/ folder.  Then a fresh ./manage.py makemigrations followed by ./manage.py migrate . Now even if your luck didn't hold the first time, you are still likely to end up with a newly created database on your hot new server. You can now use the loaddata management command to put the data back in.

The migration can stil fail in some edge cases. For example you may find that some of the model fields you have used such as ArrayField)are incompatible with the new RDBMS you are moving to. I leave you to solve that yourself with the reminder that ArrayField should never have been used in the first place. And the moral of the story is that you shouldn't rely too heavily on non standard vendor specific features and extensions present in your RDBMS.

 

Odo

Anyone who has ever tried to use Django's loaddata command can tell you that it will choke on the django Content Types app. Once you get past that hurdle, you will still find all sorts of errors popping up. Of course you can use MySql's SELECT INTO OUTFILE or Postgresql's COPY command to make the import export process less painfull but the only catch is if you have a large number of models, you will find yourself doing a lot of typing. Enter Odo.

import os, sys

if __name__ == '__main__': #pragma nocover
    # Setup environ
    sys.path.append(os.getcwd())

    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "myapp.settings")

    import django
    django.setup()

from odo import odo
from django.apps import apps

for m in apps.get_models():
    odo("sqlite://db.sqlite3::{0}".format(m._meta.db_table),"/tmp/{0}.csv".format(m._meta.db_table))

The above code will export data from every single table in your database into a set of CSV files using sqlite's .output command for the export, while most home brewed exporters would save the data line by line. Right now, you can import these into another database right? What if I told you that you can actually import export without intermediate CSV dumps? yep, quite possible but your milage might vary.