Django Web Framework Tutorials

How to Migrate Data from SQLite to PostgreSQL and MySQL in Django with Example

Last Updated: by

Migrating data from SQLite to other database systems like PostgreSQL or MySQL in a Django project is a common task when transitioning from development to production or switching database engines for various reasons. In this blog, we’ll walk you through the steps to successfully migrate data from SQLite to both PostgreSQL and MySQL in a Django application, complete with practical examples.

Prerequisites

Before you begin, make sure you have the following prerequisites:

  1. A Django project with data stored in SQLite.
  2. Access to a PostgreSQL or MySQL database (either locally or on a server).
  3. The necessary database connectors for your target databases:
  • psycopg2 for PostgreSQL (install it with pip install psycopg2).
  • mysqlclient for MySQL (install it with pip install mysqlclient).

Before diving into implementation, please read our blogs on:

Recommended: if you face any challenge in database integration read above blogs for reference .

Step 1: Create Target Databases

First, create empty target databases for PostgreSQL and MySQL that will receive the data from your SQLite database. Use the respective database command lines or GUI tools to create the databases.

For PostgreSQL:

createdb <postgres_database_name>

For MySQL:

mysql -u <mysql_user> -p

Once logged in, create the MySQL database:

CREATE DATABASE <mysql_database_name>;

Step 2: Update Django Settings

Next, update your Django project’s settings to connect to the PostgreSQL and MySQL databases. Open the settings.py file and modify the DATABASES configuration as follows:

# settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',  # Keep the SQLite engine for now
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'postgres': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': '<postgres_database_name>',       # Replace with your PostgreSQL database name
        'USER': '<postgres_database_user>',       # Replace with your PostgreSQL database user
        'PASSWORD': '<postgres_database_password>', # Replace with your PostgreSQL database password
        'HOST': 'localhost',                      # Replace with your PostgreSQL host
        'PORT': '',                               # Leave empty to use the default PostgreSQL port (5432)
    },
    'mysql': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': '<mysql_database_name>',           # Replace with your MySQL database name
        'USER': '<mysql_database_user>',           # Replace with your MySQL database user
        'PASSWORD': '<mysql_database_password>',   # Replace with your MySQL database password
        'HOST': 'localhost',                      # Replace with your MySQL host
        'PORT': '',                               # Leave empty to use the default MySQL port (3306)
    }
}

Make sure to replace <postgres_database_name>, <postgres_database_user>, <postgres_database_password>, <mysql_database_name>, <mysql_database_user>, and <mysql_database_password> with your database details.

Step 3: Run Initial Migrations for PostgreSQL and MySQL

Before migrating your data, apply the initial migrations to create the necessary database schema in PostgreSQL and MySQL. Run the following commands:

For PostgreSQL:

python manage.py migrate --database=postgres

For MySQL:

python manage.py migrate --database=mysql

Step 4: Export Data from SQLite

Export the data from your SQLite database into a format that can be imported into PostgreSQL and MySQL. Use the dumpdata command to create JSON or XML files for each target database:

python manage.py dumpdata --database=postgres > data_postgres.json
python manage.py dumpdata --database=mysql > data_mysql.json

These commands will create data_postgres.json and data_mysql.json files containing your data for PostgreSQL and MySQL, respectively.

Step 5: Import Data into PostgreSQL and MySQL

Now, import the data into the PostgreSQL and MySQL databases using the loaddata command:

For PostgreSQL:

python manage.py loaddata data_postgres.json --database=postgres

For MySQL:

python manage.py loaddata data_mysql.json --database=mysql

Django will read the data from the JSON files and insert it into the PostgreSQL and MySQL databases.

Step 6: Update Foreign Keys (if necessary)

If your database schema includes foreign keys, you might need to update them after importing the data. This is because the primary keys of records may have changed during the migration process. You can use Django’s update command to automatically update the foreign keys:

python manage.py update_foreign_keys

Step 7: Test Your Application

After migrating your data, thoroughly test your Django application with both the PostgreSQL and MySQL databases to ensure everything works as expected. Pay close attention to any database-related functionality and perform comprehensive testing to catch any potential issues.

Conclusion

Migrating data from SQLite to PostgreSQL and MySQL in a Django project is essential when transitioning to production or switching database engines . By following the steps outlined in this blog, you can seamlessly move your data and ensure your application runs smoothly on both PostgreSQL and MySQL. Remember to take regular backups and thoroughly test your application to avoid any data loss or unexpected behaviuor during the migration process.

Blogs you might like to Read!