Database Management on Django and Github

Question

I am trying to set up a website using the Django Framework. Because of it's convenience, I had choosen SQLite as my database since the start of my project. It's very easy to use and I was very happy with this solution.

Being a new developer, I am quite new to Github and database management. Since SQLite databases are located in a single file, I was able to push my updates on Github until that .db file reached a critical size larger than 100MB. Since then, it seems my file is too large to push on my repository (for others having the same problem I found satisfying answers here: GIT: Unable to delete file from repo).

Because of this problem, I am now considering an alternative solution:

Since my website will require users too interact with my database (they are expected post a certain amount data), I am thinking about switching SQLite for MySQL. I was told MySQL will handle better the user inputs and will scale more easily (I dare to expect a large volume of users). This is the first part of my question. Is switching to MySQL after having used SQLite for a while a good idea/good practice or will it lead to migration problems?

If the answer to that first question is yes, then I have other questions about how to handle this change. Since SQLite is serverless, I will have to set up a new server for MySQL. Will I be able to access my data remotely with that server? Since I used to push my database on my Github repository, this is where I use to get my data from when I wanted to work remotely. Will there be a way for me to host my data on a server (hopefully for free) and fetch it the same way I fetch my code on Github?

Thank you very much for your help and I hope you have a nice day.

Answer 1

First of all, you shouldn't be uploading any sensitive data to your repository. That includes database passwords, Django's secret key or the database itself in the case of SQLite.

Answering your first question, there shouldn't be any problem switching from SQLite to MySQL. Django handles migrations exceptionally and SQLite has less features than MySQL. To migrate your data to a mysql database you can use django's dumpdata and loaddata.

Now, your second question is a bit more complicated. You can always expose your database to the Internet, but that is usually not a good idea unless you know exactly what you're doing and know how to secure it properly. If you go this way though, you can just change the database parameters in your settings file to point to your MySQL database's public IP and add the db name, user and password.

My recommendation though is to have one database for development in your dev PC and another in your production server that is behind a firewall and can only be accessed through localhost. I don't think you need the db in your dev pc to be always up to date, if you have some sample data that should be enough.

So, instead of writing sensitive data into the settings file you can have a secrets.json file in the root of your project that looks like this:

{
   "secret_key": "YOURSUPERSECRETKEY",
   "debug": true, TRUE IN YOUR DEV PC, FALSE IN YOUR PROD SERVER
   "allowed_hosts": ["127.0.0.1" , "localhost", "YOUR"], 
   "db_name": "YOURDBNAME",
   "db_user": "YOURDBUSER",
   "db_password": "YOURDBPASSWORD",
   "db_host": "localhost",
   "db_port": 3306
}

This file should be included in your .gitignore so it doesn't get pushed to your repository and you would have one in your local pc and another one with different settings in your production server (you can use vi or nano to create the file).

Then in your settings.py file you can do the following:

import json

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

try:
    with open(os.path.join(BASE_DIR, 'secrets.json')) as handle:
        SECRETS = json.load(handle)
except IOError:
    SECRETS = {}


SECRET_KEY = SECRETS['secret_key']

ALLOWED_HOSTS = SECRETS['allowed_hosts']

DEBUG = SECRETS['debug']

...

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': SECRETS['db_name'],
        'USER': SECRETS['db_user'],
        'PASSWORD': SECRETS['db_password'],
        'HOST': SECRETS['db_host'],
        'PORT': SECRETS['db_port'],
    }
}