Multiple databases

This topic guide describes Django’s support for interacting withmultiple databases. Most of the rest of Django’s documentation assumesyou are interacting with a single database. If you want to interactwith multiple databases, you’ll need to take some additional steps.

See also

See Multi-database support for information about testing with multipledatabases.

Defining your databases

The first step to using more than one database with Django is to tellDjango about the database servers you’ll be using. This is done usingthe DATABASES setting. This setting maps database aliases,which are a way to refer to a specific database throughout Django, toa dictionary of settings for that specific connection. The settings inthe inner dictionaries are described fully in the DATABASESdocumentation.

Databases can have any alias you choose. However, the aliasdefault has special significance. Django uses the database withthe alias of default when no other database has been selected.

The following is an example settings.py snippet defining twodatabases – a default PostgreSQL database and a MySQL database calledusers:

DATABASES = {
    'default': {
        'NAME': 'app_data',
        'ENGINE': 'django.db.backends.postgresql',
        'USER': 'postgres_user',
        'PASSWORD': 's3krit'
    },
    'users': {
        'NAME': 'user_data',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'priv4te'
    }
}

If the concept of a default database doesn’t make sense in the contextof your project, you need to be careful to always specify the databasethat you want to use. Django requires that a default database entrybe defined, but the parameters dictionary can be left blank if it will not beused. To do this, you must set up DATABASE_ROUTERS for all of yourapps’ models, including those in any contrib and third-party apps you’re using,so that no queries are routed to the default database. The following is anexample settings.py snippet defining two non-default databases, with thedefault entry intentionally left empty:

DATABASES = {
    'default': {},
    'users': {
        'NAME': 'user_data',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'superS3cret'
    },
    'customers': {
        'NAME': 'customer_data',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_cust',
        'PASSWORD': 'veryPriv@ate'
    }
}

If you attempt to access a database that you haven’t defined in yourDATABASES setting, Django will raise adjango.db.utils.ConnectionDoesNotExist exception.

Synchronizing your databases

The migrate management command operates on one database at atime. By default, it operates on the default database, but byproviding the —database option, you can tell itto synchronize a different database. So, to synchronize all models ontoall databases in the first example above, you would need to call:

$ ./manage.py migrate
$ ./manage.py migrate --database=users

If you don’t want every application to be synchronized onto aparticular database, you can define a databaserouter that implements a policyconstraining the availability of particular models.

If, as in the second example above, you’ve left the default database empty,you must provide a database name each time you run migrate. Omittingthe database name would raise an error. For the second example:

$ ./manage.py migrate --database=users
$ ./manage.py migrate --database=customers

Using other management commands

Most other django-admin commands that interact with the database operate inthe same way as migrate – they only ever operate on one database ata time, using —database to control the database used.

An exception to this rule is the makemigrations command. Itvalidates the migration history in the databases to catch problems with theexisting migration files (which could be caused by editing them) beforecreating new migrations. By default, it checks only the default database,but it consults the allow_migrate() method of routers if any are installed.

Automatic database routing

The easiest way to use multiple databases is to set up a databaserouting scheme. The default routing scheme ensures that objects remain‘sticky’ to their original database (i.e., an object retrieved fromthe foo database will be saved on the same database). The defaultrouting scheme ensures that if a database isn’t specified, all queriesfall back to the default database.

You don’t have to do anything to activate the default routing scheme– it is provided ‘out of the box’ on every Django project. However,if you want to implement more interesting database allocationbehaviors, you can define and install your own database routers.

Database routers

A database Router is a class that provides up to four methods:

• dbfor_read(_model, **hints)
• Suggest the database that should be used for read operations forobjects of type model.

If a database operation is able to provide any additionalinformation that might assist in selecting a database, it will beprovided in the hints dictionary. Details on valid hints areprovided below.

Returns None if there is no suggestion.

• dbfor_write(_model, **hints)
• Suggest the database that should be used for writes of objects oftype Model.

If a database operation is able to provide any additionalinformation that might assist in selecting a database, it will beprovided in the hints dictionary. Details on valid hints areprovided below.

Returns None if there is no suggestion.

