Switching databases in Django in production

Switching databases in Django in production

Not all management commands are quite obvious, you'll love this one!

There are times when you're done with the development stage in your Django web application, and you've got some sort of data in your db.sqlite3 database which you'd like to copy as-is to your new DB. Also it happens that you might want to switch databases while retaining information.

Let's begin!

I'm working with the idea of switching from an SQLITE3 database to a MYSQL DB.

Strip the needed data like a pro

The idea is to strip out the data into a serializable format: JSON. We do that with:

python manage.py dumpdata > data.json

Where data.json is the file you'll be storing the data from your current DB.

Switch DB while no one's looking

Next, change your database settings to the new one. This would involve reworking the DATABASES variable and a host of other stuffs (Environmental variables and all). Sqlite3 settings:

    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3'                            #BASE_DIR is defined in my Django settings already

New settings for MySQL:

    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': DB_NAME,
        'USER': DB_USERNAME,
        'HOST': DB_HOST,
        'PORT': DB_PORT,
        'TEST': {
            'NAME': DB_TEST_NAME,
        'OPTIONS': {
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
            'charset': 'utf8mb4'

Note: I've left out actual values in place of variables you'd have defined in your .env file. If you're actually using MySQL, you'd want to install the client with pip install mysqlclient.

Run migrations with an absolute sense of purity

Next, you'd create migrations with python manage.py makemigrations and then run them with python manage.py migrate on the new database.

Let's bring back the prodigal data

The next step is to load the stripped out data. We'll do this with:

python manage.py loaddata data.json


And that's it! You'll probably lose sessions and currently logged in users will be logged out, but it's all for the greater good. Half the world will be happy and live in ultimate utopia.

We go jam later!

Photo by Jan Antonin Kolar on Unsplash