Use Multiple Databases in One Django Project: MySQL, Postgres & SQLite

Huzaif Sayyed

8 months ago

Django is a versatile web framework that allows developers to work with multiple databases in a single project. In this blog, we’ll explore how to set up and use multiple databases, including SQLite, MySQL, and PostgreSQL, in a Django project.. We’ll provide a step-by-step guide along with practical examples to help you understand the process.

Before diving into implementation, please read our blogs on:

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

Prerequisites

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

A Django project set up and running.
MySQL and PostgreSQL installed and configured on your server or local machine.
The mysqlclient and psycopg2 Python packages installed to connect Django to MySQL and PostgreSQL respectively. You can install them using pip:

   pip install mysqlclient psycopg2

Step 1: Define Database Settings

Open your Django project’s settings.py file, where you will define the settings for each database you want to use. Add the following code to the DATABASES dictionary:

# settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    },
    'mysql_db': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'mysql_database_name',
        'USER': 'mysql_database_user',
        'PASSWORD': 'mysql_database_password',
        'HOST': 'localhost',
        'PORT': '3306',
    },
    'postgres_db': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'postgres_database_name',
        'USER': 'postgres_database_user',
        'PASSWORD': 'postgres_database_password',
        'HOST': 'localhost',
        'PORT': '5432',
    },
}

In this example, we have defined three databases: default, mysql_db, and postgres_db. The default database uses SQLite, while mysql_db and postgres_db use MySQL and PostgreSQL, respectively. Customize the settings for each database according to your setup.

Step 2: Create Database Routers

To specify which dattabase should be used for each model, you need to create custom database routers. Create a Python file, such as database_routers.py, in your Django app directory and define the routers as shown below:

# database_routers.py

class MySQLRouter:
    def db_for_read(self, model, **hints):
        if model._meta.app_label == 'your_app_label':
            return 'mysql_db'
        return None

    def db_for_write(self, model, **hints):
        if model._meta.app_label == 'your_app_label':
            return 'mysql_db'
        return None

class PostgresRouter:
    def db_for_read(self, model, **hints):
        if model._meta.app_label == 'your_app_label':
            return 'postgres_db'
        return None

    def db_for_write(self, model, **hints):
        if model._meta.app_label == 'your_app_label':
            return 'postgres_db'
        return None

Replace 'your_app_label' with the actual label of the app where you want to use these databases.

Step 3: Configure Database Routers

In your settings.py file, configure the custom database routers you just created:

# settings.py

DATABASE_ROUTERS = ['your_app_name.database_routers.MySQLRouter', 'your_app_name.database_routers.PostgresRouter']

Make sure to replace 'your_app_name' with the actual name of your app.

Step 4: Define Database Usage in Models

In your models, you can specify which database to use by adding the using attribute to the Meta class of each model. For example:

# models.py

from django.db import models

class MySQLModel(models.Model):
    # Fields
    name = models.CharField(max_length=100)

    class Meta:
        db_table = 'mysql_table'
        app_label = 'your_app_label'
        using = 'mysql_db'

class PostgresModel(models.Model):
    # Fields
    name = models.CharField(max_length=100)

    class Meta:
        db_table = 'postgres_table'
        app_label = 'your_app_label'
        using = 'postgres_db'

In the above example, we’ve defined two models, MySQLModel and PostgresModel, and specified the database for each using the using attribute.

Step 5: Run Migrations

Now that you’ve configured multiple databases, you can create tables for each database using the following commands:

python manage.py makemigrations
python manage.py migrate

This will create tables for all databases specified in your settings.py.

Step 6: Use the Databases in Views and Queries

In your views and queries, you can explicitly specify which database to use. For example:

# views.py

from django.shortcuts import render
from .models import MySQLModel, PostgresModel

def my_view(request):
    mysql_data = MySQLModel.objects.using('mysql_db').all()
    postgres_data = PostgresModel.objects.using('postgres_db').all()
    # Your view logic here

In this example, we’re using the .using() method to specify the database for each model query.

Conclusion

Using multiple databases in a Django project, such as SQLite, MySQL, and PostgreSQL, allows you to optimize your application for different use cases. By following the steps outlined in this blog and customizing them to your specific needs, you can effectively manage multiple databases within your Django application

Find this project on Github.