How to migrate your Django project from SQLite to PostgreSQL

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 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 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 SQLite database to PostgreSQL.

So here is step by step guide.

First, start your pipenv environment.

pipenv shell

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

python manage.py dumpdata > db.json

Now create 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 code below. It should fix incompatible content types, that is 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 beta phase!

Share it!


Want to support me in my journey 🚀? You can send me a donation below 👇.

Donate with Paypal Become a Patron!

Leave a Reply

avatar

Want to support me in my journey 🚀? You can send me a donation below 👇.

Donate with Paypal Become a Patron!

Made with 🔥 by Viet Phan 2018-2019 Privacy Policy