New

Experience Smart HR with Horilla Mobile App

Google Play Store
Home / Blogs

How to Set Up Multiple Databases in Django [2025]

Django
·

December 12, 2024

how-to-set-up-multiple-databases-in-django

Django’s ORM makes it easy to interact with databases, but many projects must work with more than one database at a time.

This guide will show you how to set up and manage multiple databases in Django, including configuration, routing, and basic operations.

Why Use Multiple Databases?

  • Data Segregation: Different databases can be used for specific types of data. For instance, user data might be stored in one database, while product and sales data are stored in another.
  • Improved Performance: Distributing your workload across multiple databases can improve performance, especially for read-heavy or write-heavy applications.
  • Data Source Compatibility: In some cases, your application may need to interact with legacy databases or external services with their databases.

Setting Up Multiple Databases in Django

1. Configure Multiple Databases in settings.py

    Django uses the DATABASES setting in settings.py to define database connections. To use multiple databases, add entries in the DATABASES dictionary.

    Code:

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.postgresql',
            'NAME': 'default_db',
            'USER': 'default_user',
            'PASSWORD': 'password',
            'HOST': 'localhost',
            'PORT': '5432',
        },
        'employee': {
            'ENGINE': 'django.db.backends.mysql',
            'NAME': 'employee_db',
            'USER': 'employee_user',
            'PASSWORD': 'employee_password',
            'HOST': 'localhost',
            'PORT': '3306',
        },
        'asset': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': BASE_DIR / 'asset.db',
        },
    }

    Here, default, employee, and asset are the names of the databases we’ll work with.

    2. Create Database Routers

      A database router allows Django to direct queries to the correct database. Create a db_routers.py file within an app or a utils folder to define custom routing rules:

      # db_routers.py
      
      class EmployeeRouter:
          def db_for_read(self, model, **hints):
              if model._meta.app_label == 'employee_app':
                  return 'employee'
              return None
      
          def db_for_write(self, model, **hints):
              if model._meta.app_label == 'employee_app':
                  return 'employee'
              return None
      
          def allow_migrate(self, db, app_label, model_name=None, **hints):
              if app_label == 'employee_app':
                  return db == 'employee'
              return None
      
      class AssetRouter:
          def db_for_read(self, model, **hints):
              if model._meta.app_label == 'asset_app':
                  return 'asset'
              return None
      
          def db_for_write(self, model, **hints):
              if model._meta.app_label == 'asset_app':
                  return 'asset'
              return None
      
          def allow_migrate(self, db, app_label, model_name=None, **hints):
              if app_label == 'asset_app':
                  return db == 'asset'
              return None
      • db_for_read: Directs read queries to the specified database.
      • db_for_write: Directs write queries to the specified database.
      • allow_migrate: Controls which database migrations apply to which databases.

      3. Specify Routers in settings.py

      After creating the routers, add them to the DATABASE_ROUTERS setting in settings.py.

      # settings.py
      
      DATABASE_ROUTERS = ['path.to.db_routers.EmployeeRouter','path.to.db_routers.AssetRouter']

      4. Define Models and Associate Them with Databases

      You need to link Django’s built-in User model (in the default database) with an Employee model (in the employee database) using a one-to-one relationship. Django doesn’t support foreign key constraints across different databases, so we’ll handle the relationship manually.

      # employee_app/models.py
      
      from django.db import models
      from django.conf import settings
      
      class Employee(models.Model):
          user_id = models.PositiveIntegerField(unique=True)  # Store the related user’s ID
          employee_id = models.CharField(max_length=50, unique=True)
          position = models.CharField(max_length=100)
      
          class Meta:
              app_label = 'employee_app'
      
          def get_user(self):
              # Retrieve the associated User instance from the default database
              return settings.AUTH_USER_MODEL.objects.using('default').get(id=self.user_id)
      • user_id: Stores the primary key of the User instance in the default database.
      • get_user(): A helper method to retrieve the associated User instance, allowing access to user-related data.

      5. Running Migrations on Specific Databases

      When migrating, specify the database to ensure Django applies migrations only to the correct database.

      python manage.py migrate --database=employee
      python manage.py migrate --database=asset

      6. Creating and Linking Instances

      When creating an Employee instance, manually set the user_id to link it to a User. Here’s an example:

      from django.contrib.auth.models import User
      from employee_app.models import Employee
      
      # Assume we're creating a user in the default database
      user = User.objects.using('default').create(username='admin', password='admin')
      
      # Create a corresponding employee in the employee database
      employee = Employee.objects.using('employee').create(
          user_id=user.id,
          employee_id='E12345',
          position='Software Engineer'
      )

      Advanced Tips for Multiple Database Management

      1. Using Transactions Across Databases :

        Use atomic blocks to ensure that operations across multiple databases are atomic. However, this can be challenging when working across databases with different engines, as not all engines support the same level of transaction control.

        from django.db import transaction
        
        with transaction.atomic(using='aaset'):
            # Perform operations on the 'asset' database
            Asset.objects.using('asset').create(name="Lenovo")

        2. Handling Database Constraints :

          When working with multiple databases, you may need to add database constraints (like foreign keys) carefully. Since Django doesn’t support foreign keys across different databases, consider implementing custom validation logic to enforce relationships.

          3. Admin Access for Multiple Databases

            Django Admin doesn’t natively support multiple databases. However, you can register specific models in the admin and override the query set to point to the correct database.

            # admin.py
            
            from django.contrib import admin
            from legacy_app.models import LegacyModel
            
            class AssetModelAdmin(admin.ModelAdmin):
                def get_queryset(self, request):
                    return super().get_queryset(request).using('asset')
            
            admin.site.register(LegacyModel, LegacyModelAdmin)

            Conclusion

            Configuring Django to use multiple databases provides a way to scale applications, separate data concerns, and optimize performance. With database routers and the using method, Django allows you to manage complex data architectures while leveraging its powerful ORM.

            Horilla Editorial Team Author

            Horilla Editorial Team is a group of experienced writers and editors who are passionate about HR software. We have a deep understanding of the HR landscape and are committed to providing our readers with the most up-to-date and informative content. We have written extensively on a variety of HR software topics, including applicant tracking systems, performance management software, and payroll software etc. We are always looking for new ways to share our knowledge with the HR community. If you have a question about HR software, please don't hesitate to contact us.