Django and its standard configuration come with a single database by default. Often, when projects get large and complex, that is not enough, and we would want to separate different apps/logs through multiple databases in Django.
Can Django use multiple databases?
Yes, as one of the most popular web frameworks, Django has support for implementing multiple databases in projects. All you have to do is add configuration for every database you want to use in your DATABASES variable located in settings.py.
In this article, we’ll cover three crucial concepts when working with multiple databases in Django:
- Multiple database configuration
- Django command line with multiple databases
- Django Database routers
Django multiple database configuration
All database configurations happen in the settings.py file where you define your connections in the DATABASE variable. You can use multiple database management systems in one project.
The most popular and widely used databases in Django are:
- SQLite
- MySQL
- PostgreSQL
| Database | Pros | Cons |
| SQLite | It is lightweight and easiest to set up. No need to install it because it is built within Python. Fastest of all mentioned databases. | A lot of limitations. No real production use.No concurrency support (allows at most one write simultaneously). |
| MySQL | Is reliable and free. Cross-platform database. Large community. | Hard to scale. Performance degrades when the application grows. |
| PostgreSQL | Extremely large community. It is tested, reliable, and free. Good support, unlike MySQL. | Slower than MySQL. |
The following example uses all three of these databases. SQLite is used as the default database for user management (users are shared across applications). The default database has to be defined because when no database is explicitly selected, the default database is used. MySQL will be used for application1, and finally, PostgreSQL is used for application.
WARNING - SQLite should not be used in the production environment!
# settings.py
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': BASE_DIR / 'db.sqlite3',
},
'app1': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'DB_NAME',
'USER': 'DB_USER',
'PASSWORD': 'DB_PASSWORD',
'HOST': 'localhost', # Or an IP Address that your DB is hosted on
'PORT': '3306',
},
'app2': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'DB_NAME',
'USER': 'DB_NAME',
'PASSWORD': 'DB_PASSWORD',
'HOST': 'localhost',
'PORT': '5432',
}
}
As you can see above, we have a few options we need to understand to configure the database in Django:
ENGINE– Database backend engine. Django builtin engines are described in Django settings documentation (and GitHub), by default Django has 4 built-in engines:'django.db.backends.postgresql''django.db.backends.mysql''django.db.backends.sqlite3''django.db.backends.oracle'
NAME– Name of the database we use.USER– Username used for authentication on the specified database.PASSWORD– Password used for authentication on the specified database.HOST– Host where the database is placed.PORT– Port used when connecting to database.
WARNING - If you don't specify any of these given options, they all default ''(empty string), and host defaults 'localhost'.
Django migrate command with multiple databases
Django has command-line utilities for executing essential tasks such as making migrations, migrating, creating superuser, …
In this section, we’ll cover migrate command. Briefly, migrate command updates database to a current state defined by the makemigrations command.
$ django-admin migrate [app_label] [migration_name] $ python manage.py migrate [app_label] [migration_name] $ python -m django migrate [app_label] [migration_name]
Any of the above commands can be run for migrating to the database.
When using multiple databases, we would not want to have all tables across all databases, so firstly, let’s check out how to migrate to a specified database. In the following examples, we will use the manage.py command.
To migrate changes from the authapp to the default database, execute the following command:
python manage.py migrate authapp –database default
Similarly, if we want to migrate app1 to app1 database, we would execute the following command:
python manage.py migrate app1 -database app1
Now, we can check out Database Routers and how to use them.
Querying Multiple Databases in Django
In the project, we defined three apps, authapp, app1, and app2. Authapp consists of a SharedUser table (the idea of this app is to contain all users that are shared across applications). App1 is connected to the app1 database and stores Films and their directors. App2 is related to the app2 database and stores Books and Authors.
First, let’s see the database schema.
Using Django ORM, we’ll show you a few examples with multiple databases:
# SharedUser table is in default database and is successfully fetched
>>> SharedUser.objects.get(id=1)
<SharedUser: SharedUser object (1)>
# Querying Film from app1 causes OperationalError because it tries to query default database that doesn't contain given table
>>> Film.objects.get(id=1)
django.db.utils.OperationalError: no such table: app1_film
# Correct way of querying with using() method
>>> Film.objects.using('app1').get(id=1)
<Film: Film object (1)>
# Delete or update works without again having to specify what database we are using
>>> film = Film.objects.using('app1').get(id=1)
>>> film.delete()
(1, {'app1.Film': 1})
WARNING - Objects you receive can be deleted and updated without using() method.
Saving looks a bit different, but essentially it comes down to using parameter instead of using() method, as explained in an example:
>>> author = Author(first_name="William", last_name="Shakespeare") >>> author.save(using='app2')
Having that understood, we can now move to a more complex way of choosing data flow in databases, using database routers in Django.
Django Database Routers
Django doesn’t specify the DATABASE_ROUTERS variable but defaults to [ ] (empty array). This variable allows us to create and specify the path to created router.
What is a database router in Django?
Database router is a class that provides up to four methods that define over which database each query will be executed. Four methods are:
db_for_read(model, **hints)db_for_write(model, **hints)allow_relation(obj1, obj2, **hints)allow_migrate(db, app_label, model_name=None, **hints)
Further explanations of the given method are in the examples below.
class ApplicationRouter:
"""
A router that is used to route queries from an application to its database
"""
# Defines application labels
route_app_labels = {'app1', 'app2'}
def db_for_read(self, model, **hints):
"""
If the model is from app1, it suggests querying the app1 database. If the model is from app2, then app2
"""
if model._meta.app_label in self.route_app_labels:
return model._meta.app_label
return None
def db_for_write(self, model, **hints):
"""
If the model is from app1, it suggests querying the app1 database. If the model is from app2, then app2
"""
if model._meta.app_label in self.route_app_labels:
return model._meta.app_label
return None
def allow_relation(self, obj1, obj2, **hints):
"""
Allow relations if a model in the app1 or app2 apps is
involved.
"""
if (
obj1._meta.app_label in self.route_app_labels or
obj2._meta.app_label in self.route_app_labels
):
return True
return None
def allow_migrate(self, db, app_label, model_name=None, **hints):
"""
Make sure the app1 and app2 apps only appear in the
app1 or app2 database.
"""
if app_label in self.route_app_labels:
return db == app_label
return None
def allow_migrate(self, db, app_label, model_name=None, **hints):
"""
All app1 or app2 models end up in this pool.
"""
return True
Finally, you should define the DATABASE_ROUTERS variable in your settings.py file containing the path to the created router.
# settings.py DATABASE_ROUTERS = ['path.to.ApplicationRouter']
What did we achieve with this router?
Previously we used using() method to define which database to query. Now, we can query the database without worrying if our query will end up querying the wrong database.
# Previous example
>>> Film.objects.using('app1').get(id=1)
<Film: Film object (1)>
# After implementing ApplicationRouter into project
>>> Film.objects.get(id=1)
<Film: Film object (1)>
Finally, there are many discussions about where to place your database routers. But there is no good answer, some developers define it in the application root, and some define them in the project root directory. I like them defined in the routers directory in the project root. Find what you find easiest and most intuitive to use.
Django multiple databases with Django’s admin interface
Django admin is a powerful tool that automatically generates an interface for content management. There is no need for writing and “frontend” code to use, and it has a lot of customization possibilities.
Can we use multiple databases in Django admin?
Yes and no, Django admin doesn’t have multiple database support by default. Therefore, we need to write custom admin classes that will specify and connect models to a database we intend to use.
This section will show you how to register app1 models for the Django admin interface. First, we need to create our custom class and extend admin.ModelAdmin class and override five methods:
save_model(self, request, obj, form, change)– used for saving objects from the Django admin interface.delete_model(self, request, obj)– used for deleting objects from Django admin interface.get_queryset(self, request)– used for getting queryset shown in the Django admin interface.formfield_for_foreignkey(self, db_field, request, **kwargs)– used for populating ForeignKey widgets.formfield_for_manytomany(self, db_field, request, **kwargs)– used for populating ManyToMany widgets.
With that in mind, let’s create our custom Django ModelAdmin with support for multiple databases.
# app1/admin.py
from django.contrib import admin
from app1.models import Film, Director
class App1ModelAdmin(admin.ModelAdmin):
using = 'app1'
def save_model(self, request, obj, form, change):
obj.save(using=self.using)
def delete_model(self, request, obj):
obj.delete(using=self.using)
def get_queryset(self, request):
return super().get_queryset(request).using(self.using)
def formfield_for_foreignkey(self, db_field, request, **kwargs):
return super().formfield_for_foreignkey(db_field, request, using=self.using, **kwargs)
def formfield_for_manytomany(self, db_field, request, **kwargs):
return super().formfield_for_manytomany(db_field, request, using=self.using, **kwargs)
# Register your models here.
admin.site.register(Film, App1ModelAdmin)
admin.site.register(Director, App1ModelAdmin)
Finally, we need to create a superuser, and the Django admin interface is ready to use!