Every time you start a new Django project, it will use SQLite as its default database system.

SQLite might not be the best database system in the world, but it gets job done during the prototyping phase.

But as your Django project gets more matured, you might need more fancy database features such as concurrent sessions or analytical functions.

Suddenly you stand in front of an annoying task.

You need to migrate your SQLite database to something more professional, like PostgreSQL.

Normally you would have to cover these high-level steps to successfully migrate any database:

  • Create a backup file by dumping all your data
  • Recreate database structures in your new database
  • Import your data from a backup file to new tables
  • Switch connection drivers from old database to new database in your backend

Fortunately, the process is not so daunting as it sounds.

I should know because I just went through migrating my Django React project from the SQLite database to PostgreSQL.

Step By Step Guide

First, start your pipenv environment.

pipenv shell

Then dump all your data from the SQLite database to a file.

python manage.py dumpdata > db.json

Now create a new PostgreSQL database.

I assume you have PostgreSQL already installed.

createdb mynewdb

Then change your Django database settings so it points to your new PostgreSQL database.

In…

settings.py

Replace…

DATABASES = {
    'default': {
    'ENGINE': 'django.db.backends.sqlite3',
    'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}

With…

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'myproject',
        'USER': 'myprojectuser',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '',
    }
}

Don’t forget to install PostgreSQL drivers for python 3.

pipenv install psycopg2-binary

Now you can recreate database structures from your model.py definition.

python manage.py migrate

You can check new database tables with any PostgreSQL client, like DBeaver.

Fire up python shell.

python manage.py shell

Enter the code below. It should fix incompatible content types, that are not supported by PostgreSQL.

from django.contrib.contenttypes.models import ContentType
ContentType.objects.all().delete()

Finally, load data from your backup.

python manage.py loaddata db.json

Oh, and don’t forget to change your raw SQL queries to PostgreSQL syntax.

For example, I had to change…

ifnull()

to PostgreSQL function…

coalesce()

And with PostgreSQL, it is not enough to execute cursor, like this.

result = cursor.execute(sql)

This will just return None type.

You need to fetch data after execution like so.

cursor.execute(sql)
result = cursor.fetchall()

Hope this guide was helpful.

Migration was much smoother than I anticipated. Now I am ready to move on with my Django React project to the beta phase!
4.5 4 votes
Article Rating

Share it!