• allowrelation(_obj1, obj2, **hints)
• Return True if a relation between obj1 and obj2 should beallowed, False if the relation should be prevented, or None ifthe router has no opinion. This is purely a validation operation,used by foreign key and many to many operations to determine if arelation should be allowed between two objects.

If no router has an opinion (i.e. all routers return None), onlyrelations within the same database are allowed.

• allowmigrate(_db, app_label, model_name=None, **hints)
• Determine if the migration operation is allowed to run on the database withalias db. Return True if the operation should run, False if itshouldn’t run, or None if the router has no opinion.

The app_label positional argument is the label of the applicationbeing migrated.

modelname is set by most migration operations to the value ofmodel.meta.model_name (the lowercased version of the model__name) of the model being migrated. Its value is None for theRunPython andRunSQL operations unless theyprovide it using hints.

hints are used by certain operations to communicate additionalinformation to the router.

When model_name is set, hints normally contains the model classunder the key 'model'. Note that it may be a historical model, and thus not have any custom attributes, methods, ormanagers. You should only rely on _meta.

This method can also be used to determine the availability of a model on agiven database.

makemigrations always creates migrations for model changes, butif allow_migrate() returns False, any migration operations for themodel_name will be silently skipped when running migrate onthe db. Changing the behavior of allow_migrate() for models thatalready have migrations may result in broken foreign keys, extra tables,or missing tables. When makemigrations verifies the migrationhistory, it skips databases where no app is allowed to migrate.

A router doesn’t have to provide all these methods – it may omit oneor more of them. If one of the methods is omitted, Django will skipthat router when performing the relevant check.

Hints

The hints received by the database router can be used to decide whichdatabase should receive a given request.

At present, the only hint that will be provided is instance, anobject instance that is related to the read or write operation that isunderway. This might be the instance that is being saved, or it mightbe an instance that is being added in a many-to-many relation. In somecases, no instance hint will be provided at all. The router checks forthe existence of an instance hint, and determine if that hint should beused to alter routing behavior.

Using routers

Database routers are installed using the DATABASE_ROUTERSsetting. This setting defines a list of class names, each specifying arouter that should be used by the master router(django.db.router).

The master router is used by Django’s database operations to allocatedatabase usage. Whenever a query needs to know which database to use,it calls the master router, providing a model and a hint (ifavailable). Django then tries each router in turn until a databasesuggestion can be found. If no suggestion can be found, it tries thecurrent _state.db of the hint instance. If a hint instance wasn’tprovided, or the instance doesn’t currently have database state, themaster router will allocate the default database.

An example

Example purposes only!

This example is intended as a demonstration of how the routerinfrastructure can be used to alter database usage. Itintentionally ignores some complex issues in order todemonstrate how routers are used.

This example won’t work if any of the models in myapp containrelationships to models outside of the other database.Cross-database relationshipsintroduce referential integrity problems that Django can’tcurrently handle.

The primary/replica (referred to as master/slave by some databases)configuration described is also flawed – itdoesn’t provide any solution for handling replication lag (i.e.,query inconsistencies introduced because of the time taken for awrite to propagate to the replicas). It also doesn’t consider theinteraction of transactions with the database utilization strategy.

So - what does this mean in practice? Let’s consider another sampleconfiguration. This one will have several databases: one for theauth application, and all other apps using a primary/replica setupwith two read replicas. Here are the settings specifying thesedatabases:

DATABASES = {
    'default': {},
    'auth_db': {
        'NAME': 'auth_db',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'swordfish',
    },
    'primary': {
        'NAME': 'primary',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'spam',
    },
    'replica1': {
        'NAME': 'replica1',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'eggs',
    },
    'replica2': {
        'NAME': 'replica2',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'bacon',
    },
}

Now we’ll need to handle routing. First we want a router that knows tosend queries for the auth app to auth_db:

class AuthRouter:
    """
    A router to control all database operations on models in the
    auth application.
    """
    def db_for_read(self, model, **hints):
        """
        Attempts to read auth models go to auth_db.
        """
        if model._meta.app_label == 'auth':
            return 'auth_db'
        return None
 
    def db_for_write(self, model, **hints):
        """
        Attempts to write auth models go to auth_db.
        """
        if model._meta.app_label == 'auth':
            return 'auth_db'
        return None
 
    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the auth app is involved.
        """
        if obj1._meta.app_label == 'auth' or \
           obj2._meta.app_label == 'auth':
           return True
        return None
 
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        Make sure the auth app only appears in the 'auth_db'
        database.
        """
        if app_label == 'auth':
            return db == 'auth_db'
        return None

