How to use multiple database with Django and SQLAlchemy

Description:

I have a Django application that currently stores data for multiple brands in a single database. Each brand's data, including user details and relevant information, is stored in separate tables within this database. However, I now have a requirement to migrate the application to use separate databases for each brand.

Current Setup:

One database containing tables for multiple brands. User details and relevant data for each brand stored in separate tables within this database.

Desired Setup:

Separate databases for each brand. User details and relevant data for each brand stored in their respective databases.

Tools Used:

Django for web framework. SQLAlchemy for database ORM. Alembic for database migrations.

Key Questions:

  1. How can I configure Django to use separate databases for each brand?
  2. What changes do I need to make to SQLAlchemy models to support this new database structure?
  3. How should I handle database migrations using Alembic to ensure seamless transition to the new setup?
  4. Are there any best practices or potential pitfalls I should be aware of during this migration process?

Currently, I'm following the simple technique to connect the single Database.

engine = create_engine(<Database URL>)
session = scoped_session(sessionmaker(bind=engine, autocommit=True))
Base.metadata.create_all(engine)

Any guidance, examples, or resources would be greatly appreciated. Thank you!

In Django, you can configure multiple databases by modifying the DATABASES setting in your settings.py file. You can specify a database for each brand by adding a new key-value pair to the DATABASES dictionary. The key will be the alias for the database, and the value will be a dictionary containing the database configuration options.

For example:

DATABASES = {
    'brand_one': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'brand_one_db',
        'USER': 'mydatabaseuser',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '5432',
    },
    'brand_two': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'brand_two_db',
        'USER': 'mydatabaseuser',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '5432',
    },
    # etc
}

Django by default uses the ‘default’ database, but you can specify a database when querying models by using the using() method.

For example:

Brand.objects.using('brand_one').all()

For SQLAlchemy, you will need to create multiple session makers, one for each brand’s database. You can store these session makers in a dictionary and access them as needed.

For example:

session_makers = {
    'brand_one': scoped_session(sessionmaker(bind=create_engine(<brand_one_db_url>), autocommit=True)),
    'brand_two': scoped_session(sessionmaker(bind=create_engine(<brand_two_db_url>), autocommit=True)),
    # etc
}

And then you can access the session for a specific brand like so:

session = session_makers['brand_one']

For Alembic, you will need to configure multiple environments, one for each brand’s database. This can be done by creating a separate alembic.ini file for each database, and specify the appropriate SQLAlchemy url for each one. You can then run the alembic command with the -n option to specify the environment.

For example:

alembic -n brand_one upgrade head

As for ensuring that all brand tables are updated when you alter a column or add a new one, you can create a base model with the common fields, and have all your brand models inherit from this base model. This way, any changes made to the base model will be reflected in all brand models.

As for best practices, make sure to thoroughly test the new setup with a dummy database before migrating the actual data. Also, keep a backup of the original database in case something goes wrong.

As for potential pitfalls, be aware that this setup can make database operations more complex, as you will need to specify the database everytime you perform a query. Also, you will need to manage multiple database connections, which can lead to increased resource usage.

Back to Top