Upload Existing Django Sqlite database to heroku.

Aiden Ellis — Wed, 12 May 2021 08:46:46 +0000

So, for a few days i've been struggling to upload my old database which was sqlite3 to heroku's Postgres database.

Now here comes the challanges:

Convert old sqlite3 database to postgresql with all of the existing data into it
Upload that postgresql database to heroku

This might look easy but it really takes too much time and effort to find the way to do it.And in this post i'm gonna help you out here, to the mooooon! ;)

1.Converting database

To convert the database (sqlite3) to postgresql, first we
need Postgresql and PG Admin installed in our machine.Go and download and install these 2.
NOTE: While installing PG Admin it will ask you for a passwords, make sure to remember that password.

Now we are going to create an empty Postgres Database.
Head into PG Admin, Enter the password that when it ask's for.And follow these steps to create an empty database.

1.1) Click on `Objects` in the top.

1.2) Then click on `create`.

1.3) Now we gotta create a group name.In this case i named it Tutorial

click on save.Now in the left you can see out new group 'Tutorial'.

1.4) Now let's create the server.Right click on that 'Tutorail' Group.Then Create > Server

1.5) Fill up the Name, I named it 'CONVERT_DB'

1.6) Now click on Connection and fill up 'hostname', 'password'

Here type localhost in host name/address and in the password input type the password that you gave PG Admin while installing it.And Click SAVE.

There You go we just created an empty server.

Now to transfer all of the sqlite3 data to our empty postgresql database.We first have to create a dumpfile from sqlite3

1.7) Make sure that you have set the sqlite3 database in your settings.py


DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'old_database_name.sqlite3',
    }
}

1.8) Create datadump.json from manage.py

python3 manage.py dumpdata > datadump.json

This will create a datadump.json file.
Note: Sometime, this generates broken json file so you have to format it yourself if that json file shows any error.Even if you have print('') in settings.py it will add it to the json file.This is a bug so you have to deal with it.

1.9) Now we are going to change the DATABASE to the postgresql server.so head on to settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'CONVERT_DB', # The Server name from 1.5
        'USER': 'postgres', # The username from 1.6
        'PASSWORD': 'secretpassword', # The password from installation
        'HOST': 'localhost' # Host name/address from 1.6,
        'PORT': '5432' # Port from 1.6
    }
}

Set the information the the server information. from (1.6)

1.9.1) Now migrate the empty database with --run-syncdb.

python3 manage.py migrate --run-syncdb

1.9.2) Go to shell using manage.py

python3 manage.py shell

1.9.3) Now as you are in shell type these commands.

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

1.9.4) Finally! It's time to load our old data to this new database

python3 manage.py loaddata datadump.json

This will load the data from our old database to the new database using the datadump.json file that we created from sqlite3.

Auhhhh! What a relief.We converted the data but, we still got 1 more challange left which is Uploading our new database (with old data) in to heroku.Lets goooo (My cat is on my keyboard xD)

2.Uploading the postgres database in heroku

Now as you know that you can't upload file to Heroku through their website, You can upload with github but thats a worst way you can do it with risking your data.

So we are going to upload the database through heroku CLI.Open Your command prompt and type (Make sure your PG Admin in running.)

pg_dump -Fc --no-acl --no-owner -h localhost -U USERNAME DATABASE_NAME > mydb.dump

So the 'USERNAME' is the name from the database from (1.6).Defaultly it was set to postgres and i didn't chage it in my case, DATABASE_NAME is the database 'Server name' from (1.5) where i set it to 'CONVERT_DB'.
So in my case the command will be:

pg_dump -Fc --no-acl --no-owner -h localhost -U postgres CONVERT_DB > mydb.dump

This will ask for a passwords: which is the password from installation
After you enter the password This will create a mydb.dump file.So if you've got through this far then congrats we are really close.And if you got any error just give a comment below.

now that we have our mydb.dump file.We can upload it to heroku but to upload it we have to do it via some online file storage service.You have to store the 'mydb.dump' file to the cloud and make it public to it's accessible from anywhere.

You can upload it to aws, or any other file server.But the link should be the direct download link, like if you paste it to the browser it will automatically start downloading. Now it could be overwelming and it's a bit hard since there's no easy way for free.If you got stuck here then just comment below i will try to help you out with the cloud storing easily. :)

And now we have the dump_file_link we can upload it to heroku
our imaginary dump_file_link is 'https://some_cloud_server/path/to/file.dump'.

Before we upload make sure you have heroku cli installed and are logged in.Make sure to delete the old database from heroku and create a new fresh database there.

2.0) Open command prompt and type

heroku pg:backups:restore <'dump_file_link'>

This will ask for you conformation and you have to enter your app name to confirm and KABOOM!!! You'r database is now updated.

PS: I just started using heroku a few days ago and im still figuring out stuffs.If i weren't able to help then im sorry for that but i actually tried my best to solve it in a post an d you know its hard to solve overwelming problems in just a post.Anyway if you made it so far Congratulations!!!!!! Now i'll go and take a nap with my cat. see ya ;) Meow uwu

DEV Community: Aiden Ellis