How to add multiple PostgreSQL databases to a Django app

If you have any questions about Django or need a professional software engineer to help with a project you're working on, schedule an intro call with CodeConda for free!

1. Install Required Packages

Make sure you have PostgreSQL installed on your system. You'll also need the `psycopg2` package to connect Django to PostgreSQL. Install it using pip:

$ pip install psycopg2-binary

2. Configure Database Settings

Open your Django project's `settings.py` file and find the `DATABASES` section. Update the default database configuration to use PostgreSQL:

   DATABASES = {
       'default': {
           'ENGINE': 'django.db.backends.postgresql',
           'NAME': 'your_database_name',
           'USER': 'your_database_user',
           'PASSWORD': 'your_database_password',
           'HOST': 'localhost',  # Change this to your PostgreSQL host if necessary
           'PORT': '5432',       # Default PostgreSQL port
       }
   }

3. Create the Database

After updating the settings, run the following command to create the database:

$ python manage.py migrate

4. Adding Multiple Databases

Django supports using multiple databases within a single project. To add another database, update your `DATABASES` settings like this:

   DATABASES = {
       'default': {
           'ENGINE': 'django.db.backends.postgresql',
           'NAME': 'default_db',
           'USER': 'db_user',
           'PASSWORD': 'db_password',
           'HOST': 'localhost',
           'PORT': '5432',
       },
       'secondary': {
           'ENGINE': 'django.db.backends.postgresql',
           'NAME': 'secondary_db',
           'USER': 'db_user',
           'PASSWORD': 'db_password',
           'HOST': 'localhost',
           'PORT': '5432',
       }
   }

In this example, a second database named "secondary" has been added.

5. Routing Database Queries

You'll need to create a database router to determine which database to use for different parts of your app. Create a new Python file, e.g., `routers.py`, in your app directory and define a router class:

   class SecondaryDatabaseRouter:
       """
       A router to control all database operations on models in the
       secondary database.
       """
       def db_for_read(self, model, **hints):
           if model._meta.app_label == 'your_app_label':
               return 'secondary'
           return None

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

       def allow_relation(self, obj1, obj2, **hints):
           if (
               obj1._meta.app_label == 'your_app_label' or
               obj2._meta.app_label == 'your_app_label'
           ):
               return True
           return None

       def allow_migrate(self, db, app_label, model_name=None, **hints):
           if app_label == 'your_app_label':
               return db == 'secondary'
           return None

Replace `'your_app_label'` with the actual label of the app you want to route to the secondary database.

6. Enable the Router

In your `settings.py`, add the following line to enable the custom database router:

DATABASE_ROUTERS = ['your_app.routers.SecondaryDatabaseRouter']

Replace `'your_app'` with the actual name of your app.

7. Migrate and Use Multiple Databases

After creating the router, run migrations again:

$ python manage.py migrate --database=secondary

You can now specify the database to use when querying or saving data. For instance:

# Querying data from the default database
default_data = MyModel.objects.all()

# Querying data from the secondary database
secondary_data = MyModel.objects.using('secondary').all()

Remember to adjust these instructions according to your project's specifics. Always ensure you have proper backups of your data before performing major changes like switching database backends or adding multiple databases.