Django: Database Migration from sqlite to mariadb

 

Migrating Your Django Database from SQLite to MariaDB

Switching from SQLite to MariaDB in Django involves a few key steps: adding the new database, migrating data, and updating your settings. Follow this step-by-step guide to ensure a smooth transition.

---

1. Add the New Database to settings.py

First, update your settings.py file to include the new MariaDB database. Add the following entry under the DATABASES dictionary:

'new': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': 'your_database_name',
    'USER': 'your_database_user',
    'PASSWORD': 'your_database_password',
    'HOST': 'localhost',  # Change if using a remote server
    'PORT': '3306',
}

After adding this, your DATABASES section should look something like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'new': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'your_database_name',
        'USER': 'your_database_user',
        'PASSWORD': 'your_database_password',
        'HOST': 'your_database_host',
        'PORT': '3306',
    }
}

Note: Ensure that you have installed the MariaDB (or MySQL) client on your machine before proceeding.

---

2. Migrate Database Schema to the New Database

Run the following command to create tables in your new MariaDB database:

python manage.py migrate --database=new

Important: If you have accidentally deleted the migrations folder, recreate it by running python manage.py makemigrations before proceeding.

---

3. Transfer Data to the New Database (Optional)

If you want to migrate existing data from SQLite to MariaDB, follow these steps:

a) Clear the New Database

Ensure the new database is empty before transferring data:

python manage.py flush --database=new

b) Export Data from SQLite

Dump the data from your current SQLite database into a JSON file:

python -Xutf8 manage.py dumpdata --exclude auth.permission --exclude contenttypes --output data.json

c) Load Data into MariaDB

Now, import the data into your new database:

python manage.py loaddata data.json --database=new

---

4. Switch to the New Database

Once the migration is complete, update settings.py to make MariaDB the default database. Remove the old SQLite entry and rename new to default:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'your_database_name',
        'USER': 'your_database_user',
        'PASSWORD': 'your_database_password',
        'HOST': 'your_database_host',
        'PORT': '3306',
    }
}

---

5. Final Check

Restart your Django server and verify that your application is using the new MariaDB database.

python manage.py runserver

If everything is working as expected, you have successfully migrated from SQLite to MariaDB!

Need Help? Feel free to ask any questions in the comments, and we’ll try to address them.