And we also want a router that sends all other apps to theprimary/replica configuration, and randomly chooses a replica to readfrom:

import random
 
class PrimaryReplicaRouter:
    def db_for_read(self, model, **hints):
        """
        Reads go to a randomly-chosen replica.
        """
        return random.choice(['replica1', 'replica2'])
 
    def db_for_write(self, model, **hints):
        """
        Writes always go to primary.
        """
        return 'primary'
 
    def allow_relation(self, obj1, obj2, **hints):
        """
        Relations between objects are allowed if both objects are
        in the primary/replica pool.
        """
        db_list = ('primary', 'replica1', 'replica2')
        if obj1._state.db in db_list and obj2._state.db in db_list:
            return True
        return None
 
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        All non-auth models end up in this pool.
        """
        return True

Finally, in the settings file, we add the following (substitutingpath.to. with the actual Python path to the module(s) where therouters are defined):

DATABASE_ROUTERS = ['path.to.AuthRouter', 'path.to.PrimaryReplicaRouter']

The order in which routers are processed is significant. Routers willbe queried in the order they are listed in theDATABASE_ROUTERS setting. In this example, theAuthRouter is processed before the PrimaryReplicaRouter, and as aresult, decisions concerning the models in auth are processedbefore any other decision is made. If the DATABASE_ROUTERSsetting listed the two routers in the other order,PrimaryReplicaRouter.allow_migrate() would be processed first. Thecatch-all nature of the PrimaryReplicaRouter implementation would meanthat all models would be available on all databases.

With this setup installed, lets run some Django code:

>>> # This retrieval will be performed on the 'auth_db' database
>>> fred = User.objects.get(username='fred')
>>> fred.first_name = 'Frederick'
 
>>> # This save will also be directed to 'auth_db'
>>> fred.save()
 
>>> # These retrieval will be randomly allocated to a replica database
>>> dna = Person.objects.get(name='Douglas Adams')
 
>>> # A new object has no database allocation when created
>>> mh = Book(title='Mostly Harmless')
 
>>> # This assignment will consult the router, and set mh onto
>>> # the same database as the author object
>>> mh.author = dna
 
>>> # This save will force the 'mh' instance onto the primary database...
>>> mh.save()
 
>>> # ... but if we re-retrieve the object, it will come back on a replica
>>> mh = Book.objects.get(title='Mostly Harmless')

This example defined a router to handle interaction with models from theauth app, and other routers to handle interaction with all other apps. Ifyou left your default database empty and don’t want to define a catch-alldatabase router to handle all apps not otherwise specified, your routers musthandle the names of all apps in INSTALLED_APPS before you migrate.See Behavior of contrib apps for information about contrib appsthat must be together in one database.

Manually selecting a database

Django also provides an API that allows you to maintain complete controlover database usage in your code. A manually specified database allocationwill take priority over a database allocated by a router.

Manually selecting a database for a QuerySet

You can select the database for a QuerySet at any point in theQuerySet “chain.” Call using() on the QuerySet to get anotherQuerySet that uses the specified database.

using() takes a single argument: the alias of the database onwhich you want to run the query. For example:

>>> # This will run on the 'default' database.
>>> Author.objects.all()
 
>>> # So will this.
>>> Author.objects.using('default').all()
 
>>> # This will run on the 'other' database.
>>> Author.objects.using('other').all()

Selecting a database for save()

Use the using keyword to Model.save() to specify to whichdatabase the data should be saved.

For example, to save an object to the legacy_users database, you’duse this:

>>> my_object.save(using='legacy_users')

If you don’t specify using, the save() method will save intothe default database allocated by the routers.

Moving an object from one database to another

If you’ve saved an instance to one database, it might be tempting touse save(using=…) as a way to migrate the instance to a newdatabase. However, if you don’t take appropriate steps, this couldhave some unexpected consequences.

Consider the following example:

>>> p = Person(name='Fred')
>>> p.save(using='first')  # (statement 1)
>>> p.save(using='second') # (statement 2)

In statement 1, a new Person object is saved to the firstdatabase. At this time, p doesn’t have a primary key, so Djangoissues an SQL INSERT statement. This creates a primary key, andDjango assigns that primary key to p.

When the save occurs in statement 2, p already has a primary keyvalue, and Django will attempt to use that primary key on the newdatabase. If the primary key value isn’t in use in the seconddatabase, then you won’t have any problems – the object will becopied to the new database.

However, if the primary key of p is already in use on thesecond database, the existing object in the second databasewill be overridden when p is saved.

You can avoid this in two ways. First, you can clear the primary keyof the instance. If an object has no primary key, Django will treat itas a new object, avoiding any loss of data on the seconddatabase:

>>> p = Person(name='Fred')
>>> p.save(using='first')
>>> p.pk = None # Clear the primary key.
>>> p.save(using='second') # Write a completely new object.

The second option is to use the force_insert option to save()to ensure that Django does an SQL INSERT:

>>> p = Person(name='Fred')
>>> p.save(using='first')
>>> p.save(using='second', force_insert=True)

This will ensure that the person named Fred will have the sameprimary key on both databases. If that primary key is already in usewhen you try to save onto the second database, an error will beraised.

Selecting a database to delete from

By default, a call to delete an existing object will be executed onthe same database that was used to retrieve the object in the firstplace:

>>> u = User.objects.using('legacy_users').get(username='fred')
>>> u.delete() # will delete from the `legacy_users` database

To specify the database from which a model will be deleted, pass ausing keyword argument to the Model.delete() method. Thisargument works just like the using keyword argument to save().

For example, if you’re migrating a user from the legacy_usersdatabase to the new_users database, you might use these commands:

>>> user_obj.save(using='new_users')
>>> user_obj.delete(using='legacy_users')

Using managers with multiple databases

Use the db_manager() method on managers to give managers access toa non-default database.

For example, say you have a custom manager method that touches thedatabase – User.objects.create_user(). Because create_user()is a manager method, not a QuerySet method, you can’t doUser.objects.using('new_users').create_user(). (Thecreate_user() method is only available on User.objects, themanager, not on QuerySet objects derived from the manager.) Thesolution is to use db_manager(), like this:

User.objects.db_manager('new_users').create_user(...)

db_manager() returns a copy of the manager bound to the database you specify.

Using get_queryset() with multiple databases

If you’re overriding get_queryset() on your manager, be sure toeither call the method on the parent (using super()) or do theappropriate handling of the _db attribute on the manager (a stringcontaining the name of the database to use).

For example, if you want to return a custom QuerySet class fromthe get_queryset method, you could do this:

class MyManager(models.Manager):
    def get_queryset(self):
        qs = CustomQuerySet(self.model)
        if self._db is not None:
            qs = qs.using(self._db)
        return qs

Exposing multiple databases in Django’s admin interface

Django’s admin doesn’t have any explicit support for multipledatabases. If you want to provide an admin interface for a model on adatabase other than that specified by your router chain, you’llneed to write custom ModelAdmin classesthat will direct the admin to use a specific database for content.

ModelAdmin objects have five methods that require customization formultiple-database support:

class MultiDBModelAdmin(admin.ModelAdmin):
    # A handy constant for the name of the alternate database.
    using = 'other'
 
    def save_model(self, request, obj, form, change):
        # Tell Django to save objects to the 'other' database.
        obj.save(using=self.using)
 
    def delete_model(self, request, obj):
        # Tell Django to delete objects from the 'other' database
        obj.delete(using=self.using)
 
    def get_queryset(self, request):
        # Tell Django to look for objects on the 'other' database.
        return super().get_queryset(request).using(self.using)
 
    def formfield_for_foreignkey(self, db_field, request, **kwargs):
        # Tell Django to populate ForeignKey widgets using a query
        # on the 'other' database.
        return super().formfield_for_foreignkey(db_field, request, using=self.using, **kwargs)
 
    def formfield_for_manytomany(self, db_field, request, **kwargs):
        # Tell Django to populate ManyToMany widgets using a query
        # on the 'other' database.
        return super().formfield_for_manytomany(db_field, request, using=self.using, **kwargs)

The implementation provided here implements a multi-database strategywhere all objects of a given type are stored on a specific database(e.g., all User objects are in the other database). If yourusage of multiple databases is more complex, your ModelAdmin willneed to reflect that strategy.

InlineModelAdmin objects can be handled in asimilar fashion. They require three customized methods:

class MultiDBTabularInline(admin.TabularInline):
    using = 'other'
 
    def get_queryset(self, request):
        # Tell Django to look for inline objects on the 'other' database.
        return super().get_queryset(request).using(self.using)
 
    def formfield_for_foreignkey(self, db_field, request, **kwargs):
        # Tell Django to populate ForeignKey widgets using a query
        # on the 'other' database.
        return super().formfield_for_foreignkey(db_field, request, using=self.using, **kwargs)
 
    def formfield_for_manytomany(self, db_field, request, **kwargs):
        # Tell Django to populate ManyToMany widgets using a query
        # on the 'other' database.
        return super().formfield_for_manytomany(db_field, request, using=self.using, **kwargs)

Once you’ve written your model admin definitions, they can beregistered with any Admin instance:

from django.contrib import admin
 
# Specialize the multi-db admin objects for use with specific models.
class BookInline(MultiDBTabularInline):
    model = Book
 
class PublisherAdmin(MultiDBModelAdmin):
    inlines = [BookInline]
 
admin.site.register(Author, MultiDBModelAdmin)
admin.site.register(Publisher, PublisherAdmin)
 
othersite = admin.AdminSite('othersite')
othersite.register(Publisher, MultiDBModelAdmin)

This example sets up two admin sites. On the first site, theAuthor and Publisher objects are exposed; Publisherobjects have a tabular inline showing books published by thatpublisher. The second site exposes just publishers, without theinlines.

Using raw cursors with multiple databases

If you are using more than one database you can usedjango.db.connections to obtain the connection (and cursor) for aspecific database. django.db.connections is a dictionary-likeobject that allows you to retrieve a specific connection using itsalias:

from django.db import connections
with connections['my_db_alias'].cursor() as cursor:
    ...

Limitations of multiple databases

Cross-database relations

Django doesn’t currently provide any support for foreign key ormany-to-many relationships spanning multiple databases. If youhave used a router to partition models to different databases,any foreign key and many-to-many relationships defined by thosemodels must be internal to a single database.

This is because of referential integrity. In order to maintain arelationship between two objects, Django needs to know that theprimary key of the related object is valid. If the primary key isstored on a separate database, it’s not possible to easily evaluatethe validity of a primary key.

If you’re using Postgres, Oracle, or MySQL with InnoDB, this isenforced at the database integrity level – database level keyconstraints prevent the creation of relations that can’t be validated.

However, if you’re using SQLite or MySQL with MyISAM tables, there isno enforced referential integrity; as a result, you may be able to‘fake’ cross database foreign keys. However, this configuration is notofficially supported by Django.

Behavior of contrib apps

Several contrib apps include models, and some apps depend on others. Sincecross-database relationships are impossible, this creates some restrictions onhow you can split these models across databases:

• each one of contenttypes.ContentType, sessions.Session andsites.Site can be stored in any database, given a suitable router.
• auth models — User, Group and Permission — are linkedtogether and linked to ContentType, so they must be stored in the samedatabase as ContentType.
• admin depends on auth, so its models must be in the same databaseas auth.

• flatpages and redirects depend on sites, so their models must bein the same database as sites.In addition, some objects are automatically created just aftermigrate creates a table to hold them in a database:

• a default Site,

• a ContentType for each model (including those not stored in thatdatabase),
• the Permissions for each model (including those not stored in thatdatabase).For common setups with multiple databases, it isn’t useful to have theseobjects in more than one database. Common setups include primary/replica andconnecting to external databases. Therefore, it’s recommended to write adatabase router that allows synchronizingthese three models to only one database. Use the same approach for contriband third-party apps that don’t need their tables in multiple databases.

Warning

If you’re synchronizing content types to more than one database, be awarethat their primary keys may not match across databases. This may result indata corruption or data loss.